Shulou(Shulou.com)05/31 Report--

This article mainly introduces the CentOs7 mysql5.7 how to achieve master-slave replication configuration, the article is very detailed, has a certain reference value, interested friends must read it!

Specific steps for MySQL5.7 master and slave configuration:

Click (here) to collapse or open

1. Two MySQL servers

Main: 192.168.253.138 Port: 1221

From: 192.168.253.139 port: 1221

two。 Modify master-slave configuration parameters

2.1 Primary Server

# # add the following content

Vi / etc/my.cnf

[mysqld]

Log-bin = mysql-bin # # binary format logs must be enabled

Server-id=138 # # server-id must be unique, usually the Mantissa of IP

2.2 from the server

# # add the following content

Vi / etc/my.cnf

[mysqld]

Log-bin = mysql-bin # # enable log in binary format, optional configuration

Server-id=139 # # server-id must be unique, usually the Mantissa of IP

3. Restart the master and slave server to make the changes take effect

Primary server (138)

[root@my01 ~] # service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

Slave server (139)

[root@my02 ~] # service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

4. Create a replication account on the primary server and authorize slave

[root@my01] # mysql-uroot-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 5

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql > grant replication slave on *. * to 'mysync'@'%' identified by' mysql123'

Query OK, 0 rows affected, 1 warning (0.02 sec)

Mysql >

5. View primary server status

# # record the values of File and Position, use them when configuring the slave server, and then do nothing on the master server to avoid the failure of slave server configuration due to the change of values

Mysql > show master status

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 1306 | |

+-+

1 row in set (0.00 sec)

6. Configure and start the slave server

[root@my02] # mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 7

Server version: 5.7.19-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.253.138', MASTER_PORT=1221, MASTER_USER='mysync', MASTER_PASSWORD='mysql123', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1306

Query OK, 0 rows affected, 2 warnings (0.01 sec)

Mysql > start slave

Query OK, 0 rows affected (0.01 sec)

7. View replication status from server

# # the most important thing is to check whether Slave_IO_Running: Yes,Slave_SQL_Running: Yes is YES. If so, the master / slave configuration is successful, otherwise it fails.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.253.138

Master_User: mysync

Master_Port: 1221

Connect_Retry: 60

Master_Log_File: mysql-bin.000001

Read_Master_Log_Pos: 1306

Relay_Log_File: my02-relay-bin.000003

Relay_Log_Pos: 320

Relay_Master_Log_File: mysql-bin.000001

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

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: 1306

Relay_Log_Space: 526

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:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 138

Master_UUID: dfb01359-857f-11e7-8ed4-000c2997411c

Master_Info_File: / data/db/mysql/1221/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp:

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

Replicate_Rewrite_DB:

Channel_Name:

Master_TLS_Version:

1 row in set (0.00 sec)

Mysql >

8. Test the master and slave situation

# # Master Server

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

+-+

4 rows in set (0.01sec)

Mysql > create database test

Query OK, 1 row affected (0.02 sec)

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > use test

Database changed

Mysql > show tables

Empty set (0.00 sec)

Mysql > create table t (id int,name varchar (10))

Query OK, 0 rows affected (0.04 sec)

Mysql > insert into t values (1, "zhang san")

Query OK, 1 row affected (0.07 sec)

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

+-+

1 row in set (0.00 sec)

Mysql > select * from

-> t

+-+ +

| | id | name |

+-+ +

| | 1 | zhang san |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into t values (2, "li si")

Query OK, 1 row affected (0.01sec)

Mysql >

# from the server

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | mysql |

| | performance_schema |

| | sys |

| | test |

+-+

5 rows in set (0.00 sec)

Mysql > use test

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with-A

Database changed

Mysql > show tables

+-+

| | Tables_in_test |

+-+

| | t |

+-+

1 row in set (0.00 sec)

Mysql > select * from t

+-+ +

| | id | name |

+-+ +

| | 1 | zhang san |

| | 2 | li si |

+-+ +

2 rows in set (0.00 sec)

Mysql >

# Congratulations on your success!

# # Note: the permissions of the master server are configured as follows:

Mysql > update user set user.Host='%' where user.User='root'

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > flush privileges

Query OK, 0 rows affected (0.01 sec)

# this configuration ensures that other servers can connect to the master server, otherwise replication from the slave server will fail!

The above is all the contents of the article "how to implement master-slave replication configuration in mysql5.7 in CentOs7". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.