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

Environment:

MySQL5.7.24 version

CentOS release 6.5

Note:

MySQL5.7 version Slave can not enable binlog, which can save this part of disk I binlog consumption, while MySQL5.6 version must enable binlog, because GTID information needs to be stored in binlog (log_slave_updates). Only when binlog is enabled can you use the GTID feature. The MySQL5.7 version records GITD information (mysql.gtid_executed) through GTID system tables, and inserts GTID information into the table when each transaction commits

Master configuration:

[root@master ~] # cat / etc/my.cnf

Server_id=1

Gtid_mode=on

Enforce_gtid_consistency=on

Log_bin=/var/lib/mysql/binlog

Binlog_format=row

Character_set_server=utf8

[root@master ~] # service mysqld restart

Slave configuration:

[root@slave ~] # cat / etc/my.cnf

Server_id=2

Gtid_mode=on

Enforce_gtid_consistency=on

Binlog_format=row

Relay_log=/var/lib/mysql/relaylog

Replicate_do_db=edusoho_e

Character_set_server=utf8

[root@slave ~] # service mysqld restart

Master:

View the current binlog situation:

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000001 | 154 | |

+-+

1 row in set (0.00 sec)

Authorized replication connection user:

Mysql > grant replication slave on *. * to repliter@'192.168.32.2' identified by PASSWORD'* 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'

Query OK, 0 rows affected, 2 warnings (0.00 sec)

Mysql > show warnings

+- -- +

| | Level | Code | Message | |

+- -- +

| Warning | 1287 | 'IDENTIFIED BY PASSWORD' is deprecated and will be removed in a future release. Please use IDENTIFIED WITH AS instead |

| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. | |

+- -- +

2 rows in set (0.00 sec)

Create a statistic library:

Mysql > create database statistic

Query OK, 1 row affected (0.01sec)

Create the statistic.t1 table:

CREATE TABLE `statistic`.`t1` (

`id`INT (11) UNSIGNED NOT NULL AUTO_INCREMENT

`xname`VARCHAR (20) NOT NULL DEFAULT''

`address`CHAR (20) NOT NULL DEFAULT''

`sex` TINYINT (1) NOT NULL DEFAULT'1'

`hobby` VARCHAR (30) NOT NULL DEFAULT''

`age`TINYINT (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

Create the edusoho_ e library:

Mysql > create database edusoho_e

Query OK, 1 row affected (0.01sec)

Create the edusoho_ e.t1 table:

CREATE TABLE `edusoho_ e`.`t1` (

`id`INT (11) UNSIGNED NOT NULL AUTO_INCREMENT

`xname`VARCHAR (20) NOT NULL DEFAULT''

`address`CHAR (20) NOT NULL DEFAULT''

`sex` TINYINT (1) NOT NULL DEFAULT'1'

`hobby` VARCHAR (30) NOT NULL DEFAULT''

`age`TINYINT (2) DEFAULT '18'

PRIMARY KEY (`id`)

KEY `idx_ name` (`xname`)

) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `statistic`.`t1` (`xname`, `address`, `hobby`) VALUES ('statistic',' Beijing', 'Games')

INSERT INTO `edusoho_ e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e',' Shanghai', 'Development')

View the current binlog situation:

Mysql > show master status

+-+

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

+-+

| | mysql-bin.000001 | 2443 | c13c1b45-2741-11e9-abb0-000c29b85ea6:1-7 |

+-+

1 row in set (0.00 sec)

Prepare to replicate data:

[root@master ~] # mysqldump-uroot-p-B edusoho_e > `date +% F`.sql (check your own help for warning information)

Enter password:

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass-- set-gtid-purged=OFF. To make a complete dump, pass-all-databases-triggers-routines-even

Slave imports replication data:

[root@slave] # mysql-uroot-p

Change master to master_auto_position=1,master_host='192.168.32.3',master_port=3306

Query OK, 0 rows affected (0.04 sec)

Mysql > start slave user='repliter' password='123456'; (scrolls the relay log log file)

Query OK, 0 rows affected, 1 warning (0.01 sec)

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Retrieved_Gtid_Set:

Executed_Gtid_Set: c13c1b45-2741-11e9-abb0-000c29b85ea6:1-7

Auto_Position: 1

At this point, the master-slave replication of MySQL5.7 based on GTID mode has been built. If you are in a MySQL5.6 environment, please refer to MySQL5.6 's master-slave replication build based on GTID mode, and of course, there are some common replication problems that need to help you.

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.