MySQL Daily problem handling

1. General exception only need to skip one step to recover

>slave stop;

>set GLOBAL sql_slave_skip_counter = 1;

>slave start;

2. The last Bin-log log of the main library is restored when the power failure causes the master/slave to be unable to synchronize

On the main library server, Mysqlbinlog mysql-bin.xxxx > Binxxxx.txt

Tail-n 100000 binxxxx.txt > Tail-binxxxx.txt

Vim Tail-binxxxx.txt Open a tail-binxxxx.txt file to find the last Postion value

Then on the From library, change the host to the corresponding correct value

>slave stop;

>change Master to master_host= ' IP ', master_user= ' username ', master_password= ' password ', master_log_file= ' Mysql-bin.xxxx ', master_log_pos=xxxx;

>slave start;

>show slave status\g;

3. The primary key conflict, the existence of the table and other error codes such as 1062,1032,1060, can be specified in the MySQL master configuration file

Skipping this type of exception and continuing with the next SQL synchronization can also avoid many of the exception interrupts of master-slave synchronization

[Mysqld]

Slave-skip-errors = 1062,1032,1060

The basic process for MySQL replication is as follows:

1. Slave the above IO line thread attached to master and request a specified location from the specified log file (or from

Log content after the beginning of the log);

2. Master receives a request from an IO thread from slave, the IO thread that is responsible for replication is based on the requested

The request information reads the log information after the specified log location and returns the IO thread to the slave end. Return letter

In addition to the information contained in the log, the binary log on the master side of the returned information is included in the

The name of the file and the location in binary Log;

3. After the Slave IO thread receives the information, it writes the received log content to the Slave end of the

Relay Log file (mysql-relay-bin.xxxxxx) at the end, and will read to the master end of the Binlog

The filename and location of the Master-info file, so that it will be clear at the next read

High Speed master "I need to start from somewhere in the Bin-log log content, please send me"

4. Slave's SQL thread detects the new additions to the relay log and resolves the log text immediately

Executable query statements on the master side, and the contents of the

Line these query. In this way, the same query is actually executed on the master and slave sides,

The data at both ends is exactly the same.

In fact, MySQL had thought of this for a long time, so in MySQL's binary Log

The current MySQL Server-id is recorded, and this parameter is also when we build MySQL Replication.

Must be explicitly specified, and the Server-id parameter values of master and slave are inconsistent to make MySQL

Replication build success. Once you have the value of Server-id, MySQL can easily tell if a change is

From which MySQL Server was originally generated, so it is easy to avoid the case of cyclic replication. Moreover, if

We do not open the option of recording slave binary log (--log-slave-update) when the MySQL root

This will not record changes in the copy process to binary Log, not to worry about the possibility of cyclic replication

The shape.