在阿里云Centos7配置Mysql主从同步的那些事儿

秒速五厘米 2023-03-13 13:07 122阅读 0赞

### 目录 ###

*   *  相关知识储备
     *  一、操作步骤
     *  二、开始配置
     *  三、配置完主从库后，将主库数据同步至从库（主库已有数据）
     *  四、如果从库状态显示主从同步出现异常

在正式开始之前，我们先要配置好阿里云Centos7的远程服务器，具体教程请移步此链接：  
[Centos7安装MySQL5.7，亲测可用][Centos7_MySQL5.7]

--------------------

今天总结一下利用**MySQL**主从配置，实现读写分离，减轻数据库压力。  
明确目的，部署**mysql**集群，采用一主一从的策略，写入操作使用主库，从库实时同步主库的数据，从库负责读取的业务，从而完成读写分离的目的。

## 相关知识储备 ##

**mysql**主从同步的原理很简单：

*  从库生成两个线程，一个**I/O**线程，一个**SQL**线程；
 *  **I/O**线程去请求主库 的**binlog**（二进制日志），并将得到的**binlog**日志写到从库本地的**relay log**（中继日志） 文件中；
 *  主库会生成一个**log** **dump**线程，用来给从库的**i/o**线程传递**binlog**日志；
 *  **SQL** 线程，会读取**relay** **log**文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。
 *  最后**I/O** 线程和**SQL** 线程将进入睡眠状态，等待下一次被唤醒。  
    ![主从服务器][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3_size_16_color_FFFFFF_t_70]  
    **Mysql**支持哪些复制操作：

1.  基于语句的复制: 在主服务器执行SQL语句，在从服务器执行同样语句。

> 注：MySQL默认采用基于语句的复制，效率较高。一旦发现没法精确复制时, 会自动选基于行的复制。

1.  基于行的复制: 把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从mysql5.0开始支持
2.  混合类型的复制: 默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

**Mysql**的主从复制解决的问题：

*  数据分布 (Data distribution )
 *  负载平衡(load balancing)
 *  据备份(Backups) ，保证数据安全
 *  高可用性和容错行(High availability and failover)
 *  实现读写分离，缓解数据库压力

## 一、操作步骤 ##

按照原理，我们开启mysql主从复制，我们大体需要做以下操作：

1.  开启**master**的二进制日志
2.  开启**slave**的二进制日志
3.  将**slave**指向**master**
4.  开始复制

## 二、开始配置 ##

进入master服务器

修改mysql配置文件 `vim /etc/my.cnf`，加入如下配置：

server-id=1
    innodb_flush_log_at_trx_commit=2
    sync_binlog=1
    log-bin=mysql-bin-1

配置说明：

#设置主服务 的ID (id可以自己随便设置但是要保证和slave的id不一样)
    server-id=1
    
    #设为1当然是最安全的，但性能也是最差的（相对其他两个参数而言，但不是不能接受）。
    #如果对数据一致性和完整性要求不高，完全可以设为2，
    #如果只最求性能，例如高并发写的日志服务器，设为0来获得更高性能
    innodb_flush_log_at_trx_commit=2
    
    #开启binlog 志同步功能
    sync_binlog=1
    
    #binlog 日志文件名
    log-bin=mysql-bin-200
    
    #这个表示只同步某个库 (如果没有此项，表示同步所有的库)
    binlog-do-db=xxxx

保存后，重启**mysql**

systemctl restart mysqld

进入**mysql**命令行 `mysql -uroot -p`你的密码

输入授权命令

GRANT REPLICATION SLAVE ON *.* to 'yonghuming'@'%' identified by 'nidemima';

意思是所有**slave**都可以通过账号**yonghuming**和密码**nidemima**来同步master的数据

然后查看**master**的状态：

show master status;

![查看主机状态][20200513222132695.jpg]  
把**file**列和**Position**列记录下来，一会配置**slave**要用到

此时**Master**的配置已经搞定，登录一下从机(**slave**)

同理修改**slave**服务器的**mysql**配置 `vim /etc/my.cnf`加入下面的配置，需要注意的是**server-id**不要和**master**一样。

进入**slave**服务器

server-id=201 
    innodb_flush_log_at_trx_commit=2 
    sync_binlog=1 
    log-bin=mysql-bin-201

保存后重启服务 `systemctl restart mysqld`

进入**mysql**命令行`mysql -uroot -p`\+你的密码

输入命令：

change master to master_host='你的主机IP',master_user='yonghuming' ,master_password='nidemima', master_log_file='mysql-bin.000002' ,master_log_pos=154

命令说明：
    
    master_host: 主机的ip
    
    master_user : 主机授权的用户.
    
    master_password : 主机授权时候填写的密码
    
    master_log_file : 主机show master status;中的File
    
    master_log_pos: 主机show master status;中的Position.

输入命令启动**slave**

mysql> start slave;

可以查看slave的状态：

show slave status\G;

能够查看到下图的状态，说明主从配置成功！  
![mysql主从配置][mysql]  
然后我们就可以测试一下对**master**进行写入，看看**salve**是否可以同步数据了。  
![主库添加数据，从库查看情况][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3_size_16_color_FFFFFF_t_70 1]  
当然了，**mysql**的读写分离主从配置并不是万能的，根据不同的应用场景选择不同的策略，**MySQL**的主从复制功能有一定的延迟性，如果对数据实时一致性的要求比较高的场景不推荐使用。

## 三、配置完主从库后，将主库数据同步至从库（主库已有数据） ##

上面的步骤配置完毕后，退出数据库，在主数据库所在的服务器中执行：

mysql> flush tables with read lock;

这一步的目的是锁定主数据库，只允许读取不允许写入，这样是防止备份过程中或备份完成之后有新数据插入，导致备份数据和主数据数据不一致。

锁定后，退出主数据库，在**shell**中执行：

mysqldump --host=host1  -p123456 --opt sourceDb| mysql --host=host2 -p123456 -C targetDb

使用上面的的命令可以将**host1**上的**sourceDb**复制到**host2**的**targetDb**，前提是**host2**主机上已经创建**targetDb**数据库，就可以实现跨主机复制啦！

接下来，我们还要做最后一件事，进入主数据库，解锁主库：

mysql > unlock tables;

## 四、如果从库状态显示主从同步出现异常 ##

重新设置主从同步：

1.  首先停止从机

mysql > stop slave;

1.  设置跳过一个事务

mysql > set global sql_slave_skip_counter=1;

1.  开启从机

mysql > start slave

1.  查看主从状态

mysql > show slave status \G;

这个时候应该能够看到，主从同步的状态已经刷新了。

[Centos7_MySQL5.7]: https://www.cnblogs.com/yss818824/p/12349719.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3_size_16_color_FFFFFF_t_70]: /images/20230312/1b6ff4bebfb74d489a17bef2f8c58e65.png
[20200513222132695.jpg]: /images/20230312/3fef7ca490ac4b2db46a8858b1671646.png
[mysql]: https://img-blog.csdnimg.cn/20200513223505524.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200513223036924.jpg?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMwMTA4MjM3,size_16,color_FFFFFF,t_70