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.
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.