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
They are two things that can be good for you in this instance
- GOOD THING #1 : You can convert the two MyISAM tables to InnoDB
- GOOD THING #2 : The two MyISAM tables are never changing
If either one of these good things apply, then I have good news for you
STEP01) Zap All Binary Logs on the Master (OPTIONAL)
Run this command on the Master:
mysql> RESET MASTER;
If this part scares you, you could either skip it or make a copy of the binary logs before doing it
STEP02) Create the mysqldump as follows:
echo "STOP SLAVE;" > MySQLSlaveReload.sql
mysqldump -v --master-data=1 --single-transaction --routines .... >> MySQLSlaveReload.sql
echo "START SLAVE;" >> MySQLSlaveReload.sql
STEP03) Move the MySQLSlaveReload.sql
to the Slave
STEP04) Load MySQLSlaveReload.sql
on the Slave
On the Slave, load the script as follows:
mysql -u... -p... -A < MySQLSlaveReload.sql
The script will stop the slave, load the data, and start the slave. What about the log file and position? Before you do STEP02, look at line 22 of MySQLSlaveReload.sql
head -22 MySQLSlaveReload.sql | tail -1
The mysqldump option --master-data=1
recorded the log file and position as of the start of the mysqldump on line 22.
Give it a Try !!!
Best Answer
I am glad you found your own answer.
I am sure you found out you can add this to
my.cnf
You do not have to restart mysql. You login as
root@localhost
and run this:To trigger the auto rotate immediately, run
If you want to zap binary logs from the mysql console that is older that two weeks, use the PURGE BINARY LOGS BEFORE syntax. For example, here is a query to give you midnight two weeks ago from today's date:
Just apply that query to PURGE BINARY LOGS BEFORE