Shulou(Shulou.com)06/02 Report--

This paper gives an example of how to set up mysql master-slave replication and read-write separation in CentOS server. Share with you for your reference, the details are as follows:

Advantages of mysql master-slave replication:

① if there is a problem with the master server, you can quickly switch to the service provided by the slave server to ensure high availability

② can perform query operations on the slave server to reduce the access pressure on the master server.

③ can perform backups on the slave server to avoid affecting the services of the master server during the backup

Note:

① server-id must be unique, generally using the last three digits of ip

Possible reason for Slave_IO_Running:NO of ② slave library: account does not have permission to operate

③ Can't execute the query because you have a conflicting read lock, unlock tables can be unlocked

Generally speaking, only the data that is updated infrequently or the data with low real-time requirements in ④ can be queried from the server, and the data with high real-time requirements still need to be obtained from the master database.

⑤ needs to restart mysql:service mysqld restart after modifying the configuration of the master-slave server.

Host A: 192.168.10.111

Slave B: 192.168.10.124

Please install mysql separately first. The version should be consistent. If you install it, you can skip it.

The copy code is as follows: yum install mysql mysql-server # enter y to install automatically until the installation is complete

1. Log in to host A, set up an account in the slave database on the master server, and use REPLICATION SLAVE (slave replication) to grant permissions, such as:

The copy code is as follows: mysql > GRANT REPLICATION SLAVE ON *. * TO 'backup'@'192.168.10.124' IDENTIFIED BY' 123456'

Give slave permission, if there are multiple slaves, execute multiple times.

Mysql > flush privileges

2. Open the my.cnf of host An and enter the following: (modify the configuration file my.cnf of the main database, open BINLOG, and set the value of server-id. After modification, you must restart the mysql service)

Server-id = 1 # host mark, integer log_bin = / var/log/mysql/mysql-bin.log # ensure that the file is writable, open bin-log read-only = 0 # host, read and write binlog-do-db = test # need to back up data, multiple write multiple lines binlog-ignore-db = mysql # databases that do not need backup, multiple write multiple lines

You can verify that the binary log has been started by mysql > show variables like 'log_%';.

3. Now you can stop the update operation of the master data and generate a backup of the master database. We can use mysqldump to get data everywhere to the slave database. Of course, you can also directly use the cp command to copy the data files to the slave database. Pay attention to READ LOCK the master database before exporting the data to ensure the consistency of the data.

Mysql > flush tables with read lock;Query OK, 0 rows affected (0.19 sec)

Then mysqldump exports the data:

The copy code is as follows: mysqldump-h227.0.0.1-p3306-uroot-p test > / data/backup/test.sql

4. Get the current binary log name and offset of the master server. The purpose of this operation is to recover the data from this point after starting from the slave database.

Mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | mysql-bin.000007 | 517 | test | mysql | +-+ 1 row in set (0.00 sec)

It is best to resume the write operation after the backup of the primary database is completed.

Mysql > unlock tables;Query OK, 0 rows affected (0.28 sec)

5. Copy the test.sql from the master data backup to the slave database (navicat, phpmyadmin, command line) and import it.

6. Modify the my.cnf of the slave database, add the server-id parameter, specify the user used for replication, the ip and port of the master database server, and the file and location where the replication log starts. Open the my.cnf of Slave B and enter (you must restart the mysql service after modification)

Server-id = 2 log_bin = / var/log/mysql/mysql-bin.log master-host = 192.168.10.111 master-user = backup master-pass = 123456 master-port = 3306 master-connect-retry=60 # if the slave server finds that the master server is down, the time difference between reconnecting (in seconds) replicate-do-db = test # only copy a library replicate-ignore-db=mysql # do not copy a library

7. On the slave server, start the slave process

Mysql > start slave

8. Performing show salve status verification from the server

Mysql > SHOW SLAVE STATUS\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.10.111 Master_User: root Master_Port: 3306 Connect_Retry: 5 Master_Log_File: mysql-bin.000007 Read_Master_Log_Pos: 263 Relay_Log_File: mysqld-relay-bin.000002 Relay_Log_Pos: 408 Relay_Master_Log_File: mysql-bin.000007 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: test Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate _ Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 263 Relay_Log_Space: 564 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_ CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

Prompt

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

It means that the configuration is successful.

9. Test whether the master and slave servers can synchronize

Insert, modify, delete, add fields, modify fields, add tables, you can test yourself.

[xjp@server22] $mysql-uroot-p123456 mysql > create database test; mysql > create table user (id int); mysql > insert into user values (1), (2), (3), (4), (5), (6); Query OK, 2rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql > select * from user; +-+ | id | +-+ | 1 | 2 | 2 | +-+ 2rows in set (0.00 sec) mysql > select * from user +-+ | id | +-- + | 1 | | 2 | 3 | 4 | 5 | 6 | +-- + 6 rows in set (0.00 sec) mysql > update user set id=11 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql > select * from user +-+ | id | +-+ | 11 | 2 | 3 | 4 | 5 | 6 | +-- + 6 rows in set (0.00 sec) mysql > delete from user where id=2; Query OK, 1 row affected (0.00 sec) mysql > select * from user; +-+ | id | +-+ | 11 | 3 | 4 | 5 | 5 | 6 | +-- + 5 rows in set (0.00 sec) mysql > alter table user add name varchar (50) Query OK, 5 rows affected Records: 5 Duplicates: 0 Warnings: 0 mysql > select * from user; +-- +-+ | id | name | +-- +-+ | 11 | NULL | 3 | NULL | 4 | NULL | 5 | NULL | 6 | NULL | +-+-+ 5 rows in set (0.00 sec) mysql > ALTER TABLE user MODIFY COLUMN name VARCHAR Query OK, 5 rows affected (0.01sec) Records: 5 Duplicates: 0 Warnings: 0 mysql > desc user +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | id | int (11) | NO | | NULL | | name | varchar | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > create table user2 (id int) Query OK, 0 rows affected mysql > show tables; +-+ | Tables_in_test | +-+ | test | | user | | user2 | +-+ 3 rows in set (0.00 sec) mysql >

Check whether it is synchronized from the server if the consistent statement is successful

Mysql > use test; Database changed mysql > select * from user; +-+ | id | +-+ | 1 | 2 | 3 | 4 | 5 | | 6 | +-- + 2 rows in set (0.00 sec) mysql > select * from user; +-+ | id | +-+ | 1 | 2 | 3 | 4 | 5 | 6 | +-+ 6 rows in set (0.00 sec) mysql > select * from user +-mysql > select * from user; +-- id | +-+ | 11 | | 3 | 4 | 5 | 6 | +-- + 5 rows in set (0.00 sec) mysql > select * from user +-mysql > desc user. +-+ | Field | Type | Null | Key | Default | Extra | +-+-- -+ | id | int (11) | NO | | NULL | | name | varchar | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > show tables +-+ | Tables_in_test | +-+ | test | | user | | user2 | +-+ 3 rows in set (0.00 sec) mysql > mysql binlog log view: show binlog events\ G * * 12. Row * * Log_name: mysql-bin.000007 Pos: 985 Event_type: Query Server_id: 1 End_log_pos: 1075 Info: use `test` Delete from user where id=2 * * 13. Row * * Log_name: mysql-bin.000007 Pos: 1075 Event_type: Query Server_id: 1 End_log_pos: 1175 Info: use `test` Alter table user add name varchar (50) * * 14. Row * * Log_name: mysql-bin.000007 Pos: 1175 Event_type: Query Server_id: 1 End_log_pos: 1287 Info: use `test` ALTER TABLE user MODIFY COLUMN name VARCHAR * * 15. Row * * Log_name: mysql-bin.000007 Pos: 1287 Event_type: Query Server_id: 1 End_log_pos: 1376 Info: use `test`; create table user2 (id int) 15 rows in set (0.00 sec)

It was built a long time ago, took a lot of detours, and wrote down according to the records at that time.

I hope what is described in this article will be helpful to you in setting up the CentOS server.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.