一、环境准备
操作系统: centos7
数据库版本: mysql5.7
数据库信息:
主机 | 主机ip | 账户 | 密码 | 端口 | 数据库 |
master | 192.168.2.131 | root | 123456 | 3306 | appt_db |
slave | 192.168.2.132 | root | 123456 | 3306 | appt_db |
二、主从配置
1、master主节点配置
(1) 配置my.cnf
log-bin=mysql-bin
#log-error=mysql-error
server-id=1
#不同步数据库
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
#只仅同步数据库
binlog-do-db=app_db
#mysql复制模式,三种:SBR(基于sql语句复制),RBR(基于行的复制),MBR(混合模式复制)
#binlog_format=MIXED
(2)主节点授权从节点复制
授权可以复制本机数据的从节点
grant replication slave,reload,super on *.* to 'root'@'192.168.2.132' identified by '123456';
flush privileges;
(3)查看master状态
查看master状态,获取mysql-bin和position
show master status;
2、slave从节点配置
(1)配置my.cnf
(1)slave从机配置my.cnf
server-id=2
log-bin=mysql-bin
replicate-do-db=app_db
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
#binlog_format=MIXED
(2)从机授权(master_log_file主机二进制日志 master_log_pos 复制位置)
change master to master_host='192.168.2.131', master_user='root',master_password='root',master_port=3306,
master_log_file='mysql-bin.000009',master_log_pos=154;
flush privileges;
(3)启动从节点查看状态
start slave;
show slave status\G;
(2)设置从节点复制bin-log
change master to master_host='192.168.2.131', master_user='root',master_password='root',master_port=3306,
master_log_file='mysql-bin.000009',master_log_pos=154;
flush privileges;
(3) 启动从节点
start slave;
show slave status\G;
3、mysql密码策略
SHOW VARIABLES LIKE 'validate_password%';
set global validate_password_policy=0;
set global validate_password_length=4;
set global validate_password_mixed_case_count=2;
set global validate_password_number_count=0;
三、清除主从关系
master操作:
reset master;
slave操作
stop slave;