Analysis of high-availability architecture and Master/Slave replication in mysqllinux (1)

Master-slave replication of mysql databases is relatively simple, but it is also a basis for the high availability of mysql databases, in my understanding, the high availability of all mysql Databases evolved from this master database and from simple replication. This blog is written because

Master-slave replication of mysql databases is relatively simple, but it is also a basis for the high availability of mysql databases, in my understanding, the high availability of all mysql Databases evolved from this master database and from simple replication. This blog is written because

Master-slave replication of mysql databases is relatively simple, but it is also a basis for the high availability of mysql databases, in my understanding, the high availability of all mysql Databases evolved from this master database and from simple replication. I wrote this blog because a colleague recently told me that he was doing a mysql ha experiment using the master and slave architectures of keepalived + mysql, which left me confused, I will review the high-availability architecture of mysql ha with him again, knowing that this architecture is not ideal.

My understanding is that there is a time difference between master and slave replication, and Database Consistency is not guaranteed. It takes time to use shell to monitor replication of slave databases, in a simple example, when the master is down, the master has never been completely copied or has not been copied, and there is a big problem in the actual production environment. The architecture of master and master replication can effectively solve the Database Consistency problem, which can be achieved through keepalived.

After all, the master and slave replication architecture is for database backup. Although it is a hot standby architecture, it cannot be used for high redundancy. In fact, I still appreciate this architecture, there is also a benefit of reading and writing separation to improve the I/O performance of the database. Although I have not established many e-commerce platforms, this architecture is basically the most popular. After the master node fails, the architecture still needs to be switched manually, so there is no split-brain problem. After all, the database is involved, so it is better to be secure. Database affairs in e-commerce are all major issues.

In another architecture, I really appreciate and have used the master/Master automatic switch architecture. This solves the single point of failure problem and can also automatically switch over (you must set an alarm when switching, or online.) At the same time, it also backs up each other. After reading an article about mysql database optimization (below) by Dr. fuqin, I was inspired by the fact that if the database investment is sufficient, the architecture of the master database and slave database can also be implemented, although I have never applied it directly, it should be theoretically the most stable mysql database application at the bottom. If I have the opportunity to apply it, it should be difficult to add load balancing in this architecture. If you have time, you must perform this test to see the effect.

The load balancing and HA architecture of mysql + lvs + cluster + keepalived have also been applied, but the effect is not very satisfactory, and the cost is a bit high, and later maintenance is also a problem, in the future, I will list the implementation steps and configurations of this architecture and study it with interested friends.

In my opinion, both the high-availability mysql architecture should at least implement local and remote dual-Backup.

Master and slave replication are much simpler. We all know that the principle of master and slave replication is as follows:

-Bin = mysql-bin

Binlog-do-db = falvhezi2 (database to be synchronized)
Binlog-ignore-db = mysql (synchronization not required)

Binlog-ignore-db = test (synchronization not required)

Succeeded.

Pay attention to the following two issues:
Slave_IO_Running: NO
If this parameter is set to "NO", it is mostly a connectivity problem.
1. network connection between two server systems
2. Database User Permissions
Slave_ SQL _Running: NO
The database Binary File Permission is incorrect.

This article is from the "just have a drink" blog. Please keep this source