Simple MySQL master-slave replication Configuration tutorial

Benefits of master-slave replication

It ensures real-time asynchronous data backup and the primary server is secure and reliable without being influenced by external data.

Master-slave replication principle

Distributing data to multiple systems is achieved by copying data from one of Mysql's master hosts to another (slave) host and executing it again;

One server acts as the master server during the replication process, and one or more other servers act as the slave server. The master server writes updates to binary log files and maintains an index of the files to track log loops.

These logs can record updates sent to the slave server. When an slaves server is connected to the master server, it notifies the master server of the last successful update location read from the server in the log. The server load balancer receives any updates that have occurred since then, blocks them, and waits for the master server to notify you of new updates.

MySQL master-slave replication configuration

1. The master and slave servers perform the following operations respectively:
① Consistent version
② Initialize the table and start mysql in the background
③ 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 // [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 'q123456 ';

# Generally, the root account is not used. "%" indicates that all clients may be connected. If the account and password are correct, the specific client IP address can be used here, such as 192.168.145.226, to enhance security.
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.145.222 ', master_user = 'TB', master_password = 'q123456 ',
Master_log_file = 'MySQL-bin., 100', master_log_pos = 000004; // do not disconnect. "308" has no single quotation marks.
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: myrync // authorize account name, avoid using root
Master_Port: 3306 // database port, which is unavailable in some versions
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 // # location where binary logs are synchronously read, greater than or equal to> = 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 // 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.

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 in the slave server Mysql:

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; // You can view the data added to the master server.
+ ------ +
| Id | name |
+ ------ +
| 1 | bobu |
+ ------ +
1 row in set (0.00 sec)

10. Additional: Monitoring

Write a shell script and use nagios to monitor two "yes" of slave. If only one or zero "yes" is found, it indicates that the master and slave nodes are faulty. Send a text message to alert you.