6-MYSQL主从同步(DBA)配置

vlambda
2020-03-10

6-MYSQL主从同步(DBA)配置

(1)主从介绍
(2)主从架构
(3)主从应用场景
(4)主从原理
(5)主从实战
(6)主从三个线程状态
(7)主从配置总结
(n)主从复制半同步(待补充)

企业实战-主从复制

1.主库宕机从库切换主继续和从同步过程
2.从库宕机从库重新同步过程
3.主主(M-M)双向同步互为主从配置过程(不建议使用)
4.MYSQL从库记录BINLOG方法
5.主从生产场景中读写分离多种方案
6.主从复忽略指定库bin-log同步

主从故障(Q&A)

文章目录

(1) 主从介绍
(2) 主从架构

(1)主从介绍

Q：为啥要引入主从同步机制？
A：防止业务数据库突然宕掉，不能快速的恢复业务正常运行，有利于数据库架构的健壮性，提升访问速度，方便运维保证的数据物理安全(容灾备份)；

引入普通文件的同步的方法：
1.NFS网络文件共享同步
2.samba共享数据同步
3.定时任务+rsync/scp/git/svn
4.常用inotify和rsync触发实时数据同步
5.FTP/SFTP数据同步
6.不常用：http、sersync、csync2(多向)，union(双向)；

总结数据同步方案：

文件级别的同步方案
文件级别也可以利用mysql，mongdb等数据库软件
多台服务器同时写入数据，双写就是一个同步机制。
文件系统级别同步
drbd(基于文件系统同步网络RAID1)，同步所有的业务(Oracle数据库常常这样干)
mysql数据库的官方推荐drbd同步数据，所有的单点的服务器都能利用其同步；
数据库同步机制
自身同步机制： mysql replication(mysql主从复制|逻辑的SQL从写)，ORACLE DATAGUARD(物理的磁盘块|逻辑的SQL从写)
第三方DRDB，百度即可；

(2)主从架构

描述：MySQL支持单向、双向、环状、链式级联，实时，异步复制，同步复制(半同步插件-Google)；在当前的大多数Mysql应用中都是异步复制方式，即不是严格的实时的数据同步；

在DMZ区中，一台服务器充当主服务器(Master)，而一个或者多个充当从服务器(Slave)

主从复制常见架构：

单向 M=>S
双向主主 M <===> M
链式级联复制 A->B->C->D
#集群高可用架构 类似 内存数据库(memcache), 进行分布式数据HASH存储，采用DBPOROXY进行将用户的请求根据算法选择最优的存储(采用HASH进行取模的值进行判断到底在哪一个数据库中) #2014百度使用的

6-MYSQL主从同步(DBA)配置

实时和异步：
当配置好主从复制后，所有对数据库内容的操作就必须在主服务器上进行，以避免用户对主服务器上数据库内容更新对从服务器上的数据库内容的更新不一致而导致发生冲突，从服务器一般都是进行读；

防止数据写从库的方法：

方法1：采取忽略授权表方式的同步，然后对从服务器上面的用户仅仅授权select读权限，(注意不要同步mysql库，防止自读权限被更改)
方法2：在slave服务器启动选项中增加参数或者在my.cnf配置文件中加入read-only参数(启动选项加入也可以)，该参数可以让slave服务器只允许来着slave服务器线程和具有SUPER权限的用户进行更新，确保不接受来着普通用户的更新；(当然方法1和方法2可以一起使用)

#实现方法：复制环境用户的授权：
写库 blog  10.0.0.7
读库 blog  10.0.0.8
-------------------------------------------
# Master 生产环境主库用户授权 (建议实际用到什么权限分配什么权限)
GRANT SELECT,INSERT,UPDATE,DELETE ON `blog`.* TO 'blog'@'10.0.0.%' identified by 'oldboy456';

# Slave 生产环境用户授权
GRANT SELECT ON `blog`.* TO 'blog'@'10.0.0.%' identified by 'oldboy456';
REVOKE INSERT,UPDATE,DELETE ON `blog`.* FROM 'blog'@'10.0.0.%';     #建议结合--read-**only**
#最后需要在主库配置中加上：binlog-ignore-db=mysql

(3)主从应用场景

主从服务器互为备份：当主服务器出现问题时候，我们可以从人工或者自动的方法切换到从服务器中；(需要注意误操作)
主从服务器读写分离：通过程序实现读写分离(PHP，java，推荐：性能，效率最佳)或代理软件(MYSQL-PROXY，amoeba)对用户(客户端)的请求读写分离；降低用户查询响应时间以及读写同时在主服务器对于更新，将update，insert，delete交给Master服务器；门户网站常用分布式dbproxy(读写分离，hash负载均衡，健康检查)
根据服务器拆分业务独立并分担压力：有为外部用户提供查询服务的从服务器，有DBA用来备份的从服务器，还有供公司内部人员访问的后台；

6-MYSQL主从同步(DBA)配置

(4)主从原理

MySQL Replication 主从复制同步流程：

Slave服务器上执行start slave 开启主从开关；
Slave服务器IO线程通过Master授权用户权限连接Master服务器，并请求BInlog文件名称与请求POS位置(日志名和位置在配置主从服务 change master命令指定)
Master服务器收到从服务器IO线程请求后如果BINLOG存在变化，则在指定的binlog文件中读取指定的位置的Binlog信息，发送给Slave的IO线程；
Slave服务器IO线程接收到Master服务器IO线程发送日志内容以及日志文件名称和位置点以后，Slave依次将内容写入自身RelayLog(中继日志)，同时将本次Binlog文件名称与位置写在Msater.info中，以便下次发送给Matser服务器；
Slave服务器端的SQL线程会实时的检测本地的RelayLog变化，及时把Log文件中的内容解析成为SQL语句(导致 => 异步同步)，并且按照顺序执行这些SQL语句，应用完毕后清理应用后的日志；
经过上面的流程，即Master端和Slave端执行了同样的SQL语句。

6-MYSQL主从同步(DBA)配置

Master端原理图：
6-MYSQL主从同步(DBA)配置

Slave端原理图：
6-MYSQL主从同步(DBA)配置

总结与注意：
0) 主从复制是异步同步方式,通过逻辑同步模式,多种模式(混合模式),默认是通过SQL语句执行(M与S可能会不一致时间长了以后)

主库通过记录BIN-LOG日志实现从库的同步,在设置主从同步前先将Master数据库复制到Slave数据库中，记录下初始的binlog文件名称与位置点, 需要确定同步前主从位置点相同一致的； #biglog记录更新的SQL语句
同步线程，在Slave服务器上有IO线程、SQL线程两个线程，在Master服务器上有IO线程；
在Slave服务器上面配置主库的IP/user/pass/bin-file/bin-POS位置等等
主库建立一个专用于主从复制的账号，并且打开Binlog功能；
从库上开启同步开关Start Slave；
从库的关键文件master.info(从库IO线程请求主库IO信息点),relay-log(从库读取主库发过来的bin-log并存入中级日志中),relay-info(从库SQL线程读取SQL语句写入数据库中)
在主库中导出数据库mysqldump使用带--master-data=1备份的全备数据库恢复到从库时候,从库在CHANGE MASTER TO 不需要加log-file和log-pos;
同步开启后需要注意检查同步状态及上面三个从库关键文件

(5)主从实战

描述：使用一台服务器作为多实例数据库来讲解主从复制；
注意：一般做主从是在不同的机器上面实现，且监听端口默认都为3306，完成单数据库多实例的基础之上就可以按照上面的进行在不同的机器上完成；

实验环境：
单机单数据库多实例环境下 Centos 6.x 环境
Master 192.168.1.107 3306 server-id = 1
Slave 192.168.1.107 3307 server-id = 2

Step1.主库操作：

# 配置 3006 的 my.cnf
[mysqld]  #注意放置的模块
log-bin = /data/3306/binlog       #开启 binlog 参数 （在mysql show的时候是 log_bin ）
server-id = 3306       #设置server-id 为 3306 （避免实例ID和不同机器ID重复，一般常用IP地址的后8位表示(1-255)） -> 0 < server-id < 2^32 -1  

# egrep "log-bin|server-id" /data/3306/my.cnf

# 修改后重新实例数据库
/data/3306/mysql restart 

#查看log-bin 是不是生效了 
mysql -uroot -p123@456. -S /data/3306/mysql.sock -e "show variables like 'log_bin|server_id'";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

#建立主从复制账户

grant replication slave on *.* to 'rep'@'%' IDENTIFIED BY 'System123@';   #replication slave是一个mysql权限 （mysql8.0又变化）

#mysql 8.0 主从账户
CREATE USER 'rep'@'%' IDENTIFIED WITH mysql_native_password BY 'System123@'; 
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

#加一个读锁（防止写东西进入） - 且mysql>界面不能quit
mysql> flush tables with read lock; # 5.5 就是table 、 5.1是tables （注意区别）
Query OK, 0 rows affected (0.00 sec)

#在导出前拿到pos位置点（重要）
mysql> show master status; 
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      588 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show master logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       177 |
| binlog.000002 |       421 |
| binlog.000003 |       588 |
+---------------+-----------+
3 rows in set (0.00 sec)


#导出数据库将要灌入从库进行备份 （这时候读锁的界面是没有关闭的）
#另外一种方式：记录下 FILE 及 Position 的值。将主服务器的数据文件（整个/opt/mysql/data目录）复制到从服务器，建议通过tar归档压缩后再传到从服务器解压。
mysqldump -uroot -p'System123@' -S /data/3306/mysql.sock -A -B --events --master-data=2 | gzip >/opt/rep.sql.gz
mysqldump -uroot -p'System123@' -S /data/3306/mysql.sock -A -B --events --master-data=2 >/opt/rep.sql #注意这里如果master-data 添为 1 的时候则下面可以不指定LOG_FILE,LOG_POS

# 关闭读锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)


#打开主从同步后 （查看是否能不能同步）
mysql> create database demo;
Query OK, 1 row affected (0.01 sec)

Step2.从库操作：

# 配置 3007 的 my.cnf
[mysqld]
log-bin = /data/3307/binlog       #开启 binlog 参数  (目录后都没 / )   #[开启 binlog 参数,可选,如果是链路主从复制的化]
server-id = 3307      #设置server-id 为 3307
      
# egrep "log-bin|server-id" /data/3307/my.cnf

# 主库的数据库灌入从库：
 mysql -uroot -p'System123@' -S /data/3307/mysql.sock < /opt/rep.sql
 
#登录执行
mysql > CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='System123@',MASTER_LOG_FILE='binlog.000006',MASTER_LOG_POS=760;
Query OK, 0 rows affected, 2 warnings (0.08 sec)

#开启复制
mysql> start slave;  # stop slave; 停止复制
Query OK, 0 rows affected (0.04 sec)

#查看是否成功（主要参数）
show slave status \G;
    Slave_IO_Running: yes
    Slave_SQL_Running: Yes
   Seconds_Behind_Master:0    #落后主库的秒数
   
   
[root@WeiyiGeek 3307/data]# mysqlbinlog WeiyiGeek-relay-bin.000001
[root@WeiyiGeek data]# cat master.info
25
binlog.000003
588
10.0.2.15
rep
System1213@
3306
60  #重试时间
0

[root@WeiyiGeek data]# cat relay-log.info
7
./WeiyiGeek-relay-bin.000001
4
binlog.000003
588   #POS 位置
0
0
1

6-MYSQL主从同步(DBA)配置

(6)主从三个线程状态

描述:三个线程的状态以及状态说明;
6-MYSQL主从同步(DBA)配置

#列出了主服务器的Binlog Dump线程的state列最常见的状态,如果没看见binlog线程则说明复制没有在运行，目前没有连接任何从服务器;
(1) send binlog event to slave : 线程已经从二进制日志读取了一个事件并且正将它发送打到从服务器中;
(2) Finished reading one binlog,switching to next binlog : 线程已经读完二进制文件并且正打开一个要发送到从服务器的日志服务器;
(3) Master has sent all binlog to slave; waiting for more updates : 线程已经从二进制日志中读取所以注意的更新并已经发送到从服务器;
(4) Waiting to finalize termination 线程停止时候发生的一个简单的状态;

6-MYSQL主从同步(DBA)配置

# 从服务器IO线程的state状态
(1) Connecting to master : 线程正试图连接主服务器。
(2) Checking master version : 建立同主服务器之间的连接后立即临时出现的状态。
(3) Registering slave on master : 建立同主服务器之间的连接后立即临时出现的状态。
(4) Requesting binlog dump : 建立同主服务器之间的连接后立即临时出现的状态。线程向主服务器发送一条请求，索取从请求的二进制日志文件名和位置开始的二进制日志的内容。
(5) Waiting to reconnect after a failed binlog dump request ：如果二进制日志转储请求失败（由于没有连接），线程进入睡眠状态，然后定期尝试重新连接。可以使用--master-connect-retry 选项指定重试之间的间隔。
(6) Reconnection after a failed binlog dump request ：线程正尝试重新连接主服务器
(7) Waiting for master to send event : 线程已经连接上主服务器，正等待二进制日志事件的到达,如果主服务器空闲,会持续较长的时间(如果等待持续slave_read_timeout秒,则发生超时重连)
(8) Queueing master event to thr relay log : 线程已经读取一个事件，正将它复制到中继日志中供SQL线程处理;
(9) Waiting to reconnect after a failed master event read :读取时候由于没有连接错误，线程企图重新连接将睡眠master-connet-retry秒；
(10) Reconnecting  after a failed master event read : 线程正尝试重新连接到主服务器中，当重新连接后，状态变为Waiting for master to send event;
(11) Waiting for the slave SQL thread to free enough relay log space; : 中继日志大小值超过设定的relay_log_space_limit的值,IO线程等待知道SQL线程处理中继日志内容并删除部分中继日志文件来是否足够的空间;
(12) Waiting for slave mutex on exit ： 线程停止时发生的一个简单的状态;



# 从服务器SQL线程状态
(1) Reading event from the relay log; : 线程已经从中继日志读取一个事件，可以对事件进行处理；
(2) Slave has read all relay log; waiting for more updates  ：线程已经处理了中继日志文件中的所有时间，现在等待IO线程将新事件进行写入中继日志中;
(3) Waitting for slave mutex on exit;   : IO线程中state列也可以显示语句的文本,说明线程已经从中继日志读取了一个事件,从中提取了语句,并且执行语句;

查看三个线程的用途：

通过mysql线程同步状态查看数据库同步是否完成，
用于主库宕机或者人工数据库文件从切换迁移等等；
主库宕机选择最快的从库提升为主，需要查看当然也可以利用mysql的半同步功能,选择固定的库提升为主；

(7)主从配置总结

服务器环境与多台数据库环境，或者单台多实例环境，测试正常启动和登录；
配置数据库的my.cnf配置文件，主库（log-bin，server-id），从库（server-id），注意一般不开启从库的log-bin，配置参数后需要重启生效；
登录主库增加用于从库连接主库同步的账号例如：rep并授权replication slave 同步权限；
登录Master，整库锁表flush table with read lock（窗口关闭后即失效，不能关闭），然后show master status查看binlog的位置状态；
新建立一个shell窗口导出主库的数据库，并拷贝到从库所在的服务器目录中；（数据库量大的时候不用mysqldump）
解锁主库读锁，unlock tables；
把主库的数据灌入从库中；
根据主库的show master status 查看 binlog 的位置状态，在从库执行change master to （主库IP，端口，用户，密码，LOG_FILE，LOG_POS）语句
从库开启同步开关，start slave，注意需要停止stop slave 才能更改change master；
从库show slave status \G，检查同步状态，并在主库进行更新测试，在从库的data目录下会出现，masterinfo，与relay-log；

(n)主从复制半同步(待补充)

描述：mysql5.5以上才支持半同步；一主多从主库宕机,如何恢复,通过master.info确定新的主库。

直接对设置半同步的从库确定为主库,让某个稳定的从库与主库数据完全一致，即是主库与从库都将数据更新完毕后,才返回数据给用户，此时更新成功( == 实时备份同步功能)

优点：确保至少一个从库和主库数据一致
缺点:主从之间网络延迟或者从库有问题时候,返回数据给用户的时间长;(当然可以设置超时时间10s)

企业实战-主从复制

1.主库宕机从库切换主继续和从同步过程

在一主多从的情况下,主库master DOWN宕掉,解决流程:

1.登陆从库查看两个线程的更新状态与POS位置;
show processlist;
在从库 3307,3308 数据库目录中进行查看 master.info
cat /data/3307/data/master.info
cat /data/3308/data/master.info
#确保更新完毕,在从库中选择POS节点最大，甚至是于DOWN机的主库是一致的。
#当然利用半同步功能,就不用进行查看,由于只有一台是完整的POS(就是进行实时同步的这个从库 == 太子)。

2.确保所有的relay log更新完毕，在从库中执行:
Stop slave io_threaed;
show processlist;
#直到看见has|read all relay log,表示从库都指向完毕。

3.登陆选定的作为主库的从库,配置同步的用户与主库一致就行。
mysql -uroot -p123456 -S /data/3307/mysql.sock
stop slave;
retset master;
quit;

4.进入到数据库目录中,删除master.info , relay-log.info;
cd /data/3307/data && rm -rf master.info relay-log.info

5.提升3307作为主库,检查授权表。
vi /data/3307/my.cnf  #修改开启
log-bin = /data/3307/log-bin
#如果存在log-slave-updates 和read-only 等将其注释
/data/3306/mysql restart

6.登陆其他从库的操作，已检查同步的user rep 均存在
stop slave;
change master to master_host = '3307的IP';
start slave; 
show slave status;
#主库宕机切换成功

7.修理损坏的主库,完成后作为从库使用,主库down进行恢复了,将源主库直接指向现有主库的IP地址。可以修改hosts解析。

提示：如果主库服务器没有宕机,需要去拉取主库的binlog进行补全.

2.从库宕机从库重新同步过程

从库slave Down 机,恢复方法是重新做slave，直接灌数据。
恢复流程：

> stop slave;
$ gzip -d backup.sql.gz
$ mysql -uroot -p'123456' -S /data/3307/mysql.sock < ackup.sql.gz &
> change master to master_host = '192.168.1.35',master_user = 'repl',master_password = 'slavepass', master_log_file = 'mysql-bin.001440,master_log_pos = 68824 '
> start slave;
> show slave status\G;

3.主主(M-M)双向同步互为主从配置过程(不建议使用)

使用主主前提:1.表的主键自增,2.程序写库的时候进行选择库自增ID(M1:1,3,5)(M2:2.4.6)
执行流程步骤：

1.如果主主数据不同,先将从库的数据进行导出;
$ mysqldump -uroot -pSystem123@ -S /data/3306/mysql.sock -A -B --master-data=1 -x --events > 3306.bak.sql
#mysqldump: [Warning] Using a password on the command line interface can be insecure.

$ mysql -uroot -pSystem123@ -S /data/3307/mysql.sock < 3306.bak.sql
#删除原有的 binlog 日志文件(记的备份)
rm -rf binlog*
rm -rf master.info relay-log.info WeiyiGeek-relay-bin.*


2.在3306/3307互为主从进行设置主要参数
$ vi 3306/my.cnf
  auto_increment_increment = 2
  auto_increment_offset = 2
  log-bin = /data/3306/binlog
  binlog-ignore-db = information_schema
  binlog-ignore-db = mysql
  log-slave-updates  

$ vi 3307/my.cnf
  auto_increment_increment = 2   #主主自增ID间隔, 1 3 5 间隔2
  auto_increment_offset = 1     #主主偏移ID起点
  log-bin = /data/3307/binlog    #binlog记录文件
  binlog-ignore-db = information_schema
  binlog-ignore-db = mysql  #忽略binlog日志
  log-slave-updates  #启用从库binlog日志
  skip-slave-start   #启动时候忽略从库启动

6-MYSQL主从同步(DBA)配置

3.登陆主主3306/3307机器,设置3307/3306机器master(设置对方IP与端口即可);
$ mysql -uroot -pSystem123@ -S /data/3307/mysql.sock
CHANGE MASTER TO 
MASTER_HOST='127.0.0.1',
MASTER_PORT=3306,
MASTER_USER='rep',
MASTER_PASSWORD='System123@';


$ mysql -uroot -pSystem123@ -S /data/3306/mysql.sock
CHANGE MASTER TO 
MASTER_HOST='127.0.0.1',
MASTER_PORT=3307,
MASTER_USER='rep',
MASTER_PASSWORD='System123@';

start slave; #开启主从

6-MYSQL主从同步(DBA)配置

4.需要建立自增表,为了进行验证。
mysql> create database study;
Query OK, 1 row affected (0.01 sec)
mysql> use study

mysql> create table student(
  id int(4) not null AUTO_INCREMENT,
  name char(20) not null,
  primary key(id)
);
insert into student (name) values ('weiyigeek');

6-MYSQL主从同步(DBA)配置

4.MYSQL从库记录BINLOG方法

Q:Mysql中binlog日志作用时什么？
A:用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录

什么时候需要记录binlog的情况？

当前从库作为其他从库的主库(级联同步)
把从库作为数据库备份服务器时候;

应用场景：级联复制或者从库做数据备份，A--》B--》C , B 服务器要开启记录binlog日志功能；

配置方法：

#再从库的my.cnf配置文件[mysqld]中加入
log_bin = /data/3307/mysql-bin
log-slave-updates 
expire_logs_days = 7 #过期时间设置 find /data/3307/ -type f -name "mysql-bin.000*" -mtime +7 | xargs rm -f

6-MYSQL主从同步(DBA)配置

恢复流程:

新建机器,先恢复全部数据库备份 2013-01-20.sql.gz
恢复全备到数据库损坏时间节点所产生的bin-log进行导入。

5.主从生产场景中读写分离多种方案

描述：常见的是写数据在主库（简单地说就是所有的更新），从库只是读取；如果主从同时进行输入数据会导致主从复制失败；

#常用方法：SLAVE 服务器
(1) 修改用户的只读权限;
(2) web程序写的指向主库,读指向从库;
(3) 在从库my.cnf配置中进行read-only参数;

6-MYSQL主从同步(DBA)配置

生产环境中常常采用的方式，然后对从服务器(slave)上的用户仅仅授权select读权限，不同同步mysql库从而保证主库和从库相同的用户可以授权不同的权限。

GRANT SELECT ON 'blog'.* TO 'blog'@'10.0.0.%' identified by 'oldboy' #也可利用revoke进行权限回收

通过read-only参数来防止数据写从库的方法,可以在slave服务器启动选项增加参数或者在my.cnf配置文件中加入r-o参数来确保从库只读;但需要注意用户得权限，root是不受到read-only限制得，也可以在mysql启动的时候加入--read-only效果也一样；

#主库同步建立测试用户（或者在加入read-only参数前建立得用户,super权限除外）
root@localhost master>GRANT select,insert,update,delete ON *.* TO 'web'@'%' identified by 'web@web';
root@localhost master>flush privileges;

# 采用建立得mysql低权限账户登陆数据库
$ mysql -uweb -pweb@web -S "/data/3307/mysql.sock"

#验证从库是否是只读，不能写。
web@localhost slave>create database test;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement   #完成收工

6.主从复忽略指定库bin-log同步

如忽略mysql库与information_schema库主从同步,在MYSQL主库得my.cnf文件中添加下面得参数，然后进行重启即可。

replicate-ignore-db = mysql  #主从忽略的库
binlog-do-db  = testdb   #binlog需要记录的库 【master端】
binlog_ingore_db = mysql #二进制忽略的数据库多个数据库使用,分割
binlog_ingore_db = information_schema
#下面是从库/主库效果

6-MYSQL主从同步(DBA)配置
注意事项： 在 mysql5.0~5.1只在【主/从】库上面配置replication-ignore-db=mysql，才能做到主从库不同步mysql库。

主从故障(Q&A)

Q:四种方法恢复主库与从库的数据不统一?

拖拉MAster主库binlog文件到从服务器上面,进行导入到Slave从数据库中;
进行数据库双写(同时写入成功才行),需要在程序业务中进行设置;
通过临时写在在内存中的数据库数据进行同步恢复(memcache)
将异步同步变成实时同步(google有个半同步插件),将主从绑定在一起,主从都成功才返回OK；

Q:CHANGE MASTER LAST IO ERROR 1236 FROM MASTER
原因：change master 时候参数两边多了空格;MASTER_LOG_FILE=' mysql-bin.000010 '; #如这样

Q:mysql锁表时问题？
答：锁表命令的时间，在不同的引擎的情况，会受到下面参数的控制；并且锁表时候如果超过设置时间(或者时间太短)，它自己操作自动解锁表

interactive_timeout = 60;
wait_timeout = 60
    
#默认情况下的
> show variables like '%timeout%';
> set global interactive_timeout = 1600;   #进行设置（需要退出进入生效）
> set global wait_timeout = 1660
> set global lock_wait_timeout = 3156000

#锁表语句
> flush table with read lock;    #5.5 ok
> flush tables with read lock;   #5.1

Q:由于切换binlog 导致 show master status 位置变化无影响？

#在锁表的时候进行mysqldump导出数据库
mysqldump -uroot -p123456 -F -A -B --events > /opt/back.sql;   //-F 会刷新bin-log 可导致 master status中

Q:MySQL同步故障"Slave_IO/SQL_Running:No"解决办法?
Slave_IO_Running:No 解决办法一：
查看master.info中主库的slave replication 账号密码是否正确，主库IP、port能否正常连接；

1.网络不通      #互ping机器ip，无丢包率正常访问，排除
2.密码不对      #MySQL重新授权远程用户登陆账号，重新设置密码，排除
3.pos不正确     #主服务器,登陆数据库重新查看起始偏移量show master status,排除
4.ID问题        #ID的问题，在安装完mysql数据库的时候默认他们的server-id=1 但是在做主从同步的时候需要将ID号码设置不一样才行,查看数据库配置文件cat /etc/my.cnf，文件写的不同
5.防火墙策略     #查看防火墙策略，是否放通双方的服务端口 iptables -nL,最后发现 防火墙策略写了多端口防火墙策略的端口不生效，解决防火墙策略单独开放端口，暂停从服务的io读取stop slave;

Slave_SQL_Running:No 解决办法一：
1.程序可能在slave上进行了写操作
2.也可能是slave机器重起后，事务回滚造成的，一般是事务回滚造成的：

#解决办法：
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

#解决办法二：
首先停掉Slave服务：slave stop 到主服务器上查看主机状态，记录File和Position对应的值进入master；
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000006 |      760 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

#然后到slave服务器上执行手动同步：
stop slave;
CHANGE MASTER TO MASTER_HOST='127.0.0.1',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='System123@',MASTER_LOG_FILE='binlog.000006',MASTER_LOG_POS=760;
start slave;
mysql> show slave status\G

vlambda博客
学习文章列表