MySQL backup and database restoration mysqldump instance and Parameter Details

MySQL backup and database restoration mysqldump instance and Parameter Details
We will certainly encounter the situation of backing up the database and restoring the database during the operation of the project. We generally use the following two methods: 1. the advantage of using into outfile and load data infile to import and export backup data is that the exported data can be in the specified format and exported as pure data without table creation information, you can directly import different tables in the same database, which is more flexible than mysqldump. Let's take a look at the following example: (1) the mysql Command below exports data from the select mytable table to the/home/db_bak2012 file. Select * from mytable where status! = 0 and name! = ''Into outfile'/home/db_bak2012 'fields terminated by' | 'enabledby' "'Lines terminated by '\ r \ n'; import the data backed up just now, you can use the load file method and the following mysql command to import the exported data to the mytable_bak table: load data infile '/home/db_bak2012 'into table mytable_bak fields terminated by' | 'enabledby' "'Lines terminated by' \ r \ n'; 2. use mysqldump to export a database with fixed conditions: (1) Export the mysqldump-u username-p Database Name> exported file name mysqldump-u wcnc-p sm Gp_rj_wcnc> wcnc. SQL (2) export a table mysqldump-u user name-p Database Name> export file name mysqldump-u wcnc-p smgp_rj_wcnc users> wcnc_users. SQL (3) export a database structure mysqldump-u wcnc-p-d -- add-drop-table smgp_1__wcnc> d: \ wcnc_db. SQL #-d: only export structure without exporting data -- add-drop-table add a drop table (4) before each create statement to import data to the database, frequently Used source commands # Go To The mysql Database Console, mysql-u root-p mysql> use Database mysql> set names utf8; (first confirm the encoding, if not set, garbled characters may occur, note not a UTF-8) # Then use so Urce command, followed by a script file (as used here. SQL) the instances above mysql> source d: \ wcnc_db. SQL are only the most basic. Sometimes we may need to export multiple databases in batches, And we can add -- databases or-B, the following statement: mysqldump-uroot-p -- databases test mysql # When spaces are separated, we may need to back up all the databases in the database. Then we can use-all-databases, the following statement: mysqldump-uroot-p-all-databases may have more requirements. The following describes all the parameters I have found online, post it for your reference. Parameter description -- all-databases,-A export all databases. Mysqldump-uroot-p -- all-databases -- all-tablespaces,-Y exports all tablespaces. Mysqldump-uroot-p -- all-databases -- all-tablespaces -- no-tablespaces,-y does not export any tablespace information. Mysqldump-uroot-p -- all-databases -- no-tablespaces -- add-drop-database add the drop database statement before each database is created. Mysqldump-uroot-p -- all-databases -- add-drop-database -- add-drop-table add the drop table statement before each data table is created. (The default value is "Open" and the -- skip-add-drop-table cancel option is used.) mysqldump-uroot-p -- all-databases (the drop statement is added by default) mysqldump-uroot-p -- all-databases-skip-add-drop-table (cancel the drop statement) -- add-locks adds lock tables and unlock table before each table is exported. (The default value is "open". Use the -- skip-add-locks cancel option.) mysqldump-uroot-p -- all-databases (the LOCK statement is added by default) mysqldump-uroot-p -- all-databases-skip-add-locks (cancel LOCK statement) -- allow-keywords allows creation of column names that are keywords. This is done by the table name prefix on each column name. Mysqldump-uroot-p -- all-databases -- allow-keywords -- apply-slave-statements add 'Stop SLAVE 'before 'change Master ', add 'start SLAVE 'At the end of the export '. Mysqldump-uroot-p -- all-databases -- apply-slave-statements -- character-sets-dir character set file directory mysqldump-uroot-p -- all-databases -- character-sets-dir =/usr/local/mysql/share/mysql/charsets -- comments additional comments. It is enabled by default. You can use -- skip-comments to cancel mysqldump-uroot-p -- all-databases (default record comment) mysqldump-uroot-p -- all-databases -- skip-comments (uncomment) -- The data exported by compatible will be compatible with other databases or old MySQL versions. The values can be ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_tables_options, and no_field_options. Separate them with commas. It does not guarantee full compatibility, but is as compatible as possible. Mysqldump-uroot-p -- all-databases -- compatible = ansi -- compact exports less output information (for debugging ). Remove comments, headers, and tails. Available options: -- skip-add-drop-table -- skip-add-locks -- skip-comments -- skip-disable-keys mysqldump-uroot-p -- all-databases -- compact -- complete-insert, -c use the complete insert Statement (including the column name ). This can improve the insertion efficiency, but may be affected by the max_allowed_packet parameter, resulting in insertion failure. Mysqldump-uroot-p -- all-databases -- complete-insert -- compress, -C enables compression between the client and the server to pass all information. mysqldump-uroot-p -- all-databases -- compress -- create-options, -a includes all MySQL feature options in the create table statement. (Enabled by default) mysqldump-uroot-p -- all-databases -- databases,-B Exports several databases. All name parameters following the parameter are considered as the database name. Mysqldump-uroot-p -- databases test mysql -- debug outputs debug information for debugging. The default value is d: t: o,/tmp/mysqldump. trace mysqldump-uroot-p -- all-databases -- debug = "d: t: o,/tmp/debug. trace "-- debug-check memory and open file usage 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, the default value is utf8 mysqldump-uroot-p -- all-databases -- default-character-set = latin1 -- delayed-insert. insert delayed is used) export data mysqldump-uroot-p -- all-databases -- delayed-insert -- delete-master-logs master to delete logs after backup. this parameter will automatically activate -- master-data. Mysqldump-uroot-p -- all-databases -- delete-master-logs -- disable-keys for each table, use /*! 40000 alter table tbl_name disable keys */; And /*! 40000 alter table tbl_name enable keys */; the statement references the INSERT statement. This allows you to import dump files faster, because it creates an index after inserting all rows. This option is only applicable to MyISAM tables. It is enabled by default. Mysqldump-uroot-p -- all-databases -- dump-slave this option will cause the binlog location and file name of the master to be appended to the exported data file. When set to 1, the command change master will be output to the data file; when set to 2, the instructions will be added before the command. This option will enable -- lock-all-tables unless -- single-transaction is specified. This option will automatically disable 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 event. Mysqldump-uroot-p -- all-databases -- events -- extended-insert,-e uses the INSERT syntax with multiple VALUES columns. In this way, the exported file is smaller and the import speed is accelerated. The value is enabled by default, and the -- skip-extended-insert cancel option is used. Mysqldump-uroot-p -- all-databases -- skip-extended-insert (cancel option) -- the specified field is ignored in the fields-terminated-by export file. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-terminated-by = "#" -- fields-enclosed -by: each field in the output file is enclosed by a given character. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally -Each field in the enclosed-by output file is selectively wrapped with a given character. Used with the -- tab option, it cannot be used for the -- databases and -- all-databases options mysqldump-uroot-p test -- tab = "/home/mysql" -- fields-enclosed-by = "#" -- fields-optionally -enclosed-by = "#" -- fields in the fields-escaped-by output file ignore the given characters. Used with the -- tab option, cannot be used for -- databases or -- all-databases options mysqldump-uroot-p mysql user -- tab = "/home/mysql" -- fields-escaped-by = "#" -- flush-logs refresh the log before you start exporting. Note: If you export multiple databases at a time (using options -- databases or -- all-databases), the logs will be refreshed one by one. Except for -- lock-all-tables or -- master-data. In this case, the log is refreshed once, and the corresponding table is locked at the same time. Therefore, if you want to export and refresh logs at the same time, use -- lock-all-tables or -- master-data and -- flush-logs. Mysqldump-uroot-p -- all-databases -- flush-logs -- flush-privileges issues a flush privileges statement after exporting the mysql database. To restore data correctly, this option should be used to export data from the mysql database and dependent mysql database at any time. Mysqldump-uroot-p -- all-databases -- flush-privileges -- force ignores SQL errors during export. Mysqldump-uroot-p -- all-databases -- force -- help displays help information and exits. Mysqldump -- help -- hex-blob exports binary string fields in hexadecimal format. This option is required if binary data exists. The affected field types include BINARY, VARBINARY, and BLOB. Mysqldump-uroot-p -- all-databases -- hex-blob -- host, -h: the host information mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table to be exported does not export the specified table. When you specify to ignore multiple tables, you need to repeat multiple times for each table. The database and table names must be specified for each table. Example: -- ignore-table = database. table1 -- ignore-table = database. table2 ...... Mysqldump-uroot-p -- host = localhost -- all-databases -- ignore-table = mysql. user -- include-master-host-port: 'change master .. 'Add 'master _ HOST =