CENTOS7 configuring MySQL Master-slave: one master multiple slave

MySQL master-slave replication principle
1. First master records data updates to a binary Binlog file
2.slave requests the Binlog log file to the master via the I/O thread to specify the content after the location
3.master receives slave IO request, it will start from binlog corresponding location point, to slave log
4.slave after receiving the log, it is written to the local relay log relay log
5.slave reads the contents of the trunk log through the SQL thread, performs the appropriate operations in the database, ends with the same data on master and slave, and then slave the server into a wait state, waiting for a subsequent update of master
System Environment

Server	IP Address	Operating System	Installing the Software
Mysql_master	192.168.2.217	Centos 7 64-bit	mysql-5.7.23
Mysql_slave1	192.168.2.218	Centos 7 64-bit	mysql-5.7.23
Mysql_slave2	192.168.2.219	Centos 7 64-bit	mysql-5.7.23

MySQL is compiled through the source of the installation, configuration files:/etc/my.cnf, installation directory:/data/mysql, data storage directory:/data/mysql/data,bin-log log directory:/data/mysql/bin-log, Test Synchronization database: Zabbix
Working on Master
(1) Create Bin-log log storage directory

mkdir /data/mysql/bin-logchown -R mysql:mysql /data/mysql/bin-log

(2) Modify the master configuration file and add the following configuration under [Mysqld]

server-id=1   #master IDlog-bin=/data/mysql/bin-log/mysql-bin   #指定binlog日志目录binlog-do-db=zabbix   #指定生成binlog日志的数据库

(3) If the FIREWALLD firewall is turned on, you need to open the MySQL port

firewall-cmd --permanent --add-rich-rule="rule family="ipv4" source address="192.168.2.0/24" port protocol="tcp" port="3306" accept"firewall-cmd --reload

(4) Restart MySQL
service mysqld restart
(5) Create the Zabbix database and import the table structure

mysql> create database zabbix character set utf8 collate utf8_bin;mysql> use zabbix;mysql> source /tmp/schema.sql;

(6) View Bin-log and master status

mysql> show variables like "log_bin";mysql> show master status;

(7) Create a sync user

mysql> grant replication slave on *.* to ‘xuad‘@‘192.168.2.%‘ identified by ‘xu123456‘;mysql> select user,host from mysql.user;

operate from a library on two sets
(1) Create a Zabbix database from two libraries

mysql> create database zabbix character set utf8 collate utf8_bin;mysql> exit

(2) Back up a complete database on master and import on two slave

mysqldump -uroot -p123456 --single-transaction --master-data=2 zabbix > /tmp/zabbix.sqlscp -P 22 /tmp/zabbix.sql [email protected]:/tmp/scp -P 22 /tmp/zabbix.sql [email protected]:/tmp/mysql -uroot -p123456 zabbix < /tmp/zabbix.sql

(3) Modify the Slave configuration file and add the following configuration under [Mysqld]

server-id=2  #slave ID，另一台配置成3replicate-do-db=zabbix   #指定同步的数据库

(4) Restart two slave MySQL
service mysqld restart
(5) Create master-slave synchronization on two slave respectively

mysql> change master to  master_host=‘192.168.2.217‘, master_port=3306, master_user=‘xuad‘, master_password=‘xu123456‘, master_log_file=‘mysql-bin.000001‘, master_log_pos=211943;mysql> start slave;mysql> show slave status\G

Test
(1) Import Zabbix data on Master

mysql -uroot -p123456 zabbix < /tmp/images.sqlmysql -uroot -p123456 zabbix < /tmp/data.sql

(2) See if the position of the master and slave states are consistent
mysql> show master status;

mysql> show slave status\G

(3) Check if data is already available from the library.

mysql> use zabbix;mysql> select userid,alias,name,passwd from users;

Now mysql a master dual slave configuration complete

CENTOS7 configuring MySQL Master-slave: one master multiple slave