MySQL5.6 Master-slave replication (read/write separation)

First, preface: Why does MySQL want to master from copy (read-write separation)?
in layman's terms, the performance of a business system degrades if both the read and write of the database are operated on the same database server.
in order to improve the performance of the business system and optimize the user experience, it is possible to reduce the load of the primary database by the master from replication (read/write separation).
and if the primary database goes down, you can quickly switch the business system to the slave database to avoid data loss.

Second, MySQL master-slave replication (read and write separation) and cluster differences:
I was just beginning to study MySQL, not very professional. My understanding is:
1, master-slave replication (read and write separation): Generally need two and above the database server (one for writing data, one for synchronizing the master data and for data query operations).
Limitations:
(1) After the configuration of the master-slave replication, the same table, only one server write operations. If a write operation is performed from the top, and then the master operates the table, or causes the master to never synchronize, it is said that it can be configured as the main master mode, but I have not studied it yet.
(2) The primary database server is down and the business system needs to be manually switched to the slave database server. High availability is not possible (unless the deployment of keepalive is made into a highly available scenario).
2, the cluster is composed of n database server, the data write and query is random to any database server, the other database server will automatically synchronize the operation of the database.
Any database outage does not have a large impact on the entire cluster.
limitations: I have been tested to know that the MySQL cluster version (MySQL Cluster) can only synchronize the data of the NDB storage engine, if it is a innodb or other MySQL storage engine. This also led me to abandon the application of this scheme in the business system.

third, the return to the point, the next start MySQL5.6.12 master-slave copy tutorial:
1, MySQL5.6 start master-slave replication in two ways: based on the log (Binlog); Gtid (global transaction identifier).
Note: The Gtid method does not support temporary tables! So if your business system is going to use a temporary table, do not consider this way, at least the latest version of MySQL5.6.12 Gtid replication does not support the temporary table.
so this tutorial is mainly to tell you how to copy from the log (Binlog) way!

2. mysql official MySQL replication tutorial:
http://dev.mysql.com/doc/refman/5.6/en/replication.html
This official tutorial is highly recommended for everyone to read (requires a certain amount of English reading ability Oh!) No, just Google Translate and then read it ~).

3. Preparatory work:
(1) Before you configure MySQL master-slave replication (read and write separation), you need to install the MySQL5.6 on both the master and slave servers first.
(2) The current MySQL5.6 GA version is MySQL5.6.12 (click here to download the MySQL5.6.12 source package).
personally recommended Linux (Redhat/centos 6.4) source code compilation installation, the specific can see this tutorial: Redhat/centos source code compiled installation MySQL5.6.12
(3) Note:
(a) If you need to use a production environment, do not be anxious to do a MySQL boot operation when installing MySQL in a tutorial. It is recommended to delete MySQL initialization generated/usr/local/mysql/mysql.cnf and then put your optimized MySQL configuration file my.cnf into/etc.
(b) It is recommended to host two servers on the same LAN, and to master and prepare two database networks for interoperability.
(4) My environment:
Primary Database ip:192.168.100.2
from the database ip:192.168.100.3

4. Modify the configuration file of the master database:
1 [mysqld]
2 server-id=1
3 Log-bin=mysqlmaster-bin.log
4 sync_binlog=1
5 #注意: The following parameter needs to be modified to about 70% of the server's memory
6 innodb_buffer_pool_size = 512M
7 Innodb_flush_log_at_trx_commit=1
8 Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero
9 Lower_case_table_names=1
Ten Log_bin_trust_function_creators=1

to restart MySQL after the modification:
1 #/etc/init.d/mysql Restart

attach a master database profile that I have optimized: Click here to download

5. Modify the configuration file from the database (Server-id is configured to a number greater than 1):
1 [mysqld]
2 server-id=2
3 Log-bin=mysqlslave-bin.log
4 sync_binlog=1
5 #注意: The following parameter needs to be modified to about 70% of the server's memory
6 innodb_buffer_pool_size = 512M
7 Innodb_flush_log_at_trx_commit=1
8 Sql_mode=strict_trans_tables,no_auto_create_user,no_engine_substitution,no_auto_value_on_zero
9 Lower_case_table_names=1
Ten Log_bin_trust_function_creators=1

to restart MySQL after the modification:
1 #/etc/init.d/mysql Restart

attach a profile from a database that I have optimized: Click here to download

6. SSH Login to the main database:
(1) Create an account for master-slave replication on the primary database (192.168.100.3 exchange your slave database IP):
1 # mysql-uroot-p
2 mysql> GRANT REPLICATION SLAVE on * * to ' repl ' @ ' 192.168.100.3 ' identified by ' repl ';

(2) Primary database lock table (no re-inserting data to get the binary log coordinates of the primary database):
1 mysql> FLUSH TABLES with READ LOCK;

(3) then clone an SSH session window and open the MySQL command line in this window:
1 # mysql-uroot-p
2 mysql> SHOW MASTER STATUS;
3 +------------------------+----------+--------------+------------------+-------------------+
4 | File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
5 +------------------------+----------+--------------+------------------+-------------------+
6 | mysqlmaster-bin.000001 |              332 |                  |                   | |
7 +------------------------+----------+--------------+------------------+-------------------+
8 1 row in Set (0.00 sec)
9 mysql> exit;

in this example, the binary log file is mysqlmaster-bin.000001, the location is 332, and the two values are recorded, which will be used later.
(4) Create a data snapshot using the mysqldump command on the primary database:
1 #mysqldump-uroot-p-h127.0.0.1-p3306--all-databases--triggers--routines--events >all.sql
2 # Next you will be prompted to enter the root password of the MySQL database, after the input is completed, if the current database is not large, you will soon be able to export the completed.

(5) Jian Di (2) Step Master Data lock table operation:
1 mysql> UNLOCK TABLES;

7. SSH login to the slave database:
(1) Upload the primary database snapshot All.sql from the previous backup to a path from the database via FTP, SFTP, or by other means, such as I put in the/home/yimiju/directory;
(2) from the Import Master snapshot:
1 # Cd/home/yimiju
2 # Mysql-uroot-p-h127.0.0.1-p3306 < All.sql
3 # Next you will be prompted to enter the root password of the MySQL database, after the input is completed, if the current database is not large, will soon be able to import completed.

(3) to set the primary database information to be replicated from the database (note the values of Master_log_file and Master_log_pos are modified):
1 # mysql-uroot-p
2 mysql> Change MASTER to master_host= ' 192.168.100.2 ', master_user= ' repl ', master_password= ' repl ', Master_log_ File= ' mysqlmaster-bin.000001 ', master_log_pos=332;
3 # Then start the replication thread from the database:
4 mysql> START slave;
5 # then query the slave state of the database:
6 mysql> SHOW slave STATUS \g
7 # If the following two parameters are yes, then the master-slave configuration is successful!
8 Slave_io_running:yes
9 Slave_sql_running:yes

(4) Next you can create a database, table, insert data on the primary database, and then see if the operations are synchronized from the database

MySQL5.6 Master-slave replication (read/write separation)