MySQL master-slave replication under Linux

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.

Two, 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 master-slave replication is configured, the same table can only write to one server. 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 ~).

Three: preparatory work

Before you configure MySQL master-slave replication (read and write separation), you need to install MySQL5.5 on both the master and slave servers first. My environment is an experiment of 2 units .

System: CentOS6.4 32-bit

mysql:5.5 version

SELinux shutdown Firewall off

0 setvice iptables Stop

master:42.51.153.157

slave:42.51.155.219

2. is to determine the MySQL on the server

Yum install MySQL mysql-server-y

3. Confirm Time Synchronization

Yum Install NTP-vi /etc/ntp.conf    127.127.  1.0127.127. 1.0 8

[Email protected] ~]#/etc/init.d/ntpd restart   #重启ntpd服务

2) time synchronization on a node server

[Email protected] ~]#/etc/init.d/~yuminstall -42.51. 153.157

Four: Configure MySQL

1. First modify the configuration file (master) of the MySQL master configuration

[email protected] ~]# \CP/etc/my.cnf/etc/My.cnf.old # #修改之前备份 [[email protected]~]#VI/etc/my.cnf # #修改server-ID= One                                     //ModifyLog-bin=master-bin//ModifyLog-slave-updates=true                                  //Increase[[Email protected]~]#/etc/init.d/mysqld Restart # #重启Mysql [[email protected]~]# Mysql-u Root–ppassword//log in to the database to authorize from the servermysql> grant replication Slave on *.'Yanghongfei'@'42.51.155.219'Identified by'123456'; MySQL>Flush Privileges;mysql>Show master status; # #这个日志后面会用的到+------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+| Mysql-bin.000005|343|                  | |+------------------+----------+--------------+------------------+1RowinchSet (0.00Sec

2. Configuring the Slave server (Slave)

[Email protected] shell]#VI/etc/MY.CNF Server-ID= -                                                        //Modify the ID here as long as it is larger than masterRelay-log=relay-log-bin//IncreaseRelay-log-index=slave-relay-bin.index//IncreaseRestart MySQL service after modification complete login mysql, configure sync [[email protected] shell]# service mysqld restart[[email protected]~]# MySQL-u root–ppasswordmysql>stop Slave; # #首先停止mysql> Change Master to master_host='42.51.153.157', master_user='Yanghongfei', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=343; MySQL>Start Slave;mysql> show slave status \g;

See if it's successful ~

The above 2 is the time to say yes has been successful ~ ~

3. Test whether the master-slave replication succeeds, the primary server creates the database to see if it is synchronized.

Master:

Slave:

Next I'll install a discuz forum we will test the next data sheet will not be synchronized, the installation site here no longer details I use the environment is LNMP first download a discuz package online there are many

Unzip Discuz_7.2_full_sc_utf8. Zip   mv upload//usr/share/nginx/html/~]# cd/usr/share/nginx/html/mv        upload/chmod777 -R/discuz/~]# service nginx Restart

Master:

Slave:

Above can see the master-slave replication Success ~ ~

MySQL master-slave replication under Linux