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