mysql之主从复制
MySQL主从复制介绍
MySQL的主从复制是其自带的功能,经过逻辑的binlog日志复制到要同步的服务器本地,主服务器（Master），接收来自用户的内容更新，而一个或多个其余的服务器充当从服务器（Slave），接收来自主服务器binlog文件的日志内容，解析出SQL，从新更新到从服务器，使得主从服务器数据达到一致。前端
MySQL主从复制都是异步的复制方式，既不是严格实时的数据同步，可是正常状况下给用户的体验是真实的。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中记录中继日志的文件名与位置sql
MySQL主从复制重点:
- 主从复制是异步的逻辑的SQL语句级的复制
- 复制时，主库有一个I/O线程，从库有两个线程，即I/O和SQL线程
- 实现主从复制的必要条件是主库要开启记录binlog功能
- 做为复制的全部MySQL节点的server-id都不能相同。
- binlog文件只记录对数据库有更改的SQL语句（来自主数据库内容的变动），不记录任何查询（如select，show）语句。
主从复制实战
此处应用为 一台电脑 多实例mysql 主从复制 和单实例多服务器主从复制原理同样数据库
mysql主服务配置
设置server-id值并开启binlog功能参数vim
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 status\G"
主从同步是否成功，最关键的为下面的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 status\G,检查同步状态，并在主库进行更新测试。
分之一和分之二的区别在于导出主备文件
在企业中增长从库 只须要深夜挂定时任务执行
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 processlist\G
#红色内容表示上述状态的意思是线程已经从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 processlist\G
红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通常用于系统的库和表。。