antisaWiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
{{tag>mysql cli}}
====== mysqldump tips ======
<WRAP center round important 60%>
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''
</WRAP>

===== 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:mysql_miscellaneous_commands]]


====== References ======
  * https://dba.stackexchange.com/questions/9306/how-do-you-mysqldump-specific-tables
  * https://stackoverflow.com/questions/2342356/import-single-database-from-all-databases-dump
  * https://stackoverflow.com/questions/1013852/can-i-restore-a-single-table-from-a-full-mysql-mysqldump-file?noredirect=1&lq=1