Master Server IP : 172.168.190.15
Slave Server IP : 172.168.190.35
For more interesting posts on MySQL, please click on INDEX
1. Take dump on master and restore on slave
bin/mysqldump --defaults-file=/mysqldatabase/TEST/admin/my.cnf -uroot -pmysql --master-data=2 --quick --single-transaction -vvv --force -A > /mysqldatabase/dump/TEST_all.sql
Functions and events options needs to be given separately while taking mysqldump
Here the denominations suggests :
–master-data=2 –> This suggests the current binary log position and doesn’t apply to the database. A manual intervention is required to apply the bin logs. If this is 1, then it automatically applies the bin log after noting down the bin log position. But a value of 2 is suggested, so that, if any error occurs during restoration, then the whole instance may get corrupted
-vvv –> Verbose. To get details of the backup during the run phase
2. Restore in slave server :
nohup /mysqldatabase/product/binary/5.7.35/bin/mysql -uroot -pmysql -S/mysqldatabase/RESTORE/admin/mysql.sock < /mysqldatabase/dump/TEST_all.sql &
If there are any foreign key related errors:
mysql> set global foreign_key_checks=0;
3. Make necessary changes in master configuration file and restart master
binlog_format=mixed
server-id = 1
4. Create replication user in Master instance
CREATE USER 'repl'@'172.168.190.35' IDENTIFIED BY 'repl';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.168.190.35' IDENTIFIED BY 'repl';
FLUSH PRIVILEGES;
4. Make changes in slave configuration file and restart slave
server-id = 2
relay-log = /mysqldatabase/RESTORE/logs/target-relay-bin.index
5. Login into slave
CHANGE MASTER TO MASTER_LOG_FILE='TEST-mysql-bin.000005', MASTER_LOG_POS=2417,master_host='172.168.190.15', master_user='repl', master_password='repl',master_port=3306;
You can get the above details by checking the mysqldump
6. On slave:
mysql> START SLAVE;
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.168.190.15
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: TEST-mysql-bin.000002
Read_Master_Log_Pos: 7640
Relay_Log_File: target-relay-bin.000002
Relay_Log_Pos: 1879
Relay_Master_Log_File: TEST-mysql-bin.000002
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: 7640
Relay_Log_Space: 2087
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 9d1367c4-238d-11ec-aeab-0800273cc1fa
Master_Info_File: /mysqldatabase/SLAVE/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)