Build mysql master-slave server under linux

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!