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 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 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 Loaded plugins: fastestmirror, security Loading mirror speeds from cached hostfile * base: mirror.logol.ru * extras: mirror.logol.ru * updates: mirror.logol.ru Setting up Install Process Package policycoreutils-python-2.0.83-19.39.el6.x86_64 already installed and latest version [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.
##2##
[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_info\G
*************************** 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