MySQL 配置一主多从方法

1、确保每台机器mysql正常运行

ansible mysql -m shell -a 'ps -ef|grep mysql' -k  

2、4台机器确保server-id不相同即可

ansible mysql -m shell -a 'cat /etc/my.cnf|grep server-id' -k

3、master上操作

CREATE USER 'repl'@'192.168.122.%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.122.%';

FLUSH PRIVILEGES;

4、master上操作

show master status;

记住mysql-bin.000004 |      771 |

5、三台从库上执行

change master to master_host='192.168.122.114', master_user='repl', master_password='123456', master_log_file='mysql-bin.000004', master_log_pos=771;

6、三台从库上执行

mysql> start slave;

Query OK, 0 rows affected (0.06 sec)

mysql> 

7、三台从库执行

show slave status\G;

8、主库上创建一个库进行测试

mysql> create database devops0303 character set utf8 collate utf8_bin;

Query OK, 1 row affected (0.04 sec)

mysql> grant all privileges on devops0303.* to devops@'%' identified by 'a123';

Query OK, 0 rows affected, 1 warning (0.04 sec)

mysql> FLUSH PRIVILEGES;

Query OK, 0 rows affected (0.05 sec)

mysql> 

9、三台从库分别测试

show databases;