GTID主从配置

GTID主从搭建

简介

• GTID与传统主从的区别:
  传统的主从是基于二进制日志文件的复制,必须配置唯一的服务器id,并且服务器的id主必须大于从,否则主设备将拒绝来自从设备的任何连接
  传统的主从复制从端，binlog是不用开启的，但是在GTID中从端的binlog是必须开启的，目的是记录执行过的GTID
  GTID不用像传统复制那样保证binlog的坐标准确，因为根本不需要binlog以及坐标,并且不在使用binlog+pos开启复制.而是使用master_auto_postion=1的方式自动匹配GTID断点进行复制.

环境
主服务器:192.168.149.133 有数据
从服务器:192.168.149.137 无数据
若都无数据,无需做备份
授权

给从库授权
mysql> create user 'roo'@'192.168.149.137' identified by 'xialuo123!';
Query OK, 0 rows affected (0.02 sec)

mysql> grant replication slave on *.* to 'roo'@'192.168.149.137';
Query OK, 0 rows affected (0.01 sec)

确保数据一致

对库锁表
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.01 sec)

全备主库
[root@host ~]# mysqldump -uroot -pxialuo123! --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

复制备份文件到从库
[root@host ~]# scp all.sql root@192.168.149.137:
root@192.168.149.137's password: 
all.sql                                                                            100%  854KB  26.7MB/s   00:00 

在从库恢复备份文件
[root@host ~]# mysql -uroot -pxialuo123! < all.sql      
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@host ~]# mysql -uroot -pxialuo123! -e 'select * from xkq.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | oo   |   20 |
+----+------+------+

配置主库

[root@host ~]# vim /etc/my.cnf 
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
datadir = /opt/mysql-data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql
skip-name-resolve

server_id=1     主库的server_id必须小于从库
gtid_mode=on

enforce_gtid_consistency=on
log_bin=master-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1

重启
[root@host ~]# systemctl restart mysqld

配置从库

[root@host ~]# vim /etc/my.cnf 
[mysqld]
datadir = /opt/mysql-data
basedir = /usr/local/mysql
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql-data/mysql.pid
user = mysql
skip-name-resolve

gtid_mode=on
enforce_gtid_consistency=on
server_id=2           从库的server_id必须大于主库
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row
skip_slave_start=1

重启
[root@host ~]# systemctl restart mysqld

配置主从复制

mysql> change master to
    -> master_host='192.168.149.133',
    -> master_user='roo',
    -> master_password='xialuo123!',
    -> master_port=3306,
    -> master_auto_position=1;

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.149.133
                  Master_User: roo
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: host-relay-bin.000002
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.000001
             Slave_IO_Running: Yes        这里必须为yes(注意防火墙要关闭)
            Slave_SQL_Running: Yes        这里必须为yes(注意防火墙要关闭)

测试

在主库中添加数据:
mysql> insert into student(id,name,age) values(2,'tom',30);
Query OK, 1 row affected (0.02 sec)

从:
mysql> use xkq;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from student;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | oo   |   20 |
|  2 | tom  |   30 |
+----+------+------+
2 rows in set (0.00 sec