Change binary log and relay log location to /home? SELinux issue

Recently in my slave server there was a full disk error because of the huge size of binary logs.Which is lead to critical error and BUG report which is still stays as open: http://bugs.mysql.com/bug.php?id=72437
So the issue was a partition problem which in partitioning stage haven’t been specified for “/” (root) filesystem. But in “/home” there were left enough space. The quick decision was to change binary and relay log location(path) to “/home”. But there was a great challenge to get real working slave again. Now we will explore all steps in our test virtual machine.
Test conditions for me: 1. CentOS 6.5 (x86_64) (both master and slave) 2. MySQL 5.6.19 GA (from official yum repo) (both master and slave)
Master’s my.cnf:

# BINARY LOGGING # 
server_id = 1 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1 
log_slave_updates = 1

Slave’s original my.cnf:

# BINARY LOGGING # 
server_id = 2 
log_bin = /var/lib/mysql/data/mysql-bin 
log_bin_index = /var/lib/mysql/data/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /var/lib/mysql/data/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

So we assume that we have already working master->slave topology. Let’s examine Slave Host for further explanation:

[root@linuxsrv2 home]# df -h 
Filesystem Size Used Avail Use% Mounted on 
/dev/mapper/vg_linuxsrv1-lv_root 48G 2,3G 43G 6% / 
tmpfs 246M 0 246M 0% /dev/shm 
/dev/sda1 485M 57M 403M 13% /boot 
/dev/mapper/new_lv_linuxsrv1-lv_home 7,4G 145M 6,9G 3% /home

So we have seperate free space on /home directory and now we want to change slave’s binary log and reloy log files into this directory. Step-by-step:

1. Create directory:

[root@linuxsrv2 ~]# mkdir /home/binlogs 
[root@linuxsrv2 ~]# ls -ld /home/binlogs/ 
drwxr-xr-x. 2 root root 4096 2014-06-13 15:39 /home/binlogs/

2. Change Slave’s my.cnf to reflect new location. So the new my.cnf of slave must be something like this:

# BINARY LOGGING # 
server_id = 2 
log_bin = /home/binlogs/mysql-bin 
log_bin_index = /home/binlogs/mysql-bin 
expire_logs_days = 14 
sync_binlog = 1 
binlog_format = row 
relay_log = /home/binlogs/mysql-relay-bin 
log_slave_updates = 1 
read_only = 1 
gtid-mode = on 
enforce-gtid-consistency = true 
master-info-repository = TABLE 
relay-log-info-repository = TABLE 
#slave-parallel-workers = 2 
binlog-checksum = CRC32 
master-verify-checksum = 1 
slave-sql-verify-checksum = 1 
binlog-rows-query-log_events = 1

3. Stop MySQL slave server:

[root@linuxsrv2 ~]# service mysqld stop 
Stopping mysqld: [ OK ]

4. Move all binary log and relay log files to new directory “/home/binlogs”:

[root@linuxsrv2 ~]# cd /var/lib/mysql/data/
[root@linuxsrv2 data]# ls 
mysql-bin.000001 mysql-bin.index mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-relay-bin.000003 mysql-relay-bin.000004 mysql-relay-bin.index mysql-slow.log 
[root@linuxsrv2 data]# mv mysql-relay-bin.* /home/binlogs/ 
[root@linuxsrv2 data]# ls mysql-slow.log 
[root@linuxsrv2 data]# ls -l /home/binlogs/ 
total 20 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 37 2014-06-13 15:28 mysql-bin.index 
-rw-rw----. 1 mysql mysql 741 2014-06-13 15:31 mysql-relay-bin.000003 
-rw-rw----. 1 mysql mysql 471 2014-06-13 15:46 mysql-relay-bin.000004 
-rw-rw----. 1 mysql mysql 86 2014-06-13 15:31 mysql-relay-bin.index

5. Change owner of /home/binlogs to mysql:mysql:

[root@linuxsrv2 data]# cd /home 
[root@linuxsrv2 home]# ls binlogs lost+found my_changed.cnf my_original.cnf
[root@linuxsrv2 home]# chown -R mysql:mysql binlogs

6. Go to new directory edit *.index file of both binary logs and relay logs. Why? Open these files and you will see that, the old path is still there:

[root@linuxsrv2 binlogs]# cat mysql-bin.index
/var/lib/mysql/data/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/var/lib/mysql/data/mysql-relay-bin.000003
/var/lib/mysql/data/mysql-relay-bin.000004 

So after editing it must be something like:

[root@linuxsrv2 binlogs]# cat mysql-bin.index 
/home/binlogs/mysql-bin.000001 

[root@linuxsrv2 binlogs]# cat mysql-relay-bin.index
/home/binlogs/mysql-relay-bin.000003 
/home/binlogs/mysql-relay-bin.000004

7. Now try to start MySQL:

[root@linuxsrv2 binlogs]# service mysqld start 
MySQL Daemon failed to start. 
Starting mysqld: [FAILED]

See error log:

140613 15:46:19 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 140613 16:01:50 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 
2014-06-13 16:02:15 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 
2014-06-13 16:02:16 12358 [Warning] Buffered warning: Performance schema disabled (reason: init failed). ^G/usr/sbin/mysqld: File '/home/binlogs/mysql-bin.index' not found (Errcode: 13 - Permission denied) 
2014-06-13 16:02:16 12358 [ERROR] Aborting 
2014-06-13 16:02:16 12358 [Note] Binlog end 
2014-06-13 16:02:16 12358 [Note] /usr/sbin/mysqld: Shutdown complete

But as you see we changed owner of this directory to mysql user, still error occured. SELinux come ups with it’s blocking functionalty here to prevent mysql to use other directories rather than, /var/lib/mysql. Simply you can disable SELinux at all, but it is not a best practice. First possible way is to run following SElinux related commands:

$ semanage fcontext -a -t mysqld_db_t “/home(/.*)?” 
$ restorecon -Rv /home

But if you are not comfortable with these commands, you can just do it automatically using audit2allow:

8. Install “policycoreutils-python” on Linux:

[root@linuxsrv2 ~]# yum install policycoreutils-python
[root@linuxsrv2 ~]# rpm -qa | grep policy*
selinux-policy-3.7.19-231.el6_5.3.noarch
policycoreutils-python-2.0.83-19.39.el6.x86_64 checkpolicy-2.0.22-1.el6.x86_64
policycoreutils-2.0.83-19.39.el6.x86_64
selinux-policy-targeted-3.7.19-231.el6_5.3.noarch 

9. Configuring SELinux:
##1##

[root@linuxsrv2 home]# audit2allow -w -a 
type=AVC msg=audit(1402659916.941:291): avc: denied { search } for pid=14356 comm="mysqld" name="/" dev=dm-2 ino=2 scontext=unconfined_u:system_r:mysqld_t:s0 tcontext=system_u:object_r:file_t:s0 tclass=dir Was caused by: Missing type enforcement (TE) allow rule. You can use audit2allow to generate a loadable module to allow this access. 


[root@linuxsrv2 home]# audit2allow -a 
#============= mysqld_t ============== #
!!!! The source type 'mysqld_t' can write to a 'dir' of the following types: # var_log_t, mysqld_var_run_t, mysqld_db_t, tmp_t, mysqld_tmp_t, var_lib_t, var_run_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:dir { write search read remove_name open add_name }; 
#!!!! The source type 'mysqld_t' can write to a 'file' of the following types: # mysqld_var_run_t, mysqld_db_t, mysqld_tmp_t, mysqld_log_t, cluster_var_lib_t, cluster_var_run_t, root_t, cluster_conf_t allow mysqld_t file_t:file { rename read create write getattr unlink open append };

##3##

[root@linuxsrv2 home]# audit2allow -a -M mymysqlallow 
******************** IMPORTANT *********************** 
To make this policy package active, execute: semodule -i mymysqlallow.pp 

##4##

[root@linuxsrv2 home]# ls 
binlogs lost+found my_changed.cnf mymysqlallow.pp mymysqlallow.te
my_original.cnf 
[root@linuxsrv2 home]# semodule -i mymysqlallow.pp 

10. Try again to start MySQL:

[root@linuxsrv2 ~]# service mysqld start Starting mysqld: [ OK ]

But actually Slave does not work now:
From slave status: ** Slave_IO_Running: No Slave_SQL_Running: No**
And from error log:

2014-06-13 17:03:36 15360 [ERROR] Failed to open the relay log '/var/lib/mysql/data/mysql-relay-bin.000004' (relay_log_pos 448). 
2014-06-13 17:03:36 15360 [ERROR] Could not find target log file mentioned in relay log info in the index file '/home/binlogs/mysql-relay-bin.index' during relay log initialization. 
2014-06-13 17:03:36 15360 [ERROR] Failed to initialize the master info structure 
2014-06-13 17:03:36 15360 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted. 

Here is another issue, in fact we defined path correctly in mysql-relay-bin.index file but it shows the old path. So Why? As examining mysql database you will see some tables like:

mysql> show tables like 'slave%'; 
+--------------------------+ 
| Tables_in_mysql (slave%) |
+--------------------------+ 
| slave_master_info | 
| slave_relay_log_info | 
| slave_worker_info | 
+--------------------------+ 
3 rows in set (0,00 sec)

Select from slave_relay_log_info table:

mysql> select * from slave_relay_log_infoG 
*************************** 1. row *************************** 
Number_of_lines: 7 
Relay_log_name: /var/lib/mysql/data/mysql-relay-bin.000004 
Relay_log_pos: 448 
Master_log_name: mysql-bin.000002 
Master_log_pos: 478 
Sql_delay: 0 
Number_of_workers: 0 
Id: 1 
1 row in set (0,00 sec) 

Yes as you see , although, we have defined new path it is not updated in this table. So quite straight option is manually updating this table to new path:

mysql> update slave_relay_log_info set relay_log_name='/home/binlogs/mysql-relay-bin.000004' where id=1; 
Query OK, 1 row affected (0,05 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

11. Restart MySQL again:

[root@linuxsrv2 ~]# service mysqld restart 
Stopping mysqld: [ OK ] 
Starting mysqld: [ OK ]

Check Slave if it is working properly now? Slave_IO_Running: Yes Slave_SQL_Running: Yes Yes now the binary logs and relay logs of slave server is located in /home directory:

[root@linuxsrv2 ~]# ls -l /home/binlogs/ 
total 28 
-rw-rw----. 1 mysql mysql 1320 2014-06-13 15:46 mysql-bin.000001 
-rw-rw----. 1 mysql mysql 214 2014-06-13 17:21 mysql-bin.000002 
-rw-rw----. 1 mysql mysql 333 2014-06-13 17:26 mysql-bin.000003 
-rw-rw----. 1 mysql mysql 93 2014-06-13 17:22 mysql-bin.index 
-rw-rw----. 1 mysql mysql 173 2014-06-13 17:22 mysql-relay-bin.000006 
-rw-rw----. 1 mysql mysql 590 2014-06-13 17:26 mysql-relay-bin.000007 
-rw-rw----. 1 mysql mysql 74 2014-06-13 17:22 mysql-relay-bin.index

Thanks for reading 😉