Will improper bin-log purging leads to deletion of mysql database

Question

We had master to master and master to multiple slave environment in one of our client location. In one of the slave Db host we had disk space issue with bin-logs. At first i deleted the bin-log files manually while the mysql server is running using rm -f binlog.000015, binlog.000016.......binlog.000026. But later i came to know this procedure is wrong by issuing the commmand :

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| 0             |           |
| 0             |           |
| 0             |           |
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

What ever the bin logs deleted using rm -rf was not getting cleared in slave. Later i issued the command : PURGE BINARY LOGS TO 'mysql-bin.000026'; and could see the bin logs got cleared properly.

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000027 |    724935 |
| binlog.000028 |    733481 |
+---------------+-----------+

But this didnt affected the slave replication and replication is fine. But after some time i could noticed that missing of mysql database. What could be root cause. Whether mysql dropped due to mis handling of the bin-logs.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| client_prod        |
+--------------------+
2 rows in set (0.01 sec)

====

As the other slave is fine.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| client_prod        |
+--------------------+
3 rows in set (0.00 sec)

We do not see anything indicative of the folder being removed or the database dropped in logs or history. What would be the mystery behind. How could be recover apart from taking dump and restoring, can we take dump of only mysql and restore it in this slave. If doing so will it leads to any replication problem.

Answer 1

If your slave does not also act as a master to another slave, then you should not have any issues from deleting binary logs. The relay logs are important to the slave.

You need to further investigate on why mysql schema disappeared. Is mysql schema still present on the disk, even though MySQL is not showing it? If you have not stopped the slave instance yet, then you could run under same user as MySQL is running:

lsof | grep '/path_to_mysql'

You might see mysql schema tables marked as deleted in there:

(deleted)

Another possibility is that you are connecting with a user that has limited privileges and just does not see mysql schema. Run SHOW GRANTS; to see what privileges you currently have.

As Rolando pointed out, use PURGE BINARY LOGS as best practice for cleaning up binary logs. If MySQL is down, you could delete the files manually, but then you have to also delete the same file names from the index file. Be careful on master servers, as binary logs might still be needed by slaves.

And if you do need to copy mysql schema, you can do it with just these steps on the slave. Although, I would recommend executing FLUSH TABLES; on master before doing these steps.

/etc/init.d/mysql stop
scp -rp master_server:/var/lib/mysql/mysql /var/lib/mysql/
/etc/init.d/mysql start

Answer 2

Since none of the tables in the mysql schema are InnoDB, you could simply do the following on the slave instead of doing a mysqldump

• Step 01) service mysql stop
• Step 02) mkdir /var/lib/mysql/mysql
• Step 03) scp master-ip:/var/lib/mysql/mysql/* /var/lib/mysql/mysql/.
• Step 04) chown -R mysql:mysql /var/lib/mysql/mysql
• Step 05) service mysql start

This should not affect replication because when you ran the CHANGE MASTER TO command, the replication user's authentication happens on the master, not the slave. In fact, the credentials for the replication user is stored in master.info on the slave.

BTW You should never use rm -f to remove binary logs while mysqld is running because mysql has an internal memory map loaded from an index file. Only use PURGE BINARY LOGS, RESET MASTER, and FLUSH LOGS to maniuplation the index file of binary logs.

It just dawned on me. You have this in your question:

What ever the bin logs deleted using rm -rf was not getting cleared in slave. Later i issued the command : PURGE BINARY LOGS TO 'mysql-bin.000026'; and could see the bin logs got cleared properly.

It may be possible that you ran rm -rf mysql. That will snuff out the mysql folder. Check your Linux history.