Shulou(Shulou.com)05/31 Report--

MySQL database how to achieve backup, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

1. Add database, table, input information

Before the backup, create the user information database client and the user tariff data table user_info according to the demand, and insert the data of the first three users according to the table in the requirement description. As shown below:

two。 Make a full backup first

In order to verify the incremental recovery function of the binary log, we first make a full backup of the user_info data table of the client database after inserting three pieces of user data, and then execute the "mysqladmin-u root-p flush-logs" command at the linux system command line or "flush logs;" at the "mysql >" command prompt to generate a new binary log. As shown below:

3. Continue to enter new data and make incremental backups

Continue to enter the data of the two users, and execute the "mysqladmin-u root-p flush-logs" command to refresh the binary log for incremental backup. In this way, only the operation of inserting two user data is retained in the binary log file mysql-bin.000003. As shown below:

4. Simulated misoperation to delete user_ info table

5. Restore operation

"when performing a restore operation, you need to restore the full backup first, and then restore the incremental backup."

Location-based recovery

1. Since the recovery operation has already been done above, we still simulate the misoperation to delete the user_info table, and then restore the full backup. If the operation is the same as above, we will skip it here.

two。 "to recover data based on location or point in time, you must first determine the location or point in time of the recovery by looking at the binary log file." Use the mysqlbinlog-no-defaults binary log file to view the specific contents of the log file. As shown below:

By looking at the specific contents of the log file, you can see that there is a unique number before each operation, such as "# at 458". This number increases as the number of operands increases, and we call it an operation ID. The operation ID is followed by a time stamp. To recover data based on location or point in time, you need to rely on the operation ID or time stamp in the binary log file, respectively. For example, you can know from the binary log file that the user data of "pockmarked Wang" is inserted into the user_ info table when the operation ID is "458". Therefore, by executing the following command, you can only restore the data before the operation ID is "458", that is, do not restore the "pockmarked Wang" information. At this point, the recovered data is from the beginning of the binary log file to the specified location. As shown below:

In the above operation command, "--stop-position" specifies the location of the stop. if only the information of "pockmarked Wang" is restored and the information of "Zhao Liu" is skipped, you can use the "--start-position" option to specify the location where the data recovery starts. At this time, the recovered data starts from the specified location to the binary log file. As shown below:

Third, recovery based on point in time

The option used to recover data based on a point-in-time is "--stop-datetime", and the specified time is also the result of querying binary log files.

As shown in the following figure: perform the following operations to restore the data before "2:38:32", that is, do not restore the "pockmarked Wang" information.

Point-in-time recovery can also be used to specify the time to start data recovery using the "--start-datetime" option. The command format is the same as the location-based recovery format, which is no longer shown here.

Fourth, the train of thought of formulating enterprise backup strategy.

In the enterprise, the backup strategy is not the same, but according to the actual production environment and business needs of each enterprise to specify the appropriate backup strategy. Whether you choose full backup or incremental backup, you need to consider their advantages and disadvantages and whether they are suitable for the current environment. At the same time, in order to ensure the integrity of the recovery, it is recommended to turn on the binary log function. Binary log files also bring great flexibility to the recovery work, which can be recovered based on point-in-time or location. Considering the database performance, we can save the binary log files to other secure hard drives.

During the hot backup, the backup operation and the application service run at the same time, which consumes the system resources and leads to the decline of the database service performance, which requires us to choose the appropriate time, for example, when the application burden is very small.

It should be noted that everything will not be all right after the backup. * confirm whether the backup is available. Therefore, the recovery test after the backup is very necessary, and the backup time should be adjusted flexibly. Such as:

If the data is updated frequently, it should be backed up frequently.

The importance of data, backup when there are appropriate updates

Make backups during periods of low pressure on the database, such as a full backup once a week and an incremental backup every day.

For small and medium-sized companies, full backup is usually done once a day.

Large companies can make full backups once a week and incremental backups every day.

Try to implement master-slave replication architecture for enterprises to increase the high availability of data

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.