Detailed description of the deployment and switch of the MySQL database Master/Slave server document

MySQL databaseHow to ImplementMaster/Slave serversWhat about document deployment? How does one perform between master and slave servers?Document SwitchingWhat about it? This article mainly introduces this part of content. Next we will introduce it.

I. deployment documentation

1. Ensure that the MySQL version installed on the master server and slave server is consistent.

2. Set a connection account for the slave server on the master server

Mysql grant replication slave, SUPER, reload on *. * TO 'username' @ 10.1.1.4 'identified by' use.

Master Server IP Address: 10.1.1.3

Slave Server IP: 10.1.1.4

1. Ensure that the MySQL version installed on the master server and slave server is consistent.

2. Set a connection account for the slave server on the master server

 
 

  
  
mysql> GRANT REPLICATION SLAVE, SUPER, RELOAD ON *.* TO IDENTIF  
  
  
 
  
  
IED BY 'userpassword'; 
 
 

3. Execute flush tables with read lock to LOCK the table.

 
 

  
  
mysql> FLUSH TABLES WITH READ LOCK； 
 
 

4. Keep the client program running, and issue the flush tables statement to keep the read lock valid. (If you exit the client program, the lock is released ). Go to the data directory of the master server and run the following command:

 
 

  
  
shell> tar -cvf /tmp/mysql-snapshot.tar .   
  
  
 
  
  
shell> tar -xvf /tmp/mysql-snapshot.tar 
 
 

Read the current binary log name File and offset value (Position) on the master server, and record it:

Mysql> show master status; | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | mysql-bin.003 | 73 | test | manual, mysql | after obtaining a snapshot and recording the log name and offset, you can re-enable the Write activity on the master server:

Mysql> unlock tables;

5. Make sure that the [mysqld] section of the my. cnf file on the master server host contains a log_bin option.

 
 

  
  
[mysqld]  
  
  
 
  
  
Log_bin=mysql-bin  
  
  
 
  
  
server-id=1 
 
 

6. Stop the server used for the slave server and add the following lines to its my. cnf file:

 
 

  
  
[mysqld]  
  
  
 
  
  
server-id=2 
 
 

7. If you back up the data of the master server in binary mode, copy the data from the slave server to the data directory of the slave server before starting the slave server.

Ensure that the permissions on these files and directories are correct. The user running MySQL on the server must be able to read and write files, just as on the master server.

8. Use the -- skip-slave-start option to start the slave server so that it does not try to connect to the master server immediately.

9. Execute the following statement on the slave server:

 
 

  
  
mysql> CHANGE MASTER TO MASTER_HOST='10.1.1.3',MASTER_USER='username',MASTER_PASSWORD='userpassword',  
  
  
 
  
  
MASTER_LOG_FILE='recorded_log_file_name',MASTER_LOG_POS=recorded_log_position; 
 
 

9. Start the slave server thread:

Mysql> start slave;

10. Verify that the deployment is successful.

 
 

  
  
mysql> show slave status\G  
  
  
 
  
  
*************************** 1. row ***************************  
  
  
 
  
  
Slave_IO_State: Waiting for master to send event  
  
  
 
  
  
Master_Host: 10.1.1.3  
  
  
 
  
  
Master_User: rep_slave  
  
  
 
  
  
Master_Port: 3306  
  
  
 
  
  
Connect_Retry: 60  
  
  
 
  
  
Master_Log_File: mysql-bin.000058  
  
  
 
  
  
Read_Master_Log_Pos: 27324573  
  
  
 
  
  
Relay_Log_File: cacti-11-111-relay-bin.000008  
  
  
 
  
  
Relay_Log_Pos: 27324718  
  
  
 
  
  
Relay_Master_Log_File: mysql-bin.000058  
  
  
 
  
  
Slave_IO_Running: Yes  
  
  
 
  
  
Slave_SQL_Running: Yes  
  
  
 
  
  
Replicate_Do_DB:  
  
  
 
  
  
Replicate_Ignore_DB: mysql  
  
  
 
  
  
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: 27324573  
  
  
 
  
  
Relay_Log_Space: 27325025  
  
  
 
  
  
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  
  
  
 
  
  
1 row in set (0.00 sec) 
 
 

If Yes is displayed for both Slave_IO_Running and Slave_ SQL _Running, the synchronization is successful.

Ii. Switch document

1. Make sure that the slave server has processed all statements in the relay log. Mysql> stop slave IO_THREAD.

2. Check the output of the show processlist statement until you see the Has read all relay log.

3. When the slave server completes these operations, they can be reconfigured as a new setting.

4. Execute stop slave, reset master, and reset slave operations on the SLAVE server promoted to the MASTER server.

5. Restart the mysql service.

6. RESET the MASTER on the MASTER server. Then change master to MASTER_HOST = '10. 1.1.4 ', MASTER_USER = 'rep _ slave', MASTER_PASSWORD = 'userpassword'; the switchover is complete.

This article describes how to deploy the MySQL database master/slave server document and how to switch the Master/Slave document.