Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Time:2022-5-9

Configuring master-slave server based on docker to realize MySQL master-slave replication

edition

mysql - latest(8.0)

thank

herethankStation BWhere’s brother ShangfeiCrazy God says JavaUploaded on this IPVideo resources

The master-slave copy reports an error 2061: authentication plugin ‘caching_ sha2_ Password ‘reported error: authentication requires secure connection

(1 message) docker builds MySQL 8 master-slave replication (the most detailed version of the whole network)_ Bright blog – CSDN blog

Shardingshpere Database Middleware – column – kuangstudy

(1 message) error 1776 (HY000): parameters master_ LOG_ FILE, MASTER_ LOG_ POS, RELAY_ LOG_ FILE and RELAY_ LOG_ POS can_ Xiaowei’s blog – CSDN blog

Messages (global) @ @1 GTID_ Mode = off problem_ Wangxuelei036 blog – CSDN blog

Wait.

And thank youStation BCSDNdeveloppaperAnd so on!

MySQL – common configuration

Install MySQL
docker pull mysql:latest

Select the MySQL version you need to install. The latest version is selected here8.0, this version contains a connected pit, which I will talk about later!

If you don’t useDockerInstead of installing MySQL directly under Linux, IRecommended tutorial(02~03)

The blog isKaungStudyDocuments provided by Feige.

Is MySQL installed successfully?
View successfully installed images
docker images
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

View docker installed image

existencemysqlThe red box indicates that the installation is successful!

MySQL startup
docker run -p 3306:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD=12345678 -d mysql:latest

Run: start

-P 3306:3306: specify mapping port, – P external port: internal port (inside the container)

Name MySQL master: alias the container and give it a name to call later (my main MySQL is MySQL)

-e MYSQL_ ROOT_ Password = 12345678: set the password of root user

-D: background operation

MySQL: latest: MySQL: tag. Tag is the version number of MSYQL you selected

Check whether the MySQL container starts successfully?

After MySQL is started successfully, the ID of the currently running container will be printed out

Or you can check the container status yourself!

docker ps
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

View the list of running containers

This indicates that the startup is successful.

MySQL configuration master

Enter the MySQL container first. My main MySQL here ismymysql, the command is as follows:

docker exec -it mymysql /bin/bash
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Enter container

You can see the next line[email protected]It’s different, and this ID is the container ID of the container you’re currently entering,

Check the internal configuration file of the container. I have put my The screenshot of CNF is as follows:

Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

my. CNF path

/etc/mysql/my. CNF (this works!)

OK, I think I’m talking too much nonsense. Let’s speed up the progress now!

After we get the path, we enter firstmy.cnf,

Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

VIM enters my conf

Well, there is no VIM inside the container. You can install it yourself.

apt-get update

apt-get install vim

I didn’t install it like this. I used it directlydocker cp

Use docker CP to configure my cnf
  1. Put my CNF copy from container to host
Docker CP container ID (or container name): / etc / MySQL / my CNF / docker / MySQL / Master (or slavel1) / my cnf

Place the container ID or the container named XXX in / etc / MySQL / my CNF is copied to / docker / MySQL / Master / my CNF file

  1. Modify my CNF (add corresponding master-slave configuration)

    The main nodes are as follows:

[mysqld]
##Be unique in the same LAN
server-id=100  
##Turn on the binary log function, and you can take it at will (key)
log-bin=master-bin
Binlog format = row // there are three types of log formats: row, statement and mixed
Binlog do DB = database name // the name of the synchronized database. If it is not configured, it means that all databases are synchronized

The slave nodes are as follows:

[mysqld]
#Set up server_ ID, pay attention to be unique
server-id=201  
#Enable the binary log function for use when slave is the master of other slave
log-bin=mysql-slave-bin   
# relay_ Log configure relay log
relay_log=mysql-relay-bin  
#Set to read-only. If this item is not set, it means that the slave is readable and writable
read_only=1
  1. The modified my CNF copy back to container
Docker CP / docker / MySQL / Master (or slavel1) / my CNF container ID (or container name): / etc / MySQL / my cnf

It is the reverse of the instruction path in step 1.

  1. Restart service
Docker restart container name / container ID

Note that the server ID of each master-slave node must be unique!!!

Master configuration

After the joint configuration, we first enter the master Mysql to view the information required by the corresponding configuration slave node.

Enter MySQL
#Enter the inside of the container
Docker exec - it container ID / container name / bin / Bash
#Enter MySQL
MySQL - uroot - P password
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Enter master MySQL

Login successful

View the binlog file name and location of the master service (executed by the master node)
show master status;
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Binlog file name and location of master service

OK, then configure the slave node.

Slave configuration

The initial steps are the same as those of the master. They are configured together, and the only difference is modificationmy.cnfDifferent, please pay attention!

Enter MySQL
#Enter the inside of the container
Docker exec - it container ID / container name / bin / Bash
#Enter MySQL
MySQL - uroot - P password
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Enter slave1 MySQL

)

Login successful

Execute change master to
change master to master_user='root',master_password='mysql8.0',master_host='192.168.168.101',master_port=3306,get_master_public_key=1, master_log_file='master-bin.000004', master_log_pos=1097, master_connect_retry=30;

master_ Host: the address of the master library
master_ Port: the port number of the master, which refers to the port number of the container
master_ User: the user used for data synchronization
master_ Password: the password of the user used for synchronization
master_ log_ File: specifies which log file slave starts copying data from, that is, the value of the file field mentioned above
master_ log_ Pos: which position to start reading, that is, the value of the position field mentioned above
master_ connect_ Retry: the time interval between retries if the connection fails. The unit is seconds. The default is 60 seconds_ master_ public_ Key = 1: solveAuthentication plugin ‘caching_sha2_password’ reported error:Authentication require secure connectionPassword plug-in problem caused by – msyql8 0.

Note: the text in the current field refers to the anchor point-View the binlog file name and location of the master service (executed by the master node)The master node information queried there.

start slave
start slave;
Check whether the configuration is successful?
show slave status \G

\G: indicates line feed.

Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

Query slave section status

Two yes’s make it!

What are the problems when configuring slave nodes?

FeigeI’ve already cited a large part of the examples here. I won’t talk about them one by one. Place the portal:https://www.kuangstudy.com/zl/sharding#1369645557613608962

Let me talk about the problems in my host configuration?

  1. Authentication plugin ‘caching_sha2_password’ reported error:Authentication require secure connectionPassword plug-in problem caused by – msyql8 0.

From configuration anchor-Execute change master toThe configuration to be solved has been added in the. See get_ master_ public_ Key = 1, fromBlogThe solution (in the comment section).

  1. No query specified

    show slave status\G;

    Multiple semicolons (;).

  1. The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

    Well, I’m because my slave node is connected to the master node, and the IP address is configured incorrectly.

  2. Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS can

    Due to the previous master_ auto_ Position = 1, now change back to 0 and use the POS method change again.

    #Stop slave
    stop slave;
    #Clear configuration
    reset slave;
    change master to master_auto_position=0;
    change master to master_user='root',master_password='mysql8.0',master_host='192.168.168.101',master_port=3306,get_master_public_key=1, master_log_file='master-bin.000004', master_log_pos=1097, master_connect_retry=30;
    #Start slave
    start slave;

Using root causes the slave MySQL server to be able to manipulate data (crud)?

(2 messages) can I write MySQL from the database_ MySQL master-slave replication. The slave database is set to read-only and cannot be written_ L Neo blog – CSDN blog

I suggest assigning an ordinary user account to the slave database and only assigning corresponding permissions!

#For example, create an account whose name is' username 'and password is' password' and any IP address can be connected 
create user 'username'@'%' identified by 'password';
#Assign permissions. For example, I only give him select permission
grant select on *.* to 'username'@'%';
#Refresh required
flush privileges;
#View effect
show grants for 'username'@'%';
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

View permissions

Then you can test it yourself. If the account is created successfully first?

#Query user table information under MySQL database
select host,user from mysql.user;
Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

View user table information

After the permission assignment is successful, I can use it herenavicatLog in to the ‘username’ account and (modify, add, delete) the data. The following error message appears!

Configure master-slave server based on docker to realize MySQL master-slave replication (8.0)

No permission prompt

Recommended Today

(function test collection) common test points of pop-up window

Type of pop-up windowPop up window operation setPop up loading and displayPop up elementsProperties of pop-up windowPop up compatibilityRisk of pop-upreference prefaceMobile app pop-up window is a popular form of content display at present. According to the nature of the content, it can be divided into news, notification, advertising, marketing, etc. the display form is […]