MySQL 5.6.14 Master-slave replication (also known as MySQL AB replication) environment Configuration method _mysql

First, MySQL master (called Master) from (called slave) Copy principle:

(1). master records data changes to the binary log (binary log), which is the Log-bin specified file (These records are called binary log events binary log event)
(2). Slave copy Master's binary log events to its relay logs (relay log)
(3). Slave redo the event in the relay log, changing the data that reflects it (the data repeats itself)

Attached schematic diagram:

Second, the MySQL master-slave replication support type:

(1). Statement based replication: An SQL statement executed on the primary server that executes the same statement from the server. MySQL defaults to use statement based replication, high efficiency

word-spacing:0px "> (2). Row based replication: Copy the changed content directly to the past without caring about exactly which statement caused the content to change. Starting from mysql5.0 support
word-spacing:0px "> (3). Mixed-type replication: statement-based replication is used by default, and is based on row replication once a statement-based, inaccurate replication is found.

Third, the master-slave configuration needs to pay attention to place:

(1). Primary DB server is consistent with version from DB Server database
(2). Primary DB server is consistent with data from DB Server database [This will enable you to restore the master backup from above, or you can copy the main data directory directly to the corresponding data directory from]
(3). The primary DB server opens the binary log, and both the primary DB server and the SERVER_ID from DB server must be unique

Four, the master-slave configuration of the brief steps:

With a brief schematic:

1. Configuration on the primary DB server

(1). Install database
(2). Modify database configuration file, indicate server_id, open binary log (log-bin)
(3). Start the database, see which log is currently, position number is
(4). Login to the database, authorized users [IP address from the computer IP address, if the two-way master and subordinate, here also need to authorize the IP address of this computer (at this time their own IP address is from the IP address)]
(5). Back up the database [remember to lock and unlock]
(6). Transfer backup to from DB server
(7). Start the database

The following steps for a one-way master and subordinate to build success, want to build a two-way master and subordinate needs of the steps:

(1). Log in to the database, specify the address of the primary DB server, user, password and other information [This step is only bidirectional master and subordinate, need]

(2). Turn on sync, view status

2. Configuration from the DB server

(1). Install database
(2). Modify the database configuration file, indicating server_id[if it is to build two-way master and slave, but also to open the binary log (Log-bin)]
(3). Start database, restore Backup
(4). See which log is currently, position number is [one-way master from this step does not need, two-way master needs]
(5). Specify the address of the primary DB server, user, password, and other information
(6). Turn on sync, view status

Five, one-way master-slave environment [also known as MySQL A/b replication] of the building case:

1. Primary DB server and from DB Server installed the corresponding version of the database, my two DB server has been installed (5.6.14 version), will be a dual-instance, this is not a demonstration of installation, you can refer to MySQL source compiler installation and MySQL multiple instance configuration two articles
Note: Both machines of the SELinux are disable (permanently close selinux, please modify the/etc/selinux/config, SELinux to Disabled), the firewall can choose to close, open words also line [no words, add Firewall policy]
2. Modify the primary DB server's configuration file (/ETC/MY.CNF), turn on logging and set server_id value to ensure unique [client102 Primary DB Server]

[root@client102 scripts]# vim/etc/my.cnf 
# Modify the configuration file, the following two parameters: 
# Set SERVER_ID, general recommendation set to IP, or add some numbers 
server_id = 102
# Open binary log function, you can take it, preferably have meaning 
Log-bin=mysql3306-bin

3. Start the database server and log in to the database, and grant the appropriate users to synchronize

# I'm here for multiple instance MySQL, so startup is like this, if everyone is a single instance, it can be started directly [/etc/init.d/mysqld start] [root@client102 scripts]# mysqld_multi start 3306 # Login to MySQL server [root@client102 scripts]# mysql-uroot-s/usr/local/mysql/mysqld3306.sock-p # Grant user privileges for master-slave synchronization mysql& Gt 
Grant replication slave on *.* to ' KongZhong ' @ ' 192.168.1.100 ' identified by ' KongZhong '; 
Query OK, 0 rows Affected (0.00 sec) # Refresh authorization table information mysql> flush privileges; Query OK, 0 rows Affected (0.00 sec) # To view the position number, note the position number (important, you need this position and now log files from the machine, I'm here 414 and mysql3306-bin.0000 
Mysql> Show master status; +----------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | 
Executed_gtid_set | +----------------------+----------+--------------+------------------+-------------------+
|   mysql3306-bin.000001 |       414 |         |          | 
| +----------------------+----------+--------------+------------------+-------------------+ 1 row in Set (0.00 sec) 

4. To ensure consistency between the primary DB server and data from DB Server, the primary backup is used here, and the initial data is consistent from the restore

Copy Code code as follows:

# Temporary Lock table
Mysql> flush tables with read lock;
# I have implemented a full library backup, in practice, we may only sync one library, you can only back up a library
# Open a new terminal, do the following
[root@client102 data]# mysqldump-p3306-uroot-p-s/usr/local/mysql/mysqld3306.sock--all-databases >/tmp/mysql.s Ql
# unlock
mysql> unlock tables;
# transfer backed-up data to the machine for recovery
[root@client102 data]# Scp/tmp/mysql.sql root@192.168.1.100:/tmp

5. Only one item can be modified from the DB server configuration file and the rest is done using the command line

Copy Code code as follows:

[Root@client100 ~]# vim/etc/my.cnf
# set up server_id, generally recommended to IP, or add some numbers
server_id =100

6. Start the database, restore the backup data

Copy Code code as follows:

# Start Database
[root@client100 ~]# mysqld_multi Start 3306
# Restore data backed up by the primary DB server
[root@client100 ~]# mysql-uroot-s/usr/local/mysql/mysqld3306.sock-p </tmp/mysql.sql

7. Log in to the database, add related parameters (main dbserver ip/port/sync user/Password/position number/read which log file)

Copy Code code as follows:

[Root@client100 ~]# mysql-uroot-s/usr/local/mysql/mysqld3306.sock-p
Mysql> Change Master to
-> master_host= ' 192.168.1.102 ',
-> master_user= ' KongZhong ',
-> master_password= ' KongZhong ',
-> master_port=3306,
-> master_log_file= ' mysql3306-bin.000001 ',
-> master_log_pos=414;
#/* Below is a partial note:
#/* Specify the IP address of the primary DB server
Master_host= ' 192.168.1.102 '
#/* Specifies the user for synchronization [This is the user we authorize in the primary DB server]
Master_user= ' KongZhong '
#/* Specifies the password for the user to use for synchronization
Master_password= ' KongZhong '
#/* Specify the port of the primary DB server [one of the following examples, you can focus on this]
master_port=3306
#/* specify which log file to start from DB Server [use Show master status to see Log on Primary DB server]
Master_log_file= ' mysql3306-bin.000001 '
#/* specify from which position to start reading
master_log_pos=414
# Open master/slave sync
mysql> start slave;
# View master-slave sync status
Mysql> show Slave status\g;
# mainly look at the following two parameters: [These two parameters if yes means the master-slave sync Normal]
Slave_io_running:yes
Slave_sql_running:yes

8. Below you can create a new table on the main DB server to see if you can sync to the DB server, and I'm not testing it here.

[Note: Do not manually insert data from DB Server, the data will not be consistent, the master and slave will be disconnected, need to reconfigure]

If you have a problem, you can try to turn off iptables (/etc/init.d/iptables stop) and SELinux (Setenforce 0: Temporarily close SELinux, permanently close selinux, modify/etc/selinux/ Config, change selinux to Disabled)

9. The above is a one-way master, but also with a relatively many, some people want to know how the two-way master is built, in fact, is the main DB server and from the DB sever are open logging function, and then in the main DB server to execute authorized users [here is authorized as a server, This is where the IP address is the IP address of the primary DB server, and then the Chang Master operation is performed on the primary DB server. There is no understanding of the message can be asked.