MySql 主从
主从原理
MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点
image.png
- 主节点binary log dump 线程
当从节点链接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发送给从节点之前，锁会被释放 - 从节点I/O线程
从节点上执行‘start slave’命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。IO线程收到主节点点binlog dumo 进程发来的更新之后，保存在本地的relay-log中。 - 从节点sql线程
sql线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性
对于每一个主从连接，都需要三个线程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O线程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。
要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。
因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作
ED360451-2256-4BA8-A2A1-4DA430AD8781.png
主从搭建
配置主服务器
- 准备两台装有mysql的服务器
1.172.16.122.131 主服务器
2.172.16.122.132 从服务器 - 准备工作
同步主从数据库已有的数据
1、将主服务器数据库中数据导出,导入到从服务器
mysqldump -uroot -p123456 --databases menagerie > /opt/menagerie.sql;
scp menagerie.sql root@172.16.122.132:/opt
# 从服务器上执行
source /opt/menagerie.sql;
2、修改配置
vi /etc/my.cnf
#主数据库端ID号
server_id = 1
#开启二进制日志
log-bin = mysql-bin
#需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
#binlog-do-db = db
#将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
#控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
sync_binlog = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1
#这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1
#二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
#将函数复制到slave
log_bin_trust_function_creators = 1
3、在 Master 的数据库中建立一个复制账户，每个 Slave 使用该账户连接 Master 进行复制，需要 replication slave 和replication client 权限，Master 的连接信息会存储在文本文件 master.info 文件中。(master.info文件在 Slave 的数据目录中)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'172.16.122.132' IDENTIFIED BY '123456';
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
说明：创建了一个用户名为 replication 的用户，密码为 123456,只允许在 172.16.122.132 这个 Slave 上登录。
3.重启
[root@localhost opt]# systemctl restart mysqld
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 154 | db | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
配置从服务器
1、修改配置
vi /etc/my.cnf
server_id = 2
log-bin = mysql-bin
log-slave-updates
sync_binlog = 0
#log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候，不会主动触发写入磁盘的操作
innodb_flush_log_at_trx_commit = 0
#指定slave要复制哪个库
#replicate-do-db = db
#MySQL主从复制的时候，当Master和Slave之间的网络中断，但是Master和Slave无法察觉的情况下（比如防火墙或者路由问题）。Slave会等待slave_net_timeout设置的秒数后，才能认为网络出现故障，然后才会重连并且追赶这段时间主库的数据
slave-net-timeout = 60
log_bin_trust_function_creators = 1
3、重启mysql
[root@localhost ~]# systemctl restart mysqld
2、执行同步命令
# 执行同步命令，设置主服务器ip，同步账号密码，同步位置
mysql> change master to master_host='172.16.122.131',master_user='replication',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos= 154;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
#开启同步功能
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
# 查看slave 状态
mysql> show slave status\G;
image.png
坑
描述：由于一开始主服务器上是由数据的，从服务器上没有。配置好主从后，删除主服务器上的数据从服务器读取bin-log执行发现没有数据报错
image.png
解决方法：从机可以直接跳过
stop slave;
set global sql_slave_skip_counter=1;
start slave;
1、几月以后打开虚拟机发现主从不同步了。。这可如何是好
image.png
image.png
打开日志文件/var/log/mysqld.log
image.png
原因找到了是因为找不到'./localhost-relay-bin.000022' (relay_log_pos 367)
slave reset；
slave reset执行候做了这样几件事：
1、删除slave_master_info ，slave_relay_log_info两个表中数据；
2、删除所有relay log文件，并重新创建新的relay log文件；
3、不会改变gtid_executed 或者 gtid_purged的值
mysql> reset slave;
Query OK, 0 rows affected (0.01 sec)
mysql> change master to ......
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
启动slave
2. change maser 报错
> change master to master_host='172.16.122.131',master_user='replication',master_password='123456',master_log_file='mysql-bin.000002',master_log_pos= 154;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL '' first.
需要先stop slave
命令
mysql --help|grep 'my.cnf'
日志相关
配置文件：/etc/my.cnf
错误日志：/var/log/mysqld.log
参考：https://www.jianshu.com/p/db19a1d384bc
https://blog.51cto.com/pangge/1319304
https://segmentfault.com/a/1190000020709478
https://blog.51cto.com/xiaocao13140/2120039
将binlog伪装成relaylog
http://blog.itpub.net/29773961/viewspace-2143726/
