MySQL server master-slave replication process analysis _ MySQL

Analysis on master-slave replication process of MySQL server bitsCN.com
The master-slave replication of the MySQL server is divided into two situations: the MySQL version of the slave server in the replication structure is the same or higher than that of the master server. ######################################## ######################################## ##################### 1. modify the configuration file on the master server: vim/etc/my. cnf/modify: server-id = 1 (1 by default) # service mysqld restart 2. create a user connected to the mysql database and GRANT the copy permission to mysql> grant replication client, replication slave on *. * TO repl @ '2017. 16. %. % 'identified BY '123'; 3. modify the configuration file on the slave server: vim/etc/my. cnf modification: servier-id = 11 turn off the binary log # log-bin = mysql-bin add the following content: Relay-log = relay-bin relay-log-index = relay-bin.index # service mysqld restart 4. clear the logs on the master and slave servers with the command: mysql> flush master; at the same time, the slave server also clears the logs on the slave server: mysql> flush slave; otherwise, an error occurs if the logs of the master and slave servers are located at the same node.
5. after clearing the log, you can connect TO the MASTER server. mysql> change master to MASTER_HOST = '2017. 16.35.1 ', MASTER_USER = 'repl', MASTER_PASSWORD = '000000'; run mysql> show slave status/G to check whether the connection is successful, the connection fails. Slave_IO_Running: Yes Slave_ SQL _Running: Yes 6. connection failure causes are as follows: 1. user errors on the master server may occur. 2. the log on the master server is not rolled back. it is necessary to scroll back before connection. 7. create or delete databases and tables on the master server to synchronize the data to the slave server. On the slave server, you can also see how long the slave server is slower than the master server. use the command: mysql> show slave status/G to locate Second_Behind_Master: 0 (0, indicates that there is no delay in time. 2. if data already exists on the master server, enable the slave server. 1. import the data on the master server to the slave server before enabling the slave server. So back up the data on the master server # mysqldump -- all-databases -- lock-all-tables -- master-data = 2>/tmp/slave. SQL 2. copy the backup to the slave server # scp/tmp/slave. SQL 172.16.35.2:/tmp/3. import the backup data to the slave server, mysql> source/tmp/slave. SQL 4, the following can be connected, but before the connection, you must first view the backup file and use the command head to view head-30/tmp/slave. SQL # check the first 30 rows with one row: CHANGE MASTER TO MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER_LOG_POS = 279; the backup location is: MASTER_LOG_FILE = 'MySQL-bin.000001 ', MASTER _ LOG_POS = 279 so you must specify this location before Connection: mysql> change master to MASTER_HOST = '100. 16.35.1 ', MASTER_USER = 'repl', MASTER_PASSWORD = '000000', MASTER_LOG_FILE = 'MySQL-bin.000001', MASTER_LOG_POS = 123456; returned results: Query OK, 0 rows affected (279 sec) indicates that the backup is successful. 5. You can start the slave server mysql> start slave; mysql> show slave status/G # check whether the slave server is successfully connected. the following shows that the slave server is successfully connected: slave_IO_Running: Yes Slave_ SQL _Running: Yes the master-slave replication of the mysql server is completed after the preceding steps. If there are differences, please come up with them to make common progress! Author: ZhouLSbitsCN.com