扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
下文给大家带来MySQL5.7实现GTID功能的实验流程及结果，希望能够给大家在实际运用中带来一定的帮助，
创新互联是专业的肃南裕固族自治网站建设公司，肃南裕固族自治接单;提供做网站、成都做网站,网页设计,网站设计,建网站,PHP网站建设等专业做网站服务;采用PHP框架,可快速的进行肃南裕固族自治网站开发网页制作和功能扩展;专业做搜索引擎喜爱的网站,专业的做网站团队,希望更多企业前来合作!
MYSQL涉及的东西比较多，理论也不多，网上有很多书籍，今天我们就用创新互联在行业内累计的经验来做一个解答。
192.168.205.37: as master server
192.168.205.47: as slave server OS: centos 7 1810 with mini install
mysql-5.7.26-el7-x86_64.tar.gzGTID(global transaction ID)全局事务标识符，mysql5.6版本开始支持，GTID复制不像传统的复制方式（异步延复制、半同步复制）需要找到binlog和pos点，只需要知道master的IP、端口、账号、密码即可，开启GDIT后，执行change master to master_auto_position=1即可，它会自动寻找同步。我们使用mysql5.7开启两个server，一个为主另一个为从，测试GDIT功能。
[root@centos7 mysql]#tar xvf mysql-5.7.26-el7-x86_64.tar.gz -C /usr/local/ [root@centos7 local]#ln -s mysql-5.7.26-el7-x86_64/ mysql[root@centos7 local]#useradd -r -s /bin/false mysql[root@centos7 mysql]#echo 'PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@centos7 mysql]#echo $PATH [root@centos7 mysql]#mkdir /data/mysql
[root@centos7 mysql]#chown mysql:mysql /data/mysql[root@centos7 mysql]#yum install libaio[root@centos7 mysql]#mysqld --initialize --user=mysql --datadir=/data/mysql
2019-08-12T00:43:03.799485Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-08-12T00:43:04.007086Z 0 [Warning] InnoDB: New log files created, LSN=45790
2019-08-12T00:43:04.043130Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-08-12T00:43:04.100702Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 24ddb90b-bc9a-11e9-856e-000c2956e1ea.
2019-08-12T00:43:04.101693Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-08-12T00:43:04.102159Z 1 [Note] A temporary password is generated for root@localhost: Ia-ClrMga7L/[root@centos7 mysql]#cp -b /etc/my.cnf{,.bak}
[root@centos7 mysql]#rpm -qf /etc/my.cnf
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@centos7 mysql]#vi /etc/my.cnf
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock[root@centos7 mysql]#cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@centos7 mysql]#chkconfig --list
[root@centos7 mysql]#chkconfig --add mysqld
[root@centos7 mysql]#chkconfig --list
[root@centos7 mysql]#service mysqld start
Starting MySQL.Logging to '/data/mysql/mysql.log'.
SUCCESS! [root@centos7 mysql]#mysql -p"Ia-ClrMga7L/"
[root@centos7 mysql]#mysqladmin -uroot -p"Ia-ClrMga7L/" password centos
[root@centos7 mysql]#mysql -pcentosmysql> desc user;
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+---------------+-----------+-------------------------------------------+
| root | localhost | *128977E278358FF80A246B5046F51043A2B1FCED |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)[root@centos7 ~]#vi /etc/my.cnf
[mysqld]
server-id=37
log-bin
gtid_mode=ON
enforce_gtid_consistency
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@centos7 ~]#service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS! mysql> grant replication slave on *.* to repluser@'192.168.205.%' identified by 'centos';
Query OK, 0 rows affected, 1 warning (0.00 sec)[root@centos7 ~]#vi /etc/my.cnf
[mysqld]
server-id=47
gtid_mode=ON
enforce_gtid_consistency
datadir=/data/mysql
socket=/data/mysql/mysql.sock
log-error=/data/mysql/mysql.log
pid-file=/data/mysql/mysql.pid
[client]
socket=/data/mysql/mysql.sock
[root@centos7 ~]#service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS! [root@centos7 ~]#mysql -pcentos
mysql> CHANGE MASTER TO
-> MASTER_HOST='192.168.205.37',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.205.37
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: centos7-bin.000005
Read_Master_Log_Pos: 453
Relay_Log_File: centos7-relay-bin.000002
Relay_Log_Pos: 670
Relay_Master_Log_File: centos7-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yesmysql> create database db1;
Query OK, 1 row affected (0.01 sec)mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec看了以上关于MySQL5.7实现GTID功能的实验流程及结果，如果大家还有什么地方需要了解的可以在创新互联行业资讯里查找自己感兴趣的或者找我们的专业技术工程师解答的，创新互联技术工程师在行业内拥有十几年的经验了。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网