Install mysql8 on win10 and configure master-slave replication

Recently, I am studying spring boot, and I want to organize a blog about spring boot integrating mybatis and configuring the master-slave database. However, mysql master-slave database is not configured on the computer win10 system. So it took a few days to get it right. Record it here.

The first step is to install two mysql8 on win10. I found a blog. Following the above steps, it can be configured successfully.

https://blog.csdn.net/imHanweihu/article/details/89404165

Here are a few questions to note:

1. Delete MySQL mysqld remove [service name], for example: mysqld remove mysql1

2. If the following error occurs, you can delete the data folder:

D:\developeTool\mysqlnew\mysql-8.0.17-winx64\bin>mysqld --initialize --user=mysql --console
2020-03-20T15:31:23.847864Z 0 [System] [MY-013169] [Server] D:\developeTool\mysqlnew\mysql-8.0.17-winx64\bin\mysqld.exe (mysqld 8.0.17) initializing of server in progress as process 15208
2020-03-20T15:31:23.848961Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2020-03-20T15:31:23.849652Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2020-03-20T15:31:23.849671Z 0 [ERROR] [MY-013236] [Server] The designated data directory D:\developeTool\mysqlnew\mysql-8.0.17-winx64\Data\ is unusable. You can remove all files that the server added to it.
2020-03-20T15:31:23.864040Z 0 [ERROR] [MY-010119] [Server] Aborting
2020-03-20T15:31:23.864162Z 0 [System] [MY-010910] [Server] D:\developeTool\mysqlnew\mysql-8.0.17-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.17) MySQL Community Server - GPL.

3. For the following errors: run cmd as administrator and execute sc delete mysql

The service already exists!
The current server installed: D:\developeTool\mysql\mysql-8.0.17-winx64\bin\mysqld MySQL

4. For starting mysql2, use the net start mysql2 command to start, then use mysql -u root -p to start, and enter the initial password of mysql2

Follow the door-to-door steps and the installation should be successful.

-----------------------------------------------------------------Dividing line------------------------------------------------------------------------------------------------

The next steps are to configure master-slave replication:

I use navicat configuration here.

1. Connect to the main library and press F6 to open the command line.

1. Create a user on the main database to copy. Execute the following commands in turn

create user 'repl'@'%' identified by 'repl';
grant replication slave,replication client on *.* to 'repl'@'%';
flush privileges;

2. Execute the show master status command,

  

2. Connect to the slave library and press F6 to open the command line.

1. Set the command line of the slave database listening to the master database;

change master to master_host='192.168.*.*',master_port=3306,master_user='repl',master_password='repl',master_log_file='mysql-bin.000006',master_log_pos=0

 master_host Main library ip
  master_port Main library port
  master_user New user above
  master_password Password of the new user above
  master_log_file In the main library command line file value
  master_log_pos In the main library command line position value

2. Under the best execution of the following code from the library, skip one error on the slave; (I stuck here for a long time, and finally solved the bug)

set global sql_slave_skip_counter=1

3. Start slave

start slave;

4. Verify; show slave status; two in the red box must be yes, that is, the setting is successful.

Next, you can create a table on the master. The table can be queried by executing the query under the corresponding database on the slave. This is where the master-slave replication of mysql8 is configured on win10.

Install mysql on linux and configure the master and slave. You can view another blog of mine:  https://www.cnblogs.com/pluto-charon/p/11925360.html 

Tags: MySQL Database Spring Mybatis

Posted on Sat, 21 Mar 2020 05:44:02 -0700 by elbowgrease