background

Recently, I am learning to use Spring Aop to achieve the function of database read and write separation. Before writing the code, I first need to deploy the mysql environment, because to achieve read and write separation, so I need to deploy at least two mysql instances, one master and one slave, and can automatically synchronize between the master and slave instances, because my native memory is not high, So I decided to set up the master/slave instance of mysql directly on Windows (I didn’t want to open the virtual machine), but there were some problems in this process, although they were solved in the end, but also took a lot of time. In order to avoid wasting time doing the same thing in the future, and for readers to replicate the same scenario, I wrote this blog post to document the process of setting up the environment.

The environment that

Local address: 127.0.0.1(localhost)

Mysql version: mysql-5.7.28-winx64

Master library service name: master, port 3307

Service name of the slave library: slave, port 3308

Install and configure the master library

download

First of all, download mysql directly to the official website to download the zip version of the installation package, here it is recommended to download a relatively new version, for example, the author’s version is 5.7, which is also recommended by many online gods.

Unzip and create the my.ini file

Unpack the installation package, name the folder master, go to the folder and create an empty text named my.ini.

[client]
The default port number is 3306. Different mysql instances in the same environment cannot have the same port number
port=3307
default-character-set=utf8

[mysqld] 
Master library configuration
server_id=1
log_bin=master-bin
log_bin-index=master-bin.index

Set the MYSQL installation directory as your own
basedir=D:/software/mysql/master
The data folder is automatically generated by MYSQL
datadir=D:/software/mysql/master/data
port=3307
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER

Enable query caching
explicit_defaults_for_timestamp=true
Copy the code

Ps: The directory address in the configuration content must use a slash. Do not use a backslash. Otherwise, the installation service will report an error that the directory cannot be found later.

Install services for the Master library

1, run as CMD administrator, go to the master bin directory,

mysqld --initialize --user=mysql --console
Copy the code

If the promotion similar to the following occurs, the initialization is successful, and the system automatically generates the data folder and the initial password.

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

Master indicates the name of the Service. –defaults-file indicates the path of the INI file. If “Service successfully installed

Mysql > install the master server. Mysql > install the master server. Mysql > install the master server.

4. Modify the registry

Press Win +R, enter regedit in the dialog box to open the registry, locate the master service, and change ImagePath to HKEY_LOCAL_MACHINE – >SYSTEM – >CurrentControlSet – >Services – > Master

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

The path is the master database folder corresponding to their own installation.

5. Start the service

The net master start command is executed in the bin directory to start the service. After the service is successfully started, the following message is displayed:

Mysql -u root -p mysql -u root -p mysql -u root -p

Mysql -u root -p3307 -p

set password=password('New password');
Copy the code

For example, the LZ password is 123456.

Install the slave library

The installation procedure for the slave library is the same as that for the master library, just modify the corresponding configuration in my.ini.

[client]
port=3308
default-character-set=utf8

[mysqld] 
Slave library configuration
server_id=2
relay-log-index=slave-relay-bin.index
relay-log=slave-relay-bin

Set the MYSQL installation directory as your own
basedir=D:/software/mysql/slave
# set to MYSQL data directory
datadir=D:/software/mysql/slave/data
port=3308
character_set_server=utf8
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER
Enable query caching
explicit_defaults_for_timestamp=true
Copy the code

Master and slave libraries implement associations

With both libraries installed, we can operate the association between master and slave libraries to achieve replication between master and slave,

Log in to the master library and enter show master status;

Log in to the slave library and 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;
Copy the code

It is easy to see that the preceding information is the configuration information of the master database. Run the start slave command to enable the master/slave replication

It is worth noting that since the two libraries are only one-way, the master library cannot be synchronized if data is written to the slave library. Therefore, the slave library can only be used for reading data, while the master library can be used for both writing and reading. Of course, most cases are used for writing data, and reading data is usually obtained from the library. This can effectively reduce the pressure on the master library, which is often referred to as read and write separation.

If you find reading this helpful, please give me a thumbs up. This is the best affirmation for me!