Mysql master-slave synchronization configuration (ultra-detailed)

0. First, prepare two machines A and B and install the same version of mysql on them (preferably 5.0 or above)
A is the master database, and B is the slave database.
1. mysql synchronization server-id format description
 
A B C
A: 1: Indicates domain name Configuration
2: indicates the SDNS Server
3: indicates the detection center Server
4: indicates the Collection Center Server
5: indicates the detection operation center Server
6: indicates the detection node Server
Average
If the function module ID is small, it is the master server.
If the function module ID is large, the slave server
B: Generally, it is a group in the same LAN.
C: The server IDs in the same functional module group are in the same group.
Example: 3 03 01
 
 
2. Master-slave synchronization configuration (master)
1) modify my. cnf
 
In the [mysqld] field of my. cnf, add
Vi/etc/my. cnf
Servers-id = 10101
Log-bin = mysql-bin # This must be set. Otherwise, an error will be reported from the database if no logs exist.
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 master server for the mysql slave server
Grant replication slave on *. * TO 'repl' @ '2. 2.2.% 'identified by 'testpass ';
 
4) Create a database
> Create database gslb;
> Create database dns;
5) display the log location
> Show master status;
+ ------------------ + ----------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ----------- + -------------- + ------------------ +
| Mysql-bin.000003 | 107 |
+ ------------------ + ----------- + -------------- + ------------------ +
 
3. Master-slave synchronization configuration (slave)
1) modify my. cnf
 
In the [mysqld] field of my. cnf, add
Vi/etc/my. cnf
Servers-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-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) The log location is displayed. If two Yes values are displayed, 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 master data
Mysql-e "create database gslb"
Mysql gslb <gslb. SQL
Mysql-e "create database dns"
Mysql dns <dns. SQL
This article is from the "one party" blog