Master-Slave MySQL and read/write splitting (Amoeba) in Linux

Master-Slave MySQL and read/write splitting (Amoeba) in Linux

I. Why do I need to perform Mysql master-slave replication (read/write splitting )?
In general, if the read and write operations on the database are performed on the same database server, the business system performance will be reduced.
To improve the performance of the business system and optimize the user experience, master-slave replication (read/write splitting) can be used to reduce the load on the primary database.
In addition, if the primary database goes down, you can quickly switch the business system to the slave database to avoid data loss.

Ii. Differences between MySQL master-slave replication (read/write splitting) and Cluster

I started my research on MySQL and was not very professional. My understanding is:
1. Master-slave replication (read/write splitting): Generally, two or more database servers are required (one for Data Writing and the other for data synchronization and data query ).
Limitations:
(1) After configuring master-slave replication, only one server can write data to the same table. If the write operation is performed on the top, and the master also operates on this table, or the master is not synchronized, it is said that the master mode can be configured, but I have not studied it yet.
(2) When the primary database server goes down, you need to manually switch the business system to the slave database server. High availability is not possible (unless keepalive is deployed to implement a high availability solution ).
2. The cluster is composed of N database servers. Data Writing and query are random to any database server. Other database servers automatically synchronize database operations.
If any database goes down, it will not have a major impact on the entire cluster.
Limitations: after testing, I know that the current mysql Cluster version (MySQL Cluster) can only perform Cluster synchronization on the data of the NDB storage engine. If it is INNODB or other MySQL storage engines, it will not work. This also led me to give up applying this solution in the business system.

Iii. Back to the question, start the master-slave replication tutorial of MySQL5.5.12:
1. There are two ways to start master-slave replication from MySQL5.5: Log-based (binlog) and GTID-based (global transaction identifier ).
Note that the GTID mode does not support temporary tables! Therefore, if your business system needs to use temporary tables, do not consider this method. At least the latest version of MySQL5.5.12 GTID does not support temporary tables.
Therefore, this tutorial mainly aims to show you how to use the binlog Method for Master replication!

2. MySQL Replication tutorials officially provided by MySQL:
Http://dev.mysql.com/doc/refman/5.6/en/replication.html
This official tutorial strongly recommends that you read it (you must have a certain degree of English Reading Skills! If it doesn't work, google Translate it and read it again ~).

Therefore, data is usually synchronized through Master-Slave replication (Master-Slave), and then through read/write splitting (MySQL-Proxy) to improve the database's concurrent load capacity for deployment and implementation.

As shown in:

MySQL5.6 master-slave Replication Based on GTID

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

Build a MySQL proxy server for read/write splitting + Master/Slave Synchronization

MySQL 5.5 master-slave bidirectional Synchronization

MySQL 5.5 master-slave synchronization troubleshooting

MySQL master-slave replication asynchronous semi-sync instance

Iii. MySQL master-slave Replication
Scenario Description:

Master database server: 42.51.153.157. MySQL has been installed and no application data exists.
Slave database server: 42.51.155.219. MySQL has been installed without application data.

System: CentOS6.4 32-bit

Mysql: Version 5.5

Disable selinux Firewall

Setenforce 0
Service iptables stop

2. Determine the Mysql on the server

Yum install mysql-server-y

3. Confirm time synchronization

[Root @ master ~] # Yum install ntp-y
[Root @ master ~] # Vi/etc/ntp. conf # Add the following two lines
Server 127.127.1.0
Fudge 127.127.1.0 stratum 8

[Root @ master ~] #/Etc/init. d/ntpd restart # restart the ntpd service

2) synchronize time on the node Server

[Root @ slave1 ~] #/Etc/init. d/iptables stop
[Root @ slave1 ~] # Chkconfig iptables off
[Root @ slave1 ~] # Yum install-y ntpdate
[Root @ slave1 ~] #/Usr/sbin/ntpdate 42.51.153.157

4. Configure Mysql

1. First modify the Mysql Master configuration file (Master)

[Root @ master ~] # \ Cp/etc/my. cnf/etc/my. cnf. old # backup before modification
[Root @ master ~] # Vi/etc/my. cnf # modify
Server-id = 11 // modify

Log-bin = master-bin // modify

Log-slave-updates = true // Add

[Root @ master ~] #/Etc/init. d/mysqld restart # restart Mysql
[Root @ master ~] # Mysql-u root-ppassword // log on to the database and authorize the slave server
Mysql> grant replication slave on *. * to 'yanghongfei' @ '42. 51.155.219 'identified by '123 ';
Mysql> flush privileges;
Mysql> show master status; # This log will be used later
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000005 | 343 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

2. Configure the Slave server (Slave)

[Root @ master ~] # \ Cp/etc/my. cnf/etc/my. cnf. old # backup before modification

[Root @ salve shell] # vi/etc/my. cnf
Server-id = 33 // modify the ID as long as it is larger than the Master.

Relay-log = relay-log-bin // Add

Relay-log-index = slave-relay-bin.index // Add

Restart the mysql service after modification.

Log on to Mysql and configure Synchronization

[Root @ salve shell] # service mysqld restart

[Root @ slave1 ~] # Mysql-u root-ppassword

Mysql> stop slave; # stop first

Mysql> change master to master_host = '42. 51.153.157 ', master_user = 'yanghongfei', master_password = '000000', master_log_file = 'mysql-bin.000005', master_log_pos = 123456;

Mysql> start slave;

Mysql> show slave status \ G;

Check whether the operation is successful ~

When both of the above are Yes, it indicates that the operation has succeeded ~~

3. test whether the master-slave database is successfully copied. If the master server creates a database, check whether the database has been synchronized.

Master:

Slave:

Next I will install a Discuz forum to test whether the data table will be synchronized, here, the website will not be detailed. The environment I use here is lnmp. First, download a Discuz package. There are many

[Root @ master ~] # Unzip Discuz_7.2_FULL_ SC _UTF8.zip

[Root @ master ~] # Mv upload // usr/share/nginx/html/

[Root @ master ~] # Cd/usr/share/nginx/html/

[Root @ master html] # mv upload/Discuz

[Root @ master html] # chmod 777-R./Discuz/

[Root @ master ~] # Service nginx restart

Master:

Slave:

The above shows that the master-slave replication is successful ~~

Common commands for Mysql master-slave Replication

1. Stop master node Replication

Mysql> stop master;

2. Clear the master node copy file

Mysql> reset master;

3. Stop slave node Replication

Mysql> stop slave;

4. Clear slave node copy files

Mysql> reset slave;

5. Enable slave node Replication

Mysql> start slave;

6. view Master replication information of the primary database

Mysql> show master status;

7. view the replication status of the master node

Mysql> show master status \ G;

8. View slave node copy status

Mysql> show slave status \ G;

For more details, please continue to read the highlights on the next page:

1 2 Next Page