Do you really know the MySQL data security system as a DBA for so long? Go

To share with you about MySQL in the data security topic, how to use some configuration to ensure data security and ensure that the storage of data is safe to the ground.

I joined Momo in 2014 and joined the network in 2015 to do the MySQL operation, including the development of automation.

Next I will introduce to you from four aspects, how the database through some configuration to achieve data security.

• Stand-alone security

• Cluster security

• Backup security

• Development

In today's industry, data is a very important asset.

How does the data guarantee security? In daily life, we all think that some commercial databases can better ensure the security of data. They think that for the emerging MySQL, it is agreed that can be used on the Internet, because the Internet data loss is irrelevant, I feel that they are a MySQL database is a misunderstanding.

The data security I'm talking about is the security of the data, not the hacker attack, not the cyber security.

In the beginning of the enterprise may have limited resources, only use a single machine, when the single-machine may be deployed one.

With the development of the enterprise, it is possible that there will be a data collapse problem, which will result in the entire data is not available. Therefore, it will be in the direction of the cluster, the master-slave mode will be used to ensure that the data distributed to multiple nodes, even if a node crashes, there are other nodes are available.

Does this guarantee that the real data will not be lost? There may be a network failure in the machine room, or the entire computer room down. This year also encountered a lot of the entire data is not available, backup is not available, this time the data is lost.

In order to better ensure data security, to be backed up. To make local and remote backups of the data so that the cluster is not available, use the backup to recover.

Four, introduce the basic development of MySQL.

The following is a detailed description of these four parts.

I. Stand-alone security

A single machine security involves two configuration parameters:

• Double Write

• Innodb_flush_log_at_trx_commit

Detailed instructions below.

1.1 Double Write

Our enterprise at the beginning of the time only one node, how to ensure that our data on the disk? It can start when MySQL is down or the system is down. During the startup process, they will detect if the data page is normal, and we will introduce a double Write.

What is double Write? All operations on a page, including headers, pages, and so on, are originally physical operations. But in order to save the log volume, change it to logical, it will record including the number of space, content fields and so on, until it is necessary to really write these logic into the physical.

In the process of writing, first ensure that the page is written correctly. If a page breaks during writing because of multiple writes, it is not writable. At this point we need a double Write, which is now a mirror and the other is a backup.

There is a 2M cache before MySQL 5.7. If there is a problem with this page during the validation process, a double write will be used to copy the two pages back so that our database can be restored to a usable state.

Four questions:

• Two times write itself page break will there be a problem?
  Before it can manipulate the page, the state before the database is recorded is consistent. At this time the page problem, itself will not go to write, there will be no problem.

• Has the recent data been overwritten?
  Because we know only 2 m of space, so will always overwrite to write.

• Performance issues?
  We know that the double write has been written two times. Does this have to do with our operation two times? We know that it has a cache space, when the cache space is full, the logic will be written into sequential write, the impact on the disk is relatively small, will not lead to performance consumption, but also bring a little performance consumption.

• Can I have a double Write?
  It's possible.

1.2 Innodb_flush_log_at_trx_commit

There are two types of database outages:

• Database Downtime Server OK

• Server outage

In the event of an outage, the data is lost if the transaction committed to the database is not written to the redo log file.

Innodb_flush_log_at_trx_commit the three-value policy for the parameter:

Do these three parameters cause data loss in the database outage?

When the value is 0, write every second, when the database is down or the system goes down, there will be lost data.

At a value of 1, each operation is written to the real redo log disk file, which is not lost regardless of the database outage or the operating system outage. At startup, redo log can be recovered in the file.

When the value is 2, it is brushed into the operating system cache, the database is down, but the operating system is complete and can be written to disk, when the data is not lost.

Two. Cluster security

In the process of a single machine, we have two parameters to ensure that our data drop disk. However, it is possible for the server to go down and not start up, when our data and so on all lost, or can use the backup data to recover part of the data, this situation for our enterprise will have a certain impact.

• Master-slave replication

• Asynchronous replication

• Semi-synchronous replication

• MySQL Galera Cluster

At this time, we can through the cluster mode, such as the establishment of a master multi-or multi-master and so on, to ensure that our data in a number of nodes, even if one node down, you can switch the read and write services to another node, to ensure that the data available.

2.1. Master-slave Replication 2.1.1 Sync_binlog

Master-slave replication is an asynchronous Pattern.

By default, we use simple master-slave replication to ensure that a primary multi-node is synchronized.

One of the most important things in the copy process is Sync_binlog, which is refreshed to the cache every time it is equal to 0, and is synchronized to disk when it is equal to 1.

The effect of this parameter on data loss:

The same database downtime is divided into two kinds, one is the system outage, one is the database outage. The database outage has been written to the OS cache and is therefore not lost. If it happens that the operating system goes down, it loses OS cache, and if the Sync_binlog value is 0, the data will be lost.

2.1.2 Binlog_format

There are three binary log formats:

• STATEMENT

• MIXED

• ROW

We are now mainly using the row format, there is a problem, that is, we have a slightly larger log volume.

2.1.3 and Copying Pos point-related parameters

In the master-slave copy process, there are two important files:

• One is the record master-info

• One is the record relay-log-info

From the MySQL 5.7 version, the table structure has been used, and it is recommended to do so.

With these two sync-master-info and sync-relay-log-info parameters, if there is an outage, we can know where the points were previously copied.

Relay_log_recovery a parameter, if set to 1, it will find the last position of the MySQL thread in the latest execution, and then use that location to re-innovate a relay-log, this value is very important to us. If not set to 1, it will cause us to start the process, the point is not the latest point, will cause a conflict of things, and even lead to master-slave replication conflicts.

2.2 Semi-synchronous

The main talk about three points:

• Semi-synchronous replication features

• Semi-synchronous vs. asynchronous comparison

• Semi-synchronous parameters: Rpl_semi_sync_master_wait_point

2.2.1 Semi-synchronous replication features

The main library and from the library will be delayed, possibly from the library not receiving the Binlog information from the main library.

MySQL introduced a semi-synchronous replication, that is, the main library will wait for at least one node from the library, whether to actually get to the Binlog log, and flush to the redo log file.

What are the features of semi-synchronous?

• Tell if the main library is semi-synchronous from the library

• The main library transaction commits are blocked

• Notifies the main library after writing relay log from the library

• Automatic conversion to asynchronous replication after the main library waits for a timeout

• Master and slave must simultaneously turn on semi-synchronous

First it tells the main library whether it is configured for semi-synchronization, and when the main library commits things, the thread blocks and waits for a reply from the library. If there is no reply, it will drop this from the library into asynchronous replication. If there is a reply, after confirmation, the main library thread will continue to do other transactions, then in this process, it will be blocked.

After receiving the Binlog from the library to the main library, it is written to the redo log.

In the semi-synchronous replication process, each transaction commits, waiting for at least one slave node, if the slave node has obtained the Binlog, then the master node will actually commit to do subsequent operations, at this time can guarantee that at least one node is the data of the master node is consistent.

2.2.2 Semi-synchronous replication features

The main difference is that semi-synchronous replication guarantees that at least one slave node will be consistent with the primary node data.

2.2.3 Rpl_semi_sync_master_wait_point

In the semi-synchronous process, there is an important parameter rpl_semi_sync_master_wait_point to control, that is, after the main library's thread commits the transaction, waits after the whole process waits or after committing the transaction.

Let's start by talking about how these two configurations affect each other.

After_commit submitted to wait for a status, waiting for one from the library, onsite reply to an ACP confirmed. After reporting the ACP confirmation from the library, enter the following state, which is to notify the client of the result.

In this situation, it may result in the data being dropped from the library, because we have committed the engine layer, and at this time we are waiting, in fact, the other session on the main library is able to know the result of the transaction has been committed. However, if the main library is still waiting in the process of the main library down, and is not recoverable, then from the library may not receive data, on the main library it thinks that the transaction has been committed successfully, it will cause the library to not get this binlog, and therefore will lose the transaction data.

In order to solve this problem, in MySQL 5.7 will be introduced another state to wait for the--after_sync, is the synchronization of the immediately after waiting.

If this time the main library is down, it is possible to receive this binlog from the library, and apply it to the database from the library, when the main library has not entered the commit, so the main library data is not committed, it is possible to get a portion of the data from the library.

I personally think that more data than the loss of better. When our service is cut into from the library, the service in the process of processing, according to the more data to do the corresponding processing, the total is better than not relatively.

2.3 MySQL Galera Cluster

The above-mentioned master-slave replication, whether using asynchronous replication or semi-synchronous replication, can have the problem of losing data. Where to go, after several years of development, years of research, a lot of the use of Galera Cluster.

MySQL Galera Cluster Note points:

• Based on Binlog replication mode

• DDL execution Card dead

• DDL Precedence

• Cannot exit during DDL execution

• Flow Control

In the business of paying, we strongly demand that the data be consistent, and the PRC can satisfy this characteristic very well. Including multi-point writes, frequent problems in routine maintenance, the possibility of restarting a database, or migrating a database.

The master-slave replication architecture requires switching the read and write requests from the business to another node. If the traffic slowly cut into the other node is OK, then the impact on the business is almost no, so we can be very bold to do some switching operations.

Double write if we do not open, it is possible that the database will not start up, and there is no problem, because the equivalent of this new node added to the PCRC process, will perform a sync operation, the data will be pulled over, but the data recovery time longer.

Redo log This parameter is intended to improve some performance. Sync-binlog, even the binlog do not have to open.

With the cluster, at this time for the business, for the enterprise, should be more assured that the data should not be lost. However, there have been several cases this year, that is, the database room outage, or cluster downtime, no backup can be used, all the data is lost, we can only rely strongly on the backup.

In the first half of this year, there were two major failures, that is, even backups are not available. This time backup security is very important to our business and to our businesses.

Three. Backup security

The backup is divided into two:

• Data backup

• Binlog Backup

In the data backup, we can mirror the data of the whole machine every day, or use other MySQL double and so on to make a backup.

Binlog It's possible that we're just backing up a bit of data backup at the time of data backup, how does this data recover between this backup and the next backup? We can use Binlog.

3.1 Data backup

Backup methods are divided into:

• Cold

• Hot Spare

The Backup tool is divided into:

• Logical Backup tool

• Mysqldump

• Mysqldumper

• Mysqlpump

• Select ... into outfile

• Physical Backup Tools

• InnoDB Ibbackup

• Percona Xtrabackup

The logical Backup tool is to save the dump file, there is a problem, it is possible to dump the time is very long, the recovery time is very long.

This is when the entire database wants to rely on this backup node to recover, and the entire business waits a long time. With logical backups, the longer the recovery time, the greater the impact on the business. So, we try to use a physical backup, such as Xtrabckup.

Backup storage is divided into:

• Local storage

• Remote Storage

If our backup database is stored on a local server, there may be a backup file that is not available and can result in loss of data.

Therefore, we have to prioritize remote backup, you can back up the MySQL data files to a different room.

3.2 Binlog Backup

With a data backup, it's just a mirror of a point, what about the data between the last backup and the next backup? Only through Binlog, so you also need to back up the binlog, and it is best to save to remote.

Four. Development

We are in use, the business is just starting, it is possible to first stand-alone mode, with the development of the business, we will expand to replication mode, as the business again grow and business importance requirements, will start to introduce the cluster mode, to ensure that the data is truly not lost, even if one node is down, other nodes have complete data.

Do you really know the MySQL data security system as a DBA for so long? Go