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

This article mainly introduces the Linux system Mysql master and slave how to achieve handouts, hoping to supplement and update some knowledge, if you have other questions to understand, you can continue to follow my updated article in the industry information.

The first thing to do here is to create a database that you want to synchronize for testing

First, create a backup account on master. The account created here is repl%, which means that repl users with any address can log in to the master main library.

GRANT REPLICATION SLAVE ON *. * TO 'repl' @'% 'IDENTIFIED BY' Abcd@1234'

GRANT ALL PRIVILEGES ON *. * TO 'repl'@'%' IDENTIFIED BY' Abcd@1234' WITH GRANT OPTION

Connect the master library from the slave machine command mysql-h IP address-uroot-p; test the slave library to connect to the master library, if unable to connect, carry on the authorization solution

(here, when I use the first account authorization, I cannot synchronize the data after the configuration is completed, because there are not enough permissions, so here I directly use the second authorization command.)

Modify my.cnf configuration file

Vi / etc/my.cnf

Add the following parameters:

Server-id=1

Log-bin=/var/lib/mysql/mysql-bin

Binlog_do_db= test # Master-Slave synchronization Database name

Character-set-server=utf8 # is only available in version 5.5 or above

After saving, restart the database service mysqld restart

Check whether server-id is in effect

Show variables like 'server_id'

If server_id' does not exist for execution, create SET GLOBAL server_id=1

View host status show master status

It is used when recording the configuration of File and Position from the slave library.

Slave library configuration

Here, it is also necessary to create a database, which must be consistent with the libraries on master (ps, test cases only. In order to ensure the integrity of the data, the formal environment needs to restore the library to be synchronized from the master database backup to the slave database).

Modify my.cnf configuration file

Vi / etc/my.cnf

Add the following parameters:

Server-id = 2

Log-bin=/var/lib/mysql/mysql-bin

Replicate-do-db = test # master-slave synchronization data name

Character-set-server=utf8 # is only available in version 5.5 or above

After saving, restart the database service mysqld restart

Check whether server-id is in effect

Show variables like 'server_id'

Stop the slave service

Stop slave

Set the account number and password of the master database to log in when connecting to the slave library, and then start slave (ps: it's best to re-check the master status in case the information changes show master status;)

CHANGE MASTER TO MASTER_HOST = '192.168.1.185', MASTER_USER =' repl'

, MASTER_PASSWORD = 'Abcd@1234'

, MASTER_LOG_FILE = 'mysql-bin.000001'

, MASTER_LOG_POS = 437

Turn on slave

Start slave

Show slave status

It is normal to see if these two items are YES,yes.

Slave_IO_Running: Yes or Connecting to master

Slave_SQL_Running: Yes

Normal means that the configuration is successful, and then the test will be fine.

Show processlist can view slave thread status on master

Read the above about the Linux system Mysql master and slave how to achieve the handout, I hope it can bring some help to everyone in practical application. Due to the limited space in this article, it is inevitable that there will be deficiencies and need to be supplemented. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.