MARIADB single-Machine multi-instance master-slave replication

Preparatory work:

To deactivate the database service:

Systemctl Stop Mariadb.service

Copy the database file from the main library as the From library data:

Cp-a-r/data/mysql_data/mysql/*/data/mysql_data/mysql2/

Finish copying the boot:

Systemctl Start Mariadb.service

Step 1: Configure the CNF file for the main library and multiple instances from the library

A CNF file is shared between the main library and the library,/ETC/MY.CNF

[Mysqld]

Log-bin=mysql-bin-log

Server-id=1

Datadir=/data/mysql_data/mysql

Socket=/data/mysql_data/mysql/mysql.sock

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Pid-file=/run/mariadb/mariadb.pid

Note: In order to use transactional InnoDB for maximum persistence and consistency in replication, you should specify the innodb_flush_log_at_trx_commit=1,sync_binlog=1 option

[mysqld3307]

port=3307 (single-machine multi-instance must specify a port different from 3306)

Log-bin=mysql-bin-log

server-id=2 (different from the ID of the main library instance)

Datadir=/data/mysql_data/mysql2

Socket=/data/mysql_data/mysql2/mysql3307.sock

Pid-file=/run/mariadb/mariadb3307.pid

Step 2: Create an account with replication privileges:

MySQL gives special permissions to the replication thread, and the I/O thread running in the repository creates a

TCP/IP connection, which means that you must create a user in the main library and give it the appropriate permissions, the standby I/O thread connects to the main library with that user name and reads the binary log.

To create a user account statement:

mysql> CREATE USER [email protected];

mysql> GRANT REPLICATION slave,replication CLIENT on *. *

-To [e-mail protected]' 127.0.0.1 ' indentified by ' repl ';

We create the account in both the master and the Repository, and note that we restrict the account to the local network because it is a privileged account (although the account cannot execute select or modify data, but still can get some data from the binary log)

Step 3: Lock the host, note the location of the binary log, and back up the host database

Execute the flush TABLES with READ lock statement on the host to flush all the tables and block the write operation:

Master> FLUSH TABLES with READ LOCK;

When the read operation lock in flush TABLES with read lock is in effect, the following command is used to read the current binary log name and the value of the deviation on the host:

master> SHOW MASTER STATUS;

file:mysql-bin-log.000001

positin:1234

This command displays a list of the Binlog file names that are used by the current main library, and the amount of error position the log in the file, which is used when the settings are later set from the library. They represent the coordinates from when the library starts to process the new updates.

Note: If the previous host does not have the Bin-log feature turned on, the log file and the position value shown with the show MASTER status command are empty, if this is the case, then the name and Positon value you want to specify in the slave log should be an empty string (') and 4 .

Next, continue to execute FLUSH TABLES with READ LOCK in the Main Library client window;

Note: You can use the following command to reopen the write operation function on the host:

Master> UNLOCK TABLES;

Step 4: Initialize replication

Connection from library: Mysql-s/data/mysql_data/mysql2/mysql3307.sock

Now we are ready to initialize replication from the library instance. Execute the following command from the library instance:

slave> slave STOP;

Next, you will enter a change MASTER command:

slave> change MASTER to master_host= ' 127.0.0.1 ',

Master_user= ' Repl ',

-Master_password= ' Repl '

Master_log_file= ' mysql-bin-log.000001 ',

master_log_pos=1234;

Parameter meaning:

• Master_host: IP of the main library
• Master_user: User granted replication slave permissions in step 2
• MASTER_PASSWORD:REPL User's password
• Master_log_file:show the file name in the master status
• Position in Master_log_pos:show Master status

Finally, start copying from the library instance:

slave> start slave;

Step 5: Basic Checks

Data insertion to verify that replication is successful

You can also check the status with the following command:

mysql> SHOW SLAVE status\g;

Mysql> SHOW processlist\g;

MARIADB single-Machine multi-instance master-slave replication