MySQL Master-slave replication

Mariadb/mysql 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 (master) to the other host (slaves) and performing 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 updates to the binary log files and maintains an index of the files to track the log loops that can record updates sent to the 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.

1. The type of replication supported by MySQL:

1) Statement-based replication: SQL statements executed on the primary server execute 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 accurately copied.

2) Row-based replication: Copy the changed content past, rather than executing the command on the server. Support Starts from mysql5.0.

3) Mixed-type replication: statement-based replication is used by default, and row-based replication occurs when a statement-based, inaccurate copy is found.

2, the Problem of replication solution:

1) data distribution (distribution)

2) Load balancing (load Balancing)

3) Backup (Backups)

4) High availability and fault tolerance (hi availability and Failover)

3. How replication works:

1) Master will change the record to binary log (binary logs events);

2) slave copies the binary log events of Master to its own relay logs (relay log);

3) Slave redo the events in the trunk log and change the data to reflect your own.

Part of this is the master record binary log. Master records These changes in binary logging before each transaction updates the data. MySQL writes the transaction serially to the binary log, even if the statements in the transaction are cross-executed. After the event is written to the binary log, master notifies the storage engine to commit the transaction.

The next step is to slave copy the Binary log of Master to its own trunk logs. First, slave starts a worker thread ――i/o thread. The I/O thread opens a normal connection on master and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for master to produce a new event. The I/O thread writes these events to the trunk log Relaylogs.

SQL from the thread (SQL slave thread) processes the last step of the process. SQL reads events from a thread from the log and replays the events in it to update the slave data so that it matches the data in master. As long as the thread is consistent with the I/O thread, the trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.

In addition, there is a worker thread in master, like other MySQL connections, slave opening a connection in master will also cause master to start a thread. The replication process has a very important limitation-replication is serialized on slave, meaning that parallel update operations on Master cannot operate concurrently on slave.

MARIADB Master-slave replication

Master:sql-master 192.168.200.101/24

Slave:sql-slave 192.168.200.102/24,192.168.200.103/24

1) Install the MariaDB software package separately on the primary and from the MariaDB server .

Yum install-y mariadb mariadb-devel mariadb-server

Systemctl Restart MARIADB
Systemctl Enable MARIADB
Cp/usr/share/mysql/my-medium.cnf/etc/my.cnf

2) set a password for the root account

Mysql_secure_installation//The command performs the following settings:

A) Set the password for root user y

b) Delete Anonymous account y

c) Remove root user telnet to Y

d) Delete test library and access to test library Y

e) Refresh the authorization form to make the modification effective Y

3 ) Modify Master , from mariadb on the MARIADB server configuration file .

Vim/etc/my.cnf

Master MariaDB Server :

Server-id=1 #数据库ID号, expressed as master at 1;

Log-bin=mysql-bin #启用二进制日志;

Binlog_format=mixed #混合模式

Two units from MariaDB Server :

server-id=2 #数据库ID号, ID number can not be duplicated;

Log-bin=mysql-bin #启用二进制日志;

Binlog_format=mixed #混合模式

Relay-log=relay-bin #启用二进制日志;

Log_slave_updates=1 #将复制事件写进自己的二进制日志;

Read_only=on #防止改变数据

4 ) On the primary, from the MARIADB server, respectively Restart MariaDB Service .

Systemctl Restart MARIADB

5 ) mainly MariaDB The server creates an account and grants replication permissions (for security reasons, permissions minimized) .

Useradd-s/sbin/nologin Rep

Mysql-u root-p

Mysql>grant replication Client,replication Slave on * * to "rep" @ "192.168.200.%" identified by "123456";

Mysql>flush privileges;

Mysql>show Master status; #记下log-file and Position values

+------------------+----------+--------------+------------------+

| File | Position | binlog_do_db | binlog_ignore_db |

+------------------+----------+--------------+------------------+

|      mysql-bin.000001 |              492 |                  | |

+------------------+----------+--------------+------------------+

Mysql>quit

#不要再操作主服务器MariaDB to prevent changes in the state value of the primary server.

6 ) if Primary MARIADB Server used when the database already exists data ( Optional Steps ).

A) lock the database as read-only on the primary server, preventing data writes from causing data changes.

Mysql-u root-p

Mysql>flush tables with read lock;

Mysql> Show master status;

b) Export the database on the primary server.

Mysqldump-u root-p Database name > file name (including path)

c) Use the SCP command on the primary server to transfer the exported database files to the slave server

SCP file name (including path) [email protected] From server IP address: file name (including path)

D) Go to the slave server and import the data (before you create an empty database, preferably with the same name)

Mysqldump-u root-p Empty database name < file name (including path)

e) Enter the main database to view the status, note log-file and position, this is the data offset, will be used later.

Mysql-u root-p

Mysql>flush privileges;

Mysql>show Master status; #记下log-file and Position values

Mysql>quit

f) Unlocking the database on the primary server

Mysql-u root-p

Mysql>unlock tables;

7) Build from MariaDB Server and Primary MariaDB connection to the server , perform the following actions on both servers from the MARIADB server.

Mysql-u root-p

mysql> Change Master to master_host= "192.168.200.101",

#主MariaDB的IP

-Master_user= "Rep", #用于复制的用户

-master_password= "123456", #复制用户的密码

master_port=3306, #连接使用的端口

-master_log_file= "mysql-bin.000001", #起点日志文件, same as above Log-file

master_log_pos=492, #起点位置, same as above position

master_connect_retry=10, #连接重试间隔

master_heartbeat_period=2;

8) Both start the slave process from the MARIADB server .

Mysql>start slave;

9) Validation Results , Check the status from server .

Mysql>show slave status\g;

Slave_io_running and slave_sql_running are both yes, stating that the critical process has started and that the master-slave replication connection is successful.

• in the main MariaDB the server writes data to view the MariaDB whether the server can replicate successfully .

> CREATE database test;

> Use test;

> CREATE table test2 (name varchar (4), age int (2));

> INSERT into Test2 (name,age) VALUES ("Chen", "39");

>insert into Test2 (name,age) VALUES ("Wang", "25");

>select * from Test2;

+------+------+

| name | Age |

+------+------+

|   Chen | 39 |

|   Wang | 25 |

+------+------+

MySQL Master-slave replication