In this tutorial I will show you show to dump data in SQL format on Ubuntu Server using mysqldump command. The discussed topics on this tutorial are: Invocation Syntax, Specify MySQL credentials, Save the output in a file, Working with multiple databases, Table structure options, Table data options, Format options, mysqldump info, –option-value and –skip-option-value and Man page.
Code used during this tutorial:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 |
# mysqldump Dumping Data in SQL Format on Ubuntu Server # MySQL Workbench # 1. Invocation Syntax mysqldump [options] db_name [tbl_name ...] mysqldump [options] --databases db_name ... mysqldump [options] --all-databases # Usage: mysqldump [OPTIONS] database [tables] # OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] # OR mysqldump [OPTIONS] --all-databases [OPTIONS] # For more options, use mysqldump --help mysqldump # mysqldump: Got error: 1045: Access denied for user 'vagrant'@'localhost' (using password: NO) # when trying to connect mysqldump db1 # 2. Specify MySQL credentials # Dumps `tbl1` table structure and data to the standard output # -u - MySQL user name to use when connecting to server # -p - Password to use when connecting to server # See: provision-shell/resources/var.sh mysqldump -u usrmysql -ppwd-mysql-usrmysql db1 tbl1 # 3. Save the output in a file # By default, mysqldump writes information as SQL statements to the standard output mkdir /vagrant/share/mysqldump mysqldump -u usrmysql -ppwd-mysql-usrmysql db1 tbl1 > /vagrant/share/mysqldump/mysqldump-01.sql # --result-file - Direct output to a given file # This option should be used on Windows to prevent newline “\n” characters from being converted to # “\r\n” carriage return/newline sequences. mysqldump -u usrmysql -ppwd-mysql-usrmysql --result-file=/vagrant/share/mysqldump/mysqldump-01-2.sql db1 tbl1 # PhpStorm: Compare "mysqldump-01.sql" and "mysqldump-01-2.sql" # 4. Working with multiple databases # Dumps all tables structure and data from db1 database mysqldump -u usrmysql -ppwd-mysql-usrmysql db1 > /vagrant/share/mysqldump/mysqldump-02.sql # Dumps all tables structure and data from db1 and db2 databases # --databases - Interpret all name arguments as database names mysqldump -u usrmysql -ppwd-mysql-usrmysql --databases db1 db2 > /vagrant/share/mysqldump/mysqldump-02-2.sql # Dumps all tables structure and data from db1 and db2 databases # except db1.tbl2 and db2.tbl1 # --ignore-table - Do not dump given table mysqldump -u usrmysql -ppwd-mysql-usrmysql --ignore-table='db1.tbl2' --ignore-table='db2.tbl1' --databases db1 db2 > /vagrant/share/mysqldump/mysqldump-02-3.sql # Dumps all tables structure and data from all databases, including `mysql` # search for "CREATE DATABASE" # -- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly. mysqldump -u usrmysql -ppwd-mysql-usrmysql --all-databases > /vagrant/share/mysqldump/mysqldump-02-4.sql # --events - Dump events from dumped databases # PhpStorm: Compare "mysqldump-02-4.sql" and "mysqldump-02-5.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --all-databases --events > /vagrant/share/mysqldump/mysqldump-02-5.sql # Dumps all tables structure and data from db1 database mysqldump -u usrmysql -ppwd-mysql-usrmysql --databases db1 > /vagrant/share/mysqldump/mysqldump-02-6.sql # With --all-databases or --databases, mysqldump writes CREATE DATABASE and USE statements prior to the # dump output for each database. # search for "CREATE DATABASE" in "mysqldump-02-6.sql" # PhpStorm: Compare "mysqldump-02.sql" and "mysqldump-02-6.sql" # --no-create-db - Do not write CREATE DATABASE statements # PhpStorm: Compare "mysqldump-02.sql" and "mysqldump-02-7.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --no-create-db --databases db1 > /vagrant/share/mysqldump/mysqldump-02-7.sql # --add-drop-database - Writes a DROP DATABASE statement preceding each CREATE DATABASE statement # PhpStorm: Compare "mysqldump-02-6.sql" and "mysqldump-02-8.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --add-drop-database --databases db1 > /vagrant/share/mysqldump/mysqldump-02-8.sql # 5. Table structure options vi mysqldump-01.sql # --no-create-info - Do not write CREATE TABLE statements that re-create each dumped table mysqldump -u usrmysql -ppwd-mysql-usrmysql --no-create-info db1 tbl1 > /vagrant/share/mysqldump/mysqldump-03.sql # 6. Table data options # --where - Dump only rows selected by given WHERE condition # Dumps only row with id=2 mysqldump -u usrmysql -ppwd-mysql-usrmysql --where='id=2' db1 tbl1 > /vagrant/share/mysqldump/mysqldump-04-1.sql # --complete-insert - Use complete INSERT statements that include column names mysqldump -u usrmysql -ppwd-mysql-usrmysql --complete-insert db1 tbl1 > /vagrant/share/mysqldump/mysqldump-04-2.sql # --extended-insert - Write INSERT statements using multiple-row syntax that includes several VALUES lists. # This results in a smaller dump file and speeds up inserts when the file is reloaded. # --extended-insert=FALSE is useful if you want to make a diff between 2 MySQL dump files. mysqldump -u usrmysql -ppwd-mysql-usrmysql --extended-insert=FALSE db1 tbl1 > /vagrant/share/mysqldump/mysqldump-04-3.sql # --insert-ignore - Write INSERT IGNORE rather than INSERT statements mysqldump -u usrmysql -ppwd-mysql-usrmysql --insert-ignore db1 > /vagrant/share/mysqldump/mysqldump-04-4.sql # --replace - Write REPLACE statements rather than INSERT statements mysqldump -u usrmysql -ppwd-mysql-usrmysql --replace db1 tbl1 > /vagrant/share/mysqldump/mysqldump-04-5.sql # --no-data - Do not dump table contents mysqldump -u usrmysql -ppwd-mysql-usrmysql --no-data db1 tbl1 > /vagrant/share/mysqldump/mysqldump-04-6.sql # 7. Format options # Decrease the size of the generated dump SQL file # --add-drop-table - Add DROP TABLE statement before each CREATE TABLE statement. # --add-locks - Surround each table dump with LOCK TABLES and UNLOCK TABLES statements. # --disable-keys - For each table, surround INSERT statements with statements to disable and enable keys. # --skip-comments - Do not write additional information in the dump file such as program version, # server version, and host. # --skip-set-charset - Do not write SET NAMES default_character_set to the output. # --skip-tz-utc - Do not add SET TIME_ZONE='+00:00' to dump file mysqldump -u usrmysql -ppwd-mysql-usrmysql --add-drop-table=FALSE --add-locks=FALSE --disable-keys=FALSE --skip-comments --skip-set-charset --skip-tz-utc db1 tbl1 > /vagrant/share/mysqldump/mysqldump-05-1.sql # --compact - Produce more compact output. This option enables the options: --skip-add-drop-table, --skip-add-locks, --skip-comments, # --skip-disable-keys, and --skip-set-charset. mysqldump -u usrmysql -ppwd-mysql-usrmysql --compact db1 tbl1 > /vagrant/share/mysqldump/mysqldump-05-2.sql # PhpStorm: Compare "mysqldump-05-2.sql" and "mysqldump-05-3.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys --skip-set-charset db1 tbl1 > /vagrant/share/mysqldump/mysqldump-05-3.sql # 8. mysqldump info # --verbose - Verbose mode # PhpStorm: Compare "mysqldump-01.sql" and "mysqldump-06-1.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --verbose db1 tbl1 > /vagrant/share/mysqldump/mysqldump-06-1.sql # --print-defaults - Print default options mysqldump --print-defaults # --version - Display version information and exit mysqldump --version # --debug-info - Print debugging information, memory, and CPU statistics when program exits mysqldump --debug-info # 9. --option-value and --skip-option-value # Most of mysqldump --option-value=FALSE has a correspondent --skip-option-value # PhpStorm: Compare "mysqldump-01.sql" and "mysqldump-07-1.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --add-drop-table=FALSE db1 tbl1 > /vagrant/share/mysqldump/mysqldump-07-1.sql # PhpStorm: Compare "mysqldump-07-1.sql" and "mysqldump-07-2.sql" mysqldump -u usrmysql -ppwd-mysql-usrmysql --skip-add-drop-table db1 tbl1 > /vagrant/share/mysqldump/mysqldump-07-2.sql # 10. Man page mysqldump --help | less man mysqldump # Useful links https://github.com/liviubalan/liviubalan.com-vagrant-ubuntu http://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/ http://stackoverflow.com/questions/12439353/mysqldump-one-insert-statement-for-each-data-row |
Useful links:
https://github.com/liviubalan/liviubalan.com-vagrant-ubuntu
http://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html
http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
http://www.thegeekstuff.com/2008/09/backup-and-restore-mysql-database-using-mysqldump/
http://stackoverflow.com/questions/12439353/mysqldump-one-insert-statement-for-each-data-row