MySQL Master-slave configuration

Master-Slave Replication configuration

The steps are as follows:

Primary server: From the server IP address, respectively

[Python]View Plain copy

1. 192.168.145.222,192.168. 145.226

1. Modify Master server Master:

[Python]View Plain copy

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

2. Modify the slave from the server:

[Python]View Plain copy

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

3. Restart MySQL for two servers

[Python]View Plain copy

1. Systemctl Restart MARIADB

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

[Python]View Plain copy

1. Mysql
2. Mysql>grant REPLICATION SLAVE On * * to ' mysync ' @'% ' identified by ' q123456 ';//normally without root account, "%" Indicates that all clients may connect, as long as the account, the password is correct, here can be replaced by specific client IP, such as 192.168.   145.226, enhance security.

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

[Python]View Plain copy

2. Mysql>show Master status;
4. +------------------+----------+--------------+------------------+
6. | File | Position | binlog_do_db | binlog_ignore_db |
8. +------------------+----------+--------------+------------------+
10. | Mysql-bin.      000004 |              308 |                  |  |
12. +------------------+----------+--------------+------------------+
14. 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

6. Configure the slave from the server:

Note that mysql-bin.000004 and 308 are file and position in the fifth step

[Python]View Plain copy

1. 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;//Note mysql-bin. 000004 and 308 are the fifth steps in the file and
2. Mysql>start slave; To start the Copy from Server feature

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

[Python]View Plain copy

2. Mysql> Show Slave Status\g
4. 1. Row ***************************
8. Slave_io_state:waiting for Master to send event
10. Master_host: 192.168. 2.222//Primary server address
12. Master_user:mysync//Authorization account name, try to avoid using root
14. Master_port: 3306//Database port, partial version not on this line
16. Connect_retry:
18. Master_log_file:mysql-bin. 000004
20. Read_master_log_pos: + //#同步读取二进制日志的位置, greater than or equal to Exec_master_log_pos
22. Relay_log_file:ddte-relay-bin. 000003
24. Relay_log_pos: 251
26. Relay_master_log_file:mysql-bin. 000004
28. Slave_io_running:yes//This status must be Yes
30. Slave_sql_running:yes//This status must be Yes
32. ......

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.

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:

[Python]View Plain copy

2. mysql> CREATE DATABASE hi_db;
4. Query OK, 1 row affected (0.00 sec)
8. mysql> use hi_db;
10. Database changed
14. Mysql> CREATE TABLE HI_TB (id int (3), name char (10));
16. Query OK, 0 rows affected (0.00 sec)
20. mysql> INSERT into HI_TB values (001,' Bobu ');
22. Query OK, 1 row affected (0.00 sec)
26. mysql> show databases;
28. +--------------------+
30. | Database |
32. +--------------------+
34. | Information_schema |
36. | hi_db |
38. | MySQL |
40. | Test |
42. +--------------------+
44. 4 rows in Set (0.00 sec)
48. MySQL Query from server:
52. mysql> show databases;
56. +--------------------+
58. | Database |
60. +--------------------+
62. | Information_schema |
64. |       hi_db | I ' M here, you see?
66. | MySQL |
68. | Test |
72. +--------------------+
74. 4 rows in Set (0.00 sec)
78. Mysql> Use hi_db
80. Database changed
82. Mysql> SELECT * from HI_TB; View new specific data on the primary server
84. +------+------+
86. | ID | name |
88. +------+------+
90. |  1 | bobu |
92. +------+------+
94. 1 row in Set (0.00 sec)
    

MySQL Master-slave configuration