MySQL read-write separation

Why does MySQL have to master replication (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.

MySQL Master-slave replication (read-write separation) and cluster differences:

I was just beginning to study MySQL, not very professional. My understanding is:

Master-slave replication (read/write separation):

Typically two or more database servers are required (one for writing data, one for synchronizing master data and for data query operations).
Limitations:

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. The primary database server is down and you need to manually switch the business system to the slave database server. High availability is not possible (unless the deployment of keepalive is made into a highly available scenario). Cluster is composed of n database server, the data write and query is random to any database server, 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 be used for cluster synchronization of NDB storage engine data, if it is InnoDB or other MySQL storage engine is not available. This also led me to abandon the application of this scheme in the business system.

Back to the Chase

MySQL5.6 begins a master-slave copy in two ways: Log-based (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!
master:192.168.15.141
slave:192.168.15.142
How to install MySQL I will not elaborate, please see Linux installed MySQL

Configuration file

Master

vi /etc/my.cnf[mysqld] server-id = 1log-bin innodb_flush_log_at_trx_commit=1 sync_binlog=1 datadir=/var/lib/mysql character-set-server=utf8 init_connect=‘SET NAMES utf8‘

What is Sync_binlog?
When each sync_binlog ' th is written to the binary log, the MySQL server synchronizes its binary logs to the hard disk (Fdatasync ()). Note that if in autocommit mode, each execution of a statement is written to the binary log once, otherwise each transaction is written once. The default value is 0 and is not synchronized with the hard drive. A value of 1 is the safest option because when you crash, you lose at most one statement/transaction in the binary log, but this is the slowest option (unless the hard disk has a battery backup cache, which makes synchronization work faster).
Slave

server-id=2 datadir=/var/lib/mysql character-set-server=utf8 init_connect=‘SET NAMES utf8‘

Set the slave user right to synchronize in the master database to start the database

[root@localhost ~]# service mysqld start

Go to MySQL

[[email protected] ~]# mysql -uroot -p123456mysql> GRANT REPLICATION SLAVE ON *.* TO ‘<root>‘@‘<192.168.15.142>‘ IDENTIFIED BY ‘<123456>‘;Query OK, 0 rows affected (0.10 sec)

Primary database lock table (prevents re-inserting data to get binary log coordinates of the primary database)

WITH READ LOCK;

Create a data snapshot using the mysqldump command on the primary database

[root@localhost ~]# mysqldump -u root -pnew-password  --databases drp > /root/test.sql

Jian Di (2) Step Master data lock table operation SSH login to slave database

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/root/directory;

From Importing a master snapshot

[root@localhost ~]# mysql -uroot -p -h127.0.0.1 -P3306< test.sqlEnter password: You have mail in /var/spool/mail/root

To set the primary database information for replication from the database (note the values of Master_log_file and Master_log_pos are modified)

Master_log_file and Master_log_pos viewing in master data

mysql> CHANGE MASTER TO MASTER_HOST=‘192.168.15.141‘,MASTER_USER=‘root‘,MASTER_PASSWORD=‘new-password‘,MASTER_LOG_FILE=‘mysqlmaster-bin.000001‘,MASTER_LOG_POS=3931mysql> START slave;Query OK, 0 rows affected (0.74 sec)

View the status of the re-database

\G

If the following two parameters are all Yes, then the master-slave configuration succeeds!

Slave_sql_running: Responsible for your own slave MySQL process
Slave_io_running: Responsible for IO communication with the host
To view the error:
Last_io_error:fatal error:the slave I/O thread stops because master and slave have equal MySQL server IDs; These IDs must is different for replication to work (or The–replicate-same-server-id option must is used on slave but thi s does not always make sense; Please check the manual before using it).
The meaning is from the server_id and the same as the Lord, after the view found from the/etc/my.cnf in the server_id=1 this line I did not comment off (copy the section below I set the server_id), so immediately put this line commented out, and then restart MySQL, The same mistake was reported.
Solution:
Check whether the my.conf server_id in the master-slave database is duplicated, and if so, change it and restart again.
This way, when the primary database is inserted into the data, the new table or the database is created, it can be automatically synchronized to the database from the ~

MySQL read-write separation