Home Database Mysql Tutorial Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication

Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication

Dec 25, 2017 am 11:21 AM
copy build

This article mainly introduces the detailed tutorial of MySQL5.7.18 master-slave replication setup (one master and one slave). Friends who need it can refer to it. I hope it can help everyone. MySQL uses 3 threads to perform the replication function (one of which is on the master server and the other two on the slave server. Let’s introduce the content of this article in detail.

1. Replication Principle

The master writes updates to binary log files and maintains an index of the files to keep track of the log rotation. These logs record updates sent to the slave. When a slave connects to the master, it notifies the master that the slave is in the log. The location of the last successful update is read. The slave server receives any updates that have occurred since then, and then blocks and waits for the master server to notify of new updates.

MySQL uses 3 threads to perform the replication function (where 1 on the master server and the other two on the slave server. When START SLAVE is issued, the slave server creates an I/O thread to connect to the master server and let it send the statements recorded in its binary log. One thread sends the content in the binary log to the slave server.

This thread reads the content sent by the main server Binlog Dump thread and transfers the data to the Binlog Dump thread on the master server. Copy to the local file in the data directory of the slave server, that is, the relay log. The third thread is the SQL thread, which is created by the slave server to read the relay log and execute the updates contained in the log

. 2. Server preparation

Operating system version: Red Hat Enterprise Linux Server release 6.7 (Santiago)

Master (master) ip: 172.16.115.245 Host name: mysql2 server_id:245

Slave (slave) ip: 172.16.115.247 Host name: mysql3 server_id:247

MySQL5.7.18 is installed on both the master and slave servers

3. Master-slave replication implementation details

1. Set a connection account for the server on the master server and grant REPLICATION SLAVE permission

1

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'repl@20170509';

Copy after login

2. Modify the master configuration file my.cnf

1

2

server-id = 245

log_bin = /data/mysqllog/3306/bin_log/binlog

Copy after login

These two values ​​must be set. After setting up, restart MySQL

3. Back up the complete data of the master

1

mysqldump -uroot -p'密码' --master-data=2 --single-transaction -R --triggers -A > /backup/all.sql

Copy after login

Description:

--master-data=2 means recording the master at the backup time. Binlog location and Position
--single-transaction means getting a consistent snapshot
-R means backing up stored procedures and functions
--triggres means backup triggers
-A means backup All libraries

4. Check the binlog name and location when backing up the main library

1

2

3

4

5

6

7

SHOW MASTER STATUS;

mysql> SHOW MASTER STATUS;

+---------------+----------+--------------+------------------+-------------------+

| File     | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+---------------+----------+--------------+------------------+-------------------+

| binlog.000004 | 79394496 |       |         |          |

+---------------+----------+--------------+------------------+-------------------+

Copy after login

Or go to the database file just backed up: vi all.sql

5. Modify the slave library configuration file my.cnf

1

2

3

4

5

6

7

server-id = 247 (唯一,不能与主库一样,一般设为服务器IP后3位)

log_bin = /data/mysql/logdir/3306/bin_log/binlog

innodb_file_per_table = ON

skip_name_resolve = ON

relay_log = /data/mysql/logdir/3306/relay_log/relay.log

binlog-format = row

log-slave-updates = true

Copy after login

read_only=ON (read-only mode)

After setting, restart MySQL.

6. Restore the master backup on the slave server

1

mysql -u root -p'密码' < all.sql

Copy after login

7. Stop the slave library, configure the master-slave parameters, and open the slave library.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

mysql> stop slave; #暂停从库

mysql>CHANGE MASTER TO MASTER_HOST='172.16.115.245',MASTER_USER='repl', MASTER_PASSWORD='repl@20170509',MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=154;

mysql> start slave; #启动复制

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 172.16.115.245

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: binlog.000004

Read_Master_Log_Pos: 104634190

Relay_Log_File: relay.000003

Relay_Log_Pos: 104632819

Relay_Master_Log_File: binlog.000004

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

Relay_Log_Space: 104634713

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

Master_UUID: 4f545573-3170-11e7-b903-000c29462d8c

Master_Info_File: /data/mysql/datadir/3306/data/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:

Copy after login

8. View master and slave related processes

master Binlog Dump thread:

1

2

3

4

5

6

7

8

9

10

mysql> SHOW PROCESSLIST \G

*************************** 1. row ***************************

Id: 13

User: repl

Host: 172.16.115.247:44602

db: NULL

Command: Binlog Dump

Time: 76514

State: Master has sent all binlog to slave; waiting for more updates

Info: NULL

Copy after login

slave IO/SQL thread:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

mysql> SHOW PROCESSLIST \G

*************************** 1. row ***************************

Id: 10

User: system user

Host: 

db: NULL

Command: Connect

Time: 81148

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 12

User: system user

Host: 

db: NULL

Command: Connect

Time: 5

State: Reading event from the relay log

Info: NULL

Copy after login

9. At this point, The master-slave configuration has been completed. You can create databases, tables and other operations on the master server to see if the slave database is synchronized!

Related recommendations:

Mysql master-slave replication setup_MySQL

How to implement the master-slave replication process in MySQL Detailed example explanation (picture)

Analysis of the principle and configuration of MySQL master-slave replication

The above is the detailed content of Detailed explanation on setting up a master-slave instance for MySQL5.7.18 master-slave replication. For more information, please follow other related articles on the PHP Chinese website!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

Hot Topics

Java Tutorial
1653
14
PHP Tutorial
1251
29
C# Tutorial
1224
24
How to copy lyrics from QQ Music How to copy lyrics How to copy lyrics from QQ Music How to copy lyrics Mar 12, 2024 pm 08:22 PM

We users should be able to understand the diversity of some functions when using this platform. We know that the lyrics of some songs are very well written. Sometimes we even listen to it several times and feel that the meaning is very profound. So if we want to understand the meaning of it, we want to copy it directly and use it as copywriting. However, if we want to use it, we still need to You just need to learn how to copy lyrics. I believe that everyone is familiar with these operations, but it is indeed a bit difficult to operate on a mobile phone. So in order to give you a better understanding, today the editor is here to help you. A good explanation of some of the above operating experiences. If you also like it, come and take a look with the editor. Don’t miss it.​

What is the shortcut key for copying? What is the shortcut key for copying? Mar 10, 2023 pm 02:00 PM

The shortcut key for copying is "Ctrl+c", and the corresponding paste key is "Ctrl+v"; on the computer, use the mouse to drag and select text, hold down Ctrl, and then click the C key to complete the copy; A shortcut key refers to completing an operation through certain specific keys, key sequences, or key combinations.

How to quickly build a statistical chart system under the Vue framework How to quickly build a statistical chart system under the Vue framework Aug 21, 2023 pm 05:48 PM

How to quickly build a statistical chart system under the Vue framework. In modern web applications, statistical charts are an essential component. As a popular front-end framework, Vue.js provides many convenient tools and components that can help us quickly build a statistical chart system. This article will introduce how to use the Vue framework and some plug-ins to build a simple statistical chart system. First, we need to prepare a Vue.js development environment, including installing Vue scaffolding and some related plug-ins. Execute the following command in the command line

PS copy layer shortcut key PS copy layer shortcut key Feb 23, 2024 pm 02:34 PM

In the PS copy layer shortcut keys, we can know that if you want to copy a layer when using PS, you can use the shortcut key [Ctrl+J] for quick copying. This introduction to the shortcut keys for copying layers can tell you the specific operation method. The following is the detailed content, so take a look. PS copy layer shortcut key answer: [Ctrl+J] Specific method: 1. Open the image in PS and select the layer that needs to be copied. 2. Press [Ctrl+J] on the keyboard at the same time to complete the copy of the layer. Other copying methods: 1. After opening the image, press and hold the layer and move the [New Layer] icon downwards. 2. After moving to the icon, let go. 3. The layer copy is completed.

Can buildings be built in the wild in Mistlock Kingdom? Can buildings be built in the wild in Mistlock Kingdom? Mar 07, 2024 pm 08:28 PM

Players can collect different materials to build buildings when playing in the Mistlock Kingdom. Many players want to know whether to build buildings in the wild. Buildings cannot be built in the wild in the Mistlock Kingdom. They must be within the scope of the altar. . Can buildings be built in the wild in Mistlock Kingdom? Answer: No. 1. Buildings cannot be built in the wild areas of the Mist Lock Kingdom. 2. The building must be built within the scope of the altar. 3. Players can place the Spirit Fire Altar by themselves, but once they leave the range, they will not be able to construct buildings. 4. We can also directly dig a hole in the mountain as our home, so we don’t need to consume building materials. 5. There is a comfort mechanism in the buildings built by players themselves, that is to say, the better the interior, the higher the comfort. 6. High comfort will bring attribute bonuses to players, such as

How to copy and move drag-and-drop elements in Vue? How to copy and move drag-and-drop elements in Vue? Jun 25, 2023 am 08:35 AM

Vue is a popular JavaScript framework that provides a convenient drag-and-drop function, allowing us to easily copy and move elements. Next, let's take a look at how to copy and move drag-and-drop elements in Vue. 1. Basic implementation of drag-and-drop elements To copy and move drag-and-drop elements in Vue, you first need to implement the basic drag-and-drop function of the element. The specific implementation method is as follows: Add the elements that need to be dragged in the template: &lt;divclass="drag-elem"

How to back up CMS DreamWeaver database files? How to back up CMS DreamWeaver database files? Mar 13, 2024 pm 06:09 PM

How to back up CMS DreamWeaver database files? In the process of using CMS to build a website, it is very important to ensure the security of database files to prevent data loss or damage. Backing up database files is an essential operation. The following will introduce how to back up CMS DreamWeaver database files and attach specific code examples. 1. Use phpMyAdmin for backup. phpMyAdmin is a commonly used database management tool through which you can easily back up the database. The following is using phpMyAdm

What should I add to the copy shortcut key ctrl? What should I add to the copy shortcut key ctrl? Mar 15, 2024 am 09:57 AM

On Windows, the shortcut key for copying is Ctrl C; on Apple, the shortcut key for copying is Command C; on Linux, the shortcut key for copying is Ctrl Shift C. Knowing these shortcut keys can improve the user's work efficiency and facilitate text or file copy operations.

See all articles