MySQL master-slave extension problem and solution

First, the configuration MySQL master-slave encountered problems and solutions:

Master-slave replication is a MySQL built-in replication feature that is the foundation for building high-performance applications and is technically mature and widely used. This is done by copying the SQL statements from the MySQL master library to the library and executing them again. In the case of master-slave replication, all commands must be made on the main library and not from the library. Otherwise, the data between the master and slave libraries will be out of sync, and replication will be interrupted.

**1、）mysql主从配置uuid相同错误解决**

When configuring MySQL master and slave, because it is a copy of the MySQL directory, resulting in the same master-slave MySQL uuid, slave_io can not start, error message as follows:

The slave I/O thread stops because master and slave have equal MySQL server UUIDs; These uuids must is different for replication to work.

Workaround: Modify the value of the UUID in the auto.cnf file in MySQL data directory, make the two MySQL different, modify and restart the MySQL service.
Problem: Unable to synchronize from database

Slave_sql_running value is NO, or seconds_bebind_master value is Null

Reason:

1. The program may have been written on the slave

2. It is also possible that after the slave machine restarts, the transaction rollback causes the

Workaround One:

msyql> stop Slave;

Msyql> set GLOBAL sql_slave_skip_counter=1;

msyql> start slave;

Workaround Two:

msyql> stop Slave;

#查看主服务器上当前的 bin-log log name and offset

Msyql> Show master status;

#获取到如下内容:

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

| mysql-bin.000005 | 286 | | |

+------------------+----------+--------------+------------------+

#然后到从服务器上执行手动同步

Msyql> Change Master to

->master_host="192.168.10.1",->master_user="user",->master_password="123456",->master_post=3306,->master_log_file="mysql-bin.000005",->master_log_pos=286;

msyql> start slave;

Scenario 1: Copying or deleting a table data file with system commands on the main library

"Impersonation Exception":

Execute INSERT INTO test values (' 111 ') after copying the table data file directly on the main library or directly on the RM table data file Master Library, or after executing rm-f test05.* on the main library, execute the CREATE table test05 (a int (11));

"Error Log":

From library log: SHOW SLAVE STATUS \g;

last_errno:1146

Last_error:error ' Table ' testdb.test ' doesn ' t exist ' on query ' Insertinto test values (' 111 ') '.

Default database: ' TestDB '. Query: ' INSERT INTO testvalues (' 111 ') '

Or as follows:

Last_error:error ' Table ' test05 ' already exists ' on query.

"Cause of Error":

Table creation or deletion is not done by executing SQL, Binlog is not written, there is no related table from the library;

"Solution":

Create this table manually from the library (the table statement can refer to the main library);

Later, the operation of the table on the main library is done through SQL, avoiding the use of SYSTEM commands to copy or delete

Scenario 2: Inconsistent data: including deletion failure, primary key duplication, update loss

"Question 1":

Primary KEY repeat: The record is already in slave, and the same record is inserted on master.

From library log: SHOW SLAVE STATUS \g;

last_errno:1062

Last_error:error ' Duplicate entry ' xxxn-66-77 ' for Key1 ' on query. Default database: ' Guild '. Query: ' INSERT into pynpcrecord setmapcode = ' Xxxn ', updatetime = ' 2015-08-07 00:00:32 '

 【解决方案】：

Scenario 1: Delete the duplicate primary key record from the library and restart the master and slave again;

Deletefrom xxxx where primary key =yyyy;

Stopslave;start slave;

   方案2：停掉主从同步，忽略一次错误，再开启同步：stop slave;  set global sql_slave_skip_counter=1;startslave;

If the new match master, ignore 3 times also reported this mistake, also can in MY.CNF Riga

  一 行： slave-skip-errors=1062  然后重启实例，再重启主从同步；

Stop slave; Start slave;

"Issue 2" deletion failed: A record was deleted on master and was not found on slave.

From library log: SHOW SLAVE STATUS \g;

last_errno:1032;

Last_error:could not execute delete_rows event ontable hcy.t1;

Can ' t find record in ' T1 ',

"Solution":

Because Master wants to delete a record and the error is not found on slave, the main library deletes it, which can be skipped directly from the library.

Available commands:

Stop slave;

Set global sql_slave_skip_counter=1;startslave;

Issue 3: Update lost: A record was updated on master, and the data was lost on slave.

From library log: SHOW SLAVE STATUS \g;

last_errno:1032;

Last_error:could not execute update_rows event ontable hcy.t1; Can ' t find record in ' T1 ',

 【解决方案】：

Fill in the missing data on the slave, and then skip the error.

Scenario 3: Field inconsistency: Including missing fields, not long enough, etc.

"Question 1"

From library log: SHOW SLAVE STATUS \g;

Slave_io_running:yes

Slave_sql_running:no

last_errno:1264

Last_error:error ' out of range value for column ' Jfnow ' at row 1 ' onquery. Default database: ' Guild '. Query: ' Update pyphbws set jfnow =jfnow-1 whereplayername = ' Mad Demon '

Although the field from the library and the main library consistent, but from the library or error:

guild> desc PYPHBWS;

+------------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+---------------------+------+-----+---------+-------+

| PlayerName | varchar (30) | NO | PRI | NULL | |

| Jfnow | Int (Ten) unsigned | YES | | NULL | |

| Jfall |int (Ten) unsigned | NO | | NULL | |

 【解决方案】

To modify a field:

ALTER TABLE guild.pyphbws MODIFY jfnowbigint (unsigned);

Restart master/slave: Stop Slave;start Slave;

"Question 2"

From the Library log:

SHOW SLAVE STATUS \g;

Slave_io_running:yes

Slave_sql_running:no

last_errno:1054

Last_error:error ' Unknown column ' Qdir ' in ' Field List ' on query. Default database: ' Club '. Query: ' INSERT into Club.question_del (Id,pid, Ques_name, title, Intime, Order_d, endtime,qdir) Select ID, Pid,ques_name, Title, Intime, Order_d, Endtime, qdir from Club.question whereid=7330212 '

"Solutions"

Main Library: Query desc club.question_del, found Club.question_del table there is no qdir this field;

From library: Execute ALTER TABLE Question_del add Qdirvarchar (+) not null;

Scenario 4: Exceeding the MyISAM data table size limit

"Error Log"

From the Library log:

SHOW SLAVE STATUS \g;

Slave_io_running:no

Slave_sql_running:yes

last_errno:1114

Last_error:error ' The table ' Tbleventlog ' is full ' onquery. Default database: ' DBLog '. Query: ' Insert into (,,, tbleventlog PlayerName ACTION VALUE PARAM , TIME ') VALUES ('?? ', '? ', ' 620 ', ': 2,:397842703 ', ' 2015-07-28 06:56:0 4 ') '

Locate the directory where the instance is located and find that the table is larger than 4GB;

"Solutions"

For MyISAM data tables, single. MyD and. Myi default 4GB.

Use the Avg_row_length and Max_Rows table options to extend this maximum to 8 million TB.

Max_Rows is mainly effective for MyISAM.

From library: adjust max_rows and restart slave.

Use DBLog; ALTER TABLE Tbleventlog max_rows=1000000000;

Stop slave; Start slave;

Trunk log relay-bin corruption in scene 5:slave

"Impersonation Exception"

Slave in the outage, or illegal shutdown, such as power failure, motherboard burning, etc., causing the trunk log corruption, synchronization stopped.

"Error Log"

From library log: SHOW SLAVE STATUS \g;

Slave_io_running:yes

Slave_sql_running:no

last_errno:1593

Last_error:error Initializing relay Log position:i/oerror reading event at position 4

"Solutions"

Locate the synchronized Binlog and POS points on the main library, and then synchronize again so that you can have a new trunk log.

mysql> change MASTER to master_log_file= ' mysql-bin.000010 ', master_log_pos=821;

Scene 6:binlog index record inconsistency

"Impersonation Exception"

The primary database suddenly stopped or the problem was terminated, the Mysql-bin.xxx log was changed, and the slave server could not find the file.

"Error Log"

From library log: SHOW SLAVE STATUS \g;

master_log_file:mysql-bin.000029

last_errno:1594

Last_error:relay Log read failure:could not parserelay Log event entry.

"Solutions"

Locate the synchronized points and log files, and then Chage master:

Change Master to master_log_file= ' mysql-bin.000025 ', master_log_pos=1010663436;

Scenario 7: Inconsistent time zone causes master-slave data inconsistency

"Impersonation Exception": Master and slave servers set different time zones

Main library: Show variables like '%timezone% '; # See variable timezone value for EDT

From library: showvariables like '%timezone% '; # See variable timezone value for CST

Error log: The main library executes insert into Tbname (dtime) VALUES (now ());

Main Library: select* from Tbname; # See field Dtime value of 2013-05-08 18:40:18

From library: select* from Tbname; # See field Dtime value of 2013-05-09 06:40:18

"Solution":

Set the master-slave to the same time zone and try to use the same server

If the Time field is written with now (), delete is used when deleting * from tbname where dtime= ' xxx ', because the master-slave time is inconsistent, it will cause the deletion of the non-same record, will also cause the primary key conflict problem.

Scenario 8: Inconsistent field sets

"Impersonation Exception"

1. Main Library: Version MySQL 4.0.18, character set gb2312, primary key field PlayerName

Show CREATE TABLE Pybcsltscore;

PRIMARY KEY ( PlayerName ) Engine=myisam DEFAULT charset=gb2312

2. From library: version MySQL 4.1.22, character set latin1, primary key field PlayerName

Show CREATE TABLE Pybcsltscore;

PRIMARY KEY ( PlayerName ) Engine=myisam DEFAULT charset=latin1

3. Main Library: Execute sql:

mysql> INSERT INTO Pybcsltscore set playername = ' rage ☆ Chop ', PT = ' pchg.c8 ';

mysql> INSERT INTO Pybcsltscore set playername = ' Rage ★ Chop ', PT = ' pchg.c8 ';

4. Main Library: Query ok

SELECT * from Pybcsltscore where playername= ' rage ☆ chop ' or playername= ' fury ★ Chop ';

"Error Log"

From library: Query exception, query solid asterisk, result in empty asterisk

SELECT * from Pybcsltscore_bak where playername= ' rage ★ Chop ';

From library: from Library State:

last_errno:1062

Last_error:error ' Duplicate entry ' Fury ★ Chop ' for key 1 ' on query. DefaultDatabase: ' test0505 '. Query: ' INSERT into pybcsltscore set playername = ' Rage ★ Chop ', PT = ' pchg.cs68 '

From library: Insert Playname= ' rage ★ Chop ' record, will prompt for primary key conflict

INSERT INTO Score_bak set playername = ' Rage ★ Chop ', PT = ' pchg.cs68 ';

ERROR 1062 (23000): Duplicate entry ' Rage ★ Chop ' for key 1

"Solutions"

Scenario 1. From library: Remove primary key

ALTER TABLE pybcsltscore_test0513 drop PRIMARY key;

Stop slave Sql_thread; Start slave sql_thread;

   方案2. 从库：修改默认编码为gb2312

MySQL--default-character-set=gb2312-s mysql3307.sock

   从库：再次查询：

SELECT * from Pybcsltscore where playername= ' rage ☆ chop ' or playername= ' fury ★ Chop ';

Scene 9:max_allowed_packet too small

"Impersonation Exception"

1. Main Library: Set Max_allowed_packet to a special small value, such as 12K:

mysql> set global max_allowed_packet=12*1024;

Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like ' Max_allowed_packet ';

+--------------------+-------+

| variable_name | Value |

+--------------------+-------+

| Max_allowed_packet | 12288 |

+--------------------+-------+

2. Restart slave io thread

#说明: Slave If you do not restart, you feel that the main library used by the master-slave relationship changes

Does not change, reboot to reload some variables

3. Main Library: Import R2.txt (only one row of records

   #du-sh r2.txt 80K r2.txt)

   ./bin/mysql test0505-e "Load data infile '/tmp/r2.txt ' into table test2;"

4. View from library status

Slave_io_running:no

Slave_sql_running:yes

5. From library:

Mysql> Show variables like ' Max_allowed_packet ';

+--------------------+----------+

| variable_name | Value |

+--------------------+----------+

| Max_allowed_packet | 16776192|

+--------------------+----------+

"Error Log"

To view the error log from the database, locate the following information:

[ERROR] Got fatal error 1236: ' Log event entry exceededmax_allowed_packet;

Increase max_allowed_packet on Master ' from the master whenreading data from binary log

or similar:

[ERROR] Error reading packet from Server:got packetbigger than ' Max_allowed_packet ' bytes (server_errno=2020)

It should be that the dump thread on master reads the data from Binlog, reading the result set beyond the max_allowed_packet limit, causing a failure to send to slave.

"Solutions"

Modify the size of the Max_allowed_packet, and then restart Slave. Recommend master-Slave consistency

Mysql> set global max_allowed_packet=161024x7681024;

Restart slave, stop salve;start Slave;

Scenario 10: The temporary table is too large to cause the disk to write full

"Error log": From library Log

Last_errno:3

Last_error:error ' Error writing file '/TMP/FEQMC ' (errcode:28) ' on query.

Default database: ' evt '. Query: ' Delete from Goodslogwhere optime< ' 2015-07-01 '

"Cause of error"

1. The TMP directory is not writable, or the disk has no space;

2. TMP has space, but the original table is too large, so the temporary table generated at query time is too large to make an error.

"Solutions"

1. Confirm/tmp writable, while the disk is not full;

2. Modify the socket directory to a larger partition, and then restart the instance;

Change socket =/tmp/mysql.sock to socket =/app/mysql.sock

MySQL master-slave extension problem and solution