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

antisaWiki

Table of Contents