Halfway through the journey to Java, I heard that the program supports reading and writing separation feels very high-end, but I haven't been in touch with it.
If you come into contact by chance, you must remember it.
Today, let’s talk about the master-slave synchronization of the database. There are two benefits:
- It can be used for separation of reading and writing. For example, if the write operation is written to the master database, if the read is read from the slave database
- It is just to backup the database to prevent the hard disk from crashing completely
Master-slave database operation preparation:
Both computers have installed mysql version 5.5 and above. It is best for both computers to install the same version of the database and can be used normally separately.
Remember the IP addresses of the two computers:
Take my home computer as an example: Master database: 192.168.0.102 From database: 192.168.0.104
【Operation under the main database】
Step 1:
Find the file in the main database mysql installation directory and open it. Add the main server configuration under mysqld
server-id=1 #Server idlog-bin=C:\Program Files\MySQL\MySQL Server 5.5\mysql-bin #Binary file storage pathbinlog-do-db=test1 #Database to be synchronized
After saving and restarting the mysql service, you can see the file and the mysql-bin.000001 file under C:\Program Files\MySQL\MySQL Server 5.5
Enter username and password: mysql -u username -p password ( mysql -uroot -proot )
Step 2,
Set permissions for the slave server to be connected: grant replication slave, reload, super on *.* to[email protected] identified by 'root';
ps: Add permissions to host 192.168.0.104, username: slave, password: root; (just enter once)
Step 3,
Enter the command show master status; # Find the values of File and Position and record them;
【Operate from the database】
Step 4:
Find the file under mysql and add the server-side configuration under its [mysqld]:
server-id=2 #Server id, cannot be consistent with the main serverreplicate-do-db=test1 #Database to be synchronized
Save and restart mysql service
Step 5:
Test from the server: mysql -u slave -p root -h 192.168.0.102 to check whether the main database can be connected successfully.
Then enter exi to exit the mysql command line or close the cmd window to open a new cmd
Step 6:
Modify the parameters of the connection to the main database: mysql>change master to master_host='192.168.0.104',master_user='slave',master_password='root',master_log_file='mysql-bin.000001',master_log_pos=107; (Please replace mysql-bin.000001 and 107 entered here with the values queried in step 3 otherwise it will not be synchronized.）
ps: An error may be reported, which roughly means that the slave thread is running and cannot be set. In this case, execute mysql>stop slave; stop the slave thread and then set the connection parameters;
Step 7:
After setting, execute mysql> start slave; start slave thread; execute mysql> show slave status\G (no semicolon), check
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
These are detailed steps. If it is not successful, please contact me to help you solve it. Follow this step. As long as the two computers are connected to each other, there should be no problem.
However, it is important to note:
If the slave database is modified, the main database will not be modified synchronously. This should be done with master-master synchronization. The method is similar.
Therefore, insert and delete update should be operations on the master database, while the select operation operates on the slave table
How to achieve reading and writing separation in specific programs, I will present it in the following form in a specific demo
The above is all the content of this article. I hope that the content of this article will help you study or work. I also hope to support me more!