Shulou(Shulou.com)05/31 Report--

This article mainly introduces "the building method of mysql master and master-slave". In the daily operation, I believe that many people have doubts about the building method of mysql master and master-slave. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "mysql master and master-slave construction method". Next, please follow the editor to study!

Establish a mysql master

Server1_mysql:192.168.1.108

Server2_mysql: 192.168.1.110

Topology:

Server1_mysql-server2_mysql

1. Create users and authorize

Server1:

Mysql > GRANT REPLICATION SLAVE ON *. * TO

IDENTIFIED BY 'server2'

Mysql > flush privileges

Server2:

Mysql > GRANT REPLICATION SLAVE ON *. * TO

IDENTIFIED BY 'server1'

two。 Modify the main configuration file / etc/my.cnf of Mysql

Server1:

Log-bin=mysql-bin # must turn on binary logging

Server-id = 1 # the other id is changed to 2

Binlog-do-db=test # enter those that need to be synchronized

Binlog-ignore-db= # fill in databases that do not require synchronization

Replicate-do-db=test # enter the database that needs to be synchronized

Replicate-ignore-db=mysql

Log-slave-updates

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=1 # increase 2 at a time

Auto_increment_offset=1 # sets the offset of the automatically growing field, that is, the initial value is 2

Start the Mysql service:

# service mysqld restart

Server2:

Log-bin=mysql-bin # must turn on binary logging

Server-id = 2

Binlog-do-db=test # enter the database that needs to be synchronized

Binlog-ignore-db=mysql # fill in databases that do not require synchronization

Replicate-do-db=test # enter the database that needs to be synchronized

Replicate-ignore-db=mysql

Log-slave-updates

Slave-skip-errors=all

Sync_binlog=1

Auto_increment_increment=2 # increase 2 at a time

Auto_increment_offset=1 # sets the offset of the automatically growing field, that is, the initial value is 2

Start the Mysql service:

# service mysqld restart

Note: only server-id is different from auto-increment- offset.

The value of auto-increment-increment should be set to the total number of servers in the entire structure, and in this case two

Server, so the value is set to 2.

3. Restart both servers

# service mysqld restart

4. In order to make the two databases the same, we back up one of the databases and restore them on the other

Again, so that the two databases are the same at the beginning.

Operate on server1:

# mysqldump-- databases luowei > / tmp/luowei.sql

Operate on server2:

Create an empty database with the same name as mydb

# mysql

> CREATE DATABASE mydb

>\ Q

# scp 192.168.1.108:/tmp/mydb.sql?. /

# mysql-uroot-p mydb

CHANGE MASTER TO

> MASTER_HOST='192.168.1.110'

> MASTER_USER='server2'

> MASTER_PASSWORD='server2'

Mysql > START SLAVE

On server2:

# mysql

> CHANGE MASTER TO

> MASTER_HOST='192.168.1.108'

> MASTER_USER='server1'

> MASTER_PASSWORD='server1'

Mysql > START SLAVE

6. View and verify:

View on two database servers

Mysql > START SLAVE

Then look at the database and the table, and you will find that the content is the same, which is the schema of the entire master Mysql.

The configuration process of.

Check master-slave synchronization

[root@node1 ~] # mysql # enter the mysql command line

Mysql > show master status; # display (different hosts have different results)

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | updatelog.000028 | 313361 | db1 | mysql | |

+-+

(if you suspect that the master and slave data are not synchronized before synchronization, you can take the cold backup remote copy method above or order the line synchronization method on the slave server)

Under the MySQL command from the server:

Mysql > slave stop; # stop the slave service first

Mysql > CHANGE MASTER TO MASTER_LOG_FILE='updatelog.000028',MASTER_LOG_POS=313361

# according to the results of the show master status of the master server above, regress the binary database records of the slave server to achieve the effect of synchronization

Mysql > slave start; # start synchronization service from server

Mysql > show slave status\ G

Use show slave status\ G; take a look at the synchronization from the server

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

If it's all yes, it means it's already in sync.

It is much more convenient to test with the client of mysql than on the command line.

At this point, the study on "the building method of mysql master and master and slave" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical 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.