Backing up a database with mysqldump (with stored procedures)

With the-r option, you can also back up stored procedures:
Mysqldump-uroot-p Dbtest-r >dbtest_routine_20100617.sql

#windows MySQL backup restore, you can restore the view without modifying My.ini (verified)

mysqldump-uroot-p123--default-character-set=gbk--opt--extended-insert=false--triggers-r--hex-blob-x db_name > F:\db.sql

mysql-uroot-p123-f db_name < F:\db.sql Linux: SQL to back up MyISAM table: /usr/local/mysql/bin/mysqldump-uroot-p123--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-uroot-p123--default-character-set=utf8--opt--extended-insert=false-- Triggers-r--hex-blob--single-transaction db_name > Db_name.sql

parameter annotations: mysqldump is a SQL-level backup mechanism that takes a data table into a SQL script file, which is relatively appropriate when upgrading between different MySQL versions, and is the most commonly used backup method. Some of the main parameters of mysqldump:

--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 , the data exported by-C 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, this parameter needs to be used with caution.

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

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-blobExport 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-tablesIt 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 the CREATE TABLE statement. --no-data,-D does not export any data, only the database table structure is exported. --optThis is just a shortcut option, 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 exports stored procedures and custom functions. --single-transactionThis 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. --triggersexport the trigger at the same time. This option is enabled by default and is disabled with--skip-triggers.

Backing up a database with mysqldump (with stored procedures)