How to configure master-slave synchronization in mysql
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.
