Solutions for inconsistent master-slave synchronization in mysql

View the running status of the master: [root @ master] mysql-uroot-p ************* [root @ master] mysqlshowmasterstatusG; * *************************** 1. row ************************** File: mysql-bin.000014 this information point should remember, below use Position: 170017372

View the running status of the master: [root @ master] mysql-uroot-p ************* [root @ master] mysql show master status \ G; * *************************** 1. row ************************* File: mysql-bin.000014 // This information point to remember, position: 170017372

View the running status of the master:

[Root @ master] mysql-uroot-p ************* [root @ master] mysql> show master status \ G; * *************************** 1. row ************************* File: mysql-bin.000014 // This information point to remember, position: 170017372 // This information point should be remembered. Binlog_Do_DB: ipharmacare_admin Binlog_Ignore_DB: mysql, information_schema, cece_schema Executed_Gtid_Set: 1 row in set (0.00 sec) should be used below)

View the running status of slave:

[root@slave] mysql -uroot -p************[root@slave] mysql> show slave status \G;    *************************** 1. row ***************************               Slave_IO_State:               Master_Host: master.mysql.ipharmacare.org              Master_User: slave              Master_Port: 3306            Connect_Retry: 60              Master_Log_File: mysql-bin.000013          Read_Master_Log_Pos: 1003623481               Relay_Log_File: mysql-bin.000022            Relay_Log_Pos: 36726417        Relay_Master_Log_File: mysql-bin.000013             Slave_IO_Running: No            Slave_SQL_Running: No              Replicate_Do_DB: ipharmacare_admin          Replicate_Ignore_DB: mysql,information_schema,performance_schema           Replicate_Do_Table:            Replicate_Ignore_Table: ipharmacare_admin.tb_hospital,ipharmacare_admin.t_customer,ipharmacare_admin.t_license,ipharmacare_admin.tb_hospital_zone_license,ipharmacare_admin.tb_hospital_license          Replicate_Wild_Do_Table: ipharmacare_admin.%      Replicate_Wild_Ignore_Table:                Last_Errno: 0               Last_Error:              Skip_Counter: 0          Exec_Master_Log_Pos: 1003623481              Relay_Log_Space: 1003624042              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: NULL    Master_SSL_Verify_Server_Cert: No            Last_IO_Errno: 0            Last_IO_Error:                Last_SQL_Errno: 0               Last_SQL_Error:       Replicate_Ignore_Server_Ids:              Master_Server_Id: 1              Master_UUID: a8ddc479-8862-11e2-b6df-2761731e3dd6             Master_Info_File: /mnt/mysql/master.info                SQL_Delay: 0          SQL_Remaining_Delay: NULL          Slave_SQL_Running_State:            Master_Retry_Count: 86400              Master_Bind:           Last_IO_Error_Timestamp:          Last_SQL_Error_Timestamp:                Master_SSL_Crl:            Master_SSL_Crlpath:            Retrieved_Gtid_Set:             Executed_Gtid_Set:             Auto_Position: 0         Replicate_Rewrite_DB:              Channel_Name:            Master_TLS_Version:     1 row in set (0.00 sec)

Conclusion: we can see that there is a lot of data synchronization latency, and we hope to make the master and slave databases fully synchronized.

First, go to the master database and lock the table to prevent data writing.

[Root @ master] mysql> flush tables with read lock;

Back up master Data

[Root @ master] cd/mnt/mysql/bakdata [root @ master] mkdir baksql [root @ master] cd baksql [root @ master] mysqldump ipharmacare_admin-uroot-p *** * ** -- opt> ipharmacare_admin. SQL or: mysqldump-uroot-p ***** -- default-character-set = utf8 ipharmacare_admin> ipharmacare_admin. SQL

Package data (optional)

[Root @ master] 7za a ipharmacare_admin_20160505.7z ipharmacare_admin. SQL

Upload the mysql backup file to the slave database for data recovery.

[root@slave] cd /usr/downloads/[root@slave] scp root@master:/mnt/mysql/bakdata/ipharmacare_admin_20160505.7z ./[root@slave] 7az x ipharmacare_admin_20160505.7z [root@slave] mysql -uroot -p*****;[root@slave] mysql> drop database ipharmacare_admin;[root@slave] mysql> CREATE DATABASE ipharmacare_admin DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;[root@slave] msyql> source baksql.sql;

Update/set synchronization progress point

 [root@slave] change master to master_host='master.mysql.ipharmacare.org', master_user='slave', master_port=3306, master_password='************', master_log_file='mysql-bin.000014', master_log_pos=170017372;

Note:

1) After MySQL master-slave replication, when using mysqldump to back up data, be sure to follow the following methods: [root @ master] mysqldump-master-data-single-transaction-user = username-password = password dbname> dumpfilename to retain the file and position information, when a new slave is created, the file and position information is updated after the database is restored, and then start slave to quickly complete incremental synchronization. 2) When you forget the master-slave replication password, you can reset it like this: [root @ master] grant replication slave on *. * TO 'slave '@ 'slave .mysql.ipharmacare.org 'identified BY 'slave ';