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

Export database

1.mysqldump exports all libraries

Mysqldump-uroot-ppassword-A > mysql_dump.sql

two。 Export all + ignore special libraries

Mysql-e "show databases;"-uroot-ppassword | grep-Ev "Database | db1 | db2 | test" | xargs mysqldump-uroot-ppassword-- databases > mysql_dump.sql

3. Regular matching exports special libraries

Mysql-e "show databases;"-uroot-ppassword | grep-E "db.* | test" | xargs mysqldump-uroot-ppassword-- databases > mysql_dump.sql

4. Export data that exclude table1 and table2 tables from the database

Mysqldump-uroot-ppassword-t-ignore-table=database.table1-- ignore-table=database.table2 > > database.sql

Additional options:

-c complete sql statement

-- skip-add-locks does not lock the table

-d Export table structure does not export data

-t export data does not export table structure

-R export stored procedures and custom functions

Restore the database

Restore a database from a backup file

The mysql-f-uroot-ppassword < mysql_dump.sql mysqldump command details-- all-databases,-An exports all databases. -- all-tablespaces,-Y exports all tablespaces. -- no-tablespaces,-y does not export any tablespace information. -- add-drop-database add drop database statements before each database is created. -- add-drop-table add drop data table statements before each data table is created. (on by default, use the-- skip-add-drop-table cancel option)-- add-locks adds LOCK TABLES before each table is exported and then UNLOCK TABLE. (on by default, use the-- skip-add-locks cancel option)-- allow-keywords allows you to create column names that are keywords. This is done by prefixing the table name to each column name. -- apply-slave-statements adds' STOP SLAVE', before 'CHANGE MASTER' and adds' START SLAVE' at the end of the export. -- directory of character-sets-dir character set files-- comments additional comment information. It is on by default and can be cancelled with-- skip-comments-- the data exported by compatible will be compatible with other databases or older versions of MySQL. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, no_field_options, and so on. Use several values, separated by commas. It is not guaranteed to be fully compatible, but as compatible as possible. -- compact exports 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--complete-insert, and-c uses 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. -- compress,-C enables compression to pass all information between the client and the server-- create-options,-an includes all MySQL feature options in the CREATE TABLE statement. (open by default)-- databases,-B exports several databases. All name parameters after the parameters are treated as database names. -- debug outputs debug information for debugging. The default value is: dazzldump.traceMotel check check the memory and open the file instructions and exit. -- debug-info outputs debug information and exits-- default-character-set sets the default character set, which defaults to utf8--delayed-insert exporting data by delayed insertion (INSERT DELAYED)-- delete logs after delete-master-logsmaster backup. This parameter will automatically activate-- master-data. -- disable-keys for each table, use / *! 40000 ALTER TABLE tbl_name DISABLE KEYS * /; and / *! 40000 ALTER TABLE tbl_name ENABLE KEYS * /; statements to reference insert 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. -- 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. -- events,-E export events. -- extended-insert,-e uses the 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. -- the given field is ignored in the fields-terminated-by export file. Used with the-- tab option, it cannot be used for-- databases and-- all-databases options-- fields in the fields-enclosed-by output file are wrapped with a given character. Used with the-- tab option, it cannot be used for-- databases and-- all-databases options-- each field in the fields-optionally-enclosed-by output file is selectively wrapped with a given character. Used with the-- tab option, cannot be used for-- databases and-- all-databases options-- fields in the fields-escaped-by output file ignore the given character. Used with the-- tab option, it cannot be used for the-- databases and-- all-databases options-- flush-logs refreshes the log before it starts exporting. 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. -- flush-privileges issues a FLUSH PRIVILEGES statement after exporting the mysql database. For proper recovery, this option should be used to export mysql databases and rely on mysql database data at any time. -- force ignores SQL errors during export. -- help displays help information and exits. -- hex-blob exports 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. -- host,-h the host information to be exported-- ignore-table does not export the specified table. 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. -- CHANGE MASTER TO..' generated by include-master-host-port in-- dump-slave Add 'MASTER_HOST=,MASTER_PORT='--insert-ignore' to the statement to use the INSERT IGNORE statement when inserting rows.-- each line of the lines-terminated-by output file is divided by a given string. Used with the-- tab option and cannot be used with the-- databases and-- all-databases options. -- lock-all-tables,-x submit request to lock 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. -- lock-tables,-l lock all tables before starting the export. 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. -- log-error appends warning and error messages to the given file-- 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. -- the maximum packet length sent and accepted by the max_allowed_packet server. -- the cache size of net_buffer_lengthTCP/IP and socket connections. -- no-autocommit uses autocommit/commit statements to wrap tables. -- no-create-db,-n only exports data without adding CREATE DATABASE statements. -- no-create-info,-t only exports data without adding CREATE TABLE statements. -- no-data,-d does not export any data, only the database table structure. -- no-set-names,-N equals-- skip-set-charset--opt equals-- 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.-- 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. -- password,-p connection database password-- pipe (available in the windows system) uses named pipes to connect to mysql--port,-P connection database port number-- the connection protocol used by protocol, including: tcp, socket, pipe, memory.--quick,-Q without buffering queries, directly exported to standard output. The default is on, and use-- skip-quick to cancel this option. -- quote-names,-Q uses (`) to cause table and column names. The default is on, and use-- skip-quote-names to cancel this option. -- replace uses REPLACE INTO instead of INSERT INTO.--result-file, and-r outputs 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. -- routines,-R exports stored procedures and custom functions. -- set-charset adds' SET NAMES default_character_set' to the output file. The default is on, using the-- skip-set-charset off option. -- 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. -- dump-date adds the export time to the output file. The default is on, using the-- skip-dump-date off option. -- skip-opt disable-opt option.-- socket,-S specifies the location of the socket file that connects to the mysql, and the default path / tmp/mysql.sock--tab,-T creates a tab-split text file for each table at a given path. Note: only for mysqldump and mysqld servers running on the same machine. -- tables overrides the-- databases (- B) parameter to specify the name of the table to be exported. -- triggers export trigger. This option is enabled by default and disabled with-- skip-triggers. -- tz-utc sets the time zone TIME_ZONE='+00:00' at the top of the export to ensure the correctness of TIMESTAMP data exported in different time zones or when the data is moved to other time zones. -- user,-u specifies the user name of the connection. -- verbose,-- v output multiple platform information. -- version,-V outputs mysqldump version information and exits-- where,-w dumps only records selected by a given WHERE condition. Note that if the condition contains special spaces or characters for the command interpreter, be sure to reference the condition. -- xml,-X export XML format.-- directory of plugin_dir client plug-ins for compatibility with different plug-in versions. -- the default_auth client plug-in uses permissions by default.

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.