1. Installation instructions

• Building MySQL master-slave replication in Windows is purely for the convenience of development and testing
• Delete the MySQL configuration in the environment variable, including path
• If a MySQL has been installed on the computer before installing the slave database, do not copy the previously used installation package, but decompress it again. A pit is encountered here, the servers of two servers_ The ID is always the same
• All cmd command lines should be run as administrator

2. Master database configuration

After decompression, there is no my in mysql-8.0.23-winx64 INI file, which needs to be created manually, the my INI file, where you should pay attention to the following: default_authentication_plugin=mysql_native_password

[mysqld]
#Master and slave libraries need to be inconsistent
server-id=1
log-bin=mysql-bin
#Synchronized database
binlog-do-db=ss_dict_db
#Databases that do not need to be synchronized
binlog-ignore-db=mysql
# Set 3306 port
port=3306
# Set mysql installation directory
basedir=D:\mysql\mysql-8.0.23-winx64
# Set the storage directory of mysql database data
datadir=D:\mysql\mysql-8.0.23-winx64\data
# Maximum connections allowed
max_connections=200
# Number of connection failures allowed.
max_connect_errors=10
# The character set used by the server is UTF8 by default
character-set-server=utf8
# The default storage engine that will be used when creating new tables
default-storage-engine=INNODB
# The "mysql_native_password" plug-in authentication is used by default
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# Set the default character set of mysql client
default-character-set=utf8
[client]
# Set the default port used by mysql client when connecting to the server
port=3306
default-character-set=utf8

For the main database, I have installed MySQL database in my computer before, so I don't need to do much work. It is mainly to create a slave user and give corresponding permissions. Note that MySQL 5 Version x and MySQL 8 The X version of the command is different.

CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE ON . TO 'slave'@'MySQL2' identified by '123456' ;
FLUSH PRIVILEGES;

You need to view the status of the master node: show master status

3. Configure from database

If the master database has not been installed, the installation steps are the same as those of the slave database.

From my. In the database INI file, only the following four items are different, and the rest are exactly the same as the configuration of the main database.

server-id=48
# Set 3307 port
port=3307
# Set mysql installation directory
basedir=D:\mysql\mysql-8.0.23-winx64-3307
# Set the storage directory of mysql database data
datadir=D:\mysql\mysql-8.0.23-winx64-3307\data

The cmd command line should be run as an administrator, and the mysql-8.0.23-winx64.0 should be re extracted from the database Zip package and rename it.

Since the MySQL environment variable is not configured, you should enter the corresponding folder and execute initialization naming when executing the command:

mysqld --initialize --user=mysql --console

Get the initialization password, copy it, and then execute the install operation

mysqld --install MySQL2 --defaults-file="D:\mysql\mysql-8.0.23-winx64-3307\my.ini"

After the registration operation, go to the service to check whether the MySQL 2 service already exists

If the MySQL L2 service has been started, enter the command line and log in: mysql -uroot -p. the password is the password obtained after initialization above. You can copy it and then modify the password.

Register synchronized master database information on the slave database

CHANGE MASTER TO
master_host = 'localhost',
master_user = 'slave',
master_password = '123456',
master_log_file = 'mysql-bin.000003',
master_log_pos = 156;

Stop and restart the slave. Finally, check the status and show slave status

Only Slave_IO_Running and slave_ SQL_ The configuration is successful only when both running items are Yes.

4. Verification

SS in primary database_ dict_ When inserting data into dB, it is found that the tables from the database will be synchronized.

5. Problems encountered during installation

The following questions need to be added to the configuration file: default_authentication_plugin=mysql_native_password

Also encountered server_ The ID configuration is the same. The solution is not to copy the installed mysql database from the database, but to decompress a new file.

And mysql5 X and mysql8 The X version command is different.