Archive | MySQL

Here’s my procedure for changing innodb_log_file_size:

1. SET GLOBAL innodb_fast_shutdown = false;
2. SET GLOBAL innodb_max_dirty_pages_pct = 0;
3. /sbin/service mysqld restart
4. /sbin/service mysqld stop
5. rm -f /var/lib/mysql/ib_logfile[01]
6. Edit /etc/my.cnf and set new value for innodb_log_file_size
7. /sbin/service mysqld start

Here’s a handy sql command to determine a good log file size:

then do this:

to get the MB per minute. Set the log file size to be large enough to hold one hour of logs.

See http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/ for the full discussion.

By default most MySQL RPM packages do not set the MySQL server to start automatically on boot so this needs to be done manually. On RedHat/CentOS the chkconfig command should be used as follows:

To simplify administration of MySQL I like to create a /root/.my.cnf file with the mysql root credentials. This way I can run the mysql command line utility without having to authenticate. Just edit /root/.my.cnf and add the following:

After install MySQL, it’s important that you secure the installation by (a) removing anonymous accounts and (b) setting a password on the admin account. This can be done with mysqladmin or with direct SQL queries. I prefer the SQL queries so that’s what I’ll show here.

1. Start mysql command line utility and select the mysql database.
2. Remove anonymous accounts.
3. Set a password on the mysql root account.
4. Flush privileges.

1. Create the tmp directory:

2. Set permissions:

3. Determine mysql user id:

4. Edit /etc/fstab and add the following line:

5. Mount the new tmpfs partition:

6. Edit /etc/my.cnf and set location of tmp directory:

7. Restart mysqd