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

Backup commands commonly used in MySQL

Common methods of mysqldump backup:

1. Export a database

Mysqldump-u user name-p password database name > exported file name

For example

# mysqldump-hhostname-uroot-ppwd dataname > dataname.sql

two。 Back up all databases on the server

For example

# mysqldump-- all-databases > allbackupfile.sql

3. Back up a table

Mysqldump-u user name-p password database name table name > exported file name

For example:

# mysqldump-hhostname-uroot-ppwd dataname specific_tabname1 specific_tabname2 > dataname_specific_tabname.sql

4. Export a database structure

For example

# mysqldump-hhostname-uroot-ppwd-add-drop-table-- no-data dataname > dataname_nodata.sql

-- no-data: no data

-- add-drop-table adds a drop table command before each create statement

5. Compress backup of MySQL database directly

For example

# mysqldump-hhostname-uusername-ppassword databasename | gzip > backupfile.sql.gz

6. Backup multiple MySQL databases at the same time

For example

Mysqldump-hhostname-uusername-ppwd-- databases dbname1 dbname2 dbname3 > multibackupfile.sql

7. For storage engines that support transaction level, the option "--single-transaction" can be used to ensure data consistency.

For example

# mysqldump-hlocalhost-uroot-p backup-- single-transaction > / tmp/backup3.sql

8. Export plain text files in a specific format (generate two .sql and txt files)

For example:

Mysqldump-root-p-T / tmp dbname tablename-fields-enclosed-by=\ "- fields-terminated-by=

-- fields-enclosed-by: a symbol that wraps the contents of a field

-- fields-terminated-by: the delimiter of two fields

If you dump more than one table at a time, each table will have two files, sql and txt; this specific format is more suitable for backup

In addition to mysqldump, you can also use the following statements to generate a specific format:

> select... Into outfile from...

For example:

Mysql > select * into outfile'/ tmp/t2.txt1'

-> fields terminated by','

-> optionally enclosed by'"'

-> lines terminated by'\ n'

-> from T2 limit 10

Optionally enclosed by: the contents of the field can be "wrapped". If you do not use "OPTIONALLY", all type data including numeric types will be "wrapped". After using "OPTIONALLY", data of numeric types will not be specified as "wrapper".

Lines terminated by: what characters need to be added at the end of each record

Fields terminated by: sets the delimiter between every two fields

Logical backup and recovery method of mysqldump

1. If it is an insert statement, you can run the script directly.

Method one

# mysql-hlocalhost-uroot-p backup

Source / tmp/t2.sql

two。 If the backup is a plain data text file separated by a special delimiter

Method one

Restore / tmp/t2.txt to library backup

# mysqlimport-uroot-p backup-- fields-enclosed-by=\ "--fields-terminated-by=, / tmp/t2.txt

Method two

Mysql > load data infile'/ tmp/t2.txt' into table T2 fields terminated by'"'enclosed by','

Backup method of mysqlhotcopy

Mysqlhotcopy is a user program written in perl, and its main implementation principle is actually through the first LOCK

Hold the meter, and then perform the FLUSH TABLES action. The normally closed meter is closed normally, and all the data of the fsync are fsync.

Then, by executing the OS-level replication (cp, etc.) command, all physical files of the table or database that need to be backed up are

Copy to the specified backup set location.

Mysqlhotcopy db_name [. / table_regex/] [new_db_name | directory]

For example:

Copy database

Mysqlhotcopy backup backup4

Back up the entire library

Mysqlhotcopy backup / tmp/

Part of the table of the reserve library

Mysqlhotcopy-allowold backup./t*/ / tmp/

Recovery method of mysqlhotcopy

Mysqlhotcopy backs up the entire database directory, which can be copied directly to the datadir directory specified by mysqld.

At the same time, we should pay attention to the problem of permissions (change the owner of the db_name directory to the mysqld running user). This command can only restore the database to the end.

Status at the time of a backup.

Backup strategies for different scenarios of mysql

Backup strategy 1:

Copy database files directly (not recommended), also known as cold physical backup, that is, to stop the mysql database, and then copy the physical files

The speed is very fast, and the recovery is also very convenient and fast. You can directly copy the backup files as they are, but no application allows you to stop the library for backup.

There is no problem of data consistency here. If the library is stopped, of course, consistency can be guaranteed. If you don't stop the library, you have to lock all

To ensure the consistency of the data

FLUSH TABLES WITH READ LOCK

This sql flushes all the data in memory to disk and locks the data table to ensure that no new data is written during the copy process.

Recovery: the data recovery of this scheme is also very simple, which can be copied directly back to the original database directory.

Backup strategy 2:

Use mysqlhotcopy to back up the database (full backup, suitable for small database backup), which requires very strict data consistency

The system is an invalid backup because there is no way to meet the consistent integrity constraints of the data. It is the best way to back up a database or a single table

Fast way, but it can only run on the machine where the database files (including data table definition files, data files, index files) are located, and

And mysqlhotcopy can only be used to back up MyISAM tables.

Recovery: the data recovery of this scheme is also very simple, which can be copied directly back to the original database directory.

Backup strategy 3:

Use mysqldump to back up the database (full + incremental backup, suitable for medium database backup), mysqldump uses SQL

Level backup mechanism that exports datasheets into SQL script files, relative to each other when upgrading between different MySQL versions

It is more appropriate, and this is the most commonly used backup method. Mysqldump is slower than a direct copy. Very strict with the consistency of data.

It is also difficult to guarantee, only through incremental backup (applicable to any engine)

Recovery: the recovery of this scenario is divided into two steps:

1. Restore the full backup file first, using a command similar to the following:

Mysql-hlocalhost-uroot-p backup < / tmp/t2.sql

two。 Restore incremental backup files in logbin chronological order

# ls-tr1 HOSTNAME-bin* | xargs mysqlbinlog | mysql-uUser-pUserPWD

Backup strategy 4:

Use the master-slave replication mechanism (replication) (to achieve real-time database backup, suitable for medium and large databases), in order not to affect online business

To achieve online backup and incremental backup, the best way is to use the master-slave replication mechanism (replication) on the slave machine.

Make a backup on the.

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.