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

This article mainly explains "the building process of MYSQL master replication". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "the building process of MYSQL master replication".

I. description of the environment:

IP

Hostnam

Database name

Connect the user

Password

192.168.47.179

Mysql01

Retail

Server01

Server01

192.168.47.178

Mysql02

Retail

Server02

Server02

Second, construction steps

2.1 create a connected user for the data

A connection user server01 is created on Mysql01 and can only be connected through 192.168.47.178

Script: mysql > GRANT REPLICATION SLAVE ON retail.* TO 'server01'@'192.168.47.178' IDENTIFIED BY 'server01'

A connection user server02 is created on Mysql02 and can only be connected through 192.168.47.149

Script: mysql > GRANT REPLICATION SLAVE ON retail.* TO 'server02'@'192.168.47.179' IDENTIFIED BY 'server01'

2.2 modify the parameter file of mysql

Modify the parameter file of Mysql01. The default is / etc/my.cnf in the main configuration file of MySQL. Modify / add the following

Server-id = 10

Log-bin = mysql-bin

Replicate-do-db = retail

Auto-increment-increment = 2

Auto-increment-offset = 1

After modification, restart msyql takes effect: service mysqld restart

Modify the parameter file of Mysql02. The default is / etc/my.cnf in the main configuration file of MySQL. Modify / add the following

Server-id = 20

Log-bin = mysql-bin

Replicate-do-db = retail

Auto-increment-increment = 2

Auto-increment-offset = 2

Parameter description:

A. When synchronizing the data of the server-id database, it is used to identify the server from which the statement was originally written, which needs to be filled in in the construction of master or slave.

B, auto-increment-increment: in database applications, we often use a unique number to identify the record. It can be generated automatically in MySQL through the AUTO_INCREMENT property of the data column. To avoid duplicate codes generated by the two primary databases, you need to set this value. In this case, two servers are used, so the auto-increment-increment value is set to

2.3 copy the database from one of the servers to another server

Because the environment is newly built, both environments are the same, and data initialization is needed.

If necessary, you can use the following steps to synchronize with Mysql01 as the source database, as follows:

Lock the database and view the status

Mysql > FLUSH TABLES WITH READ LOCK

Query OK, 0 rows affected (0.00 sec)

Mysql > SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000008 | 107 |

+-+

1 row in set (0.00 sec)

Shows that the source database is in the 107th position of binlog 8

Backup database

[root@mysql01] # mysqldump-- user=root-p retail > / tmp/retail.sql

Enter password:

Unlock the database

Mysql > UNLOCK TABLES

Create a retail database on mysql02 and import it

Mysql > create database retail

Mysql-uroot-p retail

SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| | mysql-bin.000009 | 107 |

+-+

1 row in set (0.00 sec)

Show that the backup file is located at location 107 of binlog 9.

2.4 make a communication connection with the master

A, confirm the user and binlog on mysql01

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.47.178',MASTER_USER='server02',MASTER_PASSWORD='server02', MASTER_LOG_FILE='mysql-bin.000008',MASTER_LOG_POS=107

B. Confirm the user and binlog on mysql02

Mysql > CHANGE MASTER TO MASTER_HOST='192.168.47.179',MASTER_USER='server01',MASTER_PASSWORD='server01', MASTER_LOG_FILE='mysql-bin.000009',MASTER_LOG_POS=107

2.5 start the service and run on mysql01 and mysql02: start slave

2.6 check status

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.47.149

Master_User: server2

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000008

Read_Master_Log_Pos: 107

Relay_Log_File: mysql02-relay-bin.000015

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000008

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB: retail

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 411

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Slave_IO_Running: Yes

Slave_SQL_Running: Yes indicates that the master configuration is complete

III. Testing

Create a table on top of mysql02

Check it on mysql01

It has been verified that the master synchronization has been successfully built.

At this point, I believe you have a deeper understanding of "the building process of MYSQL master replication". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.