mysqldump: backing up specific tables

Here’s a quick example of how to backup specific MySQL / MariaDB tables and piping to xz to compress…

mysqldump -h 127.0.0.1 -P3306 mysql user tables_priv | xz --compress -9 > /db_dumps/3104/mysqldump_tables_example.sql.xz

Here’s how to decompress the resulting file. Note xz will delete the input file unless you specifiy –keep on the command line…

xz --decompress mysqldump_tables_example.sql.xz

Here’s how you can count the number of create table statements in the dump file…

cat mysqldump_tables_example.sql | grep "CREATE TABLE" | wc -l