Linux commands: MySQL series 12--mysql Backup and restore (important chapters)

First, data backup

Backup type: Hot, warm, and cold backup

Hot backup: Read, write not affected;

Warm backup: Only read operation can be performed;

Cold backup: Offline backup, read and write operations are aborted;

Backup method: Physical and logical Backup:

Physical Backup: Copy data files, fast, save all the data structure of the database, if the data is strongly correlated with the database,

Lead to the physical backup portability ability is not strong;

Logical backup: Export data to a text file with slow speed and loss of floating point accuracy; easy to use text processing tools

directly to its treatment, the ability to transplant;

Backup policy: Full, incremental, and differential backups:

Full backup: Back up all data;

Incremental backup: Backs up only data that has changed since the last full backup or incremental backup;

Differential backup: Backs up only data that has changed since the last full backup;

what the database needs to back up: data, configuration files, binary logs, transaction logs

MySQL Backup tool:

Mysqldump: Logical Backup tool, MyISAM (Win Bei), InnoDB (hot Backup)

Mysqlhotcopy: Physical Backup tool, warm backup

File System Tools:

CP: Cold Standby tool

LV: Snapshot feature of logical volume, almost hot standby:

mysql> flush TABLES; Refresh Table

Mysql>lock Tables Lock Table

Create a snapshot: Release the lock and then copy the data

Third-party backup tools:

Ibbackup: Business Tools

Xtrabackup: Open Source Tools

650) this.width=650; "src=" https://s4.51cto.com/wyfs02/M02/95/E5/wKiom1kaxr6hh_uAAAN_TuHoL3g202.jpg "title=" 2.jpg "Width=" "height=" 427 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" WIDTH:700PX;HEIGHT:427PX; "alt=" Wkiom1kaxr6hh_ Uaaan_tuhol3g202.jpg "/>

　　a , using the mysqldump command to back up

The mysqldump command backs up the data in the database into a text file. The structure of the table and the data in the table are stored in the generated text file.

The mysqldump command works very simply. It first identifies the structure of the table that needs to be backed up, and then generates a CREATE statement in the text file. Then, convert all the records in the table into an INSERT statement. With these statements, you can create tables and insert data.

　　1, back up a database, before the backup needs to refresh the table, and lock the table, otherwise the table modification caused the inconsistency of the backup

Mysqldump Basic Syntax:

　　Mysqldump-u username-p dbname table1 table2 ...-> backupname.sql

which

• The dbname parameter represents the name of the database;

• The table1 and table2 parameters represent the names of the tables that need to be backed up, and the entire database is backed up as empty;

• Backupname.sql parameter table design the name of the backup file with an absolute path before the file name. Typically the database is partitioned into a file with a suffix called SQL;

　　Example: Using the root user to back up the test database

[email protected] ~]# mysqldump-uroot-p Test >/root/stu.sql #备份test数据库到/root directory

Enter Password:

[Email protected] ~]# Ls/root

Anaconda-ks.cfg Music public Templates stu.sql

The beginning of the file will record the MySQL version, the host name of the backup, and the database name.

The "--" in the file begins with the comments of the SQL language, which begins with "/*!40101", and is a MySQL-related comment. 40101 is the version number of the MySQL database, if the MySQL version is higher than 1.11, then the content between/*!40101 and/* is treated as a SQL command, if compared to 4.1.1 must not will be considered as comments.

　　2. Backing up multiple databases

Grammar:

Mysqldump-u username-p--databases dbname2 dbname2 > Backup.sql

The--databases option is added, followed by multiple databases

Mysqldump-u root-p--databases Test mysql > D:\backup.sql

　　3. Back up all databases

The syntax for backing up all databases with the mysqldump command is as follows:

Mysqldump-u username-p-all-databases > Backupname.sql

Example:

Mysqldump-u-root-p-all-databases > D:\all.sql

two　　 , copy the entire database directory directly

MySQL has a very simple backup method, which is to copy the database files in MySQL directly. This is the simplest and fastest method.

However, before you do this, you will need to stop the server before you can guarantee that the data for the database will not change during replication. If there is data written during the copying of the database, the data will be inconsistent. This situation is possible in the development environment, but it is difficult to allow backup servers in a production environment.

Note: This approach does not apply to tables of the InnoDB storage engine, but is convenient for MyISAM storage engine tables. At the same time, the MySQL version is best when restoring.

　　three , use the Mysqlhotcopy tool to quickly back up

A look at the name will know it is a hot backup. Therefore, mysqlhotcopy support does not stop the MySQL server backup. In addition, Mysqlhotcopy is faster than mysqldump backup. Mysqlhotcopy is a Perl script that is primarily used under Linux systems. It uses the lock TABLES, FLUSH TABLES, and CP for quick backups.

Principle: First add a read lock to the database that needs to be backed up, then use flush tables to write the data in memory back to the database on the hard disk, and finally, copy the database files that need to be backed up to the target directory.

The command format is as follows:

[Email protected] ~]#

• dbname: Database name;

• Backupdir: under which folder to back up;

Common options:

• --help: View Mysqlhotcopy help;

• --allowold: If the same backup file exists under the backup directory, the old backup file is added _old;

• --keepold: If the backup directory has the same backup file, do not delete the old backup file, but the old file name;

• --flushlog: After this generational, the database updates are recorded in the log;

• --noindices: Only back up data files, not backup index files;

• --user= user name: Used to specify the user name, you can use-u instead;

• --password= Password: Used to specify the password, you can use-P instead. When using-p, there is no space between the password and-p;

• --port= port number: Used to specify the access port, you can use-p instead;

• --socket=socket file: Used to specify the socket file, you can use-s instead;

Mysqlhotcopy is not a MySQL-band, it needs to install Perl's database interface package; for: http://dev.mysql.com/downloads/dbi.html

Currently, the tool is only able to back up tables of type MyISAM.

second, data restoration

　　1. The syntax for restoring a database backed up using the mysqldump command is as follows:

　　Mysql-u root-p [dbname] < Backup.sql

Example:

Mysql-u Root-p < C:\backup.sql

2. Restore the backup of the direct copy directory

　　When restoring in this way, you must ensure that the version number of the two MySQL databases is the same. Tables of the MyISAM type are valid, and table spaces for InnoDB tables cannot be copied directly for tables that have INNODB types that are not available.

This article is from the "Learn Linux history" blog, please be sure to keep this source http://woyaoxuelinux.blog.51cto.com/5663865/1929853

Linux commands: MySQL series 12--mysql Backup and restore (important chapters)