Mysql master-slave replication and switchover based on Gtid

Reference

Http://imysql.com/tag/gtid

http://mysqllover.com/?p=594

Mysql master-slave replication and switchover based on Gtid

First, master-slave replication configuration

Content configuration in my.cnf for two MySQL services

[Mysqld]

#从复制数据库表设置

Replicate-wild-ignore-table = mysql.%,information_schema.%,innodb.%,innodb_log.%,performance_schema.%,test.%,tmp .% #复制时忽略数据库及表

#my. CNF Open Gtid

Log_slave_updates # indicates that it can be used as the primary

Gtid_mode = On

Enforce-gtid-consistency = True

10.123.2.128 my.cnf

[Mysqld]

server_id=10128 # All MySQL services are kept different

10.123.2.165 my.cnf

[Mysqld]

server_id=10165 # All MySQL services are kept different

# Verify that Gtid is turned on

mysql> SHOW VARIABLES like '%gtid% ';

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

| variable_name | Value |

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

| enforce_gtid_consistency | On |

|           gtid_executed | |

| Gtid_mode | On |

| Gtid_next | AUTOMATIC |

|           gtid_owned | |

|           gtid_purged | |

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

# View the master-slave synchronization of the current MySQL service Uuid,gtid, most of the features are based on Server_uuid

mysql> SHOW VARIABLES like '%server_uuid% ';

ip:10.235.2.128

Server_uuid:aad2226a-6ed3-11e4-9ae0-080027cdcda2

ip:10.235.2.165

Server_uuid:68a061ee-b013-11e4-845a-080027a36fd3

Add Zabbix_repl user sync permissions to 10.235.2.128, 10.235.2.165 two MySQL services respectively

mysql> Grant Replication client,replication slave on * * to ' zabbix_repl ' @ '% ' identified by ' 123456 '; flush privileges;

The 10.235.2.165 MySQL service sets the main MySQL information, where the master_auto_position=1 parameter automatically determines where the data is synchronized from the main

mysql> stop Slave;

mysql> Change Master to master_host= ' 10.235.2.128 ', master_user= ' Zabbix_repl ', master_password= ' 123456 ', master_ PORT = 3306,master_connect_retry = 60,master_auto_position=1;

mysql> start slave;

Mysql> Show Slave status \g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.235.2.128

Master_user:zabbix_repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:551

relay_log_file:relay-log.000002

relay_log_pos:761

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

...

...

...

master_server_id:10128

Master_uuid:aad2226a-6ed3-11e4-9ae0-080027cdcda2 # Server_uuid of the master database

Master_Info_File:mysql.slave_master_info

sql_delay:0

Sql_remaining_delay:null

Slave_sql_running_state:slave have read all relay log; Waiting for the slave I/O thread to update it

master_retry_count:86400

Master_bind:

Last_io_error_timestamp:

Last_sql_error_timestamp:

MASTER_SSL_CRL:

Master_ssl_crlpath:

Retrieved_gtid_set:aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-2 # Logs The Gtid of the relay log from master to the location of the Binlog log, by Master_uuid: Transaction ID Composition

Executed_gtid_set:68a061ee-b013-11e4-845a-080027a36fd3:1-3, # Native Binlog execution Location Gtid, composed of Server_uuid: Transaction ID

Aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-2 # Gtid of binlog execution location on master service, consisting of Master_uuid: Transaction ID

Auto_position:1

Adding data tests to 10.235.2.128 's main MySQL

mysql> CREATE DATABASE test2;

Mysql> Show Master Status \g

1. Row ***************************

file:mysql-bin.000001

position:1014

binlog_do_db:

binlog_ignore_db:

Executed_gtid_set:aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

Check again 10.235.2.165 sync status from MySQL, Sync normal

mysql> show databases;

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

| Database |

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

| Information_schema |

| InnoDB |

| Innodb_log |

| MySQL |

| Performance_schema |

| Test |

|  Test2 | # newly added test libraries

| TESTDB1 |

| TMP |

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

Mysql> Show Slave status \g

1. Row ***************************

...

...

Slave_io_running:yes

Slave_sql_running:yes

...

...

Master_uuid:aad2226a-6ed3-11e4-9ae0-080027cdcda2

...

...

Retrieved_gtid_set:aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

Executed_gtid_set:68a061ee-b013-11e4-845a-080027a36fd3:1-3,

Aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

Auto_position:1

Second, master-slave switching

In the 10.235.2.165 host

mysql> stop Slave;

In the 10.235.2.128 host

mysql> stop Slave;

mysql> Change Master to master_host= ' 10.235.2.165 ', master_user= ' Zabbix_repl ', master_password= ' 123456 ', master_ PORT = 3306,master_connect_retry = 60,master_auto_position=1;

mysql> start slave;

Mysql> Show Slave status \g

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:10.235.2.165

Master_user:zabbix_repl

master_port:3306

Connect_retry:60

master_log_file:mysql-bin.000001

read_master_log_pos:1529

relay_log_file:relay-log.000002

relay_log_pos:888

relay_master_log_file:mysql-bin.000001

Slave_io_running:yes

Slave_sql_running:yes

...

...

Master_uuid:68a061ee-b013-11e4-845a-080027a36fd3

...

...

Retrieved_gtid_set:68a061ee-b013-11e4-845a-080027a36fd3:1-3

Executed_gtid_set:68a061ee-b013-11e4-845a-080027a36fd3:1-3 # Generally the same as the Gtid of master MySQL 10.235.2.165mysql

Auto_position:1

Mysql>

In the 10.235.2.165 host

Mysql> Show Master Status \g

1. Row ***************************

file:mysql-bin.000001

position:1529

binlog_do_db:

binlog_ignore_db:

Executed_gtid_set:68a061ee-b013-11e4-845a-080027a36fd3:1-3, # 10.235.2.165 MySQL's Gtid

Aad2226a-6ed3-11e4-9ae0-080027cdcda2:1-4

Test delete data in 10.235.2.165

mysql> drop Database test2;

Query OK, 0 rows affected (0.12 sec)

In 10.235.2.128 view data is already synchronized (no database test2 already)

mysql> show databases;

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

| Database |

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

| Information_schema |

| InnoDB |

| Innodb_log |

| MySQL |

| Performance_schema |

| Test |

| TESTDB1 |

| TMP |

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

Here, the host switchover is complete.

Third, primary master replication

Principle:

1, both nodes must have to create a user with Copy permissions # Reference A, master-slave replication Add permissions users

2. Both sides have to enable both the relay log and the binary log

3, in order to ensure that the field with automatic growth function can correctly generate ID, you need to configure two nodes using even or odd ID number

4, each other to configure their own master node # Reference one, master-slave replication change Master to ...

Add the following related configuration in my.cnf

10.235.2.165 MY.CNF Configuration

[Mysqld]

Auto-increment-offset = 1 #自增id为奇数

Auto-increment-increment = 2 #自增id的增量

Log_slave_updates # indicates that it can be used as the primary

10.235.2.128 MY.CNF Configuration

[Mysqld]

Auto-increment-offset = 2 #自增id为偶数

Auto-increment-increment = 2 #自增id的增量

Log_slave_updates # indicates that it can be used as the primary

Iv. exceptions encountered by Gtid master-slave replication

Error 1:

When there is an error in the repository replication, the traditional way to skip the error is to set Sql_slave_skip_counter and then start slave.

However, if Gtid is turned on, the failure will be set:

mysql> Set Global sql_slave_skip_counter = 1;

ERROR 1858 (HY000): Sql_slave_skip_counter can Not was set when the server is running with @ @GLOBAL. Gtid_mode = on. Instead, for each transaction so want to skip, generate a empty transaction with the same GTID as the transaction

The error message tells us that the wrong transaction can be skipped by generating an empty transaction.

We manually generated a copy of the repository error:

Last_sql_error:error ' Unknown table ' test.t1 ' on query. Default database: ' Test '. Query: ' DROP TABLE ' t1 '/* generated by server */'

In view Binlog, the DDL corresponds to a gtid of 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131

Execute on the standby:

Mysql> STOP SLAVE;

Query OK, 0 rows Affected (0.00 sec)

mysql> SET SESSION gtid_next = ' 7a07cd08-ac1b-11e2-9fcf-0010184e9e08:1131 ';

Query OK, 0 rows Affected (0.00 sec)

Mysql> BEGIN; COMMIT;

Query OK, 0 rows Affected (0.00 sec)

Query OK, 0 rows Affected (0.00 sec)

mysql> SET SESSION gtid_next = AUTOMATIC;

Query OK, 0 rows Affected (0.00 sec)

Mysql> START SLAVE;

If you look at show slave status again, you will see that the error transaction has been skipped. The principle of this method is simple, the gtid of the empty transaction is added to the gtid_executed, which is equivalent to telling the repository that the Gtid corresponding transaction has been executed.

This article comes from the "Overwhelmed ops" blog, so be sure to keep this source http://osrun.blog.51cto.com/608651/1613425

Mysql master-slave replication and switchover based on Gtid