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
It will be handled immediately on mysql startup.
You do not have to wait for a mysql restart.
First, set expire_logs_days
to be 10 in /etc/my.cnf
[mysqld]
log-bin
server-id=1
binlog-do-db=foodb1
binlog-do-db=foodb2
expire_logs_days=10
Next, log in to mysql and run this
PURGE BINARY LOGS BEFORE (date(now()) + interval 0 second - interval 10 day);
Note the query's date and time
mysql> select date(now()) + interval 0 second - interval 10 day;
+---------------------------------------------------+
| date(now()) + interval 0 second - interval 10 day |
+---------------------------------------------------+
| 2012-12-11 00:00:00 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Thus, the PURGE BINARY LOGS command will delete all binlogs whose datetime stamp predates 2012-12-11 00:00:00
.
Finally, run this command
SET GLOBAL expire_logs_days = 10;
That's it. No restart needed for installing expire_logs_days.
As for the replication parameters, yes restart is required.
Best Answer
When the folder with binary logs is full, mysqld will freeze everything that does a write to a MyISAM table (even temporary MyISAM tables). See my post "Site Offline" MySQL server failing to start and stop as to why. Anyway, mysqld will wait for free diskspace before continuing whatever operation it was doing. In this particular instance, you must go to the OS and delete the binary logs, but not all of them.
For example, if you defined
go to the OS and run this
You will see all the binary logs. Delete just the first 2 or 3 binary logs.
Log back into mysql and run
SHOW BINARY LOGS;
It will eventually kick in when the free space is acknowledged by mysqld.
Then, you can run
PURGE BINARY TO 'mysql-bin.xxxxxx';
You may also have to look into the error log or slow log. If they are big, truncate them like this:
GIVE IT A TRY !!!