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

This article mainly introduces the data backup and restore method of mysql, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, in practical sense, accumulated years of practical experience can be shared with you.

1. Data backup

1.mysqldump command backup

Mysqldump-u user-h host-ppassword dbname [tbname [tbname...]] > filename.sql

It is worth noting that the data tables that tbname needs to back up for dbnames

1. Single library backup

Mysqldump-u user-h host-ppassword dbname > filename.sql

two。 Single database specification table

Mysqldump-u user-h host-ppassword dbname a_table b_table > filename.sql

3. Multiple libraries

Mysqldump-u user-h host-ppassword-- databases dbname_1 dbaname_2 > filename.sql

4. Back up all databases

Mysqldump-u user-h host-ppassword-- all-databases > filename.sql

5. Guide-only structure

Mysqldump-u user-h host-ppassword-t dbname > filename.sql

Detailed explanation of other parameters of 6.msqldump

-- apply-slave-statements

Add 'STOP SLAVE', before' CHANGE MASTER' and add 'START SLAVE' at the end of the export.

Mysqldump-uroot-p-all-databases-- apply-slave-statements

-- compatible

The exported data will be compatible with other databases or older versions of MySQL. Values can be ansi, mysql323, mysql40, postgresql, Oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, etc.

To use several values, separate them with commas. It is not guaranteed to be fully compatible, but as compatible as possible.

Mysqldump-uroot-p-all-databases-- compatible=ansi

-- compact

Export less output (for debugging). Remove comments and head-tail structures. You can use the option:-- skip-add-drop-table-- skip-add-locks-- skip-comments-- skip-disable-keys

Mysqldump-uroot-p-all-databases-- compact

-complete-insert,-c

Use the full insert statement (including column names). Doing so can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter and cause the insert to fail.

Mysqldump-uroot-p-all-databases-- complete-insert

-compress,-C

Enable compression to transfer all information between the client and the cloud server

Mysqldump-uroot-p-all-databases-- compress

-- databases,-B

Export several databases. All name parameters after the parameters are treated as database names.

Mysqldump-uroot-p-databases test mysql

-- debug

Output debug information for debugging. The default value is: dazzldump.trace

Mysqldump-uroot-p-all-databases-- debug

Mysqldump-uroot-p-- all-databases-- debug= "dju tlu o mpmax debug.trace"

-- debug-check

Check the memory and open file instructions and exit.

Mysqldump-uroot-p-all-databases-- debug-check

-- debug-info

Output debugging information and exit

Mysqldump-uroot-p-all-databases-- debug-info

-- delete-master-logs

Delete the log after master backup. This parameter will automatically activate-- master-data.

Mysqldump-uroot-p-all-databases-- delete-master-logs

-- disable-keys

-- dump-slave

This option causes the primary binlog location and file name to be appended to the file where the data is exported. When set to 1, the CHANGE MASTER command is output to the data file; when set to 2, the description information is added before the command. This option will open-- lock-all-tables, unless-- single-transaction is specified. This option automatically turns off the-- lock-tables option. The default value is 0.

Mysqldump-uroot-p-- all-databases-- dump-slave=1

Mysqldump-uroot-p-- all-databases-- dump-slave=2

-events,-E

Export events.

Mysqldump-uroot-p-all-databases-- events

-extended-insert,-e

Use INSERT syntax with multiple VALUES columns. This makes the export file smaller and speeds up the import. The default is on, using the-- skip-extended-insert cancel option.

Mysqldump-uroot-p-all-databases

Mysqldump-uroot-p-- all-databases--skip-extended-insert (cancel option)

-- flush-logs

Refresh the log before starting the export.

Note: if you export more than one database at a time (using the option-- databases or-- all-databases), the logs will be refreshed one by one. Except for using-- lock-all-tables or-- master-data. In this case, the log will be refreshed once, and the corresponding table will be locked at the same time. Therefore, if you plan to export and refresh logs at the same time, you should use-- lock-all-tables or-- master-data and-- flush-logs.

Mysqldump-uroot-p-all-databases-- flush-logs

-- flush-privileges

After exporting the mysql database, issue a FLUSH PRIVILEGES statement. For proper recovery, this option should be used to export mysql databases and rely on mysql database data at any time.

Mysqldump-uroot-p-all-databases-- flush-privileges

-- force

Ignore SQL errors during the export process.

Mysqldump-uroot-p-all-databases-- force

-- ignore-table

The specified table is not exported. Specifies that when multiple tables are ignored, they need to be repeated multiple times, one table at a time. Each table must specify both the database and the table name. For example:-- ignore-table=database.table1-- ignore-table=database.table2.

Mysqldump-uroot-p-host=localhost-all-databases-ignore-table=mysql.user

-- include-master-host-port

The 'CHANGE MASTER TO..' produced by-- dump-slave Add 'MASTER_HOST=,MASTER_PORT='' to the statement

Mysqldump-uroot-p-host=localhost-all-databases-include-master-host-port

-lock-all-tables,-x

The submit request locks all tables in all databases to ensure data consistency. This is a global read lock and automatically turns off the-- single-transaction and-- lock-tables options.

Mysqldump-uroot-p-host=localhost-all-databases-lock-all-tables

Lock-tables,-l

Lock all tables before you start exporting. Lock the table with READ LOCAL to allow MyISAM table inserts in parallel. Tables that support transactions, such as InnoDB and BDB,--single-transaction, are a better choice because it does not need to lock the table at all.

Note that when exporting multiple databases,-- lock-tables locks tables for each database. Therefore, this option does not guarantee the logical consistency of the tables in the exported file between databases. The export status of different database tables can be completely different.

Mysqldump-uroot-p-host=localhost-all-databases-lock-tables

-- log-error

Attach warning and error messages to the given file

Mysqldump-uroot-p-host=localhost-all-databases-log-error=/tmp/mysqldump_error_log.err

-- master-data

This option appends the location and file name of the binlog to the output file.

If 1, the CHANGE MASTER command is output; if 2, comment information is added before the output CHANGE MASTER command.

This option turns on the-- lock-all-tables option unless-- single-transaction is also specified (in which case the global read lock is given a short time at the beginning of the export)

For more information, see the-- single-transaction option below). This option automatically turns off the-- lock-tables option.

Mysqldump-uroot-p-host=localhost-all-databases-master-data=1

Mysqldump-uroot-p-host=localhost-all-databases-master-data=2

-- max_allowed_packet

The maximum packet length sent and accepted by the server.

Mysqldump-uroot-p-host=localhost-all-databases-max_allowed_packet=10240

-- net_buffer_length

Cache size for TCP/IP and socket connections.

Mysqldump-uroot-p-host=localhost-all-databases-net_buffer_length=1024

-no-create-db,-n

Only export data without adding CREATE DATABASE statements.

Mysqldump-uroot-p-host=localhost-all-databases-no-create-db

No-create-info,-t

Only export data without adding CREATE TABLE statements.

Mysqldump-uroot-p-host=localhost-all-databases-no-create-info

No-data,-d

No data is exported, only database table structure is exported.

Mysqldump-uroot-p-host=localhost-all-databases-no-data

-no-set-names,-N

Equivalent to-- skip-set-charset

Mysqldump-uroot-p-host=localhost-all-databases-no-set-names

-- opt

Equivalent to-- add-drop-table,-- add-locks,-- create-options,-- quick,-- extended-insert,-- lock-tables,-- set-charset,-- disable-keys this option is enabled by default and can be disabled with-- skip-opt.

Mysqldump-uroot-p-host=localhost-all-databases-opt

-- order-by-primary

If there is a primary key, or the first unique key, sort the records for each table. Valid when exporting MyISAM tables to InnoDB tables, but it can take a long time to export.

Mysqldump-uroot-p-host=localhost-all-databases-order-by-primary

-password,-p

Connection database password

-port, P

Connection database port number

-- protocol

The connection protocols used, including: tcp, socket, pipe, memory.

Mysqldump-uroot-p-host=localhost-all-databases-protocol=tcp

-quick,-Q

The query is not buffered and exported directly to standard output. The default is on, and use-- skip-quick to cancel this option.

Mysqldump-uroot-p-host=localhost-- all-databases

Mysqldump-uroot-p-host=localhost-all-databases-skip-quick

-- quote-names,-Q

Use (`) to cause table and column names. The default is on, and use-- skip-quote-names to cancel this option.

Mysqldump-uroot-p-host=localhost-- all-databases

Mysqldump-uroot-p-host=localhost-all-databases-skip-quote-names

-- replace

Use REPLACE INTO instead of INSERT INTO.

Mysqldump-uroot-p-host=localhost-all-databases-replace

-result-file, r

Output directly to the specified file. This option should be used on systems that use carriage return line feed (\ r\ n) line feed (for example: DOS,Windows). This option ensures that only one line is used.

Mysqldump-uroot-p-host=localhost-all-databases-result-file=/tmp/mysqldump_result_file.txt

-routines,-R

Export stored procedures and custom functions.

Mysqldump-uroot-p-host=localhost-all-databases-routines

-- set-charset

Add 'SET NAMES default_character_set' to the output file. The default is on, using the-- skip-set-charset off option.

Mysqldump-uroot-p-host=localhost-- all-databases

Mysqldump-uroot-p-host=localhost-all-databases-skip-set-charset

-- single-transaction

This option submits a BEGIN SQL statement before exporting the data. BEGIN does not block any applications and ensures the consistent state of the database at the time of export.

It applies only to multi-version storage engines, InnoDB only. This option and the-- lock-tables option are mutually exclusive because LOCK TABLES will implicitly commit any pending transaction. If you want to export a large table, use the-- quick option together.

Mysqldump-uroot-p-host=localhost-all-databases-single-transaction

-- dump-date

Add the export time to the output file. The default is on, using the-- skip-dump-date off option.

Mysqldump-uroot-p-host=localhost-- all-databases

Mysqldump-uroot-p-host=localhost-all-databases-skip-dump-date

-- skip-opt

Disable the-opt option.

Mysqldump-uroot-p-host=localhost-all-databases-skip-opt

-- socket,-S

Specify the location of the socket file that connects to mysql, the default path / tmp/mysql.sock

Mysqldump-uroot-p-host=localhost-all-databases-socket=/tmp/mysqld.sock

-- tab,-T

Creates a tab-split text file for each table at the given path. Note: only for mysqldump and mysqld servers running on the same machine.

Mysqldump-uroot-p-host=localhost test test-tab= "/ home/mysql"

-- tables

Override the-- databases (- B) parameter to specify the name of the table to be exported.

Mysqldump-uroot-p-host=localhost-databases test-tables test

-- triggers

Export triggers. This option is enabled by default and disabled with-- skip-triggers.

Mysqldump-uroot-p-host=localhost-all-databases-triggers

-- tz-utc

Set the time zone TIME_ZONE='+00:00' at the top of the export to ensure the correctness of the TIMESTAMP data exported in different time zones or when the data is moved to other time zones.

Mysqldump-uroot-p-host=localhost-all-databases-tz-utc

-version,-V

Output mysqldump version information and exit

-where,-w

Only records selected by a given WHERE condition are dumped. Note that if the condition contains special spaces or characters for the command interpreter, be sure to reference the condition.

Mysqldump-uroot-p-host=localhost-all-databases-where= "user='root'"

-- xml,-X

Export XML format.

Mysqldump-uroot-p-host=localhost-all-databases-xml

-- plugin_dir

The directory of the client plug-in, which is compatible with different plug-in versions.

Mysqldump-uroot-p-host=localhost-all-databases-plugin_dir= "/ usr/local/lib/plugin"

-- default_auth

The client plug-in uses permissions by default.

Mysqldump-uroot-p-host=localhost-all-databases-default-auth= "/ usr/local/lib/plugin/"

two。 Copy the entire database directory

1. Can only be copied between the same version

You can lock tables the table before backup, then flush tables the table and then physically copy it.

You can also close mysql and execute

two。 Restore the database

1.mysql command import

Mysql-u user-p [dbname]

< filename.sql 2.使用source 命令导入 use 命令切换到指定数据库 source 路径 3.如果直接复制的整个数据库目录 可以 直接复制到data 即可 3.直接迁移 mysqldump -hhost1 -uroot -ppassword1 dbname | mysql -hhost2 -uroot -ppassword2 4.表的导出 1.select columnlist from table where condition into outfile 'filename' [options] --options 选项： fields terminated by 'value' fields [optionally] enclosed by 'value' fields escaped by 'value' lines starting by 'value' lines terminated by 'value' fields terminated by 'value' 设置字段之间分隔符 fields [optionally] enclosed by 'value' 设置字段之间包围字符 使用optionally 则只有char 和varchar 有效 fields escaped by 'value' 这只如何写入和读取特殊字符 默认为 '\' lines starting by 'value' 每行开始字符 lines terminated by 'value' 每行结尾字符 默认为'\n' 2.mysqldump -T path -u root -p dbname [tables][options] --options 选项： --fields-terminated-by=value --fields-optionally-enclosed-by=value --fields-escaped-by=value --lines-starting-by=value --lines-terminated-by=value options含义同上 3.mysql -u root -p [--vartical(多行)] --execute="select 语句" dbname >

Filename

You can also specify the file type to be exported through-- xml-- html

5. Import of tabl

1. Use load data infile

Load data infile 'filename' into table tablename [OPTIONS] [IGNORE number LINES]

-- options option:

Fields terminated by 'value'

Fields [optionally] enclosed by 'value'

Fields escaped by 'value'

Lines starting by 'value'

Lines terminated by 'value'

IGNORE number LINES ignores the number (lines) line at the beginning of the file

2.mysqlimport Import

Mysqlimport-u root-p dbname filename [options]

-- options option:

-- fields-terminated-by=value

-- fields-optionally-enclosed-by=value

-- fields-escaped-by=value

-- lines-starting-by=value

-- lines-terminated-by=value

-- ignore-lines=n

Read the above introduction of mysql data backup and restore methods, hoping to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.