MySql之主从复制

程序员文章站 2024-03-20 21:01:16

...

为什么要做主从复制？

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

主从复制的原理

MySql之主从复制

主库db的更新事件(update、insert、delete)被写到binlog
从库发起连接，连接到主库

此时主库创建一个binlog dump thread，把binlog的内容发送到从库
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

注：上面的解释是解释每一步做了什么，整个mysql主从复制是异步的，不是按照上面的步骤执行的。

同步操作通过 3 个线程实现，其基本步骤如下：

主服务器将数据的更新记录到二进制日志中（记录被称作二进制日志事件）-- 主库线程；
从库将主库的二进制日志复制到本地的中继日志（relay log）-- 从库 I/O 线程；
从库读取中继日志中的事件，将其重放到数据中 -- 从库 SQL 线程。

本篇博客记录的是一主一从模式

环境搭建

准备工作

主从数据库版本最好一致
主从数据库内数据保持一致
主数据库：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表，以及表中的数据

MySql之主从复制

为什么要做主从复制？

主从复制的原理

环境搭建

准备工作

配置主库Master

配置从库Slave

注意事项

验证

mysql高可用集群部署方案（mha+atlas+keepalived）

搭建高可用mysql集群PXC

Mysql组复制-简单集群搭建

开机脚本之——快速测试AHK代码热键

win10卸载干净mysql数据库，亲测有效

mysql主从备份

MySql之主从复制

漫谈Mysql之主从复制

MySQL优化之主从复制

LVS+Keepalived+MysqlCluster搭建高可用的Mysql系统(实测可行)