Mysql GTID

2）修改从配置文件

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
gtid-mode=on
enforce-gtid-consistency=on
server-id=2
log-bin=slave-binlog
log-slave-updates=1
binlog-format=row
skip-slave-start=1

3）配置基于GTID的复制

//主服务器上创建复制账号
mysql> grant replication slave on *.* to ‘repl‘@‘192.168.56.23‘ identified by ‘repl123!‘;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

//在从服务器上启动同步（需先重启mysqld服务）
mysql>  change master to master_host=‘192.168.56.20‘,master_user=‘repl‘,master_password=‘repl123!‘,master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.04 sec)

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

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.56.20
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-binlog.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000003
                Relay_Log_Pos: 375
        Relay_Master_Log_File: master-binlog.000001
             Slave_IO_Running: Yes //此处为yes表示复制线程已启动
            Slave_SQL_Running: Yes //此处为yes表示复制线程已启动

4）验证复制效果

在主库上创建一个数据库liuzhenchao
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
5 rows in set (1.03 sec)

mysql> create database liuzhenchao;
Query OK, 1 row affected (0.00 sec)

在从库上查看是否同步
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| liuzhenchao        |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.07 sec)

Mysql GTID

标签：mit   数据流   ase   insert   启动   查看   span   try   force