High Availability MySQL excerpt-security and binary logs

High Availability MySQL p59

Security and binary logs

 

Generally, a user with the replication slave permission has the permission to read all events on the master. Therefore, to ensure security, the account should not be damaged. Here are some examples of preventive measures:

1. Try to make it impossible to log on to this account outside the firewall;

2. Record all logs that attempt to log on to the account and place the logs on a separate security server;

3. encrypt the connections used between the master and salve, for example, MySQL's built-in SSL (Secure Sockets Layer) support.

Even if this account is secure, there is still some information that does not need to be stored in binary logs. Therefore, it makes sense not to store it in the first place.

A common sensitive information is the password. When you execute a statement that changes the table on the server and contains the password required to access the table, events that contain the password are written into binary logs.

A typical example is:

UpdateEmployeeSetPass=Password ('Foobar')WhereEmail= 'Mats@example.com';
 

If the copy is correct, it is best to rewrite the statement without a password. You can use the following method to calculate and store the hash password to a user-defined variable, and then use it in the expression:

Set @ Password =Password ('Foobar');UpdateEmployeeSetPass= @ Password WhereEmail= 'Mats@example.com';
 

Because the set statement is not copied, the original password will not be stored in the binary log, but will only be stored in the server's memory when the statement is executed.

This method works as long as you store the password hash to the table without a plain text password. If the original password is directly stored in the table, is there any way to prevent the password from ending in the binary log. However, it is a standard practice to store a hash password, which can prevent the original data from being obtained by learning the password.

the encapsulated Connection provides some protection for the connection between the encrypted master and the salve. However, if the binary log itself is broken, the encrypted connection is useless.