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

MySQL replication technology refers to the problems encountered in deployment

a. Replication interrupt

Mysql master-slave replication often encounters errors that lead to interruption of server replication. At this time, manual intervention is generally required to skip the error before continuing.

There are two ways to skip errors:

1. Skip the specified number of transactions:

Mysql > slave stop

Mysql > SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 # Skip a transaction

Mysql > slave start

two。 Modify the configuration file of mysql to skip all errors or specified types of errors through the slave_skip_errors parameter

Vi / etc/my.cnf

[mysqld]

# slave-skip-errors=1062,1053,1146 # Skip error of specified error no type

# slave-skip-errors=all # skip all errors

b. UUID problem of cold backup recovery

This slave is to copy all the files in the datadir directory directly to the slave server after shutting down master, and then start slave, it will report an error

Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.

Analyze the problem:

The above error says that in the replication architecture, you need to ensure that each mysql instance UUID (UUID is stored in the auto.cnf file in the datadir directory) is unique, just like server_id, you need to ensure that all mysql instances in the master-slave architecture are unique.

This problem occurs because my slave host is the host where the cloned master library is located, so the UUID saved in the auto.cnf file will be duplicated (server_id has been modified in the my.cnf file)

Resolve the problem:

The solution to this problem is actually very simple: stop the slave database instance, delete the auto.cnf file of the slave database, and start the slave database instance. At this time, the slave database will generate a new auto.cnf file (new UUID). Some people on the Internet also say that directly modify the UUID value in auto.cnf, and then restart the slave library instance, this solution has not been tested.

C. Last_SQL_Errno: 1677

Last_SQL_Errno: 1677

Last_SQL_Error: Column 0 of table 'aa.heartbeat' cannot be converted from type' smallint' to type 'int (11)'

@ Shane Bester suggests slave_type_conversions=ALL_NON_LOSSY to solve this problem. We haven't paid attention to this parameter before, and we haven't set it up in our online environment.

Slave_type_conversions: field type conversion is performed on slave, because in some cases, the master / slave may have the same table name, but the number, type and order of fields are different, which may lead to a 1677 error. In this case, you need to set the corresponding parameters to deal with it.

MySQL version 5.5.3 supports attribute promotion features, including lossy conversion and lossless conversion

Parameter supported values:

1. ALL_LOSSY: int type can be converted into tinyint, which is truncated and harmful to the conversion.

2. ALL_NON_LOSSY: truncation is not allowed. The type range of slave is larger than master before it can be converted. After setting this parameter, slave will report an error if there is truncation.

3. Truncation occurs when the next two parameters are set.

4. It is not set, and the master and backup must be consistent.

You should pay attention to type conversion on slave:

Whether you use statement-based or row-based replication, the slave's copy of the table cannot contain more columns than the master's copy if you wish to employ attribute promotion.

Reference:

Http://dev.mysql.com/doc/refman/5.5/en/replication-features-differing-tables.html#replication-features-attribute-promotion

Add a slave to a host and report when it starts.

[ERROR] Slave SQL: Slave failed to initialize relay log info structure from the repository, Error_code: 1872

The reasons may be: 1. My.cnf does not specify relay_log,2 and does not have reset slave

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.