MySQL database ab master-slave replication error and resolution process

MySQL database ab master-slave replication error and resolution process

• First, MySQL master server error Description:
  slave_io_running=no,slave_sql_running=yes,last_errno=0 MySQL Slave stop; MySQL slave start; MySQL show slave status; If Slave_io_running=yes ...
  Resolution process:
  1 if: slave_io_running=no,slave_sql_running=yes,last_errno=0
  mysql> slave stop;
  mysql> slave start;
  Mysql> Show slave status;
  If slave_io_running=yes,slave_sql_running=yes,last_errno=0
  Then, the replication of the database has been successfully started.

  2 If you are prompted to have duplicated data, delete the data directly after it, and then
  mysql> slave stop;
  mysql> slave start;
  Mysql> Show slave status;
  If slave_io_running=yes,slave_sql_running=yes,last_errno=0
  Then, the replication of the database has been successfully started.

  3 If not, check the error log to see if it is stuck on the Binlog, for example: stuck in 000079
  The
  SLAVE STOP;
  Change MASTER to master_log_file= ' mysql-bin.000080 ', master_log_pos=0;
  SLAVE START;

  
  4 If error message: [Mysql]table tblname is marked as crashed and should be repaired
  MySQL prompt tblname table is corrupted and needs fixing, workaround:
  Go to the corresponding database directory:
  Cd/var/lib/mysql/dbname
  Using Myisamchk Repair:
  Shell> Myisamchk-r Tblname
  If prompted failed
  Shell> myisamchk-f Tblname
  Forced repair
  Recovery time is long and patience waits for repair to complete
  And then restart MySQL.
  
  Second, MySQL master server error description:
  
  Alarm Mysqla is down ... Room staff feedback is hardware error (omitted), let the computer room personnel write down the error message, let them help restart, the results and normal work, (export data), after a while the alarm, mysql_ab error.
  Resolution process: 1, check from the library show slave status \g;
  Slave_io_running:yes
  Slave_sql_running:no
  A 1062 error has also occurred, prompting:
  Last_sql_error:error ' Duplicate entry ' 1001-164761-0 ' for key ' PRIMARY ' on query. Default database: ' Bugs '. Query: ' INSERT into Misdata (uid,mid,pid,state,mtime) VALUES (164761,1001,0,-1,1262623560) '
  It is clear that the primary key conflict is not synchronized from the library data because of the main library reboot. Viewing the error log finds that the error log file has become much larger, nearly several times bigger than before,
  Tail-f Mysql_error.log The first thing to see is this message.
  Find this message
  [ERROR] Slave sql:error ' Duplicate entry ' 1007-443786-0 ' for key ' PRIMARY ' on query. Default database: ' UFO '. Query: ' INSERT into Misdata (Uid,mid,pid,sta
  Te,mtime) VALUES (443786,1007,0,-1,1262598003) ', error_code:1062
  100104 17:39:05 [Warning] slave:duplicate entry ' 1007-443786-0 ' for key ' PRIMARY ' error_code:1062
  100104 17:39:05 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "Slave START". We stopped at log ' ufolog.000058
  8 ' Position 55793296
  The error and the above meaning, the first thought is to manually sync, from the library first stop slave; stop syncing
  Enter the main Library lock table,
  FLUSH TABLES with READ LOCK;
  Mysql> Show master status;
  +-------------------+-----------+--------------+------------------+
  | File | Position | binlog_do_db | binlog_ignore_db |
  +-------------------+-----------+--------------+------------------+
  | ufo.000063 |              159164526 |                  | |
  +-------------------+-----------+--------------+------------------+
  1 row in Set (0.00 sec)
  Enter from library
  Mysql>change Master to master_host= ' 192.168.1.141 ', master_user= ' slave ',
  master_password= ' xxx ',
  master_port=3306,
  Master_log_file= ' ufo.000063 ',
  master_log_pos=159164526;

  After completing these
  Start slave;
  Back to the main library
  Unlock tables; Unlock

  Back to view from gallery
  show slave status \g;
  Found normal, the advantages of a breath. But not a minute, found to start the error, or the most beginning of the mistake, to check the log again, I am surprised that tail-f Mysql_error.log appear a lot of
  .......
  100106 16:54:21 [Warning] Statement may isn't safe to log in Statement format. Statement:delete from ' system_message_1 ' where ' to_uid ' = 181464 ORDER by ' id ' ASC LIMIT 1
  .........
  There are a lot of this warning in the log, meaning should be statement format is not safe, with vim open he looked, found a lot of such warnings, I said why the error log so big!!
  Statement format should be a form of binlog, go to view from the library www.it165.net
  Show global variables like ' Binlog_format ';
  Sure enough, the current format is statement

  I need to change the format to mixed format
  Modify the my.cfg from the library
  Under [Mysqld], add the following line
  Binlog_format=mixed

  Then restart the MySQL service and find that the warnings in the error log have been stopped.

  Third, the error description:
  
  Pre-MySQL5.6 replication has two worker threads: IO thread and SQL thread. A SQL thread exception caused replication to be interrupted.

  Many DBAs suffer from an alarm that is interrupted by a standby replication thread, with the following types of common errors:

  1032 Error –ha_err_key_not_found
  1062 Error –ha_err_found_dupp_key (Ha_err_found_dupp_key or Ha_err_found_dupp_unique)

  Issue background:

  Primary and standby data is not, often due to MySQL bug caused, where MySQL replication related bugs from each release notes can see the relevant figure, on our last two weeks line encountered on the following list:

  Table map set to 0 after altering MyISAM table (also triggered for InnoDB tables)
  Failing Assertion:trx->active_trans when renaming a table with Active Trx
  Querying i_s.global_temporary_tables or Temporary_tables crashes threads working with temp TABLES (non-debug version also exists)

  Of course, one of the biggest unknowns is the failure of the main library, or even the primary and standby switching to ensure that the service is available, how much data is inconsistent, and we don't have a spectrum in mind. Post-mortem detection is done only by relying on subsequent scheduled scans. Although the MHA we are doing is going back to a certain extent to reduce the number of inconsistent data, it is not possible to achieve strict data consistency. This issue is a major problem for the MySQL community and is not discussed here.

  In addition, when an exception occurs, a DBA's non-strict operation can also exacerbate the effect of this data inconsistency, for example, when encountering an error, skipping several errors until replication works.

  [Email protected] (none) 09:43:26>set global sql_slave_skip_counter=1;

  Solution Ideas:
  For the two most common types of online processing, that is, ha_err_key_not_found or ha_err_found_dupp_key errors, using the characteristics of row mode replication, when the execution event encountered an error, the following conversions:

  Ha_err_key_not_found:
  For update event update_rows_event directly into the pre-image before making updates
  For Delete event delete_rows_event directly insert the pre-mirror before deleting.
  Ha_err_found_dupp_key:
  For UPDATE event update_rows_event first delete after mirroring and then update
  For the Insert event write_rows_event is inserted directly in the overwrite form (i.e., the first is deleted and then inserted or converted to an update).

  New variables and statuses

  Add SMART mode to Slave_exec_mode (STRICT | idempotent | SMART)

  [Email protected] (none) 01:39:34>set global Slave_exec_mode=smart;
  Query OK, 0 rows Affected (0.00 sec)

  [Email protected] (none) 01:39:39>select @ @slave_exec_mode;
  + ——————-+
  | @ @slave_exec_mode |
  + ——————-+
  | SMART |
  + ——————-+
  1 row in Set (0.00 sec)

  Increase statistics on smart processing results:

  [Email protected] (none) 01:46:51>show status like ' smart% ';
  + ——————— + ——-+
  | variable_name | Value |
  + ——————— + ——-+
  | SMART_HANDLE_DUP_PK | 1 |
  | smart_handle_failed | 0 |
  | SMART_HANDLE_NO_PK | 1 |
  + ——————— + ——-+
  3 Rows in Set (0.00 sec)

  
  Operating conditions:

  This patch addresses the vast majority of replication interrupt exceptions from online operation, greatly reducing the frequency of DBA after midnight being warned to manually fix abnormal replication. Overall, it was a very successful patch.

  But there is still a scenario that is not yet complete, such as a unique key constraint or FOREIGN KEY constraint, the update fails or cannot be processed. Smart still cannot handle a unique key update failure on an application that is online.
  
  IV, error code 1 in slave log:
  
  [ERROR] Error reading packet from Server:client requested Master to Start replication from impossible position; The last event is read from ' mysql-bin.000016 ' in 455562731, the last byte read is read from ' mysql-bin.000016 ' at 4. (server_errno=1236)
  120725 23:19:17 [ERROR] Slave I/o: Got fatal ERROR 1236 from master if reading data from binary log: ' Client request Ed Master to start replication from impossible position; The last event is read from ' mysql-bin.000016 ' at 455562 ', error_code:1236

  Workaround:
  To see if the corresponding log file in the primary server has this row, use the command
  mysqlbinlog/yourpath/' mysql-bin.000016 >>/test
  Find out if there are no 455562 rows or lines close to this number and then use the Chang Master to command to skip this line, the command format is as follows
  Stop slave;
  Change MASTER to master_host= ' 192.168.192.45 ', master_user= ' xxxx ', master_password= ' xxxx ', master_log_file= ' Mysql-bin.000016 ', master_log_pos=455678;
  Start slave;
  
  If you can't find 455562 or a line close to this number, you can use the following command to see the ' mysql-bin.000016 ' line number
  Ls-la mysql-bin.000016
  If the number of rows is significantly less than 455562, you can skip this log, start copying from the next log, and command the following
  Stop slave;
  Change MASTER to master_host= ' 192.168.192.45 ', master_user= ' backup ', master_password= ' Weiphone ', master_log_file= ' mysql-bin.000017 ', master_log_pos=4;
  Start slave;

  Five, error type Code 2:
  
  MySQL slave error_code:1062 1054

  Resolution process:
  If this code appears in the log, may be the wrong select, or update operation, Master is to skip these operations, but is recorded in the binary log, slave will be based on the binary statements do the same action, will be error, know the principle of the operation is very simple
  Stop slave;
  Set gloable sql_slave_skip_counter=n; n is the number of error statements you want to skip
  Start slave;
  If you do not want to encounter this error again, you can write to the slave configuration file in the following format:
  Slave_skip_errors = 1062
  Slave_skip_errors = ALL//skips all types of error codes

  Six, error prompt:
  
  mysql> start slave;
  ERROR 1201 (HY000): Could not initialize master info structure; More error messages can is found in the MySQL error log

  [Email protected] ~]# Tail/var/log/mysqld.log
  090105 11:53:52 [ERROR] Failed to open the relay log '/var/run/mysqld/mysqld-relay-bin.000013 ' (Relay_log_pos 235)
  090105 11:53:52 [ERROR] Could not find target log during relay log initialization
  ...

  [Email protected] mysql]# ls/var/run/mysqld/
  mysqld-relay-bin.000001 mysqld-relay-bin.000003 mysqld-relay-bin.000005 Mysqld-relay-bin.index
  mysqld-relay-bin.000002 mysqld-relay-bin.000004 mysqld-relay-bin.000006
  [email protected] mysql]# cat Master.info
  14
  mysql-bin.000010
  1235
  192.168.3.21
  Slave
  29019853
  3307
  60
  0

  
  [email protected] mysql]# cat Relay-log.info
  /var/run/mysqld/mysqld-relay-bin.000013
  235
  mysql-bin.000010
  1235

  3

  50703

  Resolution process:
  
  [[Email protected] ~]# service mysqld stop
  [Email protected] mysql]# RM-FR master.info
  [Email protected] mysql]# RM-FR relay-log.info
  [[email protected] ~]# grep skip-slave-start/etc/my.cnf
  Skip-slave-start
  [[Email protected] ~]# service mysqld start
  [Email protected] ~]# mysql-uroot-p
  Mysql> Show master status;
  +------------------+----------+--------------+------------------+
  | File | Position | binlog_do_db | binlog_ignore_db |
  +------------------+----------+--------------+------------------+
  |       mysql-bin.000011 |              98 |                  | |
  +------------------+----------+--------------+------------------+
  [Email protected] ~]# mysql-uroot-p

  Mysql> Change Master to
  Master_log_file= ' mysql-bin.000011 ',
  Master_log_pos=98,
  Master_host= ' 192.168.3.21 ',
  master_port=3307,
  Master_user= ' slave ',
  master_password= ' 12345678 ';
  Query OK, 0 rows affected (0.02 sec)
  mysql> start slave;
  Query OK, 0 rows Affected (0.00 sec)
  Mysql> Show Slave Status\g
  1. Row ***************************
  Slave_io_state:waiting for Master to send event
  master_host:192.168.3.21
  Master_user:slave
  master_port:3307
  Connect_retry:60
  master_log_file:mysql-bin.000011
  Read_master_log_pos:98
  relay_log_file:mysqld-relay-bin.000002
  relay_log_pos:235
  relay_master_log_file:mysql-bin.000011
  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:98
  relay_log_space:235
  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

  1 row in Set (0.00 sec)

  
  There are many ways to fix the MySQL master-slave replication error, I hope to meet with the same problem as a friend to see this article will get some inspiration and problem-solving methods

MySQL database ab master-slave replication error and resolution process