How to build mysql master-slave server in linux system?
Achieve the goal
Now we need to set up two MySQL servers (one master and one slave), one as the master server and one as the slave server, the master server for write operations and the slave server for read operations.
Work flow
Primary server:
Open binary log
Configure a unique server-id
Get the master binary log file name and location
Create a user account for slave and master communication.
From the server:
Configure a unique server-id
Read the master binary log using the user account assigned by master
Enable the slave service.
Preparatory work
The master-slave database version is best the same.
Data consistency in master-slave database
Main database: 192.168.244.201: 3306
From database: 192.168.244.202: 3306
Start configuration
Configure the Master master server
Find the configuration file my.cnf of the main database (my.ini in Windows), and mine is in / etc/my.cnf
Insert the following two lines in the [mysqld] section:
[mysqld] log-bin=mysql-bin # enable binary log server-id=201 # set server-id, unique value, identify the host
Restart the mysql service
Systemctl restart mysqld
Create an account / password for master-slave synchronization
Enter MySQL: mysql-u root-p enter the password.
The user I created is called "master_root" and the password is "MySql@6688".
[note: due to changing the password to support simple password 123456, there are some problems for the new users here, such as saying that the password does not comply with the policy. Later, I simply reinstalled mysql and used the default complex password, so there are not so many problems. Just get used to it. ]
Below I use%, did not write a specific ip, you can decide.
# create a user (IP is the IP that can access the master, write'% 'for any IP) mysql > CREATE USER' master_root'@'192.168.244.202' IDENTIFIED BY 'MySql@6688';# assign permissions (IP is the IP that can access the master, write'% 'for any IP) mysql > GRANT REPLICATION SLAVE ON *. * TO' master_root'@'192.168.244.202'; # Refresh permission mysql > flush privileges
Check the master status and record the binary file name (mysql-bin.000001) and location (154). It should be used in the back with the slave library.
Show master status
Configure the Slave master server
Modify the my.cnf file
Vim / etc/ my.cnf [mysqld] server-id=202 # sets server-id, a unique value that uniquely identifies the slave library
Restart the mysql service
Systemctl restart mysqld
Log in to mysql and execute synchronous sql statements (master server name, user name, 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 the slave synchronization process
Mysql > start slave
View slave status
Show slave status\ G
Be careful not to follow the semicolon; otherwise the error on the last line is as follows:
ERROR: No query specified
When both Slave_IO_Running and Slave_SQL_Running are YES, the master-slave synchronization setting is successful.
Then you can do some verification, such as inserting a piece of data into a table in the test database of the master master database, checking whether there is any new data in the same data table in the test library of slave to verify whether the master-slave replication function is effective, and you can also turn off slave (mysql > stop slave). Then modify master to see if slave is modified accordingly (after stopping slave, master changes will not be synchronized to slave), and you can complete the verification of master-slave replication function.
Other related parameters:
When binary log is enabled in master, the operations of all tables in all libraries are recorded by default. You can specify that only the specified database or even the specified table operations can be recorded through configuration. You can add and modify the following options in "mysqld" of mysql configuration file:
Which databases are not synchronized
Binlog-ignore-db = mysqlbinlog-ignore-db = testbinlog-ignore-db = information_schema
Only which databases are synchronized, other than that, others are not synchronized
Binlog-do-db = game
For example, when you look at the master status before, you can see that only the test library is recorded, while the manual and mysql libraries are ignored.
These are the details of building a mysql master-slave server under linux. Please pay more attention to other related articles!
