MySQL - Configurations

mysql

Metrics that we need to graph
Upgrading

Example configuration file:

[mysqld]
 log
 log-bin
 log-slow-queries
 log-queries-not-using-indexes
 long_query_time = 1
 skip-name-resolve
 max_connections = 200
 key_buffer_size = 2G
 key_buffer = 384M
 max_allowed_packet = 1M
 table_cache = 512
 sort_buffer_size = 2M
 read_buffer_size = 2M
 read_rnd_buffer_size = 8M
 myisam_sort_buffer_size = 64M
 thread_cache_size = 8
 query_cache_size = 32M
 # Try number of CPU's*2 for thread_concurrency
 thread_concurrency = 8

This configuration allows us to log everything, log to the binary log, log slow queries, log queries not using index. MySQL distribution has /usr/share/mysql/my-huge.cnf and /usr/share/mysql/my-innodb-heavy-4G.cnf. Read through those files.

What is the default location for log files (the general log file, the binary log file, the slow query log file, the error log)?

By default, these log files (if enabled) is in data directory. We can determine the location of datadir by SHOW VARIABLES LIKE 'datadir' or examine the configuration file.

How can we set the initial password?

mysqladmin -u root password 'new password'
mysqladmin -u root -h host.domain.com password 'new password'

How can we disable unneeded storage engine?

If we need to save memory, one way to do so is to disable unneeded storage engines. Some of the compiled-in storage engines can be enabled or disabled. Disabling an unneeded storage engine reduces the server's memory requirements because it need not allocate buffers and other data structures associated with the engine. We can disable InnoDB with —skip-innodb option at server startup. MyISAM storage engine is always compiled-in and cannot be disabled. The set of storage engines available depends both on how the server was compiled and on the options used at startup:

  • MyISAM, MERGE, and MEMORY storage engines are always available.
  • InnoDB is included in all binary distributions

How can we show which storage engines are available?

SHOW ENGINES;

How can we secure our installation?

If all clients are local clients, you can disable connections from remote clients by starting the server with —skip-networking option to disable TCP/IP connections. The server must be able to accept local connection using some other networking protocol. This is not an issue on Unix, because servers always accept connection through a Unix socket file. On Windows, local clients can use shared memory and named-pipe connections but neither of those protocols is enabled by default. Start the server with the —share-memory and —enable-named-pipe options to turns on these connection protocols.

Servers for MySQL 4.1 and up use an authentication mechanism that is more secure and provides better password protection. However, client programs from older versions do not understand this mechanism and an error occurs when they attempt to connect to a newer server. The best thing to do, if possible, is to upgrade all older (pre-4.1) clients. If that cannot be done, and your server must support older clients, you must configure the server for backward compatibility using the —old-password option. However, you should start the server with this option before setting or changing any passwords. Otherwise, you will have passwords in a mix of old and new formats.

It is possible to have the server support both old and new authentication mechanism, but this is more complex. See MySQL Reference Manual.

If you want to take the opposite approach and allow connections only by clients that have new-format passwords, start the server with —secure-auth option. This causes the server to reject connection attempts for any client that has password in the old format.

How can we install MySQL from source?

useradd mysql

tar -xvzf mysql-VERSION.tar.gz
cd mysql-VERSION
./configure --prefix=/usr/local/mysql
make
make install
scripts/mysql_install_db
chown -R mysql.mysql /usr/local/mysql
/usr/local/mysql/bin/safe_mysqld -user=mysql &

/usr/local/mysql/bin/mysqladmin -u root -p oldpasswd 'new password'
/usr/local/mysql/bin/mysqladmin -u root -h www.hostname.com -p password 'new password'

How can we apply a patch?

  1. cd to the top level directory of the MySQL source tree
  2. gunzip < patch_filename.gz | patch -pl
  3. rm config.cache
  4. make clean, and install MySQL as above. You may need to bring down any currently running server before you can "make install"

How can we compile just the MySQL client libraries and programs?

./configure --without-server

How can we compile statically linked libraries?

./configure --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static

Other configuration options:

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data
    --with-unix-socket_path=/usr/local/mysql/tmp/mysql.sock

How can we run MySQL as a service on Windows?

// For Windows NT, the server name is mysqld-nt
C:\mysql\bin\mysqld-nt --install // install MySQL as a service on Windows NT

How can we start the MySQL server on Windows?

C:\mysql\bin\mysqld
NET START mysql

The service is installed with the name MySQL. Once installed, it must be started using Service Control Manager (SCM) utility (found in Control Panel) or by using:

NET START MySQL

If any option is desired, they must be specified as "startup parameters" in the SCM utility (perhaps this is services.msc) before you start the MySQL service. Once running, MySQL can be stopped using mysqladmin or from the SCM utility or by using:

NET STOP MySQL

How can we stop the MySQL server on Windows?

C:\mysql\bin\mysqladmin -u root -p shutdown
NET STOP mysql
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License