mysql 主从搭建

新建三台虚拟机：
A 192.168.4.200 主
B 192.168.4.100 主/从
C 192.168.4.5 从

注意开启3306端口*

systemctl stop firewalld.service
SELINUX=Permissive
SELINUXTYPE=disabled
A是B的master 。 B是C的master同时也是A的slave。C是B的slave。
一、安装mysql：（三台虚拟机都需要安装mysql）
1、tar -xvf mysql-5.7.17-1.el7.x86_64.rpm-bundle.tar
30 rpm -Uvh mysql-community-*.rpm（没有安装common和client 会报错）

2、查密码之前需要启动mysql的服务
systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld
grep -i “password” /var/log/mysqld.log
3、修改密码策略
步骤：：:::::::::::::::::::::::::::::::::::::::::::::::::::（注意顺序）
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> SET PASSWORD = PASSWORD(‘123456’);
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like “%password%”;
±--------------------------------------±------+
| Variable_name | Value |
±--------------------------------------±------+
| default_password_lifetime | 0 |
| disconnect_on_expired_password | ON |
| log_builtin_as_identified_by_password | OFF |
| mysql_native_password_proxy_users | OFF |
| old_passwords | 0 |
| report_password | |
| sha256_password_proxy_users | OFF |
| validate_password_check_user_name | OFF |
| validate_password_dictionary_file | |
| validate_password_length | 6 |
| validate_password_mixed_case_count | 1 |
| validate_password_number_count | 1 |
| validate_password_policy | LOW |
| validate_password_special_char_count | 1 |
±--------------------------------------±------+

4、修改192.168.4.200 主服务器
[root@zhu ~]# vim /etc/my.cnf

validate_password_policy=0
validate_password_length=6
server_id=1
log_bin=zhu
binlog_format=mixed
sync-binlog=1

[root@zhu ~]# systemctl restart mysqld
主节点授权
GRANT REPLICATION SLAVE ON *.*TO ‘replicater’@‘192.168.4.100’ IDENTIFIED BY ‘pwd123’;（允许replicater 用户可以从192.168.4.100 用密码pwd123 来登录mysql数据库）

mysql> show master status\G （查看主服务的file值和pos值）
*************************** 1. row ***************************
File: zhu.000003
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

5、配置192.168.4.100 （主/从）服务器
root@cong1 ~]# vim /etc/my.cnf

validate_password_policy=0
validate_password_length=6
server_id=2
log_bin=c1
sync-binlog=1
read_only=1
log_slave_updates = 1（生成自己的日志文件）

[root@cong1 ~]# systemctl restart mysqld
[root@cong1 ~]# !my
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.4.200’,
MASTER_USER=‘replicater’,
MASTER_PASSWORD=‘pwd123’,
MASTER_LOG_FILE=‘zhu.000001’,
MASTER_LOG_POS=154; （可以设置为0 start slave后 自己会 跟新到）
mysql>start slave;（启用slave）

mysql> show slave status\G（查看slave状态）

Master_Log_File: zhu.000003
Read_Master_Log_Pos: 154
Relay_Log_File: cong1-relay-bin.000010
Relay_Log_Pos: 355
Relay_Master_Log_File: zhu.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主节点授权
GRANT REPLICATION SLAVE ON *.*TO ‘jiji’@‘192.168.4.5’ IDENTIFIED BY ‘pwd123’;
mysql> show master status\G
File: c1.000005
Position: 154
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

6、配置192.168.4.100 从服务器
[root@cong2 ~]# vim /etc/my.cnf
validate_password_policy=0
validate_password_length=6
server_id=3
log_bin=c2
sync-binlog=1
read_only=1

[root@cong2 ~]# systemctl restart mysqld
[root@cong2 ~]# !my
从节点配置：
mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.4.100’,
MASTER_USER=‘jiji’,
MASTER_PASSWORD=‘pwd123’,
MASTER_LOG_FILE=‘c1.000005’,
MASTER_LOG_POS=154;

mysql>start slave;

mysql> show slave status\G

Master_Log_File: c1.000005
Read_Master_Log_Pos: 154
Relay_Log_File: cong2-relay-bin.000008
Relay_Log_Pos: 353
Relay_Master_Log_File: c1.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes