Solve the Problem of master-slave database synchronization.

Solve the Problem of master-slave database synchronization.

Solve the Problem of never synchronizing mysql master

Mysql> show processlist; check whether there are too many Sleep processes. It is normal. Show master status; also normal. Mysql> show master status; + metric + ---------- + -------------- + metric + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + --------------------- + ---------- + ------------ + metric + | mysqld-bin.000001 | 3260 | mysql, test, information_schema | + ------------------- + ---------- + ---------------- + ----------------------------- + 1 row in set (0.00 sec) View mysql> show Slave status on slave; Slave_IO_Running: YesSlave_ SQL _Running: No. If yes, this indicates that Slave is not synchronized. Method 1: Ignore the error, continue to synchronize this method is applicable to situations where the data in the Master/slave database is not much different or the data requirements are not completely uniform. If the data requirements are not strict, the solution is: stop slave; # indicates skipping one step error, the following number can be set global SQL _slave_skip_counter = 1; start slave; and then use mysql> show slave status to view: Slave_IO_Running: YesSlave_ SQL _Running: Yes OK. Now the master-slave synchronization status is normal... Method 2. first, go to the master database and lock the table to prevent data writing. Use the command mysql> flush tables with read lock. Note: The statements are locked to read-only status and are case insensitive. back up data # Back up data to mysql. bak. SQL file [root @ server01 mysql] # mysqldump-h192.168.100.197-uroot-p db_doctor> db_doctor.bak. SQL; Note: Database Backup must be performed on a regular basis. You can use shell or python scripts, make sure that the data is safe. view master status mysql> show master status; + ------------------- + ---------- + -------------- + ----------------------------- + | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | + --------------------- + ---------- + ------------ + mysqld-bin.000001 | 3260 | mysql, test, information_schema | + ------------------- + ---------- + -------------- + ------------------------------- + 1 row in set (0.00 sec) 4. upload the mysql backup file to the slave database for data recovery # Use the scp Command [root @ server01 mysql] # scp db_doctor. Bak. SQL root@192.168.100.196:/tmp/5. stop slave database status mysql> stop slave; 6. then run the mysql command from the slave database to import the data backup mysql> source/tmp/mysql. bak. SQL 7. set slave Database Synchronization. Note that the synchronization point here is in the show master status information of the master database. | File | Position: change master to master_host = '2017. 168.100.197 ', master_user = 'root', master_port = 3306, master_password =' ******** ', master_log_file = 'binlog. 000046 ', master_log_pos = 8632800; 8. restart synchronization from mysql> start slave; unlock tabl Es; 9. view the synchronization status mysql> show slave status; view: Slave_IO_Running: YesSlave_ SQL _Running: Yes. The synchronization is complete.