MYSQL主从同步(DBA)配置.md

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

#实现方法：复制环境用户的授权：
写库 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

# 配置 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)

# 配置 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

#列出了主服务器的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 线程停止时候发生的一个简单的状态;

# 从服务器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列也可以显示语句的文本,说明线程已经从中继日志读取了一个事件,从中提取了语句,并且执行语句;

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解析。

> 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;

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   #启动时候忽略从库启动

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; #开启主从

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');

#再从库的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

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

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

#主库同步建立测试用户（或者在加入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   #完成收工

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

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

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

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;

#解决办法：
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