MySQL

Moving MySQL Binary Logs to a New LVM Logical Volume

I had a RHEL6 machine with lots of large MySQL binary log files in /var/log/mysql and was in need of more space on that volume. I am running MySQL 5.5 from IUS.

The system had an unused LVM logical volume. So I proceeded to mount the LVM logical volume at /var/log/mysql so that binary logs would get written to the logical volume.

Step one: unmount the logical volume

Not strictly necessary for a rename but a nice precaution.

# umount /dev/mapper/vg_db-lv_foo

Step two: rename the logical volume

The volume was named foo (not really true but it will server for this example) and was in the volume group named vg_db. I renamed it to something obvious, namely lv_mysqllogs.

# lvrename vg_db lv_foo lv_mysqllogs
  Renamed "lv_foo" to "lv_mysqllogs" in volume group "vg_db"

Step three: create a temporary directory as a mount point

I'll put the new directory right next to the existing /var/log/mysql.

# mkdir /var/log/mysql.new
chown mysql:mysql /var/log/mysql.new

Step four: purge binary logs

In order to have only a small amount of data to move, I checked with our MySQL slave server to make sure it was up-to-date, then logged into MySQL as root and purged the logs:

mysql> PURGE BINARY LOGS BEFORE '2013-06-04 00:00:01';
Query OK, 0 rows affected (3.81 sec)

Step five: mount LVM logical volume

# mount /dev/mapper/vg_db-lv_mysqllogs /var/log/mysql.new
chown mysql:mysql /var/log/mysql.new

Step six: copy the binary log files

For this, we have to stop MySQL.

# service mysqld stop
Stopping mysqld:                                           [  OK  ]

And then copy logs:

# cp -Rp /var/log/mysql/* /var/log/mysql.new/

Step seven: unmount LVM and remount at /var/log/mysql

# umount /dev/mapper/vg_db-lv_mysqllogs

We want to keep the original directory around in case things go horribly wrong.

# mv /var/log/mysql /var/log/mysql.old

And then move our temporary directory to its final destination for use as a mount point:

# mv /var/log/mysql.new /var/log/mysql

And remount the LVM logical volume to /var/log/mysql:

# mount /dev/mapper/vg_db-lv_mysqllogs /var/log/mysql

This is a good time to update /etc/fstab with your new mount entry so it will survive reboots:

/dev/mapper/vg_db-lv_mysqllogs /var/log/mysql                   ext4    defaults        1 2

Step eight: start up MySQL

Here we go:

# service mysqld start
MySQL Daemon failed to start.
Starting mysqld:                                           [FAILED]

Step nine: PANIC

(Note to my readers. Ach, who am I kidding? Note to my reader: if you follow the steps above you should not end up here. I had forgotten to change ownership of the newly-mounted volume to mysql, so when MySQL tried to start it tried really hard but couldn't -- see Bonus tidbit, below. I'm adding these details for frantic Googlers whose MySQL is down with errno 13).

No, don't panic. Just look in /var/log/mysqld.log to see why it's not starting:

mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...
InnoDB: Completed initialization of buffer pool
InnoDB: highest supported file format is Barracuda.
InnoDB: Waiting for the background threads to start
InnoDB: 5.5.31 started; log sequence number 27306868416/usr/libexec/mysqld: File '/var/log/mysql/bin-log.000189' not found (Errcode: 13)
[ERROR] Failed to open log (file '/var/log/mysql/bin-log.000189', errno 13)
[ERROR] Could not open log file
[ERROR] Can't init tc log
[ERROR] Aborting
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 27306868416
[Note] /usr/libexec/mysqld: Shutdown complete
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Step ten: breathe. Just breathe.

The errno 13 shown above is a permissions error. Let's review the permissions:

# ls -la /var/log/mysql
drwxr-xr-x.  2 mysql mysql       4096 Jun  4 08:47 .
drwxr-xr-x. 16 root  root        4096 Jun  4 08:49 ..
-rw-rw----.  1 mysql mysql 1073978999 Jun  4 03:51 bin-log.000187
-rw-rw----.  1 mysql mysql  737806907 Jun  4 08:48 bin-log.000188
-rw-rw----.  1 mysql mysql         60 Jun  4 08:47 bin-log.index

That all looks good. So it must be SELinux. Let's check:

# ls -laZ /var/log/mysql
drwxr-xr-x. mysql   mysql   system_u:object_r:var_log_t:s0   .
drwxr-xr-x. root    root    system_u:object_r:var_log_t:s0   ..
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.000187
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.000188
-rw-rw----. mysql   mysql   unconfined_u:object_r:var_log_t:s0 bin-log.000189
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.index

Aha! See how bin-log.000189 is marked var_log_t instead of mysqld_log_t? Let's restore its correct SELinux settings and look again:

# restorecon /var/log/mysql/bin-log.000189

# ls -laZ /var/log/mysql
drwxr-xr-x. mysql   mysql   system_u:object_r:var_log_t:s0   .
drwxr-xr-x. root    root    system_u:object_r:var_log_t:s0   ..
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.000187
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.000188
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.000189
-rw-rw----. mysql   mysql   unconfined_u:object_r:mysqld_log_t:s0 bin-log.index

Step eleven: start MySQL, for real

# service mysqld start
Starting mysqld:                                           [  OK  ]

Yay!

Bonus tidbit

If you start up MySQL and it doesn't start, and /var/log/mysqld.log says

mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql/usr/libexec/mysqld: File '/var/log/mysql/bin-log.~rec~' not found (Errcode: 13)
[ERROR] MYSQL_BIN_LOG::open_purge_index_file failed to open register  file.
[ERROR] MYSQL_BIN_LOG::open_index_file failed to sync the index file.
[ERROR] Aborting
[Note] /usr/libexec/mysqld: Shutdown complete
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

the problem is with the ownership of /var/log/mysql. Compare what you've got

# ls -la /var/log/mysql
drwxr-xr-x.  4 root    root          4096 Jun  4 08:48 .
drwxr-xr-x. 16 root    root          4096 Jun  4 08:49 ..

with what you want to have:

# ls -la /var/log/mysql
drwxr-xr-x.  2 mysql mysql       4096 Jun  4 08:47 .
drwxr-xr-x. 16 root  root        4096 Jun  4 08:49 ..
...

See how . is owned by root:root instead of mysql:mysql? That's why you want to do

chown mysql:mysql /var/log/mysql
Subscribe to RSS - MySQL