Shulou(Shulou.com)06/01 Report--

The following mainly brings you Mysql database backup and recovery analysis. I hope that Mysql database backup and recovery analysis can bring you practical use, which is also the main purpose of my editing this article. All right, don't talk too much nonsense, let's just read the following.

The importance of database backup, in the production environment, data security is very important, any loss of data may have serious consequences, database backup, can restore important data in time. The cause of data loss: program error, human error, computer failure, disk failure, disaster (fire, earthquake) theft, from a physical and logical point of view, backup can be divided into physical backup and logical backup. Logical backup: refers to the backup of database logical components (such as tables, databases). Physical backup: backup of physical files (data files, log files) of the database operating system. Physical backup can be divided into cold backup and hot backup. Cold backup: the backup operation carried out when the database is closed can better ensure the integrity of the database. Hot backup: backup when the database is running. The backup method depends on the log files of the database. Backup can be divided into full backup, differential backup and incremental backup. Full backup: it takes a long time to back up all objects in the entire database. Differential backup: backup after full backup and incremental backup. (only full backups are identified, after backups) incremental backups: backups are made only for the content modified after the backup. Only the one added after the full backup is backed up. ) one. Use tar to package folders for backup.

1. The database file is very large and can be compressed in XZ format with high compression ratio. Install XZ compression tool first.

Yum install xz-y

two。 Package the database folder / usr/local/mysql/data/

Tar Jcvf / opt/mysql-$ (date +% F) .tar.xz / usr/local/mysql/data

3. If the database file is damaged or lost, you can extract the backup file and do the data recovery work.

Tar Jxvf / opt/mysql-2018-8-30.tar.xz / usr/local/mysql/data II. Backup using the mysqldump tool.

1. Make a full backup of a single library.

Mysqldump-u username-p [password]-- database [database] > / backup path / backup file name

Example:

Mysqldump-uroot-pabc123-- database school > / opt/school.sql

two。 Make a full backup of some tables.

Mysqldump-u user name-p [password] [database name] [table name] > / backup path / backup file name

3. Make a full backup of multiple libraries.

Mysqldump-u user name-p [password]-- databases [library name 1] [library name 2] > / backup path / backup file name

4. Make a full backup of all libraries.

Mysqldump-u user name-p [password]-- all-databases > / backup path / backup file name

5. Only the table structure of a table is backed up.

Mysqldump-u user name-p [password]-d [library name] [table name] > / backup path / backup file name 3. Restore database

1. After logging in to the database, enter the database and use the source command to restore the whole database directly.

Source backup script path

Example:

Source / opt/school.sql

Note: create a new database and execute the source command in the new database.

two。 Restore with the mysql command. (without logging in to the database)

Mysql-u user name-p [password] library name

/ opt/school.sql

Create a new library and view information about the new library.

Mysql-uroot-pabc123-e'create database school;show databases;'

Restore

Mysql-uroot-pabc123 school

School.sql

There is no need to create a new library in advance

Restore

Mysql-uroot-pabc123 school < school.sql

Third, there is no need to log in to the database to view and create databases and tables

Mysql-u user name-p [password]-e'[command]; [command];'

School;show databases;'

For the above analysis of Mysql database backup and recovery, we do not think it is very helpful. If you need to know more, please continue to follow our industry information. I'm sure you'll like it.

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.