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
Going by your question, I will like to review what I believe you did thus far:
- You stopped mysql on the Master
- You copied Master's /var/lib/mysql to the Slave's /var/lib/mysql
- I surmise the binlogs on the Master were copied as well
Look at the Slave's last binlog. From the question, it should be
mydbm1-bin.008524
- Filesize 1330529
Believe it or not, you have to do a few things:
1) On the Master, create a replication user like this:
GRANT REPLICATION SLAVE,REPLICATION CLIENT
ON *.* TO replicator@'%'
IDENTIFIED BY 'r3plic4t0R';
2) Make /var/lib/mysql on the Slave owned by mysql
user
chown -R mysql:mysql /var/lib/mysql
3) Make sure Master's server_id is explicitly set in my.cnf
[mysqld]
server_id = 1
4) Make sure Slave's server_id is explicitly set in my.cnf
[mysqld]
server_id = 2
5) Startup mysql on the Slave
service mysql start
6) Setup replication by running this on the Slave
CHANGE MASTER TO
MASTER_HOST='IPAddressOfMaster',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='r3plic4t0R',
MASTER_LOG_FILE='mydbm1-bin.008524',
MASTER_LOG_POS=1330529;
START SLAVE;
SELECT SLEEP(5);
SHOW SLAVE STATUS\G
You will see something like this:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.48.20.253
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000254
Read_Master_Log_Pos: 858190247
Relay_Log_File: relay-bin.066069
Relay_Log_Pos: 873918
Relay_Master_Log_File: mysql-bin.000254
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 858190247
Relay_Log_Space: 873772
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.00 sec)
If Slave_IO_Running
and Slave_SQL_Running
are both Yes
, CONGRATULATIONS !!!
I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with essentially the same steps.
I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master
Best Answer
Why would you want this? The binlogs are already not identical between these two instances, even besides the last two, because the filenames and sizes are different.
Binlogs start new files if
FLUSH LOGS
is run, or if MySQL Server restarts, or the file reaches the max binlog size. The number of files and their sizes are bound to be out of sync between the source instance and its replicas. I would guess in this case that restoring the snapshot caused two restarts.It's not a problem for binlog files to have different sizes. As long as they contain the same changes, they can be stored in few files or many files, and they don't have to be the same.
To apply changes in binlogs, you must have a contiguous set of changes since the last snapshot. If you were to purge the last two files, you could never use the binlogs for replication or point-in-time recovery, at least not past mysql-bin-changelog.603813.
Purging the binlog files older than the latest snapshot is common and recommended, but not the newest binlog files. In fact,
PURGE BINARY LOGS
will never remove the latest binlog file, because the MySQL Server is still using it.One solution for you is to set the
expire_logs_days
in the parameter group to a modest value such as 2 or 3, then wait that number of days for the current set of binlogs to expire. After that, the small binlog files will be gone.