To achieve the goal
Now you need to build two MySQL servers (one master and one slave), one as the master server and one as the slave Server, the master server performs write operations, and the slave server performs read operations.
Workflow
Main server:
Enable binary log
Configure unique server-id
Obtain the name and location of the master binary log file
Create a user account for slave and master communication.
Slave server:
Configure a unique server-id
Use the user account assigned by the master to read the master binary log
Enable the slave service.
(Free learning video tutorial recommendation:mysql video tutorial)
Preparation
Master-slave database version It is best to keep the same
The data in the master and slave databases should be consistent
Master database: 192.168.244.201: 3306
Slave database: 192.168.244.202: 3306
Start configuration
Configure Master server
Find the configuration file my.cnf of the master database (my.ini in Windows), my In /etc/my.cnf
Insert the following two lines in the [mysqld] section:
[mysqld] log-bin=mysql-bin #开启二进制日志 server-id=201 #设置server-id,唯一值，标识主机
Restart the mysql service
systemctl restart mysqld
Create for the main Enter MySQL from the synchronized account/password
: mysql -u root -p and press Enter to enter the password.
The user name I created is "master_root" and the password is "MySql@6688"
[Note: Because the password was changed to support the simple password 123456, a new one is created here. The user had some problems, saying that the password did not comply with the policy, etc. Later, I simply reinstalled mysql and used the default complex password, and there were no more problems. It's actually good to get used to it. 】
I use % below. I don’t write the specific IP. You can decide by yourself.
#创建用户（IP为可访问该master的IP，任意IP就写'%'） mysql> CREATE USER 'master_root'@'192.168.244.202' IDENTIFIED BY 'MySql@6688'; #分配权限（IP为可访问该 master的IP，任意IP就写'%'） mysql> GRANT REPLICATION SLAVE ON *.* TO 'master_root'@'192.168.244.202'; #刷新权限 mysql>flush privileges;
Check the master status and record the binary file name (mysql-bin.000001) and location (154). It will be used later when configuring the slave library.
show master status;
Configure the Slave main server
Modify the my.cnf file
vim /etc/my.cnf
[mysqld] server-id=202 #设置server-id，唯一值，唯一标识从库
Restart the mysql service
systemctl restart mysqld
Log in to mysql and execute the synchronization sql statement (master server name, user name for master and slave, password, binary file name, location)
mysql> CHANGE MASTER TO -> MASTER_HOST='192.168.244.201', -> MASTER_USER='master_root', -> MASTER_PASSWORD='MySql@6688', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154;
Start slave synchronization process
mysql>start slave;
View slave status
show slave status\G
Be careful not to follow the semicolon; otherwise the last line The error reported is as follows:
ERROR: No query specified
When both Slave_IO_Running and Slave_SQL_Running are YES, it means that the master-slave synchronization setting is successful.
Next, you can perform some verification. For example, insert a piece of data into a table in the test database of the master database, and check whether there is new data in the same data table of the slave test database to verify. To check whether the master-slave replication function is valid, you can also close the slave (mysql>stop slave;), and then modify the master to see if the slave is modified accordingly (after stopping the slave, the modifications of the master will not be synchronized to the slave), and then the master-slave operation can be completed. Copy functionality verified.
Other related parameters:
After the master turns on the binary log, it records the operations of all databases and all tables by default. You can specify to only record the specified database or even the specified table through configuration. For specific operations, you can add and modify the following options in [mysqld] of the mysql configuration file:
Which databases are not synchronized
binlog-ignore-db = mysql binlog-ignore-db = test binlog-ignore-db = information_schema
Which databases are only synchronized, except In addition, other out-of-synchronization
binlog-do-db = game
For example, when you checked the master status before, you can see that only the test library is recorded, and the manual and mysql libraries are ignored.
Recommended related articles and tutorials:mysql tutorial
The above is the detailed content of Build mysql master-slave server under linux. For more information, please follow other related articles on the PHP Chinese website!