background

I’m learning to use it recently Spring Aop To achieve the database read-write separation function .
Before writing code , The first thing is to deploy mysql Environment , Because to achieve read-write separation , So at least two need to be deployed mysql example , A master from , And the master and slave instances can be synchronized automatically , Because my native memory is not high , So I’m going to windows Go straight to build mysql The master-slave instance of ( Don’t want to open a virtual machine ), But there are some problems in the process , Although it was finally solved , But it took a lot of time . To avoid wasting time on the same things later , At the same time, it is convenient for readers to copy the same scene , So write down this blog to record the process of setting up the environment .

Environmental statement

Native address ：127.0.0.1(localhost)

mysql edition ：mysql-5.7.28-winx64

Main library service name ：master, port 3307

From library service name ：slave, port 3308

Install and configure the main library master

download

First, download mysql, Direct to Official website download zip Version of the installation package , It is recommended to download a newer version , For example, the author’s version is 5.7, It’s also the advice of many gods on the Internet ,

Unzip and create my.ini file

Unzip the installation package , Name the folder master, Enter folder , Create a file called my.ini The empty text of ,

The text is as follows ：

[client]
# Port number , The default is 3306, Different in the same environment mysql Instance port number cannot be the same
port=3307
default-character-set=utf8
[mysqld]
# Main library configuration
server_id=1
log_bin=master-bin
log_bin-index=master-bin.index
# Set it to itself MYSQL Installation directory
basedir=D:/software/mysql/master
# Set to MYSQL Data directory for ,data Folder by mysql Automatic generation
datadir=D:/software/mysql/master/data
port=3307
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
# Turn on query cache
explicit_defaults_for_timestamp=true

ps： The directory address in the configuration content should be slashed , You can’t use backslashes , Otherwise, the installation service will report an error and cannot find the directory .

install master Library Services

1、cmd Administrator status function , Get into master Of bin Under the table of contents ,

2、 Execute the initialization command ：

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

If a promotion similar to the following appears, the initialization is successful , After initialization, the system will automatically generate data Folder , And generate the initial password ,

3、 Once the initialization is complete , Execute the command to install the service ：

mysqld --install master --defaults-file="D:\software\mysql\master\my.ini"

master For the name of the service ,–defaults-file by ini Path to file , appear “Service successfully installed.” That is success

notes ： If the installation fails , See if there’s no administrator running cmd.

Normally , installed master We’ll be able to start mysql 了 , But there’s a hole , It’s direct mysql I will report a mistake later , Because we have one less place , It’s in the registry master Service information .

So before you modify the registry , It is recommended not to execute the startup command first .

4、 Modify registry

Press win+R, Type… In the box regedit Open the registry , find master service , The path is a HKEY_LOCAL_MACHINE–>SYSTEM–>CurrentControlSet–>Services–>master, modify ImagePath by

D:\software\mysql\master\bin\mysqld --defaults-file=D:\software\mysql\master\my.ini master

The path corresponds to your own installation master The database folder can .

5、 Start the service

Is still in bin Directory to start the service , The order is net master start, After successful startup, the following prompt will appear ：

6、 Get into mysql

Get into mysql The order is ：mysql -u root -p , But because we changed the port number , So the order should be changed to

mysql -u root -P3307 -p, Then enter the initialization password just generated to enter mysql,

Because this initial password is too painful , So we’d better change the password , Change the statement to ：

set password=password(' New password ');

for example LZ Set the password to 123456,

This successfully sets up root The user’s password , Then we can use Navicat Wait for graphic tools to operate and connect the database ,

Install from library slave

The installation procedure of the slave library is the same as that of the main library , It’s just my.ini Modify the corresponding configuration in .

[client]
port=3308
default-character-set=utf8
[mysqld]
# Configuration from library
server_id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin
# Set it to itself MYSQL Installation directory
basedir=D:/software/mysql/slave
# Set to MYSQL Data directory for
datadir=D:/software/mysql/slave/data
port=3308
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
# Turn on query cache
explicit_defaults_for_timestamp=true

The master-slave library implements Association

Both libraries are installed , We can then manipulate the correlation between the master and slave Libraries , Realize the replication between master and slave ,

Log in to the main library first , Input show master status;

It can be found that a binary file is generated in the main library , This is a log file , It can be synchronized with the master database by associating the slave database .

Log in from library , Execute the following command ,

change master to master_host='127.0.0.1',master_port=3307,master_user='root',master_password='123456',master_log_file='master-bin.000001',master_log_pos=0;

It is not difficult to see that the above are the configuration information of the main library , Execute command after success start slave Enable master-slave replication

When it’s done , Let’s simply test , stay master Create a database in the library , The name is called. test,

then slave The same database was generated in test,

such , Master-slave replication is successful .

It’s worth noting that , Because the two libraries are only one-way related , If you want to write data from the library , The main database cannot be synchronized . So from the library can only be used to read data , And the main database can write , Can also read , Of course , Most of the time it’s used to write data , Reading data is generally obtained from the library , This can effectively reduce the pressure of the main warehouse , That is to say, separation of reading and writing .