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

1. What is GTID?

GTID (Global Transaction ID) is the number for a committed transaction and is a globally unique number

GTID is actually made up of UUID+TID. Where UUID is the unique identity of an MySQL instance. TID represents the number of transactions that have been committed on the instance and increases monotonously as the transaction commits

# check the uuid number of this database instance:

Root@localhost [(none)] > select @ @ server_uuid

+-+

| | @ @ server_uuid |

+-+

| | 83373570-fe03-11e6-bb0a-000c29c1b8a9 |

+-+

# you can also view uudi at the system level:

[root@Darren2 data] # cat / data/mysql/mysql3306/data/auto.cnf

[auto]

Server-uuid=83373570-fe03-11e6-bb0a-000c29c1b8a9

In # linux, random uuid,mysql can be generated by uuidgen, and random uuid,mysql can be generated by select uuid ()

Such as:

[root@Darren2 ~] # uuidgen

Eceac2d7-4878-429b-81ca-e6aea02b1739

Root@localhost [none)] > select uuid ()

+-+

| | uuid () |

+-+

| | bc959381-1c89-11e7-8786-000c29c1b8a9 | |

+-+

Limitations of GTID

(1) non-thing engine is not supported

(2) create table is not supported. Copy the select statement (the main database reports an error directly)

ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE. SELECT.

(3) A sql is not supported to update tables of both transaction engine and non-transaction engine.

(4) in a replication group, you must uniformly enable GTID or disable GTID.

(5) restart is required to enable GTID (5.7 online switching is supported)

(6) when GTID is enabled, the original traditional replication method is no longer used.

(7) create temporary table and drop temporary talbe statements are not supported (no error is reported, but there is no table)

(8) sql_slave_skip_counter is not supported

two。 Environment configuration

Masterslave database version 5.7.165.7.16IP192.168.91.18192.168.91.20serverid330618330620 port number 33063306

3. Profile parameter settin

(1) master:

Settings in the configuration file:

Server-id = 330618

Binlog_format = row

Log-bin = / data/mysql3306/logs/mysql-bin

# GTID

Gtid_mode=on

Enforce-gtid-consistency=on

(2) slave:

Settings in the configuration file:

Server-id = 330620

Binlog_format = row

Relay-log=relay-bin

Relay-log-index=relay-bin.index

Read_only = on

# the replication process will not start with the startup of the database

Skip_slave_start=1

# if this slave library has a slave library, you need to enable this parameter

Log_slave_updates=0

# GTID

Gtid_mode=on

Enforce-gtid-consistency=on

4. The main library creates users

Master:

To create a rep user:

Create user rep@'192.168.91.%' identified by '147258'

Grant replication slave on *. * to rep@'192.168.91.%'

Flush privileges

5. Backup restore initialization

(1) the master database backs up the database:

Mysqldump-uroot-p147258-- single-transaction-- master-data=2-A > / tmp/master.sql

Scp master.sql root@192.168.91.20:/tmp/

(2) restore the backup file maser.sql to slave:

Mysql-uroot-p147258

< master.sql #注意： 备份文件中有这么一条命令： SET @@GLOBAL.GTID_PURGED='83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10908'; 在还原的时候使用，表示从库还原之后GTID就会达到10908，在10908之前的事物不需要同步过来，从10909开始同步事物； 6.从库 master to （1）添加主库信息到从库slave： change master to master_host='192.168.91.18', master_port=3306, master_user='rep', master_password='147258', master_auto_position=1; （2）启动从库 root@localhost [(none)]>

Start slave

(3) View the information of the main database

Root@localhost [testdb] > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000006 | 1120 | f4b6894e-c7fd-11e6-aaf8-000c29aacb77:1-5 | |

+-+

(4) View the slave information

Root@localhost [(none)] > show slave hosts

+-+

| | Server_id | Host | Port | Master_id | Slave_UUID | |

+-+

| | 330621 | | 3306 | 330618 | 5af344c7-c861-11e6-ad80-000c290f28e2 |

| | 330620 | | 3306 | 330618 | 31ba9bcb-c861-11e6-ad7f-000c29cc71ad |

+-+

(5) View replication status

Slave:

Root@localhost [testdb] > show slave status\ G

7. test

Master:

Root@localhost [(none)] > use testdb

Root@localhost [testdb] > create table T1 (id int,name char (10))

Root@localhost [testdb] > insert into T1 values (1memoriaaaaaa'), (2recorder bbb')

Slave:

Root@localhost [testdb] > select * from T1

+-+ +

| | id | name |

+-+ +

| | 1 | aaa |

| | 2 | bbb |

+-+ +

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.