Configuration notes for master-slave replication between mysql instances

MySQL-master1: 192.168.72.128
 
MySQL-master2: 192.168.72.129
 
OS Version: CentOS 5.4
MySQL version: 5.5.9 (the master and slave servers with master-slave replication have the same mysql version or the master version is later than the slave version)
 
1. MySQL master-master Configuration
 
1. Modify the MySQL configuration file
 
If you want to enable binlog for both MySQL, enable the method: In the MySQL-master1 configuration file/etc/my. cnf
 
Add to [MySQLd] Section
 
User = mysql
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1
 
In the MySQL-master2 profile/etc/my. cnf
 
Add to [MySQLd] Section
 
User = mysql
Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 2
 
Save the two configuration files and restart the mysql server.
 
The server_id value must be a positive integer between 2 and 232-1. The ID value uniquely identifies the master and slave servers in the replication cluster, so they must be different.
 
Binlog-do-db = database is the database to record logs;
 
Binlog-do-db = test and replicate-do-db = test
 
For example
 
Binlog-do-db = test1
 
Replicate-do-db = test1
 
Binlog-do-db = test2
 
Replicate-do-db = test2
 
Binlog-ignore-db is the database name that does not record logs. Multiple databases are separated by commas;
 
In the mysql configuration file my. cnf, log-slave-updates indicates that if one MASTER fails, the other MASTER will take over immediately.
 
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1 indicates frequent server refresh logs. This ensures that the log is Refresh to the other if one fails. This ensures data synchronization.
 
Auto_increment_offset = 1
Auto_increment_increment = 2
In this case, the auto_increment field of a produces the following values: 1, 3, 5, 7 ,... And so on.
 
Auto_increment_offset = 2
Auto_increment_increment = 2
In this way, the value produced by the auto_increment field of B is: 2, 4, 6, 8 ,... An even number of IDs.
 
Your auto_increment field will never be repeated between different servers, so there is no problem with the Master-Master structure. Of course, you can also use 3, 4, or N servers. You only need to ensure auto_increment_increment = N and then set auto_increment_offset to the appropriate initial value, our MySQL can have dozens of Master servers at the same time, without repeated self-growth IDs.
 
2. Set 192.168.72.128 to the master server of 192.168.72.129.
 
Create an authorized user on 192.168.72.128
 
MySQL> grant replication slave on *. * to 'replicase' @ '%' identified by 'replicase ';
Query OK, 0 rows affected (0.00 sec)
 
MySQL> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
MySQL> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| MySQL-bin.000003 | 374 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
------------------------------------------------------------------------------
In 192.168.72.129, set 192.168.72.128 as the primary server.
 
MySQL> change master to master_host = '192. 168.72.128 ', master_user = 'replicase', master_password = 'replicase', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.05 sec)
 
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
 
MySQL> show slave status/G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.72.128
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000003
Read_Master_Log_Pos: 374
Relay_Log_File: MySQL-master2-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: MySQL-bin.000003
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
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: 374
Relay_Log_Space: 235
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
1 row in set (0.00 sec)
 
The red part indicates the file postion value in the result of the show master status Command on the host 192.168.72.128.
 
 
 
3. Set 192.168.72.129 to the master server of 192.168.72.128.
 
Create an authorized user on 192.168.72.129
 
MySQL> grant replication slave on *. * to 'replicase' @ '%' identified by 'replicase ';
Query OK, 0 rows affected (0.00 sec)
 
MySQL> flush privileges;
Query OK, 0 rows affected (0.00 sec)
 
MySQL> show master status;
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| MySQL-bin.000003 | 374 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)
 
----------------------------------------------------------------------------------
In 192.168.72.128, set 192.168.72.129 as the primary server.
 
MySQL> change master to master_host = '192. 168.72.129 ', master_user = 'replicase', master_password = 'replicase', master_log_file = 'mysql-bin.000003', master_log_pos = 192;
Query OK, 0 rows affected (0.05 sec)
 
MySQL> start slave;
Query OK, 0 rows affected (0.00 sec)
 
MySQL> show slave status/G
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.72.129
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MySQL-bin.000003
Read_Master_Log_Pos: 374
Relay_Log_File: MySQL-master2-relay-bin.000002
Relay_Log_Pos: 235
Relay_Master_Log_File: MySQL-bin.000003
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB:
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: 374
Relay_Log_Space: 235
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
1 row in set (0.00 sec)
 
If the primary database has data
 
The database locks the table and prevents data from being written. Mysql> flush tables with read lock;
 
Run mysqldump.
 
Back up the database.
 
Run
 
Shell> mysqldump-uroot-p123456 -- master-data -- opt test1> backup-file. SQL
 
View the status of the primary database mysql> show master status;
 
Record the values of the File and Position items
 
Note: because the master server is not locked, the position value of the master server binary log recorded here may be greater than the value when mysqldump is performed, which will cause the slave server to lose updates during this period. If the master server does not update the new table during this period, the loss will not be affected. Otherwise, the slave server will fail to copy the thread, and the master server must be locked during mysqldump.
 
-----------------------------------------------
 
Add the option from the my. cnf file on the server
Skip-slave-start
Skip-slave-start indicates that the synchronization thread is not started when the slave server is started. This means that the synchronization thread is started manually after the slave server is started, run "start slave" at the mysql> prompt.
 
After saving my. cnf
 
Run
 
Shell> mysqladmin-uroot-p123456 create test1
 
Shell> mysql-uroot-p123456 test1 <backup-file. SQL
 
Start the slave server thread
 
Mysql> start slave;
 
Remove the master database from locking mysql> unlock tables;
 
4. Other commands
 
1. view the replication progress
Run on the master database
Mysql> show processlist \ G;
 
2. commands on the master server:
Show processlist;
Show master status
Show slave hosts
Show {master | binary} logs
Show binlog events
Purge {master | binary} logs to 'Log _ name'
Purge {master | binary} logs before 'date'
Reset master (earlier version of flush master)
Set SQL _log_bin = {0 | 1}
 
3. commands on the slave server:
Slave start
Slave stop
Slave stop IO_THREAD // This thread writes logs of the master segment to the local
Slave start IO_THREAD
Slave stop SQL _THREAD // This thread applies logs written locally to the database
Slave start SQL _THREAD
Reset slave
Set global SQL _slave_skip_counter
Load data from master
Show slave status (SUPER, replication client)
Change master to MASTER_HOST =, MASTER_PORT =, MASTER_USER =, MASTER_PASSWORD = // dynamically CHANGE master information
 
Purge master [before 'date'] deletes logs that have been synchronized from the master.
 
========================================================== ======================================
Skip-slave-start
Server-id = 1
 
Log-bin = C:/Program Files/MySQL Server 5.0/00/repbinlog
Log-error = C:/Program Files/MySQL Server 5.0/rep. err
Relay-log = C:/Program Files/MySQL Server 5.0/rep/beltal_relay_log
Sync_binlog = 1
Log-slave-updates
Innodb_flush_log_at_trx_commit = 1
Binlog-do-db = sync
 
# Slave
Master-host = 192.168.1.144
Master-user = replicate
Master-password = replicate
Master-port = 3306
Master-connect-retry = 60
Replicate-do-db = sync
Report-host = 192.168.1.80


From the blog of roockee