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
