Shulou(Shulou.com)06/01 Report--

How to solve the problem of Slave_SQL_Running: No mysql synchronization failure, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Master-slave server mode. After restarting the slave server, it is found that the backup has stopped. The following error occurred when starting mysql > start slave; manually:

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

Treatment method:

1. Dealing with vim / etc/my.cnf

Just add relay-log=xxx # (relay-log=fb-relay)

two。 Delete the master.info and relay-log.info files from the server and restart the mysql service

Note:

The relay-log log records the binary log from the master server to the local file of the slave server, and then the SQL thread reads the contents of the relay-log log and applies it to the slave server

Mysql slave cannot synchronize Last _ SQL_Error: Error 'Duplicate entry'

Published 54 days ago, "No comment on Database Application" was watched 394 times.

After a mysql slave was restarted yesterday, a Last_SQL_Error: Error 'Duplicate entry' error occurred and could not be updated synchronously

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.10.100

Master_User: slave_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000773

Read_Master_Log_Pos: 63325

Relay_Log_File: server122-relay-bin.000002

Relay_Log_Pos: 165661

Relay_Master_Log_File: mysql-bin.000771

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Error 'Duplicate entry' 1438019 'for key' PRIMARY'' on query. Default database: 'otrs'. Query: 'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES (' 2012061310001851, Your order ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'

Skip_Counter: 0

Exec_Master_Log_Pos: 41969067

Relay_Log_Space: 625695

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: 1062

Last_SQL_Error: Error 'Duplicate entry' 1438019 'for key' PRIMARY'' on query. Default database: 'otrs'. Query: 'INSERT INTO ticket (tn, title, create_time_unix, queue_id, ticket_lock_id, user_id, group_id, ticket_priority_id, ticket_state_id, ticket_answered, escalation_start_time, timeout, valid_id, create_time, create_by, change_time, change_by) VALUES (' 2012061310001851, Your order ORD201205A000016 was bounced back', 1339585744, 44, 1, 43, 1, 3, 4, 0, 1339585744, 0, 1, current_timestamp, 43, current_timestamp, 43)'

1 row in set (0.00 sec)

ERROR:

No query specified

To search for information on the Internet, the solution is:

Mysql > slave stop

Mysql > set GLOBAL SQL_SLAVE_SKIP_COUNTER=1

Mysql > slave start

I haven't tried the above method, but I use the following method:

Modify mysql configuration file / etc/my.cnf add a line slave_skip_errors = 1062 under [mysqld] and save. Restart mysql. Mysql slave can be synchronized normally.

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

Reason:

1. The program may have written on slave.

two。 It may also be caused by the rollback of the transaction after the slave machine is restarted.

Solution I:

1. First stop the Slave service: slave stop

two。 Go to the primary server to view the host status:

Record the values 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. Perform a manual synchronization on the slave server:

Mysql > change master to

> master_host='master_ip'

> master_user='user'

> master_password='pwd'

> master_port=3307

> master_log_file='mysql-bin.000020'

> master_log_pos=135617781

1 row in set (0.00 sec)

Mysql > slave start

1 row in set (0.00 sec)

Check 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

My own experience: the first method is to force synchronization from a certain point, there will be some data loss without synchronization, and the subsequent deletion of record synchronization on the main server will also have some error messages, which will not affect the use. Method two may not be effective.

=]

1. Master and slave 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 Master mainly:

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Whether the Seconds_Behind_Master is 0 and 0 is already synchronized.

2, there are some optimizations and monitoring that need to be done:

Show full processlist; / / View the current synchronization thread number of mysql

Skip-name-resolve / / Skip dns name query to help speed up connection and synchronization

Max_connections=1000 / / increase the number of connections to Mysql (default is 100)

Max_connect_errors=100 / / increase the number of incorrect connections in Mysql (default 10)

Check the log some commands

1, show master status\ G

The main thing here is to see whether the files in log-bin are the same.

Show slave status\ G

The main purpose here is to see:

Slave_IO_Running=Yes

Slave_SQL_Running=Yes

If all are Yes, the configuration is successful.

2, enter show processlist\ G on 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 to

Be updated

Info: NULL

If Command: Binlog Dump appears, the configuration is successful.

Stop slave # stop synchronization

Start slave # starts synchronization and updates from the location where the log terminates.

| SET SQL_LOG_BIN=0 | 1 # host running, which requires super permission. It is used to open and stop logs and open and stop logs at will, which will cause inconsistency of host slave data and cause errors. |

The SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n # client runs to skip several events and can be executed only if the synchronization process is stopped due to an error.

RESET MASTER # runs on the host side to clear all logs. This command is the original FLUSH MASTER.

RESET SLAVE # runs from the computer, clears the log synchronization location flag and regenerates the master.info

Although the master.info has been regenerated, it is not useful. It is best to restart the mysql process of the slave machine.

LOAD TABLE tblname FROM MASTER # runs from the slave and rereads the data of the specified table from the host side. Only one table can be read at a time. Due to the limitation of timeout time, it is necessary to adjust the timeout time. To execute this command, you need to synchronize the account with reload and super permissions. And have select permission to the corresponding library. If the table is large, increase the values of net_read_timeout and net_write_timeout

LOAD DATA FROM MASTER # executes from the slave and re-reads all the data from the host. To execute this command, you need to synchronize the account with reload and super permissions. And have select permission to the corresponding library. If the table is large, increase the values of net_read_timeout and net_write_timeout

CHANGE MASTER TO master_def_list # changes some host settings online, multiple of which are separated by commas, such as

CHANGE MASTER TO

MASTER_HOST='master2.mycompany.com'

MASTER_USER='replication'

MASTER_PASSWORD='bigs3cret'

MASTER_POS_WAIT () # Slave operation

SHOW MASTER STATUS # host runs, see log export information

The SHOW SLAVE HOSTS # host runs, depending on the connected slave.

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'

After reading the above, have you mastered how to solve the problem of Slave_SQL_Running: No mysql synchronization failure? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.