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

主从复制：一般用于实时备份。也可配合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.000001 |more

#备份数据库时可以 flush logs; 就会生成新的bin-log
#恢复时间差里没有备份的数据到test库
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 |/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.000002 |/usr/local/mysql/bin/mysql -uroot -p123 test

#选择节点的恢复 即多了起始点的选择
mysql/bin/mysqlbinlog --no-defaults mysql-bin.000002 --start-position="193" --stop-position="280" |/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=5000 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000
# 可能需要先修改OS的连接数 ulimit -n 65535 
# 以及mysql的 max_connections  
show variables like '%max_connections%'  -- 最大连接数
set global max_connections=6000;         -- 改变当前进程中的设置. 永久设置需要在my.cnf中加入