MySQL backup SQL script specifying conditions

Mysqldump-uroot-proot DBName tabalename-w "condition = '???? ' "--lock-all-tables > Target path

Transferred from: http://blog.chinaunix.net/uid-27038861-id-3591736.html

mysqldump Backup Restore and mysqldump Import Export statement

mysqldump backup :

Mysqldump-u User name-p password-H host database a-w "SQL condition"--lock-all-tables > Path

mysqldump Restore :

Mysqldump-u User name-p password-H host database < path

mysqldump Export by condition :

Mysqldump-u User name-p password-H host database a--where "conditional statement"--no-Build table > Path

mysqldump-uroot-p1234 dbname a--where "tag= '"--no-create-info> c:\a.sql

mysqldump Import by condition :

Mysqldump-u User name-p password-H host database < path

Case:

mysql-uroot-p1234 DB1 < C:\a.txt

mysqldump Export Table :

Mysqldump-u User name-p password-H host database table

Case: Mysqldump-uroot-p Sqlhk9 a--no-data

Main parameters

--compatible=name
It tells Mysqldump that the exported data will be compatible with which database or which old version of the MySQL server. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , to use a few values, separate them with commas. Of course, it is not guaranteed to be fully compatible, but is as compatible as possible.
--complete-insert,-c
The exported data takes the full INSERT method that contains the field name, that is, all the values are written in one line. This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail. Therefore, it is prudent to use this parameter, at least I do not recommend it.
--default-character-set=charset
Specifies which character set to export the data in, and if the data table is not in the default Latin1 character set, you must specify this option when exporting, or you will have garbled problems after importing the data again.
--disable-keys
Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement */; and/*!40000 ALTER table Table ENABLE KEYS */; statement, which can greatly increase the speed of the INSERT statement, because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.
--extended-insert = True|false
By default, mysqldump turns on--complete-insert mode, so if you don't want to use it, use this option to set its value to false.
--hex-blob
Export binary string fields using hexadecimal format. This option must be used if you have binary data. The field types affected are BINARY, VARBINARY, BLOB.
--lock-all-tables,-x
Before starting the export, the commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and the--single-transaction and--lock-tables options are turned off automatically.
--lock-tables
It is similar to--lock-all-tables, but locks the currently exported data table instead of locking down all the tables in the library at once. This option applies only to the MyISAM table, if the Innodb table is available with the--single-transaction option.
--no-create-info,-t
Exports only data without adding a CREATE TABLE statement.
--no-data,-d
No data is exported, only the database table structure is exported.
--opt
This is just a shortcut option, which is equivalent to adding--add-drop-tables--add-locking--create-option--disable-keys--extended-insert--lock-tables at the same time-- Quick--set-charset option. This option allows mysqldump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with--skip-opt. Note that if you run mysqldump without specifying the--quick or--opt option, the entire result set is placed in memory. Problems can occur if you export a large database.
--quick,-q
This option is useful when exporting large tables, forcing mysqldump to cache records directly from the server query instead of getting all the records back into memory.
--routines,-r
Export stored procedures and custom functions.
--single-transaction
This option submits a begin SQL statement before exporting the data, and begin does not block any applications and ensures consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.
This option and the--lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed.
To export large tables, you should use the--quick option together.
--triggers
Export the trigger at the same time. This option is enabled by default and is disabled with--skip-triggers.
For additional parameter details please refer to the manual, I usually use the following SQL to back up the MyISAM table:
/usr/local/mysql/bin/mysqldump-uyejr-pyejr "
--default-character-set=utf8--opt--extended-insert=false "
--triggers-r--hex-blob-x db_name > Db_name.sql
Use the following SQL to back up the Innodb table:
/usr/local/mysql/bin/mysqldump-uyejr-pyejr "
--default-character-set=utf8--opt--extended-insert=false "
--triggers-r--hex-blob--single-transaction db_name > Db_name.sql
In addition, if you want to implement an online backup, you can also use the--master-data parameter, as follows:
/usr/local/mysql/bin/mysqldump-uyejr-pyejr "
--default-character-set=utf8--opt--master-data=1 "
--single-transaction--flush-logs db_name > Db_name.sql
It simply requests the lock table in the first moment, then refreshes the Binlog, then adds the change MASTER statement in the exported file to specify the Binlog location of the current backup, which can be done if you want to restore the file to slave.

1.2 Restore
The file backed up with Mysqldump is a SQL script that can be poured directly into it, and there are two ways to import the data.
Directly with the MySQL client
For example:
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql
Use SOURCE syntax (experiment not successful!!!) ）
In fact, this is not the standard SQL syntax, but the functionality provided by the MySQL client, such as:
Source/tmp/db_name.sql;
The absolute path to the file needs to be specified, and it must be a file that the Mysqld run user (for example, nobody) has permission to read.

MySQL backup SQL script specifying conditions