MySQL5.7-build master-slave replication based on GTID replication mode
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
< 2019-05-29.sql Enter password: Slave开始数据复制： mysql>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.
