Data backup and restoration-16 (22 lectures in total)

Directory:
I. Data Backup
Ii. data restoration
Iii. database migration
Iv. Export and Import text files


I. Data Backup:
1, mysqldump
Working principle: it first detects the structure of the table to be backed up, generates a create statement in a text file, and then converts all the records in the table into an insert statement. Generate

The. SQL file actually contains the create statement to restore the table structure and insert statements to restore table data.
Usage: You can back up one database, multiple databases, and all databases ------
Mysqldump-u root-P '000000' dbname1 Table1 Table2...>/home/wuxy/table1. SQL
Mysqldump-u root-P '000000' -- databases dbname1 dbname2>/home/wuxy/dbbacpup. SQL
Mysqldump-u root-P '000000' -- all-databases>/home/wuxy/all. SQL

2. Copy MySQL data files directly (MySQL service must be stopped; otherwise, data inconsistency may occur, but MySQL service cannot be stopped ).

Does not apply to tables. For MyISAM storage engine tables, such backup and restoration are very convenient. However, it is best to restore the MySQL database of the same version, otherwise there may be

Case of different parts types.
3. mysqlhotcopy
If the MySQL server cannot be stopped during backup, you can use the mysqlhotcopy tool, which is faster than the mysqldump command.
Working principle: mysqlhotcopy is a Perl script, which is mainly used in Linux systems. The mysqlhotcopy tool uses lock tables, flush tables, and CP for fast execution.

Backup. The working principle is: first add a read operation lock to the database to be backed up, then use flush tables to write the database in the memory back to the database on the hard disk, and finally put

Copy the database files to the target directory
Usage:
Mysqlhotcopy [Options] dbname1 dbname2 backdir/
Multiple databases can be backed up at the same time.
Help view parameters


Ii. data restoration:
1,
If you use the mysqldump command to back up data in the database into a text file, the suffix of this file is usually SQL. To restore the database, you can use the MySQL command to restore it.

Backup Data.
Working principle: the backup file usually contains the create statement and insert statement. The MySQL command can execute the create statement and insert statement in the backup file. Use the create statement

Create databases and tables. Insert statement to insert backup data
Usage: mysql-u root-P '000000' [daname] <backup. SQL
2. If you are using a backup method for directly copying data, you can directly copy the backup data to the MySQL data directory. In this mode, you must ensure that two

The primary version number of the MySQL database is the same. Only when the primary version number of the MySQL database is the same can the file types of the two MySQL databases be the same. In addition, this method

For tables of the MyISAM type, the table corresponding to the InnoDB type is unavailable because the tablespace of the InnoDB table cannot be copied directly.
In Linux, the database directories are usually in the/var/lib/MySQL,/usr/local/MySQL/data, or/usr/local/MySQL/var/directories. The above positions are only numbers.

The most common location of the Data Warehouse directory depends on the location set during installation.


Iii. database migration:
Database migration refers to moving a database from one system to another.
It can be roughly divided into three categories:
1. Migration between MySQL databases of the same version
2. migrate to another version of MySQL database
3. migrate to other types of databases
For details:
1. Migration between MySQL databases of the same version is to move databases between the same MySQL databases of the primary version. This migration method is the easiest to implement.
For example, a new server or an operating system is installed.
Because the master version of the migrated MySQL database is the same, you can migrate the database by copying the database directory (you need to stop the MySQL service; otherwise, data inconsistency will occur)

However, this method can be used only when all database tables are of the MyISAM type. You can also use mysqldump for backup and then use the MySQL command to restore the backup.
2. Data migration between MySQL Databases of different versions is usually caused by MySQL upgrades. For example, after MySQL 5.0 is released, many defects of MySQL 4.0 have been improved.

To upgrade. MySQL Databases of higher versions are generally compatible with lower versions. Therefore, you can migrate data from a lower version of MySQL database to a later version of MySQL database. However, MySQL DATA of higher versions

It is difficult to migrate the database to a MySQL database of a lower version, because the MySQL database of a higher version may have some new features that are not available in the MySQL database of a lower version.

MyISAM tables can be copied directly or mysqlhotcopy tools can be used.
However, InnoDB tables cannot use these two methods. The most common method is to use the mysqldump command for backup, and then use the MySQL command to restore the backup file to the target

MySQL database.
Be careful when migrating the database. It is best to use the mysqldump command for backup to avoid data loss during migration.

3. Migration between different databases refers to migrating from other types of databases to MySQL databases or from MySQL databases to other types of databases. For example, a website

The Oracle database is used, because the operation cost is too high, and you are expected to use the MySQL database. Or a management system uses a MySQL database and wants to use

Oracle databases, such migration between different databases often occurs, but such migration does not have a common solution.
Databases other than MySQL also have backup tools similar to mysqldump, which can back up files in the MySQL database into SQL files or text. However

The database vendor did not fully design the database according to SQL standards, which caused the difference in SQL statements used by different databases. For example, Microsoft's SQL Server software uses a T-SQL language.

, The T-SQL contains non-standard SQL statements, which causes SQL Server and MySQL SQL statements to be incompatible.
Note: Some other tools may be used for migration between different databases, which can be Baidu.

Iv. Table export and import:
During routine database maintenance, you often need to export and import tables. Tables in the MySQL database can be exported as text files, XML files, or HTML files. Corresponding text

This file can also be imported into the MySQL database.
1. Use Select... into OUTFILE to export a text file
Usage: select [column name] from table [where statement] into OUTFILE 'destination File '[Option];
Option is as follows:
Fields terminated by 'string' # Set the delimiter between each field. The default Delimiter is a tab.
Fields enclosed by 'string' # Which character is used to enclose the value of the field. By default, no character is used.
Fields optionally enclosed by 'string' # What is the cause of character-type data (char, varchar, TXT fields)? By default, no character is used.
Fields escaped by 'string' # sets the escape character. The default Delimiter is a slash.
Lines starting by 'string' # Set the start character of each line. By default, there are no characters.
Lines terminated by 'string' # set the end character of each line, for example, '\ r \ n' (line feed and carriage return). The default value is' \ n' (Press ENTER)

Example:
Select * from test. Student into OUTFILE '/home/data/student.txt' fields terminated by '\.' fields optionally enclosed

'\ "'Lines starting by' \> 'Lines terminated by '\ r \ n ';
2. Use the mysqldump command to export text files.
The mysqldump command can back up data in the database, but the create statement and insert statement are saved in the backup file during Backup.
* In addition to 2.1, the mysqldump command can also export text files.
The usage is as follows:
Mysqldump-u root-P '000000'-T target directory dbname table [Option];
Option
-- Fields-terminated-by = string # Set the string as the field separator. By default, it is a tab (\ t)
-- Fileds-enclosed-by = character # Set the character used to include the value of the field.
-- Fields-optionally-enclosed-by = character # What is the cause of character-type data (char, varchar, TXT fields)? By default, no character is used.
-- Fields-escaped-by = character # Set the transfer character. The default value is a slash.
-- Lines = terminated-by = string # set the end character of each line, for example, '\ r \ n' (line feed and carriage return ), the default value is '\ n' (Press ENTER)
Example:
Mysqldump-u root-P '000000'-T/home/data/test student "-- fields-terminater-by = ", "-- fields-optionally-enclosed-by = '"'

* 2.2 mysqldump command export XML file
Example:
Mysqldump-u root-P '000000' -- XML test student>/home/data/student. xml
# Note that this statement cannot end with a semicolon

 
3. Use MySQL commands to export text files
MySQL commands can be used to log on to the MySQL server, restore backup files, and export text files.
The syntax format is as follows:
Mysql-u root-P '000000'-e "SELECT statement" dbname>/home/data/name.txt
Example:
Mysql-u root-P '000000'-e "select * from student" TETS>/home/data/student2.txt ### the same as the data queried by select
You can also use MySQL commands to export XML and HTML files.
Example:
Mysql-u root-P '000000' -- HTML-e "select * from department" test>/home/data/department.html
4. Use load data infile to import text files (this command is executed in the database)
MySQL, you can use the load data infile command to import text files to the MySQL database.
The basic syntax format is as follows:
Load data [local] infile filename into Table tablename [Option]
Example:
Load data infile '/home/data/student2.txt' into Table student fields terminated by ', 'optionally enclosed '"';
Note:
If fields in a text file are separated by commas, you must specify the delimiter of the text file when importing data.
Similarly, if the plain data (CAHR, varchar, and txt) is enclosed in double quotation marks, you must specify

5. Use the mysqlimport command to import text files
MySQL can use the mysqlimport command to import text files to the MySQL database.
The syntax format is as follows:
Mysqlimport-u root-P '000000' [-- local] dbname file [Option]
Option option is the same as mysqldump Option
Example:
Mysqlimport-u root-P '000000' test '/home/data/student.txt' "-- fields-terminated-by-," 00fields-optionally-enclosed-by -''"

FAQs:
1. How do I select the backup database method?
A: The methods for backing up tables vary according to the storage engine type of the database. For tables of the MyISAM type, you can directly copy the MySQL DATA folder or use the mysqlhotcopy command for backup. To copy the MySQL DATA folder, stop the MySQL service. Otherwise, an exception may occur. The mysqlhotcopy command does not need to stop the MySQL service. The mysqldump command is the safest backup method. It is applicable to both MyISAM tables and InnoDB tables.
2. How to upgrade the MySQL database?
A:
(1) Use the mysqldump command to back up data in the MySQL database. This is done to avoid data loss in the MySQL database due to misoperation.
(2) To stop the MySQL service, you can directly terminate the MySQL service process, but it is best to stop the MySQL service in a safe way to avoid data loss in the buffer.
(3) uninstall the old version of MySQL database. Generally, when you uninstall the MySQL database software, the system will continue to retain the data files in the MySQL database.
(4) install and configure the new MySQL database.
(5) Start the MySQL service and log on to the MySQL database to check whether the data is complete. If the data is incomplete, use the previously backed up data for restoration.

Summary: This section describes the backup database, restoration database, database migration, export table, and import table content. Backing up a database and restoring a database is the focus of this section. In practical applications, the mysqldump command is usually used to back up the database and the MySQL command is used to restore the database. Database migration, export tables, and import tables are the difficulties in this section. database migration must consider database compatibility issues, preferably between MySQL databases of the same version. You can export tables and import tables in many ways.

Append content:

I. MyISAM engine backup and InnoDB Engine backup

Because the MyISAM engine is a table-Level Lock, it is necessary to prevent data writing during the backup from causing inconsistency. Therefore, use -- lock-all-tables to add a read lock during Backup.

Example:

Mysqldump-a-f-B -- lock-all-tables | gzip/data/backiup/$ (date must have f=.tar.gz

Because the InnoDB engine is a row-Level Lock, You can do not lock the database during backup. You can add the option -- Single-transaction to back up (option automatically turns off -- lock-tables, so it does not lock the Table). This parameter is only valid for InnoDB and can obtain consistent backup.

Example:

Mysqldump-a-f-B -- Single-transaction | gzip/data/backup/$ (date cannot exceed f0000.tar.gz

Note:

1, -- Single-transaction this parameter applies only to InnoDB engine -- master-Data = 2

2, -- Single-transaction and -- lock-all-tables are mutually exclusive and cannot be used at the same time

Data backup and restoration-16 (22 lectures in total)