Yes, it will overwrite the data on the Slave. However, you can setup replication to pick up from the point-in-time of the dump and make it roll all changes since the dump was loaded on the Slave
For this Example, let's assume
- IP of the Master is 10.1.1.20
- IP of the Slave is 10.1.1.30
Here is what you do
STEP01 : Activate Binary Logging on the Old Server
Step01-a) Add this to /etc/my.cnf on the Master
[mysqld]
server-id=101120
log-bin=mysql-bin
Step01-b) # service mysql restart
After STEP01, you should see mysql-bin.000001 and mysql-bin.index in /var/lib/mysql
STEP02 : Perform mysqldump
On the Master, you can mysqldump the data and record from what point in time it happened.
# service mysql restart --skip-networking --skip-grants
# mysqldump --single-transaction --master-data=2 --all-databases --routines --triggers > MySQLData.sql
# service mysql restart
What this does is record the Master Log File and Position the moment the mysqldump started as a comment. You can visibly see it when you view line 22:
# head -22 MySQLData.sql | tail -1
STEP03 : Load the mysqldump into the Slave
Execute the mysql client loading the mysqldump into the Slave's mysql instance
# mysql -h10.1.1.30 -uroot -p < MySQLData.sql
STEP04 : Create MySQL Replication User on the Master
# mysql -uroot -p -e"GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicator'@'10.64.51.%' IDENTIFIED BY 'replpassword'"
STEP05 : Setup the Slave with a Separate Server ID
Add this to /etc/my.cnf on the Slave
[mysqld]
server-id=101130
and # service mysql restart
STEP06 : Setup the Replication on the Slave
Goto the mysql client and run the following command
mysql> CHANGE MASTER TO
MASTER_HOST='10.1.1.20',
MASTER_PORT=3306,
MASTER_USER='replicator',
MASTER_PASSWORD='replpassword',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=1;
STEP07 : Setup the point-in-time Master Log and Position
Back in STEP02, I mentioned viewing the point-in-time position using
# head -22 MySQLData.sql | tail -1
You should see something like this:
#CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Run it as command in the mysql client on the Slave
mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=122957100;
Afterwards, run this command
mysql> SHOW SLAVE STATUS\G
You should see something like this:
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.64.113.232
Master_User: replicant
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 122957100
Relay_Log_File: relay-bin.003666
Relay_Log_Pos: 122957100
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: No
Slave_SQL_Running: No
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: 106
Relay_Log_Space: 106
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
STEP08 : Launch Replication
Start up replication with this:
mysql> START SLAVE;
Afterwards, run this command again
mysql> SHOW SLAVE STATUS\G
If you see this
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
CONGRATULATIONS, MySQL Replication is Working !!!
Best Answer
Let's assume you're managing your own database instead of AWS managed database.
/var/lib/mysql. Copy that to a safe place.