Mysql 两种主从复制方式

「爱情、让人受尽委屈。」 2021-09-21 06:16 384阅读 0赞

### 1，环境准备 ###

安装mysql

主：192.168.1.50  
从：192.168.1.51

#下载
    wget http://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
    
    #安装客户端
    yum -y install mysql57-community-release-el7-11.noarch.rpm
    
    #查看安装状态
    yum repolist enabled | grep mysql.*
    
    #安装mysql服务
    yum install mysql-community-server
    
    #重启mysql服务
    systemctl start mysqld.service
    
    #查看mysql服务状态
    systemctl status mysqld.service
    
    #查看初始密码
    grep "password" /var/log/mysqld.log
    
    #登录
    mysql -uroot -p
    
    #关闭复杂密码验证
    set global validate_password_policy=LOW;
    
    #关闭密码长度验证
    set global validate_password_length=6;
    
    #修改用户密码
    ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
    
    #超级管理员授权
    GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
    
    #刷新配置
    FLUSH PRIVILEGES;
    
    #开机自启服务
    systemctl enable mysqld
    systemctl daemon-reload

### 二，mysql基于日志点的复制 ###

**1，在主DB上**创建复制账号

create user repl@'192.168.1.%' identified by '123456';

2**，**授权

\#允许该用户访问主库和从库，这是一个全局权限，必须对所有数据库设置该权限

grant replication slave on *.* to repl@'192.168.1.%';

3**，****配置主数据库服务器**

vim /etc/my.cnf
    
    #开启二进制日志，并指定存储目录(mysql-bin)，如果未启动二进制日志，则重启后才能生效
    log_bin=mysql-bin
    
    #需要指定serverid，在复制集群中必须唯一，建议：server-id 可以用主机ip的后几段
    server-id=50
    
    binlog_format=row

4**，****配置从数据库服务器**

vim /etc/my.cnf
    
    log_bin=mysql-bin
     
    binlog_format=row
     
    server-id=51
     
    relay_log=mysql-relay-bin
     
    #log_slave_update=on
     
    # 安全配置参数，防止从写入
    read_only=on

relay\_log 启动了主从复制，该参数就会默认启动，但在默认情况下，参数名字是主机名字，如果因为某种原因更改了主机名的命名策略，这时在启动了从服务器的复制链路的就会报找不到原来的中继日志的错误，从而中断主从复制的链路，所以要更改中继日志的名字，这样就不会造成复制链路的中断了

log\_slave\_update=on 决定是否要把sql线程存放的中继日志记录到从服务器本机的二进制日志中，如果后续要做链路复制，要把该从服务器作为其他从的主服务器，该参数必须配置

**5，初始化从服务器的数据**

通过主数据库的备份，完成从数据库的初始化

备份方式有两种：

<1>mysql官方提供的工具mysqldump

使用该工具对mysql进行备份时，会把所有的数据库对象找出来存储为一个sql文件

\--single-transaction ：保证数据事务一致性，需要对数据库加锁，会造成阻塞，这样就会影响数据库的并发性，在使用频繁的系统中，使用这种方法会造成大量的阻塞

\--master-data参数，用来记录二进制日志偏移量的信息

\--混合使用表的话，则要加上local-tables参数

mysqldump  -u root -p  --single-transaction --master-data --triggers --routines --all-databases > all.sql
     
    将sql文件传到从库上
     
    scp all.sql root@192.168.1.51:/home
     
    从服务器导入数据
     
    mysql -uroot -p < all.sql

<2>xtrabackup –slave-info 热备工具。

使用innodb存储引擎是不会阻塞服务器的操作，非innodb的表同样会进行锁操作，所以建议只使用innodb的表

\--slave-info也可以记录主数据库二进制信息，以及日志数据偏移量的信息

> 注意：如果主从数据库版本一致，可以备份所有数据库，包括系统数据库，如果不一样，则只备份业务数据库即可，否则会造成未知的bug

6**，**启动复制链路  
主从服务器配置完成后，可以准备启动我们的复制链路了。需要在从服务器上操作

change master to master_host='192.168.1.50',master_user='repl',master_password='123456',MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=946;

最后这句可以在导出的文件中查找。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70][]

master\_host主服务器的ip地址

用于数据库复制的用户和密码

MASTER\_USER=’repl’，

MASTER\_PASSWORD=’PWD’,

备库要从主库的什么位置开始同步二进制日志的文件名和偏移量

MASTER\_LOG\_FILE=’MYSQL\_LOG\_FILE\_NAME’,

MASTER\_LOG\_POS=4;

从数据库查看复制链路状态（可以看到，复制链路已经好了，但是还没有启动）

show slave status \G

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 1][]

启动

start slave

可以看到，io进程和sql进程已经都启动起来了

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 2][]

从库使用show processlist 查看服务线程。两个关于复制有关的线程，一个IO线程，一个SQL线程。

数库使用show processlist 查看服务线程

主服务器启动了binlog\_dump线程，用于读取主数据库二进制日志信息的

> 如果出现连接不上主库服务器，需要关闭防火墙

service iptables stop

**7.验证复制效果：**

在主库执行。

1.创建一个表。

2.插入两条记录。

在从服务器上查询。

发现数据同步了。

**8，优缺点**

**优点：**

1.是mysql最早支持的复制技术，BUG相对较少。

2.对SQL查询没有任何限制。

3.故障处理比较容易。

**缺点：**

故障转移时重新获取新主的日志点信息比较困难。

指定偏移量错误的时候会可能出现问题。

### 三，mysql基于GTID的复制 ###

从MYSQL5.6 开始，mysql开始支持GTID复制。

**1，基于日志点复制的缺点**

从那个二进制日志的偏移量进行增量同步，如果指定错误会造成遗漏或者重复，导致数据不一致。

基于GTID复制：

1.  从服务器会告诉主服务器已执行的事务的GTID值。
2.  主库会告诉从哪些GTID事务没有被执行。

同一个事务在指定的从库执行一次。

**2，什么是GTID**

GTID即全局事务ID,器保证为每一个在主上提交的事务在复制集群中可以生成一个唯一的ID.

GTID=source\_id:transaction\_id

source\_id:是主库的server UUID，在数据目录的auto.cnf 文件中。

transaction\_id: 从1开始的一个序列。

**3，基于GTID复制的步骤**

在主DB服务器上建立复制帐号。在主库执行命令

create user repl@'192.168.1.%' identified by '123456';
    
    grant all privileges  on *.* to repl@'192.168.1.%' identified by '123456';

**4，配置主数据库服务器**

vim /etc/my.cnf
    
    log_bin=mysql-bin
    
    server_id=50
    
    gtid-mode=on
    
    #强制事务一致性，保证事务的安全
    #注意如果使用了基于事务点日志配置下面两种情况不能使用：
    #1.create table 。。select
    #2.在事务中使用create temporary table 建立临时表，使用关联更新事务表和非事务表。
    enforce_gtid_consistency
    
    #在从服务器记录主服务器发过来的修改日志（5.7之前必须有），增加了IO负载
    log-slave-updates=on

**5，配置从数据库服务器**

vim /etc/my.cnf
    
    server_id=51
    
    log_bin=mysql-bin
    
    relay_log=mysql-relay-bin
    
    gtid-mode=on
    
    enforce-gtid-consistency
    
    #建议配置
    read_only=on
    
    #保证从服务器数据安全性建议配置
    #从服务器连接主服务器的信息和中继日志存放咱 master_info，和relay_log中。
    master_info_repository=TABLE
    relay_log_info_repository=TABLE

**6.初始化从服务器数据**

#从主库导出数据
    mysqldump --single-transaction --master-data=2 --all-databases --triggers --routines --events -uroot -p  >all.sql
    
    #可以使用 scp 命令将数据从主库机器copy 到从库：
    scp all.sql root@192.168.31.101:/home
    
    #从库导入数据
    mysql -uroot -p  < all.sql

7**.****启动基于GTID的复制**

change master to master-host=’192.168.1.50’,master_user=’repl’,master_password=’123456’,master_auto_position=1

可能报错的情况：

>  @@GLOBAL.GTID\_PURGED can only be set when @@GLOBAL.GTID\_EXECUTED is empty

在命令行下执行命令：

reset master;

后重启mysql 服务。

启动复制

start slave;

查看状态

show slave status \G;

**8.测试同步**

1.在主库创建一张表，插入记录。

2.在从库查询验证是否正确，经验证配置正确。

### 四，常见设计复制拓扑 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 3][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 5][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 6][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 7][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 8][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 9][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 10][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 11][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 12][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70]: /images/20210920/fe0282d3ef0246b9a345880c7f0011ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 1]: /images/20210920/f4736d84bbfa4a45a7c38029a8e516aa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 2]: /images/20210920/685ebbec35eb4b338384523c0d61191f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 3]: /images/20210920/2069b5e33f4e4f389c257005e1513b2b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 4]: /images/20210920/f24cd2224b69488caff69aa5c59c7bf5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 5]: /images/20210920/9853e10891214de59c0bda078759063d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 6]: /images/20210920/2a115c58bfdb45a29ef8df18dbaccace.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 7]: /images/20210920/ff4aef227e654059afeec566c772093b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 8]: /images/20210920/74f00d86088a45ff8b732914def9f46b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 9]: /images/20210920/fdee47c0606740e29c8f05463515a206.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 10]: /images/20210920/81e4940b6f37485a9a7c565d3fdbaf98.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 11]: /images/20210920/87c1a54923784307b390d76b8445c59e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2VjaGl6YW8xODM5_size_16_color_FFFFFF_t_70 12]: /images/20210920/8eaf3e07d3e8489faae991e140be85af.png