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
Are you using InnoDB? Xtrabackup will allow you to do "hot" or "online" copies of your running master.
Propagate the backup made via xtrabackup to the slave. There is no need to disrupt the relay (blackhole) server, just add the extra tables to be replicated to the relay server config before you "START SLAVE" on your slave.
Best Answer
I managed to do this by examining the log files using
mysqlbinlog
.Given the following master log position on
mysql2
:I opened the log file:
And searched for the position (46678888).
The nearby line of
SET TIMESTAMP=1488370536
looked like a good candidate to search for in the corresponding log onmysql1
. So I opened up the master log file onmysql1
(there were multiple to choose from, but I went with the one with the closest timestamp to that on the slave, you might have to try a few files):And searched for
SET TIMESTAMP=1488370536
. This is what I found:I checked surrounding lines to ensure I'd got the right place and then used the corresponding
end_log_pos
(in this case 46236261) as the new log position. Therefore my master log position onmysql1
is:It's probably possible to automate this process somewhat, but to be honest it was very quick and easy to do it manually (and I don't expect to have to do again much if at all) - it's taken longer to write up the process than actually do it.