Adminbuntu

Everything for the Ubuntu Server Administrator

User Tools

Site Tools


Sidebar

Server Administration


Server Applications


At the Command Line


Elsewhere


Copyright 2013 Applied Conscious Technologies, LLC

Terms of Agreement

Contact


submit to reddit

dump_mysql_tables_by_partial_name

MySQL

Dump MySQL Tables by Partial Name

Sometimes it is necessary to copy many tables starting with the same string to another server. Or perhaps only a few tables are updated and need to be backed up.

This procedure will allow you to dump (back up) many tables at once where each table name to be dumped starts with the same string.

See also: Drop MySQL Tables by Partial Name


This will create a standard MySQL text dump file of all tables that have table names starting with a specified string.

  • Replace USERNAMEHERE with the MySQL user to use
  • Replace PASSWORDHERE with the correct password
  • Replace DATABASENAMEHERE with the name of the database
  • Replace STRING with the text that you want to match
mysql --user=USERNAMEHERE --password=PASSWORDHERE --database=DATABASENAMEHERE -e 'SHOW TABLES LIKE "STRING%";' | grep -v Tables_in | xargs mysqldump --user=USERNAMEHERE --password=PASSWORDHERE DATABASENAMEHERE >STRING_tables.dump.sql

How it Works

sub-command purpose
mysql command Returns a table that contains all tables with table names starting with STRING.
grep -v Tables_in Removes the table header row.
xargs mysqldump command Does the dump for all the selected tables.

dump_mysql_tables_by_partial_name.txt · Last modified: 2015/05/31 21:20 (external edit)