Mariadb master-slave Replication

1. perform the following operations on the master and slave servers: 1.1, version 1.2, initialization table, mysql1.3 on the background, root Password modification, and master server master: # vietcmy. cnf [mysqld] log-binmysql-bin [required] Enable binary log server-id222 [required] unique server ID, default is 1, generally take IP

1. The master and slave servers perform the following operations: 1.1, version 1.2, initialization table, mysql 1.3, root Password modification, and master server master: # vi/etc/my. cnf [mysqld] log-bin = mysql-bin // [required] Enable binary log server-id = 222 // [required] unique server ID. The default value is 1. Generally, IP address is used.

1. The master and slave servers perform the following operations respectively:

1.1 consistent version
1.2 initialize the table and start mysql in the background
1.3 modify the root password

2. Modify the master server:

# Vi/etc/my. cnf
[Mysqld]
Log-bin = mysql-bin // [required] Enable binary log
Server-id = 222 // [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is used.

3. Modify the slave server slave:

# Vi/etc/my. cnf
[Mysqld]
Log-bin = mysql-bin // [not required] Enable binary log
Server-id = 226 // [required] unique server ID. The default value is 1. Generally, the last segment of the IP address is used.

4. Restart the mysql of the two servers.

/Etc/init. d/mysql restart

5. Create an account on the master server and authorize slave:

#/Usr/local/mysql/bin/mysql-uroot-pmttang
Mysql> GRANT REPLICATION SLAVE ON.To 'mysync' @ '%' identified by '000000'; // generally, the root account is not used. "%" indicates that all clients may be connected. If the account and password are correct, the Client IP address can be used here, for example, 192.168.145.226, to enhance security.

Flush privileges

6. log on to mysql on the master server and query the master status.

Mysql> show master status;
+ ------ + ---- + ----- + ------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------ + ---- + ----- + ------ +
| Mysql-bin.000004 | 308 |
+ ------ + ---- + ----- + ------ +
1 row in set (0.00 sec)
Note: Do not operate MYSQL on the master server after this step is completed to prevent the status of the master server from changing.

7. Configure Slave server Slave:

Mysql> change master to master_host = '192. 168.2.6 ', master_user = 'mysync', master_password = '000000', master_log_file = 'mysql-bin.000001', master_log_pos = 192;
// Do not disconnect. There are no single quotation marks before and after 467 digits.

Mysql> start slave; // start the slave Server replication function

8. Check the slave server copy function status:

Mysql> show slave status \ G

*****************1. row*****************

Slave_IO_State: Waiting for master to send event Master_Host: 192.168.2.222 // master server address Master_User: mysync // authorize account name, avoid using root Master_Port: 3306 // database port whenever possible, some versions do not have this line Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 600 // # the location where the binary log is synchronously read, greater than or equal to Exec_Master_Log_Pos Relay_Log_File: ddte-relay-bin.000003: yes // This status must be YES Slave_ SQL _Running: Yes // This status must be YES ......

Note: The Slave_IO and Slave_ SQL processes must run properly, that is, the YES state. Otherwise, all processes are in the wrong state (for example, any of them is in the wrong state ).

The master and slave servers are configured.

Disable Firewall
Systemctl start firewalld. service # start firewall
Systemctl stop firewalld. service # stop firewall
Systemctl disable firewalld. service # disable firewall startup

9. Master/Slave server test:

The master server Mysql creates a database and inserts a data entry into the table in this database:

Mysql> create database hi_db;
Query OK, 1 row affected (0.00 sec)

Mysql> use hi_db;
Database changed

Mysql> create table hi_tb (id int (3), name char (10 ));
Query OK, 0 rows affected (0.00 sec)

Mysql> insert into hi_tb values (001, 'bobu ');
Query OK, 1 row affected (0.00 sec)

Mysql> show databases;
+ ------- +
| Database |
+ ------- +
| Information_schema |
| Hi_db |
| Mysql |
| Test |
+ ------- +
4 rows in set (0.00 sec)

Query from the Mysql server:

Mysql> show databases;

+ ------- +
| Database |
+ ------- +
| Information_schema |
| Hi_db | // I'm here. You can see it.
| Mysql |
| Test |

+ ------- +
4 rows in set (0.00 sec)

Mysql> use hi_db
Database changed
Mysql> select * from hi_tb; // view the new data on the master server
+ -- +
| Id | name |
+ -- +
| 1 | bobu |
+ -- +
1 row in set (0.00 sec)

10. Complete:

Write a shell script and use nagios to monitor two slave yes (Slave_IO and Slave_ SQL processes). If only one or zero yes is found, the master and slave nodes are faulty. send a text message to alert you.