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
The warning about --log-slave-updates
will only apply if you had multiple intermediary 'Master/Slave' servers.
The warning is this (my emphasis):
Then it will write updates that it receives from Master to its own binary log. When Slave 2 changes from Master to Slave 1 as its master, it may receive updates from Slave 1 that it has already received from Master
But in your scenario, Slave 2
is not changing masters, it will still point to the same Master 2
server it always was at.
So now, in case of Master 1
failing, you will need to do two things:
- Make sure your applications point to
Master 2
- Follow the instructions on promoting one of the three slaves to be new
Master 2
- make sure to enable
--log-slave-updates
on the new Master 2
Best Answer
STOP SLAVE; RESET MASTER;
is not the right way to do a failover. You can stop a slave if you want, but RESET MASTER will delete your own binary logs. You may be looking forRESET SLAVE; CHANGE MASTER TO MASTER_HOST='';
(RESET SLAVE ALL;
since 5.5). The actual failover is not performed by mysql, but by an external entity, be it a proxy or a connector.As you have resetted the master data and the slave binary logs, you need to perform a clone of the slave (current master) with its binary logs coordinates (with mysqldump or Percona XtraBackup) and set the replication in the other direction.
If you intend to do manual failovers, a master-master replication with one of the nodes in read-only mode is suggested. You also have software like MHA that can simplify the process.