Implement master-Slave synchronization between two MySQL databases

I. Overview
MySQL provides database replication (replication) functionality from the 3.23.15 release, which enables two database synchronization, master-slave, and backup-to-back modes
Second, the environment
Operating system: Linux 2.6.23.1-42.fc8 # SMP (no Xen installed)
MySQL version: 5.0.45-4.fc8
Device environment: PC (or virtual machine) two
Third, the configuration
The settings for the database replication feature are reflected in the MySQL configuration file, MySQL configuration file (typically MY.CNF): In this environment for/ETC/MY.CNF.
3.1 Setting up the environment:
Settings for IP:
A host ip:10.10.0.119#p# paging title #e#
mask:255.255.0.0
B Host ip:10.10.8.112
mask:255.255.0.0
After the IP setting is complete, you need to determine that the two host's firewall is indeed turned off. You can use the command service iptables status to view the firewall status. If the firewall status is still running. Use service iptables stop to deactivate the firewall. If you want to start the firewall off, you can use the Setup command to disable or customize it.
Finally, two hosts can ping each other to be better.
3.2 Configuring a Master (Master) b from (slave) mode
3.2.1 Configuration A is master
1, add a user to synchronize the use of the account:
GRANT FILE on *. * to ' backup ' @ ' 10.10.8.112 ' identified by ' 1234 ';
Grantreplication SLAVE on * * to ' backup ' @ ' 10.10.8.112 ' identified by ' 1234 '; #p # pagination Title #e#
Give 10.10.8.112 that is slave machine has file permissions, only give slave machine has file permission is not, but also give it replication slave permission to.
2. Add a database as a synchronization database:
Create DATABASE test;
3. Create a table structure:
CREATE TABLE mytest (username varchar), password varchar (20));
4. Modify the configuration file:
Modify the/etc/my.cnf file of a to include the following configuration in the MY.CNF configuration:
Server-id = 1 #Server标识
Log-bin
Binlog-do-db=test #指定需要日志的数据库
#p # pagination Title #e#
5. Restart the database service:
Service mysqld Restart
View Server-id:
Show variable like ' server_id ';
Instance:
Mysql> Show variables like ' server_id ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+ #p # page title #e#
1 row in Set (0.00 sec)

6, use show Master status/g command to see the log situation.
Normal is:
Mysql> Show Master status/g
1. Row ***************************
file:mysqld-bin.000002
position:198
Binlog_do_db:test,test
binlog_ignore_db:
1 row in Set (0.08 sec)
#p # pagination Header #e#3.2.2 configuration B is slave
1. Add a database as a synchronization database:
Create DATABASE test;
2. Create a table structure:
CREATE TABLE mytest (username varchar), password varchar (20));
3. Modify the configuration file:
Modify the/etc/my.cnf file of B to include the following configuration in the MY.CNF configuration:
server-id=2
master-host=10.10. 0.119
Master-user=backup #同步用户帐号
master-password=1234
#p # pagination Title #e#master-port=3306
Master-connect-retry=60 #预设重试间隔60秒
Replicate-do-db=test #告诉slave只做backup数据库的更新

5. Restart the database service:
Service mysqld Restart
View Server-id:
Show variables like ' server_id ';
Instance:
Mysql> Show variables like ' server_id ';
+---------------+-------+
#p # pagination Title #e#| variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in Set (0.00 sec)

6, use show slave status/g command to see the log situation.
Normal is:
Mysql> Show Slave status/g
1. Row ***************************
Slave_io_state:waiting for Master to send event
#p # pagination Title #e# master_host:10.10.0.119
Master_user:backup
master_port:3306
Connect_retry:60
master_log_file:mysqld-bin.000001
Read_master_log_pos:98
relay_log_file:mysqld-relay-bin.000003
relay_log_pos:236
relay_master_log_file:mysqld-bin.000001
Slave_io_running:yes
slave_sql_running:yes#p# Pagination Title #e#
Replicate_do_db:test,test
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:98
relay_log_space:236#p# Pagination Title #e#
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.01 sec) #p # page Title #e#


3.2.3 Verifying the configuration
Using INSERT, delete, update on a host for the database; Check whether the database of Host B is consistent with host a, and if it is consistent, the configuration is successful.
3.3 Dual-Machine mutual standby mode
If you join the slave setting in Host A, you can do b->a synchronization by adding master settings to Host B.
1. Add the following settings to the Mysqld configuration entry in the A host's configuration file:
master-host=10.10.8.112
Master-user=backup
master-password=1234
replicate-do-db=test#p# Pagination Title #e#
master-connect-retry=10

2. In configuration file B, mysqld the following settings:
Log-bin
Binlog-do-db=test

Note: When an error occurs, the *.err log file synchronization thread exits, and when the error is corrected, to let the synchronization mechanism work, run slave start.

Re-start A, B machine, you can achieve two-way hot backup.
Iv. Common problems and solutions
1, slave machine permissions problem, not only to the slave machine file permissions, but also to give it replication slave permissions. #p # pagination Title #e#
2, after modifying the slave machine/etc/my.cnf, slave machine after the start of the MySQL service, remember to delete the Master.info
3. When the show master status or the show slave status is not normal, see. How err is said.
4, slave on MySQL replication work has two threads, I/O thread and SQL thread. The function of I/O is to fetch its binlog from the Master 3306 port (Master has modified anything to write to its binlog to wait for slave update), and then writes to the local relay-log, and the SQL Thread is to read the local relay-log, and then convert it to the cost of MySQL can understand the statement, so the synchronization of such a step-by-step completion. Determines the I/O thread is/var/lib/mysql/master.info, and the SQL The thread is/var/lib/mysql/relay-log.info.
5, start Slave, command with start slave; re-start with restart slave
Reproduced in: http://www.itxuexiwang.com/a/shujukujishu/2016/0302/201.html?1457018614

Implement master-Slave synchronization between two MySQL databases