Mysql master-slave replication, mysql master-slave

Mysql master-slave replication, mysql master-slave

Master-slave replication: There are two ways to start master-slave replication from MySQL5.6: Log-based (binlog); GTID-based (global transaction identifier );

1. Log-based master-slave replication principle:

Mysql Replication is an asynchronous Replication process. It replicates another Mysqlinstance (called slave) from a Mysql instace (we call it the Master ), the entire replication process between the Master and slave is composedThree threads to completeTwo threads (SQL thread and IO thread) are on the Slave side, and the other thread (IO Thread 0 is on the Master side.) is to implement MysqlReplication.
First, you must enable the Binary Log on the Master end (that is, enable the bin-log function). Otherwise, in fact, the whole replication process is that Slave obtains binlog logs from the Master end, and then the slave end executes all the records and operations in sequence;

The basic process of Mysql replication is as follows:
1> the IO thread on the Slave connects to the Master, and requests the log content after the specified location of the specified log file (or from the beginning of the log;
2> after the Master receives the request from the Slave IO thread, it returns the log information to the Slave IO thread by specifying the log location based on the request information, besides all information contained in the log, the returned message also contains the Binary log (Binary) file name and Binary log (Binary) location of the returned information in the Master segment.
3> after the Slave IO thread receives the information returned by the master, it writes the received log content to the Relay log file of the slave end at one time, and record the file and location of the bin-log on the Master end to the master-info file, so that the next read can clearly tell the Master that I need to read from a bin-lo
Send me the log Content from where g starts;
4> after the Slave SQL thread detects that the Relay Log center has added content, it will immediately parse the content in the Master binary Log file and execute the Query statement in it;

2Master/Slave Environment:

Master IP: 192.168.1. 106
Slave IP: 192.168.1. 110
Install Mysql on Master and Slave
3Master operation

#: Vim/etc/my. cnf
# Comment out this line in log_slave_updates
Server-id = 1 change the id number to 1
# Mysql-uroot-p-h localhost
Mysql> grant replication slave on *. * to 'admin' @ '192. 168.1.106 'identified by '123'; # authorize 13slave to copy
Mysql> flush tables with read lock;
Mysql> show master status;

Mysql> unlock tables;

4Slave (Slave) Operation

#: Vim/etc/my. cnf
Server-id = 2 # Change server_id to 2
# Mysql-uroot-p-h localhost
Mysql> stop slave;
Mysql> change master
Master_host = '1970. 168.100.160 ',
Master_user = 'admin ',
Master_password = '000000 ',
Master_log_file = 'masterlog. 100 ',
Master_log_pos = 120;
Mysql> start slave;
Mysql> show slave status \ G;

Troubleshooting:
If the show slave status \ G; error is reported because the correct port number is not found, you can directly add a definition here.
Master_port = 3306,

Check slave Server replication status: troubleshooting:
If the show slave status \ G; error is reported because the correct port number is not found, you can directly add a definition here.
Master_port = 3306,

Check the slave server copy function status:

Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. Otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).

5Test Data Synchronization

1> Create a database table on the Master node and insert a data entry.
# Mysql-uroot-p-h localhost
Mysql-> create database zytest;
Mysql-> create table aa (
User_id int,
User_name varchar (10 ));
Mysql-> inster into aa values ('11', 'alvin ');

2> View synchronization status from top
# Mysql-uroot-p-h localhost
Mysql-> show slave status \ G;
Mysql-> show databases;

6Common troubleshooting

1> generally, you only need to skip one step to recover an exception.
Simulate fault, before creating master-slave replication. create a CC database on the master database. After enabling master-slave replication and the slave IO is YES, create a gongda database on the master database to check whether the slave database is synchronized successfully?
If it succeeds, simulate the fault and delete the CC on the master node. Does the slave status start to report an error ??
The solution is as follows:
> Stop slave;
> Set global SQL _slave_skip_counter = 1; skip this step if an error occurs, and continue to restore the instance.
> Start slave;

2> when the master and slave databases cannot be synchronized due to power failure, the last bin-log of the master database is restored.
Mysqlbinlog mysql-bin.xxxx> binxxxx.txt on the master database server
Tail-n 100000 binxxxx.txt> tail-binxxxx.txt
Vim tail-binxxxx.txt open tail-binxxxx.txt file to find the last postion Value
Then, on the slave database, change host to the correct value.
> Stop slave;
> Change master to master_host = 'IP', master_user = 'username', master_password = 'Password ',
Master_log_file = 'mysql-bin. xxxx', master_log_pos = xxxx;
> Start slave;
> Show slave status \ G;

3> primary key conflict, table existence, and other error codes, such as 1060, can be specified in the mysql main configuration file
Skip this type of exception and continue the next SQL synchronization. This can also avoid exceptions and interruptions of many master-slave synchronization tasks.
[Mysqld]
Slave-skip-errors = route 1060

================================== Common error Scenario Description and Solution
Copy error skipped manually in GTID replication Mode

1. Scenario Description:
Server A (Master)
Server B (Slave)
Create A database on server A called aatest
Check whether synchronization is performed on server B,

2. Error Scenario Description:
Delete aatest from server B (Slave.
Delete aatest from server A (Master)
Use show slave status \ G on server B at a time. The SQL thread is blocked.

3. Start to try to recover. Use the Skip method of binlog to try to skip the restoration (not solved, but a prompt is provided)　　　　

When a replication error occurs in the standby database, the traditional method of skipping the error is to set SQL _slave_skip_counter and then START SLAVE.However, if GTID is enabled, the setting fails:

Mysql> set global SQL _slave_skip_counter = 1;
ERROR 1858 (HY000): SQL _slave_skip_counter can not be set when the server is running with @ GLOBAL. GTID_MODE = ON. instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
The error message indicates that an empty transaction can be generated to skip the wrong transaction.
We manually generate a slave database copy error:
Last_ SQL _Error: Error 'unknown table 'test. t1 "on query. Default database: 'test'. Query: 'drop TABLE 't1'/* generated by server */'
Check the binlog. The GTID corresponding to this DDL is 7a07cd08-ac1b-11e2-9fcf-0010184e9e08: 1131.

 4. Skip the transaction in GTID mode to solve the problem.

4.1. Step 1:
Run the following command on the slave database:
Mysql> show slave status \ G; check
Master_Log_File: mysql-bin.000003 here we get the binlog log file for server A (Master)

4.2. Step 2:
Run the following command on the master database:
# Mysqlbinlog mysql-bin.000003
Find the context of the action of the drop database aatest statement,
We can see two sets @ SESSION. GTID_NEXT.

(1) The first one is on the drop database aatest: SET @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 31
(2) The second is under drop database aatest: SET @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 32
<We will copy 2nd sets @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 32>
#160817 1:08:06 server id 1 end_log_pos 5484 CRC32 0x961_ea GTID [commit = yes]

SET @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 31 '/*! */;
# At 5484
#160817 1:08:06 server id 1 end_log_pos 5565 CRC32 0x96ff64da Query thread_id = 44exec_time = 0 error_code = 0

Set timestamp = 1471367286 /*! */;
Drop database aatest
/*! */;
# At 5565
#160817 1:08:22 server id 1 end_log_pos 5613 CRC32 0x14d35459 GTID [commit = yes]
SET @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 32 '/*! */;

4.3. Step 3:
Run the following command on the slave database:
Mysql> select @ GTID_NEXT first queries the value of GTID_NEXT
Mysql> stop slave; stop slave first
Query OK, 0 rows affected (0.00 sec)
Mysql> SET @ SESSION. GTID_NEXT = '59ebdf10-63c8-11e6-9d86-000c2916dc3f: 32'; copy and paste the copy in step 2 to run the command,
Query OK, 0 rows affected (0.00 sec)
Mysql> BEGIN; COMMIT; to an empty transaction, and then COMMIT.
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Mysql> set session GTID_NEXT = AUTOMATIC; reset the AUTOMATIC commit transaction GTID.
Query OK, 0 rows affected (0.00 sec)

Mysql> start slave;
Check the show slave status and you will find that the error transaction has been skipped. The principle of this method is very simple. The GTID generated by a null transaction is added to GTID_EXECUTED, which is equivalent to telling the slave database that the transaction corresponding to this GTID has been processed;