How to configure the environment for mysql5.6.14 master-slave replication (also called mysqlAB replication)

This article describes how to configure the environment of mysql5.6.14 master-slave replication (also called mysqlAB replication). For more information, see I. Principles of mysql master (master) slave (slave) replication:

(1) the master records data changes to the binary log, that is, the file specified by the configuration file log-bin (these records are called binary log events and binary log events)
(2). slave copies the binary log events of the master to its relay log)
(3). The slave replays the events in the relay log and reflects the changes to its own data (data replays)

A brief schematic diagram is provided:

II. types supported by mysql master-slave replication:

(1) statement-based replication: SQL statements executed on the master server run the same statement on the slave server. MySQL uses statement-based replication by default, which is more efficient.

Word-spacing: 0px "> (2 ). row-based replication: directly copies the changed content, regardless of the statement that triggered the change. supported since mysql5.0
Word-spacing: 0px "> (3 ). hybrid replication: Statement-based replication is used by default. if statement-based replication fails, row-based replication is used.

3. notes for master-slave configuration:

(1). the versions of the master DB server and slave DB server are consistent.
(2 ). the data in the master DB server is consistent with that in the slave DB server database [here, the master backup can be restored from the top, or the master data directory can be copied directly to the corresponding data directory]
(3). enable binary logs on the master DB server. the server_id of the master DB server and slave DB server must be unique.

IV. master-slave configuration steps:

Appendix:

1. configuration on the master DB SERVER

(1). install the database
(2) modify the database configuration file, specify server_id, and enable the binary log (log-bin)
(3) start the database and check the current log and position number.
(4). log on to the database and authorize the user [the IP address is the slave IP address. if it is a two-way master/slave, the IP address of the local machine must be authorized here (in this case, the IP address is the slave IP address)]
(5). back up the database [remember to lock and unlock]
(6) transfer the backup to the slave database server
(7). start the database

Follow these steps to build a one-way master/slave instance:

(1) log on to the database and specify the address, user, password, and other information of the master database server. [this step is only required when two-way master and slave databases are used.]

(2). enable synchronization and view the status

2. configuration from DB SERVER

(1). install the database
(2) modify the database configuration file and specify the server_id. [enable the binary log (log-bin) if a two-way Master/Slave node is set up.]
(3). start the database and restore the backup.
(4). view the current log and position number. [this step is not required for one-way master. bidirectional master/slave is required.]
(5). specify the address, user, password, and other information of the master DB server.
(6). enable synchronization and view the status

V. case study of setting up A one-way master-slave environment [mysql A/B replication:

1. both the master DB server and slave DB server have installed the corresponding database version. both of my DB servers have been installed (5.6.14) and both are dual instances. we will not demonstrate the installation here, see mysql source code compilation and installation and mysql multi-instance configuration.
Note: The selinux of both machines is disable (disable selinux permanently. modify/etc/selinux/config and change SELINUX to disabled). you can disable the firewall, if it is enabled, [if not, add a firewall policy]
2. modify the configuration file (/etc/my. cnf) of the master DB server, enable the log function, set the server_id value, and ensure that the unique [client102 is the master DB server]

[Root @ client102 scripts] # vim/etc/my. cnf # Modify the following two parameters in the configuration file: # set server_id. it is generally recommended to set it to IP address, or add some numbers server_id = 102 # enable the binary log function, it is better to have a meaning log-bin = mysql3306-bin

3. start the database server, log on to the database, and grant the corresponding users for synchronization.

# Here I am using multi-instance mysql, so the startup is like this. if you are using a single instance, you can directly start it [/etc/init. d/mysqld start] [root @ client102 scripts] # mysqld_multi start 3306 # log on to the mysql server [root @ client102 scripts] # mysql-uroot-S/usr/local/mysql/mysqld3306.sock- p # grant the user permission to synchronize mysql> grant replication slave on *. * to 'kongzhong '@' 192. 168.1.100 'identified by 'kongzhong '; Query OK, 0 rows affected (0.00 sec) # refresh the authorization table information mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) # view the position, write down the position (very important, from the machine need this position and the current log file, I am here 414 and mysql3306-bin.000001) mysql> show master status; + metric + ---------- + -------------- + ---------------- + usage + | File | Position | Binlog_Do_DB | usage | + usage + ---------- + -------------- + ------------------ + usage + | mysql3306-bin.000001 | 414 | | + ---------------------- + ---------- + -------------- + ------------------ + ------------------- + 1 row in set (0.00 sec)

4. to ensure data consistency between the master DB server and slave DB server, the master backup is used here to restore the initial data.

The code is as follows:

# Temporary lock table mysql> flush tables with read lock; # full database backup implemented here. In practice, we may only synchronize one database, you can back up only one database # open a new terminal, perform the following operations: [root @ client102 data] # mysqldump-p3306-uroot-p-S/usr/local/mysql/mysqld3306.sock -- all-databases>/tmp/mysql. SQL # unlock mysql> unlock tables; # transmit the backup data to the slave machine for [root @ client102 data] # scp/tmp/mysql. SQL root@192.168.1.100:/tmp

5. you only need to modify one configuration file from the DB server.

The code is as follows:

[Root @ client100 ~] # Vim/etc/my. cnf # set server_id. it is generally recommended to set it to IP address or add some numbers server_id = 100

6. start the database and restore the backup data.

The code is as follows:

# Start the database [root @ client100 ~] # Mysqld_multi start 3306 # Restore data backed up by the master DB server [root @ client100 ~] # Mysql-uroot-S/usr/local/mysql/mysqld3306.sock-p </tmp/mysql. SQL

7. log on to the database and add relevant parameters (ip address/port of the master DBserver/user/password/position/log file to be read)

The code is as follows:

[Root @ client100 ~] # Mysql-uroot-S/usr/local/mysql/mysqld3306.sock-p mysql> change master to-> master_host = '2017. 168.1.102 ',-> master_user = 'kongzhong',-> master_password = 'kongzhong ',-> master_port = 3306,-> master_log_file = 'mysql3306-bin.000001 ', -> master_log_pos = 414; #/* The following is a part of the annotation: #/* specify the master DB server IP address master_host = '2017. 168.1.102 '#/* specifies the user used for synchronization [this is the user authorized on the master DB server] master_user = 'kongzhong' #/* specifies the password of the user used for synchronization master_password =' kongzhong '#/* specify the port of the master DB server [example below, you can focus on this] master_port = 3306 #/* specifies the log file from which the DB server reads [use show master status to view logs on the master DB server] master_log_file = 'mysql3306- bin.000001 '#/* specifies the POSITION from which to start reading master_log_pos = 414 # enable master-slave synchronization mysql> start slave; # view the master-slave synchronization status mysql> show slave status \ G; # mainly refer to the following two parameters: [if these two parameters are yes, the master-slave synchronization is normal] Slave_IO_Running: Yes Slave_ SQL _Running: Yes

8. now we can create a new table on the master DB server to see if it can be synchronized to the slave DB server. I will not test it here.

[Note: Do not manually insert data from the DB server. if the data is inconsistent, the master and slave nodes will be disconnected and need to be reconfigured]

If there is a problem, you can try to disable IPTABLES (/etc/init. d/iptables stop) and selinux (setenforce 0: temporarily disable selinux and permanently disable selinux. modify/etc/selinux/config and change SELINUX to disabled)

9. the above is a one-way master/slave mechanism, which is also widely used. some people want to know how to set up two-way master/slave. In fact, the log function is enabled for both the master DB server and slave DB server, then, authorize the user in the primary db server [authorize the user as the slave server, that is, the IP address here is the IP address of the primary db server], then perform the chang master operation on the master database server. if you have any questions, leave a message to ask.

The above is the configuration method of the mysql 5.6.14 master-slave replication (also known as mysql AB replication) environment. For more information, see The PHP Chinese network (www.php1.cn )!