MySql之主从复制

最新推荐文章于 2022-12-05 14:35:23 发布

置顶猎户星座。

最新推荐文章于 2022-12-05 14:35:23 发布

阅读量409

点赞数

分类专栏： # MySql

本文链接：https://blog.csdn.net/qq_24313635/article/details/105611675

版权

MySql 专栏收录该内容

20 篇文章 9 订阅

订阅专栏

什么是MYSQL的主从复制

主从复制是指将一个服务器作为主服务器，所有的数据更新操作都在主服务器进行，并且将主服务器的数据同步到一个或多个从服务器，保证从服务器和主服务器的数据一致。

为什么需要主从复制

高可用，主从复制实现了数据的热备份，可以有效避免单点故障导致的数据丢失。
高性能，可以用来实现读写分离，提高MYSQL服务的并发性能。

MYSQL主从复制原理

原理示意图

主节点 binlog，主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服务器启动的那一刻起，保存所有修改数据库结构或内容的一个文件。
主节点 log dump 线程，当 binlog 有变动时，log dump 线程读取其内容并发送给从节点。
从节点 I/O线程接收 binlog 内容，并将其写入到 relay log 文件中。
从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放，最终保证主从数据库的一致性。

注：主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置，从节点会保存其已接收到的偏移量，如果从节点发生宕机重启，则会自动从 position 的位置发起同步。

MYSQL主从复制的模式

1、异步模式（默认方式）

异步模式

异步模式下，主节点执行完客户端提交的事务后立即提交事务并返回给客户端，并不关心 dump 线程是否成功地将将此次事务写进 bing log 并且发送给从库。假如执行事务的主线程提交事务后，log dump 线程还未来得及写入 binlog，此时系统宕机，则会造成 binglog 中没有保存刚才提交的事务，造成主从数据不一致。

优点：异步模式下，主线程不用关关心同步操作，性能最好。

缺点：可能导致主从数据的不一致。

2、半同步复制

半同步复制

半同步方式，当主库在执行完客户端提交的事务后不是立即提交事务，而是等待 dump 线程将此次事务同步到bin log 发送给从库，并且至少一个从库成功保存到其relay log中，此时主库的才提交事务并返回客户端。

优点：相比于异步模式，半同步方式一定程度上保证了数据同步的可靠性。

缺点：增加了主库响应客户端的延时，延时至少为一个 TCP/IP 的往返时间，即 bin log 发送给从库至收到从库的响应时间。

3、全同步复制

全同步方式，当主库在执行完客户端提交的事务后，必须等待此次的bin log发送给从库，并且所有从库成功地执行完该事务后，主库才能返回客户端。其与半同步复制的区别如下：

半同步下，主库等待bin log写入到从库的relay log即可返回，全同步方式下，必须等到从库执行事务成功。
半同步下，至少一个从库响应后主库即可返回客户端，全同步下必须等待所有的从库返回。

优点：对比半同步复制方式，全同步复制方式数据一致性的可靠性进一步提高

缺点：执行事务时，主库需要等待所有的从库执行成功后才能返回，所以会大大提高主库的响应时间。

MYSQL主从复制搭建

准备工作

主从数据库版本最好一致
主从数据库内数据保持一致
主数据库：192.168.190.131 从数据库：192.168.190.132

配置主库Master

修改MySQL配置

mysql查看默认配置文件：mysql --help|grep 'my.cnf'，找到主数据库的配置文件my.cnf，我的在/etc/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#主服务器唯一ID
server-id=1
#设置需要复制的数据库
binlog-do-db=testdb
#二进制日志
#配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=STATEMENT
#自动删除的过期天数
expire_logs_days=3

#该选项用来开启查询日志，可选值：0关闭;1开启
general_log=1
#设置日志的文件名,如果没有指定,默认的文件名为host_name.log
general_log_file=mysql_query.log

#该参数用来控制慢查询日志是否开启：1开启,0关闭
slow_query_log=1
#该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
#该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10s
long_query_time=3

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

创建用户

重启服务，创建用于同步的用户，用户：salve密码：123456

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';

查看master状态

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000007 |     2400 | testdb       |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从库Slave

修改MySQL配置

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

#从服务器唯一ID
server-id=2
#启用中继日志
relay-log=mysql-relay
#二进制日志
#配置开启 binlog日志,日志的文件前缀为mysqlbin生成的文件名如: mysqlbin.000001, mysqlbin.000002
log_bin=mysqlbin
#配置二进制日志的格式
binlog_format=STATEMENT
#自动删除的过期天数
expire_logs_days=3

#该选项用来开启查询日志，可选值：0关闭;1开启
general_log=1
#设置日志的文件名,如果没有指定,默认的文件名为host_name.log
general_log_file=mysql_query.log

#该参数用来控制慢查询日志是否开启：1开启,0关闭
slow_query_log=1
#该参数用来指定慢查询日志的文件名
slow_query_log_file=slow_query.log
#该选项用来配置查询的时间限制,超过这个时间将认为是慢查询,需要进行日志记录,默认是10s
long_query_time=3

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.190.131',
    -> MASTER_USER='salve',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysqlbin.000007',
    -> MASTER_LOG_POS=2400;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

启动slave同步进程

mysql>start slave;

停止stop slave;重新配置主从reset master；

查看slave状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.190.131
                  Master_User: salve
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqlbin.000007
          Read_Master_Log_Pos: 2400
               Relay_Log_File: mysql-relay.000003
                Relay_Log_Pos: 2277
        Relay_Master_Log_File: mysqlbin.000007
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2400
              Relay_Log_Space: 2446
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 9510d25d-7d7b-11ea-9a71-000c29980d35
             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
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
1 row in set (0.00 sec)

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。如果是虚拟机克隆的情况下，注意修改/var/lib/mysql/auto.cnf中的uuid

注意事项

master主节点
在主节点的二进制事件日志中仅记录与指定数据库（数据表）相关的事件日志，但是主节点的二进制日志不完整，没有记录所有对主节点的修改操作。如果要使用该方式，则在主节点的配置文件中添加如下参数:

binlog_do_db="***,***,***";      #数据库白名单列表,二进制日志记录的数据库（多数据库用逗号隔开或重复设置多行）,即需要同步的库.不在内的不同步。（不添加这行表示同步所有）
binlog_ingore_db="***,***,***";  #数据库黑名单列表, 二进制日志中忽略的数据库 （多数据库用逗号隔开或重复设置多行）,即不需要同步,要过滤掉的库.

slave从节点
从服务器SQL Thread在Replay中继日志中的事件时仅读取于特定数据库相关的事件，并应用于本地. (但是浪费I/O ,浪费带宽) 推荐使用从节点复制过滤相关设置项:

replicate_do_db ="webdb";         #复制库的白名单. 设定需要复制的数据库（多数据库使用逗号隔开或重复设置多行）
replicate_ingore_db ="mysql";     #复制库的黑名单. 设定需要忽略的复制数据库 （多数据库使用逗号隔开或重复设置多行）
replicate_do_table="webdb.user";  #复制表的白名单. 设定需要复制的表（多数据库使用逗号隔开或重复设置多行）
relicate_ingore_table="webdb.uw"; #复制表的黑名单. 设定需要忽略的复制的表（多数据库使用逗号隔开或重复设置多行）
   
replicate-wild-do-table           #同replication-do-table功能一样，但是可以通配符. 更高级别的应用，通配符,应用到哪一类表的。
replicate-wild-ignore-table       #同replication-ignore-table功能一样，但是可以加通配符.

当在主库存在的库而从库不存在的库同步时,会出现sql错误,这时候可以排除或者从库手动导入主库数据库。从库可以使用通配符"库名.%"方式过滤主从同步时某个库的设置

replicate-wild-do-table=webdb.%      #只复制webdb库下的所有表
replicate-wild-ignore-table=mysql.%  #忽略mysql库下的所有表

特别注意: 生产库上一般不建议设置过滤规则, 如果非要设置, 强烈建议从库使用通配符方式过滤某个库

replicate-wild-do-table= "库名.%"
replicate-wild-ignore-table= "库名.%"

而不建议从库使用DB方式过滤某个库

replicate_do_db ="库名"
replicate_ingore_db ="库名"

验证

在主服务器创建testdb数据库，新建mytbl表，插入几条数据，发现从服务器也能查询到testdb数据库，mytbl表，以及表中的数据

猎户星座。

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
MySql之主从复制

为什么要做主从复制？在业务复杂的系统中，有这么一个情景，有一句sql语句需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。通过做主从复制（读写分离）来减轻主数据库的负载做数据的热备业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/...
复制链接

扫一扫