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.