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

Today, I will talk to you about how to configure master-slave synchronization in mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

0. First, install the same version of mysql on two machines (preferably above 5.0) before preparing the two machines.

An is the master database and B is the slave database

1 mysql synchronization server-id format description

A B C

Avatar 1: indicates the domain name configuration

2: indicates the SDNS server

3: indicates the probe center server

4: indicates the collection center server

5: indicates the server of the probe operation center

6: indicates the probe node server

General

The small function module ID is the master server.

The big function module ID is slave server.

B: it is usually a group in the same local area network

C: the server ID in the same functional module group is the ID number of each server in the same functional module in the same group.

For example: 3 03 01

2 master-slave synchronous configuration (master)

1) modify my.cnf

Add to the [mysqld] field in my.cnf

Vi / etc/my.cnf

Server-id=10101

Log-bin=mysql-bin # this must be set, otherwise if there is no log, an error will be reported from the database

Binlog-ignore-db=mysql,test

Innodb_flush_log_at_trx_commit=1

Sync_binlog=1

Log-slave-updates

2) restart mysql

Service mysql restart

3) add an account to the slave of mysql on the master server

GRANT REPLICATION SLAVE ON *. * TO IDENTIFIED BY 'testpass'

4) create

> create database gslb

> create database dns

5) display the log location

> SHOW MASTER STATUS

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | |

+-+

| |-bin.000003 | 107 |

+-+

3 master-slave synchronization configuration (slave)

1) modify my.cnf

Add to the [mysqld] field in my.cnf

Vi / etc/my.cnf

Server-id = 20101

Replicate-do-db = dns

Replicate-do-table = gslb.zone

Replicate-do-table = gslb.zone_rr

Master-info-file = master.info

Relay-log = relay-relay-bin

Relay-log-index = relay-relay-bin.index

Relay-log-info-file=relay-relay-log.info

2) restart mysql

Service mysql restart

3) set master server information

Change master to master_host='1.1.1.1'

Master_user='repl'

Master_password='testpass'

Master_log_file='mysql-bin.000003'

Master_log_pos=107

4) start synchronization

> start slave

5) display the location of the log. If there are two Yes, the synchronization is successful.

Mysql > show slave status\ G

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 1.1.1.1

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000006

Read_Master_Log_Pos: 270500590

Relay_Log_File: relay-relay-bin.000012

Relay_Log_Pos: 1148354

Relay_Master_Log_File: mysql-bin.000006

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table: gslb.server_threshold,gslb.server,gslb.threshold,gslb.vip,gslb.status,gslb.vip_threshold

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 270500590

Relay_Log_Space: 1148510

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: 10201

1 row in set (0.00 sec)

6) Import data from master

Mysql-e "create database gslb"

Mysql gslb < gslb.sql

Mysql-e "create database dns"

Mysql dns < dns.sql

After reading the above, do you have any further understanding of how to configure master-slave synchronization in mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.