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

This article mainly explains the way of automatic backup of MySQL. The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn the way of automatic backup of MySQL.

First, logical backup: use the mysqldump tool that comes with mysql for backup. Backup as a sql file.

Advantages: the biggest advantage is the ability to work automatically with the running mysql, ensuring that the backup is the point at that time, which automatically locks the table of the corresponding operation and does not allow other users to modify it (only access it). Modification operations may be blocked. Sql files are universal and easy to transplant.

Disadvantages: the speed of backup is slow. If it is a large amount of data. It takes a lot of time. If the database server is in a state of service to the user, it means locking the table (usually read lock, only read but not write data) during this long operation. Then the service will affect it.

Note: the so-called automatic cooperation with the mysql server actually means adding parameters to control the mysql server, for example, locking all tables can only be read, not write.

-- lock-all-tables

Physical backup: directly copy the data directory of mysql.

The disadvantage is that you cannot operate a running mysql server (in the process of copying, users access updated data through the application, so that the data at that time cannot be backed up) may not be portable to other machines. Direct copy applies only to tables of type myisam. This type of watch is independent of the machine. But the reality is that you can't use all myisam-typed tables when you design a database. Neither can you: because the myisam type table is machine-independent and portable, you choose this table, which is not the reason to choose it.

More often, you will choose table types based on business characteristics (for example, you must use innodb if you need to support transaction mechanisms), query speed, and service performance.

You must make sure that the table is not in use.

If the server changes a table while you are copying it, the copy is meaningless.

If the database table is modified during the file system backup, the backup table file subject is inconsistent, and it will be meaningless for the later recovery table.

The best way to ensure the integrity of your copy is to shut down the server, copy the files, and then restart the server.

Or, lock the corresponding table (causing access problems for front-end users).

Explain why it is not portable to copy files directly.

Mysqldump produces text files that can be ported to other machines, even machines with different hardware structures. Directly copied files cannot be migrated to other machines unless the table you want to copy uses the MyISAM storage format. ISAM tables can only be copied between machines with the same hardware structure. For example, copying files from S PARC's Solaris machine to Intel's Solaris machine (or vice versa) doesn't work. The MyISAM table storage format introduced by MySQL3.23 solves this problem because it is machine independent. Therefore, a direct copy of a file can be migrated to a machine with a different hardware structure if both of the following conditions are met: that is, another machine must also run a version above MySQL3.23, and the file must be represented as an MyISAM table, not an ISAM table.

Third, dual hot backup: there is no incremental backup mechanism for mysql database. Backup is a big problem when the amount of data is too large. Fortunately, the mysql database provides a master-slave backup mechanism (that is, dual hot backup).

Advantages: suitable for large amount of data. I get it now. Big Internet companies use hot-engine backup for mysql data backup. Set up multiple database servers for master-slave replication.

The problem often encountered in master-slave replication is how to ensure that the data is not blocked and not delayed. This problem can be tolerated and there are some solutions that can be improved. After all, there are gains and losses. This is already a very easy and labor-saving way.

=

The tradeoff of what backup strategy I should use at present:

Physical backup, fast recovery, of course, it is best to store it on a machine. Should I use physical backup or logical backup now?

Considering that the platform will be migrated in the future. To ensure versatility. I can tolerate an one-minute gap in the speed of recovery. So I prefer to use logical backups for cross-platform purposes. Stores the sql file form.

Dual heat engine backup mode, there is no more hardware at present. The technical staff is limited, and it is troublesome to need manpower to maintain it. So it's excluded.

Thank you for your reading, the above is the content of "the way of automatic backup of MySQL", after the study of this article, I believe you have a deeper understanding of the way of automatic backup of MySQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.