mysql8.0.17gtid方式实现主从同步

数据库的安装:

[root@node1 8.0.17]# rpm -ivh mysql-community-common-8.0.17-1.el7.x86_64.rpm
警告:mysql-community-common-8.0.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-8.0.17-1.e################################# [100%]
[root@node1 8.0.17]# rpm -ivh mysql-community-libs-8.0.17-1.el7.x86_64.rpm
警告:mysql-community-libs-8.0.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-libs-8.0.17-1.el7################################# [100%]
[root@node1 8.0.17]# rpm -ivh mysql-community-client-8.0.17-1.el7.x86_64.rpm
警告:mysql-community-client-8.0.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-client-8.0.17-1.e################################# [100%]
[root@node1 8.0.17]# rpm -ivh mysql-community-server-8.0.17-1.el7.x86_64.rpm
警告:mysql-community-server-8.0.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-server-8.0.17-1.e################################# [100%]
[root@node1 8.0.17]# rpm -ivh mysql-community-devel-8.0.17-1.el7.x86_64.rpm --force --nodeps
警告:mysql-community-devel-8.0.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-devel-8.0.17-1.el################################# [100%]
[root@node1 8.0.17]# systemctl start mysqld
[root@node1 8.0.17]# more /var/log/mysqld.log |grep password
2019-07-28T07:48:29.031474Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Z3(E!khsm_EE
[root@node1 8.0.17]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.17

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> alter user 'root'@'localhost' identified by 'kavl7kAkkle!';    登录成功之后必须先修改密码,才能使用。
Query OK, 0 rows affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)
环境:

master:

master配置文件:

[root@master 8.0.17]# grep -v '^#' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on

log_bin = binlog
[root@master 8.0.17]# systemctl restart mysql

[root@master 8.0.17]# mysql -uroot -pkavl7kAkkle!
mysql> create user 'repl'@'192.168.199.%' identified by 'kavl7kAkkle!';  建立同步的账号

mysql>grant replication slave on *.* to 'repl'@'192.168.199.%';
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
测试一下建立的账号是否可以在slave上登录(可以登录)
[root@node1 8.0.17]# mysql -urepl -h 192.168.199.6 -pkavl7kAkkle!
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.17 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

查看gtid是否开启:

mysql> show variables like "%gtid%";
+----------------------------------+------------------------------------------+
| Variable_name                    | Value                                    |
+----------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                       |
| enforce_gtid_consistency         | ON                                       |
| gtid_executed                    | c3a99d8b-b10e-11e9-9e44-000c297d60e3:1-7 |
| gtid_executed_compression_period | 1000                                     |
| gtid_mode                        | ON                                       |
| gtid_next                        | AUTOMATIC                                |
| gtid_owned                       |                                          |
| gtid_purged                      |                                          |
| session_track_gtids              | OFF                                      |
+----------------------------------+------------------------------------------+
9 rows in set (0.02 sec)
查看服务器的UUID:

mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| server_uuid   | c3a99d8b-b10e-11e9-9e44-000c297d60e3 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
查看主服务器的状态:

mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 |      878 |              |                  | c3a99d8b-b10e-11e9-9e44-000c297d60e3:1-7 |
+---------------+----------+--------------+------------------+------------------------------------------+

node1:

[root@node1 8.0.17]# grep -v '^#' /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
[root@node1 8.0.17]# systemctl restart mysqld

[root@node1 8.0.17]# mysql -uroot -pkavl7kAkkle!
mysql> change master to master_host='192.168.199.6',master_user='repl',master_password='kavl7kAkkle!';     指定主库IP,使用的用户,密码
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
查看slave状态:
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.199.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 878
               Relay_Log_File: node1-relay-bin.000003
                Relay_Log_Pos: 1086
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

测试gtid主从复制

在master上建立数据库slave上自动创建了。

 

在master上查看,可以看到slave的状态(主库的sever_id、从库的server_id、slave_UUID)

mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID                           |
+-----------+------+------+-----------+--------------------------------------+
|         2 |      | 3306 |         1 | 167eed9d-b10c-11e9-af77-000c2973f66b |
+-----------+------+------+-----------+--------------------------------------+

至此gtid主从复制搭建成功!!!!有什么问题欢迎留言指教。

posted @ 2019-07-28 18:46  winterforever  阅读(2280)  评论(0编辑  收藏  举报