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

A complete set of Mysqldump parameters

1. Mysqldump backup:

Mysqldump-u username-p password-h host database-w "sql condition"-- lock-all-tables > path

2. Mysqldump restore:

Mysqldump-u username-p password-h host database

< 路径mysql -uroot -p123456 app < /root/app_bak.sql 3、还原数据库有两种方法： ①/usr/local/mysql/bin/mysql -uroot -p123456 db_name < db_name.sql②source /root/db_name.sql; 指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件 4、跨主机备份： 使用下面的命令可以将host1上的sourceDb复制到host2的targetDb，前提是host2主机上已经创建targetDb数据库： mysqldump -uroot -p123456 --host=host1 --opt sourceDb| mysql --host=host2 -C targetDb -C指示主机间的数据传输使用数据压缩 5、只备份表结构： mysqldump -uroot -p123456 --no-data --databases mydatabase1 mydatabase2 mydatabase3 >

Test.dump

6. Implement cron command to realize scheduled backup:

For example, if you need to back up all databases on a host at 2:15 every morning and compress the dump file into gz format, you can add it to the / etc/crontab configuration file.

Enter the following line of code:

[root@backup /] # crontab-e152 * / usr/local/mysql/bin/mysqldump-uroot-p123456-- all-databases | gzip > / tmp/database_ `date'+% Fmure% H% M% S``. Sql.gz

7. Mysqldump gzip backup and restore

Mysqldump-hhostname-uroot-p123456 mysqldatabase | gzip > mysqlbackup.sql.gzgunzip

< mysqlbackup.sql.gz | mysql -uroot -p123456 mysqldatabase 8、mysqldump全量备份+mysqlbinlog二进制日志增量备份： 从mysqldump备份文件恢复数据会丢失掉从备份点开始的更新数据，所以还需要结合mysqlbinlog二进制日志增量备份。 确保my.ini或者my.cnf中包含下面的配置以启用二进制日志，或者mysqld ---log-bin： [mysqld] log-bin=mysql-bin mysqldump命令必须带上--flush-logs选项以生成新的二进制日志文件： mysqldump --single-transaction --flush-logs --master-data=2 >

Backup.sql

For example, if the incremental binary log file generated in this way is mysql-bin.000003, the data recovery time is as follows:

[root@backup /] # mysql-uroot-p123456 db_name

< backup_sun.sql[root@backup /]# mysqlbinlog mysql-bin.000003 | mysql -uroot -p123456 此外mysqlbinlog还可以指定--start-date、--stop-date、--start-position和--stop-position参数，用于精确恢复数据到某个时刻之前或者跳过中间某个出问题时间段恢复数据，直接摘录MySQL文档说明中相关内容如下： 参考细节： ①指定恢复时间 对于MySQL 4.1.4，可以在mysqlbinlog语句中通过--start-date和--stop-date选项指定DATETIME格 式的起止时间。举例说明，假设在今天上午10:00(今天是2017年05月23日)，执行SQL语句来删除一个大 表。要想恢复表和数据，你可以恢复前晚上的备份，并输入： mysqlbinlog --stop-date="2017-05-23 9:59:59" /var/log/mysql/bin.123456 | mysql -u root -p123456 该命令将恢复截止到在--stop-date选项中以DATETIME格式给出的日期和时间的所有数据。如果你没 有检测到几个小时后输入的错误的SQL语句，可能你想要恢复后面发生的活动。根据这些，你可以用起使 日期和时间再次运行mysqlbinlog： mysqlbinlog --start-date="2017-05-23 10:01:00" /var/log/mysql/bin.123456 | mysql -u root -p123456 在该行中，从上午10:01登录的SQL语句将运行。组合执行前夜的转储文件和mysqlbinlog的两行可以 将所有数据恢复到上午10:00前一秒钟。你应检查日志以确保时间确切。下一节介绍如何实现。 ②指定恢复位置 也可以不指定日期和时间，而使用mysqlbinlog的选项--start-position和--stop-position来指定 日志位置。它们的作用与起止日选项相同，不同的是给出了从日志起的位置号。使用日志位置是更准确 的恢复方法，特别是当由于破坏性SQL语句同时发生许多事务的时候。要想确定位置号，可以运行 mysqlbinlog寻找执行了不期望的事务的时间范围，但应将结果重新指向文本文件以便进行检查。操作方 法为： mysqlbinlog --start-date="2005-04-20 9:55:00" --stop-date="2005-04-20 10:05:00" /var/log/mysql/bin.123456 >

/ tmp/mysql_restore.sql

This command creates a small text file in the / tmp directory that displays the SQL statement when the wrong SQL statement was executed. You can use text.

The editor opens the file and looks for statements that you don't want to repeat. If the location number in the binary log is used to stop and continue the restore operation

Comments should be made. Use log_pos plus a number to mark the position. After you have recovered the previous backup files using the location number, you should type from the command line

Enter the following:

Mysqlbinlog-- stop-position= "368312" / var/log/mysql/bin.123456 | mysql-u root-p123456mysqlbinlog-- start-position= "368315" / var/log/mysql/bin.123456 | mysql-u root-p123456

Line 1 above resumes all transactions up to the stop position. The next line will restore from the given starting position to the binary log knot

All the transactions of the bundle. Because the output of mysqlbinlog includes each SQL statement that records the previous SET TIMESTAMP statement, the recovered data, and

The relevant MySQL log will reflect the original time of the transaction execution.

Detailed parameter description (red part is commonly used)

-all-databases,-A

Export all databases.

Mysqldump-uroot-p-- all-databases > test.sql

-all-tablespaces,-Y

Export all tablespaces.

Mysqldump-uroot-p-- all-databases-- all-tablespaces > test.sql

-no-tablespaces,-y

No tablespace information is exported.

Mysqldump-uroot-p-- all-databases-- no-tablespaces > test.sql

-- add-drop-database

Add drop database statements before each database is created.

Mysqldump-uroot-p-- all-databases-- add-drop-database > test.sql

-- add-drop-table

Add drop datasheet statements before each datasheet is created. (default is on, use-- skip-add-drop-table to cancel option)

Mysqldump-uroot-p-- all-databases > test.sql (add drop statement by default) mysqldump-uroot-p-- all-databases-skip-add-drop-table > test.sql (cancel drop statement)

-- add-locks

Add LOCK TABLES before each table is exported and then UNLOCK TABLE. (default is on, use-- skip-add-locks to cancel option)

Mysqldump-uroot-p-- all-databases > test.sql (add LOCK statement by default) mysqldump-uroot-p-- all-databases-skip-add-locks > test.sql (cancel LOCK statement)

-- allow-keywords

Allows you to create column names that are keywords. This is done by prefixing the table name to each column name.

Mysqldump-uroot-p-all-databases-- allow-keywords

-- 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

-- character-sets-dir

Directory of character set files

Mysqldump-uroot-p-all-databases-- character-sets-dir=/usr/local/mysql/share/mysql/charsets

-- comments

Additional comment information. It is on by default. You can cancel it with-- skip-comments.

Mysqldump-uroot-p-- all-databases (default record comment) mysqldump-uroot-p-- all-databases-- skip-comments (uncomment)

-- 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 pass all information between the client and the server

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

-create-options,-a

Include all MySQL feature options in the CREATE TABLE statement. (default is on)

Mysqldump-uroot-p-all-databases

-- 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-- debugmysqldump-uroot-p-- all-databases-- debug= "dazhutpurojinagemarge tmpOndebug.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

-- default-character-set

Sets the default character set, which is utf8

Mysqldump-uroot-p-all-databases-- default-character-set=latin1

-- delayed-insert

Export data by delayed insertion (INSERT DELAYED)

Mysqldump-uroot-p-all-databases-- delayed-insert

-- 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

For each table, reference the insert statement with / *! 40000 ALTER TABLE tbl_name DISABLE KEYS * /; and / *! 40000 ALTER TABLE tbl_name ENABLE KEYS * /; statements. This makes it faster to import the file from dump because it creates the index after all rows have been inserted. This option applies only to MyISAM tables, which are turned on by default.

Mysqldump-uroot-p-all-databases

-- 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=1mysqldump-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-databasesmysqldump-uroot-p-- all-databases--skip-extended-insert (cancel option)

-- fields-terminated-by

The given field is ignored in the export file. Used with the-- tab option, not for the-- databases and-- all-databases options

Mysqldump-uroot-p test test-tab= "/ home/mysql"-fields-terminated-by= "#"

-- fields-enclosed-by

The fields in the output file are wrapped in the given characters. Used with the-- tab option, not for the-- databases and-- all-databases options

Mysqldump-uroot-p test test-tab= "/ home/mysql"-fields-enclosed-by= "#"

-- fields-optionally-enclosed-by

Each field in the output file is selectively wrapped with a given character. Used with the-- tab option, not for the-- databases and-- all-databases options

Mysqldump-uroot-p test test-tab= "/ home/mysql"-fields-enclosed-by= "#"-fields-optionally-enclosed-by = "#"

-- fields-escaped-by

Each field in the output file ignores the given character. Used with the-- tab option, not for the-- databases and-- all-databases options

Mysqldump-uroot-p mysql user-tab= "/ home/mysql"-fields-escaped-by= "#"

-- 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

-- help

Display help information and exit.

Mysqldump-help

-- hex-blob

Export binary string fields in hexadecimal format. You must use this option if you have binary data. The field types affected are BINARY, VARBINARY, and BLOB.

Mysqldump-uroot-p-all-databases-- hex-blob

-host, h

Host information to be exported

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

-- 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

-- insert-ignore

Use the INSERT IGNORE statement when inserting rows.

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

-- lines-terminated-by

Each line of the output file is divided by a given string. Used with the-- tab option and cannot be used with the-- databases and-- all-databases options.

Mysqldump-uroot-p-host=localhost test test-tab= "/ tmp/mysql"-lines-terminated-by= "# #"

-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 this case, the global read lock is given a short time to start the export; for more information, refer to 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-autocommit

Use the autocommit/commit statement to wrap the table.

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

-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.

If you add the-- opt parameter, the generated dump file is slightly different:

. The table statement contains drop table if exists tableName

. Insert contains a lock table statement before lock tables tableName write,insert followed by unlock tables

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

-- pipe (available on windows system)

Use named pipes to connect to mysql

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

-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-databasesmysqldump-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-databasesmysqldump-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

-user,-u

Specifies the user name of the connection.

-verbose,-v

Output a variety of platform information.

-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/"

Reference document: http://www.cnblogs.com/wxb-km/p/3610594.html, written by the blogger in great detail

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.