MARIADB Master-slave replication

1, the master-slave server for the following actions:

1.1. Consistent version
1.2. Initialize the table and start MySQL in the background
1.3, change the root password

2. Modify Master server Master:

#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[must] enable binary logging
server-id=222//[must be] server unique ID, default is 1, usually take IP last paragraph

3. Modify the slave from the server:

#vi/etc/my.cnf
[Mysqld]
Log-bin=mysql-bin//[not required] enable binary logging
server-id=226//[must be] server unique ID, default is 1, usually take IP last paragraph

4. Restart MySQL for two servers

/etc/init.d/mysql restart

5. Establish an account on the primary server and authorize slave:

#/usr/local/mysql/bin/mysql-uroot-pmttang
Mysql>grant REPLICATION SLAVE on . to ' mysync ' @ '% ' identified by ' 123456789 '; Generally do not use the root account, "%" means that all clients may be connected, as long as the account, the password is correct, here can be specific client IP instead, such as 192.168.145.226, enhance security.

Flush Privileges

6, log on to the master server MySQL, query the status of master

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 the master server MySQL again after performing this step to prevent the change of the primary server state value

7. Configure the slave from the server:

mysql> Change Master to master_host= ' 192.168.2.6 ', master_user= ' Mysync ', master_password= ' 123456789 ', Master_log_ File= ' mysql-bin.000001 ', master_log_pos=467;
Be careful not to disconnect, there are no single quotes around 467 digits.

Mysql>start slave; To start the Copy from Server feature

8. Check the status of the replication function from the server:

Mysql> Show Slave Status\g

***************** 1. Row *****************

          Slave_IO_State: Waiting for master to send event          Master_Host: 192.168.2.222  //主服务器地址          Master_User: mysync   //授权帐户名，尽量避免使用root          Master_Port: 3306    //数据库端口，部分版本没有此行          Connect_Retry: 60          Master_Log_File: mysql-bin.000004          Read_Master_Log_Pos: 600     //#同步读取二进制日志的位置，大于等于Exec_Master_Log_Pos          Relay_Log_File: ddte-relay-bin.000003          Relay_Log_Pos: 251          Relay_Master_Log_File: mysql-bin.000004          Slave_IO_Running: Yes    //此状态必须YES          Slave_SQL_Running: Yes     //此状态必须YES                ......

Note: The slave_io and slave_sql processes must function normally, that is, the Yes state, otherwise it is an error state (e.g., one of the No is an error).

The above operation process, the master and slave server configuration is complete.

Shutting down the firewall
Systemctl Start firewalld.service# Startup firewall
Systemctl Stop firewalld.service# stops firewall
Systemctl Disable firewalld.service# disable firewall boot boot

9, the master-slave server test:

master server MySQL, build the database, and create a table in this library to insert a piece of data:

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)

MySQL Query from server:

mysql> show databases;

+ —————— –+
| Database |
+ —————— –+
| Information_schema |
| hi_db | I ' M here, you see?
| MySQL |
| Test |

+ —————— –+
4 rows in Set (0.00 sec)

Mysql> Use hi_db
Database changed
Mysql> select * from HI_TB; View new specific data on the primary server
+--+--+
| ID | name |
+--+--+
| 1 | Bobu |
+--+--+
1 row in Set (0.00 sec)

10. Completion:

编写一shell脚本，用nagios监控slave的两个yes（Slave_IO及Slave_SQL进程），如发现只有一个或零个yes，就表明主从有问题了，发短信警报吧。

MARIADB Master-slave replication