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

This article mainly shows you "how to build master-slave replication in Mysql5.7", the content is easy to understand, clear, hope to help you solve doubts, the following let the editor lead you to study and learn "how to build master-slave replication in Mysql5.7" this article.

I. Overview

Master-slave replication can achieve database backup and read-write separation:

In order to avoid the unavailability of services and ensure the security and reliability of data, we need to deploy at least two or more servers to store database data, that is, we need to replicate data and deploy it on multiple different servers. Even if one server fails, other servers can continue to provide services.

MySQL provides master-slave replication to improve service availability and data security and reliability.

Master-slave replication means that the server is divided into master server and slave server, master server is responsible for reading and writing, slave server is only responsible for reading, master-slave replication is also called master/slave,master is master, slave is slave, but it is not mandatory, that is to say, slave can also be written, master can also read, but generally we do not do so.

Second, master-slave replication architecture

One master and multiple slaves architecture:

Multi-master and multi-slave architecture:

Principle of master-slave replication:

When the data on the master master server changes, the change is written to the binary event log file

The salve slave server will detect the binary log on the master master server within a certain time interval to detect whether it has changed. If it detects that the binary event log of the master master server has changed, then start an master binary event log requested by the master O Thread.

At the same time, the master master server launches a dump Thread for each Imax O Thread to send binary event logs to it.

Slave saves the binary event log received from the server to its own local relay log file.

The salve slave server will start SQL Thread to read the binary log from the relay log and replay it locally to keep its data consistent with the master server.

Finally, O Thread and SQL Thread will go to sleep, waiting to be awakened next time.

Third, one master and multiple slaves to build

Build the environment:

1. Linux version CentOS release 6.9 (Final)

2. Download address of mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

1. Decompress mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

# / decompress the tar xzvf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz# renamed file mv mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz mysql under usr/local

two。 Create a multi-instance data directory

Cd / usr/local/mysqlmkdir datacd data# Master mkdir 330 slave mkdir 3307

3. Database initialization

# initialize-insecure under the bin directory of # mysql installation means that the random password of the root user in the MySQL database is not generated, that is, the root password is empty. # initialize 3306./mysqld-- initialize-insecure-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data/3306-- user=mysql# initialize 3307./mysqld-- initialize-insecure-- basedir=/usr/local/mysql-- datadir=/usr/local/mysql/data/3307-- user=mysql

If there is an error execution

Yum install libaio-devel.x86_64

Yum-y install numactl

4. Create a configuration file my.cnf for each database

Note:

1. It is recommended to create a file under linux to prevent windows from being inconsistent with linux encoding.

two。 Configure the port number to be modified under different instances.

3. Put the modified my.cnf into the 3306 and 3307 folders respectively

[client] port = 3306socket = / usr/local/mysql/data/3306/mysql.sockdefault-character-set= UTF8 [mysqld] port = 3306socket = / usr/local/mysql/data/3306/mysql.sockdatadir = / usr/local/mysql/data/3306log-error = / usr/local/mysql/data/3306/error.logpid-file = / usr/local/mysql/data/3306/mysql.pidcharacter-set-server=utf8lower_case_table_names=1autocommit = 1log-bin=mysql-binserver-id=3306

5. Multi-instance startup

Change to the / usr/local/mysql-5.7.24/bin directory, use the msyqld_safe command to specify the configuration file and start the MySQL service:

# where-- defaults-file is the specified configuration file, & match indicates background startup. / mysqld_safe-- defaults-file=/usr/local/mysql/data/3306/my.cnf &. / mysqld_safe-- defaults-file=/usr/local/mysql/data/3307/my.cnf &

6. Database initialization configuration

Configure in each instance, such as 3306:

# client connection. / mysql-uroot-p-P3306-h227.0.0.modifying the Mysql password alter user 'root'@'localhost' identified by' root';# authorizes remote access (so that remote clients can access it) grant all privileges on *. * to root@'%' identified by 'root';# refresh configuration flush privileges

Client connection test

7. Database unique id configuration

1. Make sure that each instance is started correctly and then configure the master and slave.

two。 Close the instance and add the following configuration to the my.cnf file of each instance

# / usr/local/mysql/bin shuts down the instance. / mysqladmin-uroot-p-P3307-h227.0.0.1 shutdown./mysqladmin-uroot-p-P3306-h227.0.0.1 the newly added configuration log-bin=mysql-bin # indicates enabling binary log server-id=3307 # indicates the server number, which should be unique and consistent with the port

Start each instance after joining

. / mysqld_safe-defaults-file=/usr/local/mysql/data/3306/my.cnf &. / mysqld_safe-defaults-file=/usr/local/mysql/data/3307/my.cnf &

7. Host Settin

1. Create an account to replicate data on the primary server and authorize

# execute under / usr/local/mysql/bin directory. / mysql-uroot-p-P3306-h227.0.0.1 grant replication slave on *. * to 'copy'@'%' identified by' root'

two。 View primary server status

# default initial value of mysql master server: # File:mysql-bin.000001# Position:154show master status

3. If the primary service state is not the initial state, the state needs to be reset

Reset master

7. Slave settin

1. Need to log in to the slave client of 3306 | 3307 | 3308

# execute multiple slaves'|'in / usr/local/mysql/bin directory. / mysql-uroot-p-P3308 | 3309 | 3310-h227.0.0.1

two。 View slave status

# initial status: Empty set show slave status

3. If it's not the initial state, reset it.

Stop slave; # stops replication, which is equivalent to terminating reset slave from IO and SQL threads on the server

4. Set up slave settings host configuration

Change master to master_host=' host ip',master_user='copy',master_port= host port, master_password=' connection host password', master_log_file='mysql-bin.000001',master_log_pos=154

5. Execute the start copy command

Start slave

6. View slave status

Show slave status\ G

7. Test master-slave replication

Create tables in the master database, and synchronization from the slave database is considered a success!

If you are writing from the slave library, the slave server will no longer synchronize the master database data. Execute this command in the slave library to solve the problem.

Stop slave;set global sql_slave_skip_counter = 1 world start slave;show slave status\ G

Execute this command if the master-slave copy speed is slow

Slave-parallel-type=LOGICAL_CLOCK slave-parallel-workers=16 master_info_repository=TABLE relay_log_info_repository=TABLE relay_log_recovery=ON above is all the contents of the article "how to build Master-Slave replication in Mysql5.7". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more 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.