mysql主从复制，读写分离

最新推荐文章于 2022-10-20 22:32:52 发布

空~自由

最新推荐文章于 2022-10-20 22:32:52 发布

阅读量115

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/liu_xue_xue/article/details/114557263

版权

MYSQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

为什么使用主从复制

主从复制目的：

可以做数据库的实时备份，保证数据的完整性；
可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。

随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。

主从复制基本原理

1.master 将改变（例如insert、update、delete操作）顺序记录到二进制日志(binary log) ,这些记录过程叫做二进制事件, binary log events。

2.salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。

3.slave 将 master 的binary log events 拷贝到它的中继日志(reay log)。当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

4.slave 重做中继日志中的事件,将改变应用到自己的数据库中（SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。）。mysql复制是异步的且串行化的。

主从复制基本原则

1.每个slave只有一个master

2.每个slave只能有一个唯一的id

3.一个master可以有多个slave

主从复制存在的最大问题

延时

产生所谓的主从延迟，主要看写并发；主库的写并发达到1000/s，从库的延时会有几ms；主库的写并发达到2000/s，从库的延时会有几十ms

其实这块东西我们经常会碰到，就比如说用了mysql主从架构之后，可能会发现，刚写入库的数据结果没查到。

所以实际上你要考虑好应该在什么场景下来用这个mysql主从同步，建议是一般在读远远多于写，而且读的时候一般对数据时效性要求没那么高的时候，用mysql主从同步

所以这个时候，我们可以考虑的一个事情就是，你可以用mysql的并行复制，但是问题是那是库级别的并行，所以有时候作用不是很大

所以这个时候。。通常来说，我们会对于那种写了之后立马就要保证可以查到的场景，采用强制读主库的方式，这样就可以保证你肯定的可以读到数据了吧。其实用一些数据库中间件是没问题的。

一般来说，如果主从延迟较为严重

分库，将一个主库拆分为4个主库，每个主库的写并发就500/s，此时主从延迟可以忽略不计
打开mysql支持的并行复制，多个库并行复制，如果说某个库的写入并发就是特别高，单库写并发达到了2000/s，并行复制还是没意义。28法则，很多时候比如说，就是少数的几个订单表，写入了2000/s，其他几十个表10/s。

如何实现主从复制（Master-Slave）

Master配置

使用命令行进入mysql：

mysql -u root -p

接着输入root用户的密码(密码忘记的话就网上查一下重置密码吧~)，然后创建用户：

//192.168.0.66是slave从机的IP
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.66' identified by 'JTest@123';
//192.168.0.67是slave从机的IP
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.87' identified by 'JTest@123';
//刷新系统权限表的配置
FLUSH PRIVILEGES;

创建的这两个用户在配置slave从机时要用到。

接下来在找到mysql的配置文件/etc/my.cnf，增加以下配置：

# 开启binlog
log-bin=mysql-bin
server-id=104
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=test_db
# binlog日志保留的天数，清除超过10天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10

配置完成后，重启mysql：

service mysql restart

可以通过命令行show master status\G;查看当前binlog日志的信息(后面有用)：

Slave配置

Slave配置相对简单一点。从机肯定也是一台MySQL服务器，所以和Master一样，找到/etc/my.cnf配置文件，增加以下配置：

# 不要和其他mysql服务id重复即可
server-id=106

接着使用命令行登录到mysql服务器：

mysql -u root -p

然后输入密码登录进去。

进入到mysql后，再输入以下命令：

CHANGE MASTER TO 
MASTER_HOST='192.168.0.65',//主机IP
MASTER_USER='root',//之前创建的用户账号
MASTER_PASSWORD='JTest@123',//之前创建的用户密码
MASTER_LOG_FILE='mysql-bin.000001',//master主机的binlog日志名称
MASTER_LOG_POS=862,//binlog日志偏移量
master_port=3306;//端口

还没完，设置完之后需要启动：

# 启动slave服务
start slave;

启动完之后怎么校验是否启动成功呢？使用以下命令：

show slave status\G;

可以看到如下信息（摘取部分关键信息）：

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.65
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 619
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 782
        Relay_Master_Log_File: mysql-bin.000001 //binlog日志文件名称
             Slave_IO_Running: Yes //Slave_IO线程、SQL线程都在运行
            Slave_SQL_Running: Yes
             Master_Server_Id: 104 //master主机的服务id
                  Master_UUID: 0ab6b3a6-e21d-11ea-aaa3-080027f8d623
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                Auto_Position: 0

其中Slave_IO_Running 与 Slave_SQL_Running 的值都必须为YES，才表明状态正常。

另一台slave从机配置一样，不再赘述。

如果主服务器已经存在应用数据，则在进行主从复制时，需要做以下处理：
(1)主数据库进行锁表操作，不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

验证主从复制效果

主服务器上的操作
在主服务器上创建数据库first_db
mysql> create database first_db;
Query Ok, 1 row affected (0.01 sec)

在主服务器上创建表first_tb
mysql> create table first_tb(id int(3),name char(10));
Query Ok, 1 row affected (0.00 sec)

在主服务器上的表first_tb中插入记录
mysql> insert into first_tb values (001,’myself’);
Query Ok, 1 row affected (0.00 sec)

mysql> use first_db
Database chaged

mysql> show tables;
=============================
+--------------------+
| Tables_in_first_db |
+--------------------+
| first_tb |
+--------------------+
1 row in set (0.02 sec)
=============================
数据库表first_tb也已经自动创建

mysql> select * from first_tb;
=============================
+------+------+
| id | name |
+------+------+
| 1 | myself |
+------+------+
1 rows in set (0.00 sec)
=============================
记录也已经存在

由此，整个MySQL主从复制的过程就完成了，接下来，我们进行MySQL读写分离的安装与配置。

解决错误

若在主从同步的过程中，出现其中一条语句同步失败报错了，则后面的语句也肯定不能同步成功了。例如，主库有一条数据，而从库并没有这一条数据，然而，在主库执行了删除这一条数据的操作，那么从库没有这么一条数据就肯定删除不了，从而报错了。在此时的从数据库的数据同步就失败了，因此后面的同步语句就无法继续执行。

这里提供的解决方法有两种：

（1）在从数据库中，使用SET全局sql_slave_skip_counter来跳过事件，跳过这一个错误，然后执行从下一个事件组开始。

#在从数据库上操作
mysql > stop slave;
mysql > set global sql_slave_skip_counter=1;
mysql > start slave;
（2）在从数据库中，重新连上主数据库。这种操作会直接跳过中间的那些同步语句，可能会导致一些数据未同步过去的问题，但这种操作也是最后的绝招。最好就是令从数据库与主数据库的数据结构和数据都一致了之后，再来恢复主从同步的操作。

#在从数据库上操作
mysql > stop slave;
mysql> change master to master_host='192.168.0.65',master_port=3306,master_user='root',master_password='JTest@123',master_log_file='master-bin.000001',master_log_pos=862;
mysql > start slave;

如何实现读写分离

方法一：可以通过：Amoeba+keepalived架构MySQL读写分离

Amoeba是阿里巴巴陈思儒写的开源软件，用来实现mysql的读写分离。据说该软件比Mysql官方的mysql-proxy（也是个读写分离软件）更加稳定

方法二：通过程序实现，利用分布式数据库中间件Apache ShardingSphere

Apache ShardingSphere 是一套开源的分布式数据库中间件解决方案组成的生态圈，它由 JDBC、Proxy两部分组成。

ShardingSphere-JDBC定位为轻量级 Java 框架，在 Java 的 JDBC 层提供的额外服务。它使用客户端直连数据库，以 jar 包形式提供服务，无需额外部署和依赖，可理解为增强版的 JDBC 驱动，完全兼容 JDBC 和各种 ORM 框架。读写分离就可以使用ShardingSphere-JDBC实现。

针对springboot工程配置如下：

项目配置

版本说明：

SpringBoot：2.0.1.RELEASE
druid：1.1.22
mybatis-spring-boot-starter:1.3.2
mybatis-plus-boot-starter：3.0.7
sharding-jdbc-spring-boot-starter:4.1.1

添加sharding-jdbc的maven配置：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.1.1</version>
</dependency>

然后在application.yml添加配置：

# 这是使用druid连接池的配置，其他的连接池配置可能有所不同
spring:
  shardingsphere:
    datasource:
      names: master,slave0,slave1
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.108:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: yehongzhi
        password: YHZ@1234
      slave0:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.109:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: yehongzhi
        password: YHZ@1234
      slave1:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.jdbc.Driver
        url: jdbc:mysql://192.168.0.110:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: yehongzhi
        password: YHZ@1234
    props:
      sql.show: true
    masterslave:
      load-balance-algorithm-type: round_robin
    sharding:
      master-slave-rules:
        master:
          master-data-source-name: master
          slave-data-source-names: slave0,slave1

sharding.master-slave-rules是标明主库和从库，一定不要写错，否则写入数据到从库，就会导致无法同步。

load-balance-algorithm-type是路由策略，round_robin表示轮询策略。

空~自由

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
mysql主从复制，读写分离

目录为什么使用主从复制主从复制基本原理主从复制基本原则主从复制存在的最大问题如何实现主从复制Master配置Slave配置为什么使用主从复制主从复制目的：可以做数据库的实时备份，保证数据的完整性；可做读写分离，主服务器只管写，从服务器只管读，这样可以提升整体性能。随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用主从复制、读写分离可以提高数据库的可用性。主从复制基本原理1.master 将改变（例如insert、u...
复制链接

扫一扫