| 1）mysql-master主数据库上的操作
在my.cnf文件中配置GTID主从复制
[root@mysql-master ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-master ~]# >/etc/my.cnf
[root@mysql-master ~]# cat /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 1
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
配置后，记得重启mysql服务
[root@mysql-master ~]# systemctl restart mysqld
登录mysql，并查看master状态， 发现多了一项"Executed_Gtid_Set "
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)
mysql> show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 317e2aad-1565-11e9-9c2e-005056ac6820 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
查看确认gtid功能打开
mysql> show global variables like '%gtid%';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery | ON |
| enforce_gtid_consistency | ON |
| gtid_executed | |
| gtid_executed_compression_period | 1000 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| session_track_gtids | OFF |
+----------------------------------+-------+
8 rows in set (0.00 sec)
查看确认binlog日志功能打开
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
授权slave复制用户，并刷新权限
mysql> grant replication slave,replication client on *.* to slave@'172.16.60.212' identified by "slave@123";
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql> show grants for slave@'172.16.60.212';
+-------------------------------------------------------------------------------+
| Grants for slave@172.16.60.212 |
+-------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'172.16.60.212' |
+-------------------------------------------------------------------------------+
1 row in set (0.00 sec)
再次查看master状态
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 622 | | | 317e2aad-1565-11e9-9c2e-005056ac6820:1-2 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
这里需要注意一下：
启动配置之前，同样需要对从服务器进行初始化。对从服务器初始化的方法基本和基于日志点是相同的，只不过在启动了GTID模式后，
在备份中所记录的就不是备份时的二进制日志文件名和偏移量了，而是记录的是备份时最后的GTID值。
需要先在主数据库机器上把目标库备份一下，假设这里目标库是kevin（为了测试效果，下面手动创建）
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> CREATE DATABASE kevin CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.02 sec)
mysql> use kevin;
Database changed
mysql> create table if not exists haha (id int(10) PRIMARY KEY AUTO_INCREMENT,name varchar(50) NOT NULL);
Query OK, 0 rows affected (0.27 sec)
mysql> insert into kevin.haha values(1,"congcong"),(2,"huihui"),(3,"grace");
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
把kevin库备份出来
[root@mysql-master ~]# mysqldump --single-transaction --master-data=2 --triggers --routines --databases kevin -uroot -p123456 > /root/kevin.sql
这里稍微注意下：
mysql5.6使用mysqldump备份时，指定备份的具体库，使用--database
mysql5.7使用mysqldump备份时，指定备份的具体库，使用--databases
[root@mysql-master ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-master ~]# cat /root/kevin.sql
-- MySQL dump 10.13 Distrib 5.7.24, for Linux (x86_64)
--
-- Host: localhost Database: kevin
-- ------------------------------------------------------
-- Server version 5.7.24-log
.............
.............
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='317e2aad-1565-11e9-9c2e-005056ac6820:1-5';
然后把备份的/root/kevin.sql文件拷贝到mysql-slave1从数据库服务器上
[root@mysql-master ~]# rsync -e "ssh -p22" -avpgolr /root/kevin.sql root@172.16.60.212:/root/
==============================================================
2）mysql-slave1从数据库上的操作
在my.cnf文件中配置GTID主从复制
与主服务器配置大概一致，除了server_id不一致外，从服务器还可以在配置文件里面添加："read_only＝on" ,
使从服务器只能进行读取操作，此参数对超级用户无效，并且不会影响从服务器的复制；
[root@mysql-slave1 ~]# cp /etc/my.cnf /etc/my.cnf.bak
[root@mysql-slave1 ~]# >/etc/my.cnf
[root@mysql-slave1 ~]# vim /etc/my.cnf
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
symbolic-links = 0
log-error = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid
#GTID:
server_id = 2
gtid_mode = on
enforce_gtid_consistency = on
#binlog
log_bin = mysql-bin
log-slave-updates = 1
binlog_format = row
sync-master-info = 1
sync_binlog = 1
#relay log
skip_slave_start = 1
read_only = on
配置完成后，记得重启mysql服务
[root@mysql-slave1 ~]# systemctl restart mysqld
接着将主数据库目标库的备份数据kevin.sql导入到从数据库里
[root@mysql-slave1 ~]# ls /root/kevin.sql
/root/kevin.sql
[root@mysql-slave1 ~]# mysql -p123456
.........
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> source /root/kevin.sql;
mysql> select * from kevin.haha;
+----+----------+
| id | name |
+----+----------+
| 1 | congcong |
| 2 | huihui |
| 3 | grace |
+----+----------+
3 rows in set (0.00 sec)
在从数据库里，使用change master 配置主从复制
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='172.16.60.211',master_user='slave',master_password='slave@123',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.26 sec)
mysql> start slave;
Query OK, 0 rows affected (0.02 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.16.60.211
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1357
Relay_Log_File: mysql-slave1-relay-bin.000002
Relay_Log_Pos: 417
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
................
................
Executed_Gtid_Set: 317e2aad-1565-11e9-9c2e-005056ac6820:1-5
Auto_Position: 1
由上面可知，mysql-slave1节点已经和mysql-master节点配置了主从同步关系
3） mysql-master主数据库上进行状态查看和测试测试插入
mysql> show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 1357 | | | 317e2aad-1565-11e9-9c2e-005056ac6820:1-5 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql> show slave hosts;
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 2 | | 3306 | 1 | 2c1efc46-1565-11e9-ab8e-00505688047c |
+-----------+------+------+-----------+--------------------------------------+
1 row in set (0.00 sec)
mysql> insert into kevin.haha values(4,"beijing"),(5,"hefei"),(10,"xihu");
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> delete from kevin.haha where id<4;
Query OK, 3 rows affected (0.10 sec)
mysql> select * from kevin.haha;
+----+---------+
| id | name |
+----+---------+
| 4 | beijing |
| 5 | hefei |
| 10 | xihu |
+----+---------+
3 rows in set (0.00 sec)
4）mysql-slave1从数据库上查看
mysql> select * from kevin.haha;
+----+---------+
| id | name |
+----+---------+
| 4 | beijing |
| 5 | hefei |
| 10 | xihu |
+----+---------+
3 rows in set (0.00 sec)
发现mysql-slave1从数据库已经将新插入的数据同步过来了，由此，基于GTID的主从同步复制关系已经正常部署完成了！
|
本文详细记录了在Centos7.5上部署MySQL5.7的全过程，包括基于GTID的主从复制、并行复制和半同步复制的配置，以及使用ProxySQL实现读写分离。文中介绍了GTID主从复制的优势，如避免数据不一致，简化故障转移，以及并行复制的实现原理和MySQL 5.7的组提交并行复制。此外，还涉及了半同步复制的两种模式及其优缺点，并给出了添加额外从库实现“一主两从”架构的步骤，最后通过ProxySQL配置了读写分离。
最低0.47元/天 解锁文章
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
