MySQL 5.5 master-slave synchronization troubleshooting

MySQL 5.5 master-slave synchronization troubleshooting

Slave_ SQL _Running: No mysql synchronization fault solution

Slave_ SQL _Running: No mysql synchronization troubleshooting
Check the database today and find that a MySQL Slave is not synchronized with the host. Check the Slave status:
Mysql> show slave status \ G
Slave_IO_Running: Yes
Slave_ SQL _Running: No
Last_errno.: 1062
....
Seconds_Behind_Master: NULL

Cause:
1. The program may write on slave.
2. It may also be caused by transaction rollback after the Server Load balancer instance restarts.
Solution I:
1. stop the Slave service first: slave stop
2. Check the host status on the master server:
Record the value corresponding to File and Position.
Mysql> show master status;
+ ------------------ + ----------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ----------- + -------------- + ------------------ +
| Mysql-bin.000020 | 135617781 |
+ ------------------ + ----------- + -------------- + ------------------ +
1 row in set (0.00 sec)
3. Execute manual synchronization on the slave server:
Mysql> change master
> Master_host = 'master _ ip ',
> Master_user = 'user ',
> Master_password = 'pwd ',
> Master_port = 3307,
> Master_log_file = 'mysql-bin.20.20 ',
> Master_log_pos = 135617781;
1 row in set (0.00 sec)
Mysql> slave start;
1 row in set (0.00 sec)
View the slave status again and find:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
...
Seconds_Behind_Master: 0
Solution II:
Mysql> slave stop;
Mysql> set GLOBAL SQL _SLAVE_SKIP_COUNTER = 1;
Mysql> slave start;
 
Your own experience: the first method is to force synchronization from a certain point, there will be some non-synchronous data loss, there will be some errors in the subsequent deletion records synchronization on the master server, will not affect the use. method 2 may not be effective.
 
 
======================================

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

==========================================================]
1. Master/Slave nodes cannot be synchronized:
Show slave status; Error: Error xxx dosn't exist
And show slave status \ G:
Slave_ SQL _Running: NO
Seconds_Behind_Master: NULL
Solution:
Stop slave;
Set global SQL _slave_skip_counter = 1;
Start slave;
After that, Slave will synchronize with the Master. The main points are as follows:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Seconds_Behind_Master is synchronized if it is 0 or 0
2. Some optimizations and monitoring are also required:
Show full processlist; // view the current mysql synchronization thread number
Skip-name-resolve // skip dns name query, which helps speed up connection and Synchronization
Max_connections = 1000 // increase the number of Mysql connections (100 by default)
Max_connect_errors = 100 // increase the number of Mysql error connections (default: 10)

View logs
1. show master status \ G;
Here, we mainly check whether the log-bin files are the same.
Show slave status \ G;
Here we mainly look:
Slave_IO_Running = Yes
Slave_ SQL _Running = Yes
If Yes, the configuration is successful.
2. Enter show processlist \ G on the master;
Mysql> show processlist \ G
* *************************** 1. row ***************************
Id: 2
User: root
Host: localhost: 32931
Db: NULL
Command: Binlog Dump
Time: 94
State: Has sent all binlog to slave; waiting for binlog
Be updated
Info: NULL

If Command: Binlog Dump appears, the configuration is successful.
 
Stop slave # stop Synchronization
Start slave # start synchronization and update from the log termination location.
SET SQL _LOG_BIN = 0 | 1 # run on the host. The super permission is required to enable or stop the log. If the log is enabled or disabled at will, the data on the host slave will be inconsistent, resulting in errors.
Set global SQL _SLAVE_SKIP_COUNTER = n # Run the client to skip several events. It can be executed only when the synchronization process stops when an error occurs.
Reset master # run on the host to clear all logs. This command is the original flush master.
Reset slave # Run from the machine, clear the log synchronization location mark, and regenerate master.info
Although master.info is re-generated, it cannot be used. It is best to restart the mysql process on the slave machine,
Load table tblname from master # run on the slave machine. The data in the specified TABLE can be re-viewed FROM the host. Only one TABLE can be read at a time. Due to the time limit of timeout, you need to adjust the timeout time. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Load data from master # Run FROM the slave machine and read all the data from the host. To execute this command, the synchronization account must have the reload and super permissions. And have the select permission on the corresponding database. If the table is large, add the net_read_timeout and net_write_timeout values.
Change master to master_def_list # online CHANGE of some host settings. Separate multiple settings with commas (,). For example
CHANGE MASTER
MASTER_HOST = 'master2 .mycompany.com ',
MASTER_USER = 'replicase ',
MASTER_PASSWORD = 'bigs3cret'
MASTER_POS_WAIT () # Run from the slave machine
Show master status # Run the host and view the log export information
Show slave hosts # Run the host to check the connected SLAVE machine.
Show slave status (slave)
Show master logs (master)
Show binlog events [IN 'logname'] [FROM pos] [LIMIT [offset,] rows]
PURGE [MASTER] logs to 'logname'; PURGE [MASTER] logs before 'date'

This article permanently updates the link address: