MySQL 主从复制

前言

MYSQL主从同步是目前使用比较广泛的数据库架构，技术比较成熟，配置也不复杂，特别是对于负载比较大的网站，主从同步能够有效缓解数据库读写的压力

工作原理

• 实现过程
  1).Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容。

  2).Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave 的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置。

  3).Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的 bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”。

  4).Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行。

• 主从复制作用
  a. 可以作为一种备份机制，相当于热备份
  b.可以用来做读写分离，均衡数据库负载

环境

• 主服务器：Master
• 从服务器：Slave
• 主从MySQL版本一致

实现主从复制

1. 主数据库Master

• 修改配置文件my.cn / my.ini

[mysqld]
# ---主从复制start--- #
#节点id，确保唯一，默认是1，master设置200，slave设置201
server-id=200

#开启二进制日志
log-bin=mysql-bin
# 这个参数性能消耗很大，但可减小MySQL崩溃造成的损失，为0表示不控制
sync_binlog=1
# 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除
expire_logs_days=7
# binlog每个日志文件大小  
max_binlog_size=100M
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=16M
#最大binlog缓存大
max_binlog_cache_size=512M
# 跳过的错误号：
# 1007：数据库已存在，创建数据库失败
# 1008：数据库不存在，删除数据库失败
# 1050：数据表已存在，创建数据表失败
# 1051：数据表不存在，删除数据表失败
# 1054：字段不存在，或程序文件跟数据库有冲突
# 1060：字段重复，导致无法插入
# 1061：重复键名
# 1068：定义了多个主键
# 1094：位置线程ID
# 1146：数据表缺失，请恢复数据库
# 1053：复制过程中主服务器宕机
# 1062：主键冲突 Duplicate entry '%s' for key %d
slave_skip_errors=1062
# 参数说明：
# 0-该模式速度最快，但不太安全、mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失
# 1-该模式是最安全的， 但也是最慢的一种方式，在mysqld 服务崩溃或者服务器主机crash的情况下，binary log 只有可能丢失最多一个语句或者一个事务
# 2-该模式速度较快，也比0安全，只有在操作系统崩溃或者系统断电的情况下，上一秒钟所有事务数据才可能丢失
innodb_flush_log_at_trx_commit=1

# 复制过滤（不生成日志文件的数据库）：也就是指定哪个数据库不用同步（mysql库一般不同步，多个忽略数据库可以用逗号拼接，或者 复制这句话，写多行）
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schem
# 生成日志文件
binlog-do-db=test                                  
# ---主从复制end--- #

• 重启MySQL服务，创建同步用户账号

# 建立帐户并授权slave
# 用户名：sync
# 密码：Sync@123.com
GRANT REPLICATION SLAVE ON *.* to 'sync'@'%' identified by 'Sync@123.com';

# 刷新权限
flush privileges;

• 查看master状态，File：文件名，Position：二进制日志位置

mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |    64778 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

2. 从数据库Slave

• 修改配置文件my.cn / my.ini

[mysqld]
# ---主从复制start--- #
server-id=203

#只同步test库
replicate-do-db=test

#忽略因复制出现的所有错误
slave-skip-errors=all
# ---主从复制end--- #

修改配置文件需要重启服务

• 执行同步命令
  master_host：master数据库ip
  master_user：同步账号用户名
  master_password：密码
  master_log_file：二进制文件的名称
  master_log_pos：日志位置

change master to
	master_host='82.157.xxx.xxx',
	master_user='sync', 
	master_password='Sync@123.com', 
	master_log_file='mysql-bin.000001', 
	master_log_pos=64778;

• 开启同步功能

start slave;

• 查看状态

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 82.157.xxx.xxx
                  Master_User: sync
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 64778
               Relay_Log_File: LAPTOP-EC9EOSHE-relay-bin.000008
                Relay_Log_Pos: 20927
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: test

注：Slave_IO_Running及Slave_SQL_Running进程必须正常运行，即YES状态，否则说明同步失败。

到这里，主从复制数据库设置完成，接下来可以修改数据、结构来验证。