User Tools

Site Tools


mysqldump tips

If you are going to use some of the commands below in crontab, the '%' sign needs to be escaped e.g. mysqldump -uroot -p –all-databases | bzip2 > db_dump_$(date +\%F).sql.bz2

Dump only select tables

  mysqldump -u... -p... mydb t1 t2 t3 > mydb_tables.sql

If you have a list of tables to import in mysql they need to be on the same line; this works for that

  cat tables.txt | xargs > table_oneline.txt
  mysqldump -p wordpress_multisite `cat table_oneline.txt` > mydb.sql
  mysql -u root -p mydb < mydb.sql 

Compress/Decompress sql dump on the the fly

  mysqldump -uroot -p mydb | bzip2 > mydb.sql.bz2
  bunzip2 -c mydb.sql.bz2 | mysql -uadmin -p mydb

If you get this error

ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option –binary-mode is enabled and mysql is run in non-interactive mode. Set –binary-mode to 1 if ASCII '\0' is expected. Query: 'root/backup/sporteventz/se_db_dump-2019_07_24_0400.sql'.

The sql file that was zipped, was possible tarred first then zipped with bzip2. It is then necessary to actually untar it:

tar -xf example-db.sql

Dump all databases and zip

mysqldump -uroot -p --all-databases | bzip2 > db_dump_$(date +%F).sql.bz2

Import single database from --all-databases dump

mysql -uroot -p --database mydb --one-database < dump_that_contains_multiple_dbs.sql

Import all databases

mysql -u root -p < alldb.sql

Import single table from sql file

sed -n -e '/DROP TABLE.*`mytable`/,/UNLOCK TABLES/p' mydump.sql > tabledump.sql

See also


wiki/mysqldump_tips.txt · Last modified: 2023/07/03 10:20 by antisa

Except where otherwise noted, content on this wiki is licensed under the following license: CC0 1.0 Universal
CC0 1.0 Universal Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki