mysql同步数据库，一主两从案例

试验第一目的：B、C都从主库A同步数据。第二目的：B库从A库同步，C库从B库同步数据。

主库：192.168.99.10
从库：192.168.99.11
从库：192.168.99.15

主库上配置：

1、防火墙操作：
[root@localhost ~]# systemctl status firewalld

[root@localhost ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@localhost ~]# firewall-cmd --reload

[root@localhost ~]# systemctl status firewalld

2、配置文件

[mysql@mysql1 ~]$ more /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
basedir = /home/mysql/mysql
datadir = /home/mysql/mysql/datafile
port = 3306
socket = /home/mysql/mysql/mysql.sock
character-set-server=utf8
 
log-error = /home/mysql/mysql/log/mysqld.log
pid-file = /home/mysql/mysql/data/mysqld.pid

log-bin=mysql-bin
server-id=2
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
binlog-do-db=test

登录数据库
mysql -uroot -pmysql

执行
grant all privileges on *.* to 'root'@'192.168.99.11' identified by 'mysql';
grant all privileges on *.* to 'root'@'192.168.99.15' identified by 'mysql';

mysql>show master status

从库192.168.99.11配置文件：

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
basedir = /home/mysql/mysql
datadir = /home/mysql/mysql/datafile
port = 3306
socket = /home/mysql/mysql/mysql.sock
character-set-server=utf8
 
log-error = /home/mysql/mysql/log/mysqld.log
pid-file = /home/mysql/mysql/data/mysqld.pid

log-bin=mysql-bin
server-id=3
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

进入数据库,执行如下命令：

mysql -uroot -pmysql

mysql>change master to master_host='192.168.99.10', master_user='root', master_password='mysql', master_log_file='mysql-bin.000006',
master_log_pos=1540;

mysql>show slave status

从库192.168.99.15配置文件（测试从主库192.168.99.10上同步数据）：

[root@mysql1 ~]# more /etc/my.cnf
[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
 
basedir = /home/mysql/mysql
datadir = /home/mysql/mysql/datafile
port = 3306
socket = /home/mysql/mysql/mysql.sock
character-set-server=utf8
 
log-error = /home/mysql/mysql/log/mysqld.log
pid-file = /home/mysql/mysql/data/mysqld.pid

log-bin=mysql-bin
server-id=4
binlog-ignore-db=information_schema
binlog-ignore-db=cluster
binlog-ignore-db=mysql
replicate-do-db=test
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
slave-net-timeout=60

进入数据库,执行如下命令：

mysql -uroot -pmysql
---（测试从主库192.168.99.10上同步数据）
mysql>change master to master_host='192.168.99.10', master_user='root', master_password='mysql', master_log_file='mysql-bin.000006',
master_log_pos=1540;

mysql>show slave status

---（测试从主库192.168.99.11上同步数据）
mysql>change master to master_host='192.168.99.11', master_user='root', master_password='mysql', master_log_file='mysql-bin.000006',
master_log_pos=1540;