MYSQL learning summary (5): MYSQL master-slave setup

Test machine: Master 192.168.1.71 Slave 192.168.1.74
Step 1: Generate an account on the master database to grant permissions to the slave database (see MYSQL authorization above)
Step 2: Modify the main configuration file
Server-id = 1 / /Main database id-binlog-DO-DB = DB1 // Need to synchronize library 1
binlog-db = db2 // Need a synchronous library 2
log-bin = mysql-bin // Step: Restart the master server
Step 4: Execute flush tables with read lock on the master server;
Step 5: Execute show master status on the master server; remember the binary file and location

Step 6: Get the master There are currently two methods for taking a snapshot, one is to directly package it with tar, and the other is to use mysqldump. Then copy it to the corresponding data directory of the slave library. But I heard that lvm is useful, and it’s very fast. I haven’t found the right information to learn from.
Step 7: Unlock the master library unlock tables;
Step 8: Modify the slave library configuration file
Server-id = 2 //Slave server ID to avoid duplication with the master
Log-slave-updates //Slave server SQL process Updated data is written into its own binary log
          Master-connect-retry = 60 // When the slave is disconnected, connect to the master again after 60 seconds
  Step 9: Set up synchronization on the slave library
                                                                                                                                                                                                                           to master_host='192.168.1.71',master_user='User generated in the first step',master_password='Password generated in the first step',master_log_file='mysql-bin.000062',master_log_pos=316406;
Step 10: Start from library SLAVE START; and check the Stats of the Library SLOW SLAVE STATUSG, as shown in the figure:
Pay attention to observing the slave_io_running, slave_sql_running as yes, that is, the mainstorphic communication is normal. Seconds_Behind_Master is 0, indicating that the master-slave synchronization delay status is good.
Note: Master-slave replication is implemented through the master’s dump thread and the slave’s IO thread and SQL thread. You can log in to MYSQL and use show processlist to view the thread status. The slave IO thread pulls the binary log to the master server, and then writes it to its own relay log (ie Relay_log_File). Then the slave SQL thread reads the relay log and writes the SQL statements and operations into the local database.
Frequently asked questions in MYSQL master-slave operation:
1. Make sure that both the master and slave servers MYSQL are running normally, and the binary logs are open, from which you can use the replication account to access the main library,
2. From the above IO thread shows NO, then start slave io_thread;
3. From the above, the SQL thread shows NO. You need to search the error log. After locating the problem, manually modify or directly skip this operation (to ensure safety). To skip this operation, you can use SET GLOBAL SQL_slave_SKIP_COUNTER = 1; Then start slave;
4. To re-establish the master-slave function, you need to delete the master.info and relay-log.info in the slave database.
5. When the master server hangs or the performance decreases, use the slave server on top (make sure the slave has been synchronized with All data to the master server can be judged by checking the pos and files in the master's status, and then judging by Master_log_File, Read_Master_log_pos, Relay_Master_log_file, Exec_Master_log_Pos in the slave's status). You need to execute:
Stop slave IO_thread;
Stop slave;
Reset master;
Delete master.info relay-log.info at the same time
Restart slave MYSQL


The above is the summary of MYSQL learning (5): the content of MYSQL master-slave construction. For more related content, please pay attention to the PHP Chinese website (www.php .cn)!