Shulou(Shulou.com)06/01 Report--

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!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.