MySQL主从复制介绍
MySQL的主从复制是其自带的功能,通过逻辑的binlog日志复制到要同步的服务器本地,主服务器（Master），接收来自用户的内容更新，而一个或多个其他的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，重新更新到从服务器，使得主从服务器数据达到一致。
MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步，但是正常情况下给用户的体验是真实的。
复制原理介绍
- MySQL的主从复制是一个异步的复制过程（虽然一般情况下感觉是实时的），数据将从一个MySQL数据库（我们称之为Master）复制到另一个MySQL数据库（我们称之为Slave），在Master与Slave之间实现整个主从复制的过程是由三个线程参与完成的。其中有两个线程（SQL线程和I/O线程）在Slave端，另外一个线程（I/O线程）在Master端。
- 要实现MySQL的主从复制，首先必须打开Master端的binlog记录功能，否则就无法实现。因为整个复制过程实际上就是Slave从Master端获取binlog日志，然后再在Slave上以相同顺序执行获取的binlog日志中所记录的各种SQL操作。
- 要打开MySQL的binlog记录功能，可通过在MySQL的配置文件my.cnf中的mysqld模块（[mysqld]标识后的参数部分）增加“log-bin”参数选项来实现，
MySQL主从复制原理过程详细描述
1在slave服务器上执行start slave命令开始主从复制
2.此时slave服务器的io线程会请求链接主服务器,并从指定的binlog文件位置之后开始获取日志内容
3.master服务器接收到slave服务器io线程的请求后,负责复制的io线程会根据收到的信息读取日志文件指定位置之后的日志内容,然后返回给slave端的io线程,
返回信息除了日志内容外,还有新的binlog文件以及新的指定的位置
4.当slave服务器io线程收到master服务器上的日志文件内容以及新的日志文件名和新的位置后,会把日志内容写到slave服务器端自身的relay log(中继日志)的最末端,
并将新的日志文件名和位置记录到master-info文件中,以便下次读取
5.slave服务器的sql线程会实时监测本地的relay log的内容,然后及时把relay log的内容解析成sql语句,按顺序依次执行这些sql语句,
并在relay log info中记录中继日志的文件名与位置
MySQL主从复制重点:
- 主从复制是异步的逻辑的SQL语句级的复制
- 复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程
- 实现主从复制的必要条件是主库要开启记录binlog功能
- 作为复制的所有MySQL节点的server-id都不能相同。
- binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变更），不记录任何查询（如select，show）语句。
主从复制实战
此处应用为 一台电脑 多实例mysql 主从复制 和单实例多服务器主从复制原理一样
mysql主服务配置
设置server-id值并开启binlog功能参数
vim my.cnf #修改主库的配置文件 [mysqld] #参数要放在my.cnf中的[mysqld]模块下，否则会出错。 server-id = 1 #用于同步的每台机器或实例server-id都不能相同 log-bin = /data/3306/mysql-bin #binlog日志的位置
检查配置参数后的结果
egrep "server-id|log-bin" /data/3306/my.cnf log-bin = /data/3306/mysql-bin server-id = 1
重启主库MySQL服务
/data/3306/mysql restart
登陆数据库，检查参数的更改情况
mysql -uroot -p123456 -S /data/3306/mysql.sock show variables like 'server_id'; #查看MySQL的系统变量（like类似于grep过滤） +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 1 | #配置的server_id为1 +---------------+-------+ 1 row in set (0.00 sec) show variables like 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | ON | #binlog功能已开启 +---------------+-------+ 1 row in set (0.00 sec)
建立用于从库复制的账号
grant replication slave on *.* to 'wk'@'192.168.50.%' identified by '123456';
#replication slave为mysql同步的必须权限，此处不要授权all权限
flush privileges; #创建完账号并授权后，需要刷新权限，使授权的权限生效
检查主库创建的wk复制账号命令及结果如下：
select user,host from mysql.user; +------+--------------+ | user | host | +------+--------------+ | root | 127.0.0.1 | | wk | 192.168.50.% | | root | ::1 | | | localhost | | root | localhost | | root | www | +------+--------------+
show grants for wk@'192.168.50.%'; #查看授权状况 +--------------------------------------------------------------------------------------------------------------------------+ | Grants for wk@192.168.50.% | +--------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE ON *.* TO 'wk'@'192.168.50.%' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' | +--------------------------------------------------------------------------------------------------------------------------+
#结果显示授权正确
锁表完全备份主库 (对于快速配置主从复制 不需要锁表)
对主数据库锁表只读（当前窗口不要关掉）的命令如下：
flush table with read lock; Query OK, 0 rows affected (0.00 sec)
在引擎不同的情况下，这个锁表命令的时间会受下面参数的控制。锁表时，如果超过设置时间不操作会自动解锁。
默认情况下自动解锁的时长参数值如下：
show variables like '%timeout%'; +----------------------------+----------+ | Variable_name | Value | +----------------------------+----------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 120 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | #自动解锁时间受本参数影响 | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | wait_timeout | 28800 | #自动解锁时间受本参数影响 +----------------------------+----------+
锁表后查看主库状态
show master status; #根据Position偏移量 确定是否在锁表后的主从复制时 内容有所变动 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 1193 | | | +------------------+----------+--------------+------------------+
锁表后，一定要单开一个新的SSH窗口，导出数据库的所有数据，如果数据量很大（50GB以上），并且允许停机，可以停库直接打包数据文件进行迁移，那样更快。
分之一 快速主从复制 不需要上述锁表mysql -uroot -p123456 -S /data/3306/mysql.sock -e "show master status"
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -x --master-data=1 -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz #-A表示备份所有库；-B表示增加use DB和 drop 等（导库时会直接覆盖原有的） --master-data=1 直接记录日志文件名和偏移量 不需要在change master
里写入日志文件名和偏移量 -x 导出时锁表
分支二
mysqldump -uroot -p123123 -S /data/3306/mysql.sock --events -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
#此种配置需要上述锁表同时还需要在change master里写入日志文件名和偏移量#为了确保导出数据期间，数据库没有数据插入，导库完毕可以再次检查主库状态信息，结果如下：
mysql-bin.000001 | 1193 #确保迁移量没有发生变化
在MySQL从库的配置
设置server-id值并关闭binlog功能参数
vi my.cnf [mysqld] server-id = 2 #调整等号后的数值，和任何一个数据库实例都不同 egrep "server-id|log-bin" /data/3307/my.cnf #检查配置参数后的结果 server-id = 2
重启从数据库
/data/3307/mysql restart
把从主库mysqldump导出的数据恢复到从库
cd /server/backup/ gzip -d mysql_bak.2018-08-06.sql.gz mysql -uroot -S /data/3307/mysql.sock <mysql_bak.2018-08-06.sql #这是把数据还原到3307实例的命令
登陆3307从库，配置复制参数
MySQL从库连接主库的配置信息如下：
分之一 快速配置 CHANGE MASTER TO MASTER_HOST='192.168.50.149', #这里是主库的IP MASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同 MASTER_USER='wk', #这里是主库上建立的用于复制的用户wk MASTER_PASSWORD='123456'; #这里是wk用户的密码 #由于--master-data=1记录了日志文件名和偏移量所以此处不设置MASTER_LOG_FILE和MASTER_LOG_POS 分之二 CHANGE MASTER TO MASTER_HOST='192.168.50.149', #这里是主库的IP MASTER_PORT=3306, #这里是主库的端口，从库端口可以和主库不同 MASTER_USER='wk', #这里是主库上建立的用于复制的用户wk MASTER_PASSWORD='123456', #这里是wk用户的密码 MASTER_LOG_FILE='mysql-bin.000001', #这里是show master status时查看到的二进制日志文件名称，注意不能多空格 MASTER_LOG_POS=533; #这里是show master status时查看到的二进制日志偏移量，注意不能多空格
上述操作的原理实际上是把用户密码等信息写入从库新的master.info文件中cat /data/3307/data/master.info
分之一 cat /data/3307/data/master.info 18 4 192.168.50.149 wk 123456 3306 60 0 分之二 cat /data/3307/data/master.info 18 mysql-bin.000001 1193 192.168.50.149 wk 123456 3306 60 0
启动从库同步开关，测试主从复制配置情况
mysql -uroot -S /data/3307/mysql.sock -e "start slave" mysql -uroot -S /data/3308/mysql.sock -e "show slave statusG"
主从同步是否成功，最关键的为下面的3项状态参数：
- Slave_IO_Running: Yes,这个时I/O线程状态，I/O线程负责从从库到主库读取binlog日志，并写入从库的中继日志，状态为Yes表示I/O线程工作正常。
- :Slave_SQL_Running: Yes,这个是SQL线程状态，SQL线程负责读取中继日志（relay-log）中的数据并转换为SQL语句应用到从数据库中，状态为Yes表示I/O线程工作正常。
- :Seconds_Behind_Master:0,这个是复制过程中从库比主库延迟的秒数，这个参数极度重要，但企业里更准确地判断主从延迟的方法为：在主库写时间戳，然后从库读取时间戳，和当前数据库时间进行比较，从而认定是否延迟。
此时主从复制配置成功
主从复制配置步骤小结
- 准备两台数据库环境或单台多实例环境，确定能正常启动和登陆
- 配置my.cnf文件：主库配置log-bin和server-id参数；从库配置server-id，该值不能和主库及其他从库一样，一般不开启从库log-bin功能。注意，配置参数后要重启才能生效。
- 登陆主库，增加从库连接主库同步的账户，例如：yunjisuan，并授权replication slave同步的权限。
- 登陆主库，整库锁表flush table with read lock（窗口关闭后即失效，超时参数设置的时间到了，锁表也失效），然后show master status查看binlog的位置状态。
- 新开窗口，在Linux命令行备份导出原有的数据库数据，并拷贝到从库所在的服务器目录。如果数据库数据量很大，并且允许停机，可以停机打包，而不用mysqldump。
- 导出主库数据后，执行unlock tables解锁主库。
- 把主库导出的数据恢复到从库
- 根据主库的show master status查看到的binlog的位置状态，在从库执行change master to....语句。
- 从库开启复制开关，即执行start slave；。
- 从库show slave statusG,检查同步状态，并在主库进行更新测试。
分之一和分之二的区别在于导出主备文件
在企业中增加从库 只需要深夜挂定时任务执行
mysqldump -uroot -p123456 -S /data/3306/mysql.sock --events -x --master-data=1 -A -B | gzip >/server/backup/mysql_bak.$(date +%F).sql.gz
第二天把该文件移植到从库 开启主从复制功能即可
MySQL主从复制主库I/O线程状态说明
登陆主数据库查看MySQL线程的同步状态
show processlistG
#红色内容表示上述状态的意思是线程已经从binlog日志读取所有更新，并已经发送到了从数据库服务器。线程目前为空闲状态，等待由主服务器上二进制日志中的新事件更新 *************************** 1. row *************************** #从库1IO线程 Id: 5 User: wk Host: 192.168.50.149:51932 db: NULL Command: Binlog Dump Time: 4480 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 2. row *************************** #从库2IO线程 Id: 15 User: wk Host: 192.168.50.149:51933 db: NULL Command: Binlog Dump Time: 557 State: Master has sent all binlog to slave; waiting for binlog to be updated Info: NULL *************************** 3. row *************************** Id: 17 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec)
下图中列出了主服务器binlog Dump线程中State列的最常见状态。如果你没有在主服务器上看见任何binlog Dump线程，则说明复制没有运行，二进制binlog日志由各种事件组成，事件通常会为更新添加信息。
登陆从数据库查看MySQL线程工作状态
show processlistG
红1表示线程已连接到服务器,等待二进制日志到达
红2表示已处理完所有日志等待新日志到来 *************************** 1. row *************************** #IO线程 Id: 5 User: system user Host: db: NULL Command: Connect Time: 621 State: Waiting for master to send event Info: NULL *************************** 2. row *************************** #SQL线程 Id: 6 User: system user Host: db: NULL Command: Connect Time: 385 State: Slave has read all relay log; waiting for the slave I/O thread to update it Info: NULL *************************** 3. row *************************** Id: 9 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show processlist 3 rows in set (0.00 sec)
- 通过MySQL线程同步状态可以看到同步是否正常进行，故障的位置是什么，另外还可查看数据库同步是否完成，可用于主库宕机切换数据库或人工数据库主从切换迁移等。
- 例如：主库宕机，要选择最快的从库将其提升为主库，就需要查看主从库的线程状态，如果主从复制在正常情况下进行角色切换，也需要查看主从库的线程状态，根据复制状态确定更新是否完成。
mysql主从复制出现问题
复制故障
Slave_IO_Running: Yes
Slave_SQL_Running: No 表示复制故障
Master_Server_Id: 1 主从同步状态:关闭
stop slave; #关闭主从同步 Query OK, 0 rows affected, 1 warning (0.00 sec)
set global sql_slave_skip_counter=1; #将sql线程同步指针向下移动一个，如果多次不同步，可以重复操作 Query OK, 0 rows affected (0.00 sec)
start slave; #开启主从同步 Query OK, 0 rows affected (0.00 sec)
解决方法2
根据可以忽略的错误号事先在配置文件中配置，跳过指定的不影响业务数据的错误，例如：
vim my.cnf
[mysqld] slave-skip-errors = 1032,1062,1007
让MySQL从库记录binlog日志的方法
如果从库下边还有从库需要开启从库1的binlog功能
[mysqld]
log-slave-updates #必须要有这个参数 log-bin = /data/3307/mysql-bin expire_logs_days = 7 #相当于find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f 删除7天以上的日志文件
MySQL主从复制延迟问题的原因及解决方案
问题一：主库的从库太多，导致复制延迟
从库数量以3~5个为宜，要复制的从节点数量过多，会导致复制延迟。
问题二：从库硬件比主库差，导致复制延迟。
查看Master和Slave的系统配置，可能会因为机器配置不当，包括磁盘I/O，CPU，内存等各方面因素造成复制的延迟。这一般发生在高并发大数据量写入场景中。
问题三：慢SQL语句太多
假如一条SQL语句执行时间是20秒，那么从执行完毕到从库上能查到数据至少需要20秒，这样就延迟20秒了。
一般要把SQL语句的优化作为常规工作，不断的进行监控和优化，如果单个SQL的写入时间长，可以修改后分多次写入。通过查看慢查询日志或show full processlist命令，找出执行时间长的查询语句或大的事务。
问题四：主从复制的设计问题
例如，主从复制单线程，如果主库写并发太大，来不及传送到从库，就会导致延迟。
更高版本的MySQL可以支持多线程复制，门户网站则会自己开发多线程同步功能。
问题五：主从库之间的网络延迟
主从库的网卡，网线，连接的交换机等网络设备都可能成为复制的瓶颈，导致复制延迟，另外，跨公网主从复制很容易导致主从复制延迟。
问题六：主库读写压力大，导致复制延迟。
主库硬件要搞好一点，架构的前端要加buffer及缓存层。
通过read-only参数让从库只读访问
read-only参数选项可以让从服务器只允许来自从服务器线程或具有SUPER权限的数据库用户进行更新，确保从服务器不接受来自用户端的非法用户更新。
read-only参数允许数据库更新的条件为：
- 具有SUPER权限的用户可以更新，不受read-only参数影响，例如：管理员root。
- 来自从服务器线程可以更新，不受read-only参数影响，例如：前文的yunjisuan用户。
- 再生产环境中，可以在从库Slave中使用read-only参数，确保从库数据不被非法更新。
在my.cnf里[mysqld]模块下加read-only参数重启数据库，配置如下：
[mysqld]
read-only
主从同步时设置单个或多个库不做同步
生产环境中一般会采取忽略授权表方式的同步， 然后对从服务器(slave)上的用户仅授权select 读权限。不同步mysql库，这样我们就保证主库和从库相同的用户可以授权不同的权限。。
忽略mysql库和information_ schema 库的主从同步。
[mysqld]
replicate- ignore -db=mysql4 binlog-do-db = testdh. binlog- ignore-db = mysql↔ binlog-ignore-db = per formance_ schemar binlog-ignore-db = informat ion_ schemat 提示:忽略记录binlog日志的参数binlog-ignore-db一般用于系统的库和表。。