Mysql Master never synchronizes processing-database initialization, mysql --

Mysql Master never synchronizes processing-database initialization, mysql --

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
Master_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 \ G

 

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Synchronization test:

[Master]

Mysql> use test;
Database changed

Mysql> 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: 0

Mysql> 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 test
Database changed
Mysql> 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 event
Master_Host: 10.80.18.241
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 1166
Relay_Log_File: oracle11g-dag-relay-bin.000002
Relay_Log_Pos: 1114
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1166
Relay_Log_Space: 1295
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: 0
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: 888a8d9e-cbb3-11e4-b87e-000c29d57584
Master_Info_File:/var/lib/mysql/master.info
SQL _Delay: 0
SQL _Remaining_Delay: NULL
Slave_ SQL _Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)