Master-slave replication for MySQL/MariaDB Databases

MySQL master-slave replication means that the slave server obtains binary log files from the master server, and then re-executes these logs on the slave server to maintain the slave server and the master server.

MySQL master-slave replication means that the slave server obtains binary log files from the master server, and then re-executes these logs on the slave server to maintain the slave server and the master server.

MySQL Database Replication Overview

MySQL master-slave replication means that the slave server obtains binary log files from the master server and re-executes these logs on the slave server to keep the slave server synchronized with the master server. However, due to asynchronous replication, the slave server lags behind the master server to a certain extent. The data just written to the master server may be queried by the service on the slave server.

MySQL replication principle:

(1) Create an I/O thread from the server to connect to the primary database and request binary log files from the primary database.

(2) Start Binlog Dump on the master database and send the binary log file to the I/O thread, after the I/O thread obtains data, it writes the data in the relay log of the slave database ).

(3) The SQL thread reads and executes the relay log.

There are three ways to record binary logs on the master server:

Statement: a statement-level binlog that records SQL statements used to modify data.

Row: records data changes row by row based on binlogs.

Mixed: The method automatically selected by the MySQL database to record binlog.

The statement format is only an SQL statement that records operations. The row format records changes in each row of data to the binlog, which is more detailed than the statement to ensure consistency between the master and slave databases. However, the binary log files in the row format are very large, which may affect disk I/O and bandwidth during transmission. Therefore, you need to set them according to the actual situation.

In a very busy database, there are often multiple threads in the master database to execute write operations concurrently, and these events are recorded in binary logs must be linear. There is only one SQL thread in the slave database to execute write operations. In the long run, the gap between the slave database and the master database will grow bigger and bigger. In this case, multiple SQL threads can be started from the slave database to execute write operations. Each thread is responsible for executing all related transactions of a database in the master database. In addition, after a transaction is committed on the master database, the related records are not synchronized to the disk immediately, but are recorded in the buffer and synchronized at intervals. This may also cause inconsistency of the master. You can set the parameter sync_binlog = 1 on the master server. Once the transaction is committed, the binary log file is synchronized from the memory buffer to the disk.

Database Replication Construction Process

One-master-multiple-slave architecture

Lab environment: master server (192.168.1.106), slave server (192.168.1.127)

1) First, ensure the time synchronization of each server

2) install MariaDB database, here is the use of mariadb-10, binary installation method, GTID mechanism in the mariadb-10 has become standard, no need to manually start.

[Root @ www ansible] # ansible mysql-m shell-a 'tar xf/root/mariadb-10.0.10-linux-x86_64.tar.gz-C/usr/local /'

[Root @ www ansible] # ansible mysql-m shell-a 'groupadd-g 300-r mysql'

[Root @ www ansible] # ansible mysql-m shell-a 'useradd-u 300-g mysql-r mysql'

[Root @ www ansible] # ansible mysql-m shell-a 'ln-sv/usr/local/mariadb-10.0.10-linux-x86_64 // usr/local/mysql'

[Root @ www ansible] # ansible mysql-m shell-a 'chown-R root. mysql/usr/local/mysql /*'

[Root @ www ansible] # ansible mysql-m shell-a 'chown-R mysql. mysql/data'

[Root @ www ansible] # ansible mysql-m shell-a 'mkdir/etc/mysql'

[Root @ www ansible] # ansible mysql-m shell-a 'cp/usr/local/mysql/support-files/my-large.cnf/etc/mysql/my. cnf'

[Root @ www ansible] # ansible mysql-m shell-a 'cp/usr/local/mysql/support-files/mysql. server/etc/rc. d/init. d/mysqld'

[Root @ www ansible] # ansible mysql-m shell-a 'chkconfig -- add mysqld'

Perform initialization in the/usr/local/mysql directory on each node:

./Scripts/mysql_install_db -- user = mysql -- datadir =/MySQL_DATA/data/

3) modify the configuration file

On the master server:

/Etc/mysql/my. cnf:

Datadir =/data/mydata # data storage directory

Binlog_format = mixed # record binary log format

Sync_binlog = 1 # when a transaction is committed, the binary log is synchronized to the disk.

Autocommit = off # disable automatic submission

Server-id = 1 # server id

Innodb_support_xa = 1 # support distributed transactions

Slave Server:

/Etc/mysql/my. cnf:

Server-id = 2

Datadir =/data/mydata

Relay-log =/data/relaylog/relay-log # relay log storage directory

Read_only = 1 # The slave server is read-only (ensure data consistency)

Slave_parallel_threads = 3 # Number of SQL threads started

Sync_master_info = 1 # synchronize the master.info file from the server as needed

Sync_relay_log = 1 # synchronize relay logs as needed

Sync_relay_log_info = 1 # synchronize the relay.info file as needed

4) start the service

[Root @ www ~] # Ansible mysql-m shell-a 'service mysqld start'

5) log on to mariadb on the master server and create an authorized account.

MariaDB [(none)]> grant replication slave, replication client on *. * to repuser @ '192. 192.% 'identified by 'repuser ';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

6) log on to the database from the server and connect to the master using an account with the copy permission.

First, check the location of the binary log of the master server (show master status;). If the server has been running for a period of time, you can back up the master server (back up to a certain location) first ), restore from the server, and then synchronize from this location.

MariaDB [(none)]> change master to master_host = '2017. 168.1.106 ', master_user = 'Referer', master_password = 'Referer', master_log_file = 'master-bin.000007', master_log_pos = 343;

Start the I/O thread and SQL thread from the server

MariaDB [(none)]> start slave;

Or

MariaDB [(none)]> start slave IO_THREAD

MariaDB [(none)]> start slave SQL _THREAD

View the running status on the server: