MySQL master-slave replication risk and prevention

According to the old boy's teacher's video, the risk of master-slave replication is that users with write access are connected to data changes from the library.

Several methods are given in the video

1) Reclaim the user's write permissions from the Vault (Update/delete/insert)

This method must ensure that the corresponding user on the main library does not change, and if it does change, the user's authorization will be synchronized to the library, resulting in the recovery failure.

2) Add a read-only user from the library and inform the developer if the connection from the library is used by the read-only user.

This method must work closely with the developer, the risk is also very large, once the development of the rules and software to use write access to the user to connect from the library (sometimes even development is an unintentional loss), it is bad. So this method must be think twice.

3) ignore the MySQL database while synchronizing, and add the read-only user from the library (consistent with the main library's account).

This approach is relatively safe, just in case the main library fails, from the library after taking over the account information, it is difficult to recover, and if the main library account more, there is a great risk. This is possible if the data is backed up from the point of view and the number of accounts is not many (or if the MySQL database on the main library is backed up).

4) Add read-only parameters from the library before starting

This approach is close to perfect, but not without risk, first of all, read-only can not limit the full access to users, second, when the main library fails, from the library must be removed read-only;

I personally think that the fourth kind is more recommendable, because when the main library fails,

First, the account password authorization will not be lost;

Second, these 4 methods, in addition to the second, others require manual intervention (semi-automatic), and the extent of intervention, the fourth is the lowest;

The second approach, although the development can be self-switching to achieve automatic replacement of master and slave libraries, but in the operation of the angle of view, this is a dilemma; on the management side, this approach is the most likely to maintain service durability in four ways; in a word, the second approach is a double-edged sword.

MySQL master-slave replication risk and prevention