MySQL server master-slave service detailed

MySQL master-slave replication concept

• MySQL's built-in replication capabilities are the foundation for building large, high-performance applications. The distribution of MySQL data across multiple systems is done by copying data from one of the MySQL hosts to the other host (slaves) and re-executing it again. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes the update to the binary log file and maintains an index of the file to track the log loop. These logs can record updates that are sent to the slave server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

• Note: In the case of MySQL replication, all updates to the tables in the replication must be made on the primary server. Otherwise, you must be careful to avoid conflicts between updates to tables on the primary server and updates made to tables on the server.

MySQL master-slave replication principle (1) MySQL support replication type

• Statement-based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL uses statement-based replication by default and is more efficient. Row-based replication is automatically selected as soon as it is found that it cannot be copied accurately.
• Row-based replication: Copy the changed content past, rather than executing the command from the server again. Support Starting from mysql5.0
• Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

(2) MySQL master-slave replication process

• MySQL master-slave replication Basic Flowchart:

• The replication process is detailed:

• 1, before each thing update data is complete, Master master server in the binary log record these changes, write to the binary log completion, Master notifies the storage engine to commit things.
• 2, slave the Master master server binary logs binary log into which the secondary log relay log, first slave (from the server) to start a worker thread--i/o thread, I/O thread open a normal connection on master, It then starts Binlog dump process, which reads the event from the master's binary log, and if it has followed master, it sleeps and waits for Master to produce new events, and the I/O thread writes these events to the relay log.
• 3. Sqlslave thread (SQL slave thread) handles the last step of the process, where the SQL thread reads the event from the log and replays the events in it to update the salve data so that it is consistent with the data in master as long as the thread is consistent with the I/O thread. Trunk logs are typically located in the OS cache (that is, in memory), so the trunking overhead is minimal.

(3) master-slave copy attention point

• 1, master logs the action statement to the Binlog log, and then grants the slave remote connection permission (master must turn on the Binlog binary log function, usually for data security considerations, Slave also turns on Binlog function).
• 2, slave Open two threads: IO thread and SQL thread. Where: The IO thread is responsible for reading the Binlog content of master to the trunk log relay logs; The SQL thread is responsible for reading Binlog content from the relay log log and updating it to the slave database. This will ensure that the slave data is consistent with the master data.
• 3, MySQL replication requires at least two MySQL services, of course, the MySQL service can be distributed on different servers, you can also start multiple services on a single server.
• 4. mysql replication is best to ensure that the MySQL version on the master and slave servers is the same (if the version is not consistent, make sure that the master Master node version is lower than the slave from the node version).
• 5, master and slave two time between nodes need to be synchronized.

MySQL Master Service Deployment Lab Environment

Server	Server System	IP Address	the services required
MySQL master server	centos7.3	192.168.144.128	ntp,mysql5.7
MySQL slave server	centos7.3	192.168.144.129	ntp,mysql5.7

MySQL master server

• Set the primary server as the local clock source
  Install time synchronization software:

Yum Install Ntp-y
Vim/etc/ntp.conf

Last line add:

server 127.127.144.0                    //设置本地是时钟源，注意自己所在网段是144段fudge 127.127.144.0 stratum 8          //设置时间层级为8（限制在15内）

Service NTPD Start
Service Iptables Stop
Setenforce 0

• installing MySQL Software

The MySQL5.7 version is installed here on the Centos7, please refer to the Lamp-mysql construction for detailed procedure.

• After installing MySQL:

Vim/etc/my.cnf

[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 10              //设置主从服务器身份唯一标识log-bin=master-bin          //开启二进制日志log-slave-updates=true      //开启主从功能

Systemctl Restart Mysqld.service

Mysql-u root-p

GRANT REPLICATION SLAVE ON *.* TO ‘myslave‘@‘192.168.144.%‘ IDENTIFIED BY ‘123456‘;  //将从服务器权限赋予账号myslave，可以在192.168.144.0网段登录，密码为123456 FLUSH PRIVILEGES;     //刷新状态show master status;  //查看主服务器状态

• Remember the primary server status!

MySQL slave server

• Install NTP time synchronization software, since the master server has set up a time source, from the server only need to open the time synchronization service, and synchronization with the primary server.

Yum Install Ntp-y
Systemctl Restart Mysqld.service
Systemctl Stop Firewalld/service
Setenforce 0
/usr/sbin/ntpdate 192.168.144.128//Time synchronization//

• installing MySQL Software

Vim/etc/my.cnf

[mysqld]user = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/dataport = 3306character_set_server=utf8pid-file = /usr/local/mysql/mysqld.pidsocket = /usr/local/mysql/mysql.sockserver-id = 20              //设置唯一身份标识relay-log=relay-log-bin     //打开中继日志功能relay-log-index=slave-relay-bin.index   //定义relay-log的位置和名称

• Enter the database

Mysql-u root-p//

change master to master_host=‘192.168.144.128‘,master_user=‘myslave‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=604;   //切记根据主服务器状态更改相应二进制日志文件和偏移量；start slave;    //开启从服务器状态show slave status\G;     //查看状态

Validating master-Slave results

• When you choose to create data in the primary MySQL server, the data files are also updated synchronously from the server.

MySQL server master-slave service detailed