A simple example of mysql master-slave replication

Perform the following steps for master-slave replication:

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 'q123456'; // generally, the root account is not required. & ldquo; % & rdquo; indicates that all clients may be connected, the password is correct and can be replaced by a specific client IP address, 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 = 'mysync', master_password = 'q123456 ',
Master_log_file = 'MySQL-bin.000004 ', master_log_pos = 308; // do not disconnect. There are no single quotation marks before and after the number 308.

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, which is unavailable in some versions
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 600 // # synchronous location for reading binary logs, 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 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.