Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

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
share|improve this answer
    
Thanq dabest1. I could find all the physical raw files exists. But only mysql database disappears. mysql> use mysql; ERROR 1049 (42000): Unknown database 'mysql'. But after using mysql> system hostname; select * from mysql.user; it is showing the list of users and with passwords.And mysql> select * from mysql.tables_priv; Empty set (0.00 sec) And i could also find disappearing error log. Is it a bug. I googled, and i could found the bugs related which is very old. By the way our version :5.1.51-rel11.5-log | –  Gopinath Nov 3 '11 at 1:29
    
Maybe MySQL is in a bad state after running out of disk space. Have you tried restarting it yet? –  dabest1 Nov 3 '11 at 18:34
add comment

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.

share|improve this answer
    
No Rolando, I had not used rm -f for mysql. I used it for only deleting bin-log files to clear some disk space. Even though disapperance of mysql database, i could query select * from mysql.users show users,select * from mysql.hosts and could see the result.And other help schema's also i can query which exist in mysql database. Where some of the schemas missing. If i dropped or deleted mysql database / folder, is there any possibility that these results are coming from Query cache in mysql database. In the :/var/lib/mysql is could find only mysql.sock file. –  Gopinath Nov 3 '11 at 4:55
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.