Shulou(Shulou.com)06/01 Report--

Principle of mysql master-slave replication

1. First, master records data updates to a binary binlog file

2.slave requests the content after the location of the binlog log file is specified from the master through the Icano thread.

After 3.master receives the io request from slave, it will send logs to slave starting from the corresponding location of binlog.

When 4.slave receives the log, it is written to the local relay log relay log

5.slave reads the contents of the relay log through the sql thread and performs the corresponding operation in the database. So far, the data on master and slave are the same, and then the slave server enters a waiting state, waiting for subsequent updates of master.

System environment

Server IP address operating system installation software Mysql_master192.168.2.217Centos 7 64 bit mysql-5.7.23Mysql_slave1192.168.2.218Centos 7 64 bit mysql-5.7.23Mysql_slave2192.168.2.219Centos 7 64 bit mysql-5.7.23

Mysql is compiled and installed through source code, configuration file: / 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

Operate on master

(1) create a directory for storing bin-log logs

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

(2) modify the configuration file of master and add the following configuration content under [mysqld]

Server-id=1 # master IDlog-bin=/data/mysql/bin-log/mysql-bin # specify the binlog log directory binlog-do-db=zabbix # specify the database from which the binlog log is generated

(3) if the firewalld firewall is enabled, you need to open the mysql port

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

(4) restart mysql

Service mysqld restart

(5) create zabbix database and import table structure

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

(6) check the status of bin-log and master

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

(7) create synchronous users

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

Operate on two slave libraries

(1) create zabbix databases on two slave databases

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

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

Mysqldump-uroot-p123456-- single-transaction-- master-data=2 zabbix > / tmp/zabbix.sqlscp-P 22 / tmp/zabbix.sql root@192.168.2.218:/tmp/scp-P 22 / tmp/zabbix.sql root@192.168.2.219:/tmp/mysql-uroot-p123456 zabbix

< /tmp/zabbix.sql （3）修改slave的配置文件，在[mysqld]下面添加如下配置内容 server-id=2 #slave ID，另一台配置成3replicate-do-db=zabbix #指定同步的数据库 （4）重启两台slave的mysql service mysqld restart （5）分别在两台slave上创建主从同步 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）查看master和slave状态的Position是否一致 mysql>

Show master status

Mysql > show slave status\ G

(3) check whether there is data on the slave database.

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

At this point, the configuration of mysql with one master and two slaves is complete.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.