Mysql Master never synchronizes processing-database Initialization

Mysql Master never synchronizes processing-database Initialization

Learn from online content for troubleshooting

Re-master, full synchronization

After creating a new table in the master database, the data in the slave segment is not synchronized.

Mysql version: 5.6.10

OS: rhel 5.6

 

The solution is as follows:

1. The master database locks the table to prevent data writing.

Mysql> flush tables with read lock;

 

UNLOCK command:

Mysql> unlock tables;

 

2. Back up data to the mysql. SQL file.

# Mysqldump-uroot-pmysql -- all-databases> mysql. SQL

 

3. View master Status

mysql> show master status;+------------------+----------+--------------+------------------+-------------------+| File | Position |Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 | 335 |test | | |+------------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)

 

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

Scp or ftp

 

5. Stop slave database status

Mysql> stop slave;

 

6. import data backup from the database

Mysql> source/lixora/mysql. SQL;

 

7. Set slave Database Synchronization. Note that the synchronization point here is | File | Position in the show master status information of the master database.

Change master tomaster_host = '10. 80.18.241 ', ----- master database IP address master_user = 'root', master_password = 'mysql', master_port = 3306, master_log_file = 'mysql-bin.000003', master_log_pos = 335;

MASTER_HOST is the ip address of the master,

MASTER_USER and MASTER_PASSWORD are users added to the master to copy operations.

MASTER_LOG_FILE and MASTER_LOG_POS correspond to the information in show master status.

 

8. enable synchronization again

Mysql> start slave;

 

9. View synchronization status

mysql> show slave status\GSlave_IO_Running: YesSlave_SQL_Running: Yes

 

Synchronization test:

[Master]

mysql> use test;Database changedmysql> show tables;+----------------+| Tables_in_test |+----------------+| lixora || max || test |+----------------+3 rows in set (0.00 sec)mysql> create table lixora_2015_0317 as select * from mysql.user;Query OK, 5 rows affected (0.68 sec)Records: 5 Duplicates: 0 Warnings: 0mysql> show tables;+------------------+| Tables_in_test |+------------------+| lixora || lixora_2015_0317 || max || test |+------------------+4 rows in set (0.00 sec)mysql> select * from lixora_2015_0317;。。。5 rows in set (0.00 sec)

[Slave]

mysql> use testDatabase changedmysql> show tables;+------------------+| Tables_in_test |+------------------+| lixora || lixora_2015_0317 || max || test |+------------------+4 rows in set (0.00 sec)mysql> select count(*) from lixora_2015_0317;+----------+| count(*) |+----------+| 5 |+----------+1 row in set (0.06 sec)mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 10.80.18.241Master_User: rootMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000003Read_Master_Log_Pos: 1166Relay_Log_File: oracle11g-dag-relay-bin.000002Relay_Log_Pos: 1114Relay_Master_Log_File: mysql-bin.000003Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 1166Relay_Log_Space: 1295Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 1Master_UUID: 888a8d9e-cbb3-11e4-b87e-000c29d57584Master_Info_File: /var/lib/mysql/master.infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Position: 01 row in set (0.00 sec)