关于MySQL5.7开启bin-log主从复制

时间：2023-03-08 16:59:10

主从复制：一般用于实时备份。也可配合mycat，实现读写分离。

传统的基于 ROW的主从复制

简单说下步骤： master主库配置同步，slave从库配置同步，master锁表/备份，slave恢复数据，slave启用同步，master解锁，完成。

##################### mysql5.7 bin-log日志开启, 必须设置server-id ###################

###############  master 主机

vim /etc/my.cnf  # [mysqld] 部分增加以下配置
server-id=1 #服务器id (主从必须不一样)

log-bin=mysql-bin # 打开日志(主机需要打开)，这个mysql-bin 可自定义，也可加上路径

binlog-do-db=monitor_db #要给从机同步的库

binlog-ignore-db=mysql #不给从机同步的库(多个写多行)

binlog-ignore-db=mysqlslap

binlog-ignore-db=information_schema

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

expire_logs_days=3 # 自动清理 3 天前的log文件 可根据需要修改


# 保存退出，重启mysql服务  service mysqld restart






cd /var/lib/mysql           # 可以看到mysql-bin.000001

依次执行下方SQL语句:




show variables like "%log_bin%";  # 查看 log_bin  是否 ON

-- 授权backup用户可以进行远程复制

-- grant FILE on *.* to 'backup'@'%' identified by 'backup';

-- grant replication slave on *.* to 'backup'@'%' identified by '123456';

-- flush privileges;
-- use mysql;

-- select user,authentication_string,host from user;

-- flush tables with read lock;  -- 锁定主数据库, 防止主从不一致（此时，主数据库无法再写入数据）

-- show master status;   -- ( 如果加 \G 则需要shell中执行) 显示master信息, 记下File和Position






# 然后执行主数据库备份，并还原到从数据库。（无法做到主数据库不停服）






###############  slave 从机

vim /etc/my.cnf  # [mysqld] 部分增加以下配置





log-bin=mysql-bin

server-id=2

binlog-ignore-db=information_schema

binlog-ignore-db=cluster

binlog-ignore-db=mysql

replicate-do-db=monitor_db

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

slave-net-timeout=60 






# 保存退出并重启mysql 并SQL执行以下操作:

-- stop slave;   
-- 下面一行替换成实际参数，注意用户名密码，以及 master 上拿到的File和Position

-- change master to master_host='192.168.112.6',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;

-- start slave;

-- UNLOCK TABLES;  -- 到主库上执行解锁主库

-- show slave status;    --显示slave信息

# master_log_file是 Master 主机 show master status 显示的File，而master_log_pos是显示的Position。

# 然后可以通过show slave status查看 slave 中的position和file是否和master中的对应一致。

# 对master数据库进行CURD，验证slave库是否有相应同步



################################ 其它常用操作 ############################

flush logs;           -- 刷新日志 生成新的mysql-bin文件，编号增加

show master status;   -- 查看二进制日志和Position

reset master; --清空bin-log, 从头开始 mysql-bin.000001 不要随意执行，slave会当掉。slave上需要重新指定。






-- 恢复slave方法，在slave机器上操作重新指定

-- stop SLAVE;

-- reset slave;

-- change master to master_host='192.168.112.5',master_user='backup',master_password='123456',master_log_file='mysql-bin.000001', master_log_pos=154;

-- start slave;






使用GTID方式的主从复制


需要给 master / slave 的 my.cnf 文件，在上面已有的配置基础上，再加2句：




gtid_mode=ON

enforce-gtid-consistency=ON




然后重启master 的 mysqld服务，修改一下复制用户的密码：




-- use mysql;

-- update user set authentication_string=password('backup'), password_expired ='N', password_last_changed=now() where user='backup'; -- 修改密码root

-- flush privileges;  --刷新




再重启slave的mysqld服务，然后重新连接master:




-- stop SLAVE;

-- change master to master_host='192.168.112.5',master_user='backup',master_password='backup', master_auto_position=1; -- GTID方式

-- start slave;




此时再执行 show master status; 以及 show slave status;  将得到相同的 Executed_Gtid_set


若不小心 reset master 了。那么 slave 的恢复连接执行以下方法。（数据的差异可能要手工同步了。）




-- 恢复slave方法

-- reset master; --没错，slave上也执行一次。

-- stop SLAVE;

-- reset slave;

-- change master to master_host='192.168.112.5',master_user='backup',master_password='backup', master_auto_position=1; -- GTID方式

-- start slave;




启用slave的多线程复制，修改my.cnf文件，增加以下内容，然后保存重启mysqld服务.




slave-parallel-type=LOGICAL_CLOCK

slave-parallel-workers=8 # 8个线程

master_info_repository=TABLE

relay_log_info_repository=TABLE




使用 show PROCESSLIST; 可以看到线程列表。


更多主从复制内容，可参考：https://www.cnblogs.com/uthnb/p/9719800.html    以及  https://www.cnblogs.com/wangdong/p/9808871.html


其它相关命令




\s    --（仅命令行有效）查看状态，包括字符集/运行时间/版本/线程数等。


#使用自带工具来查看bin-log

mysql/bin/mysqlbinlog --no-defaults mysql-bin. |more

#备份数据库时可以 flush logs; 就会生成新的bin-log

#恢复时间差里没有备份的数据到test库

mysql/bin/mysqlbinlog --no-defaults mysql-bin. |/usr/local/mysql/bin/mysql -uroot -p123 test

#备份数据: -F即flush logs; -l是读锁, 只能读不能写.

mysqldump -uroot -p123 test -l -F > '/tmp/test.sql'

#恢复数据: 先恢复之前的备份,再恢复时间差内的.

mysql -uroot -p123 test < /tmp/test.sql  -- 参数-f是遇到错误跳过

mysql/bin/mysqlbinlog --no-defaults mysql-bin. |/usr/local/mysql/bin/mysql -uroot -p123 test

#选择节点的恢复 即多了起始点的选择

mysql/bin/mysqlbinlog --no-defaults mysql-bin. --start-position="" --stop-position="" |/usr/local/mysql/bin/mysql -uroot -p123 test

show slave status\G # 反转行列查看,有下面两列即表示主从同步成功.

Slave_IO_Running: Yes

Slave_SQL_Running:Yes  





若需要修改账号密码，则执行：




-- update user set authentication_string=password('root'), password_expired ='N', password_last_changed=now() where user='root'; -- 修改root密码

-- flush privileges;  --刷新即可生效




若需要增大内存利用率，执行下面的set命令，官方建议物理内存的50%~70%   # (adjust value here, 50%-70% of total RAM)




-- SELECT @@innodb_buffer_pool_size;     -- 查看innodb_buffer_pool_size

-- SELECT @@innodb_buffer_pool_chunk_size; -- 块大小,默认128M

-- SELECT @@innodb_buffer_pool_instances;  -- 实例数, 默认8

-- SET GLOBAL innodb_buffer_pool_size=1073741824;  -- 1G

-- SET GLOBAL innodb_buffer_pool_size=8589934592;  -- 8G -- 设置innodb_buffer_pool_size 不必重启




若需要重启服务后依然有效，则需要修改my.cnf文件。 增加一行  innodb_buffer_pool_size = 8G


自带压力测试：




######################### 自带压力测试 #################################

# 在OS的shell下执行命令:

mysqlslap -hlocalhost -uroot -p123456 -P3306 --concurrency= --iterations= --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=

# 可能需要先修改OS的连接数 ulimit -n

# 以及mysql的 max_connections

show variables like '%max_connections%'  -- 最大连接数

set global max_connections=;         -- 改变当前进程中的设置. 永久设置需要在my.cnf中加入