How to Automate Database Table Dumping in MySQL Without Specifying Table Names?

Automating Database Table Dumping for MySQL

MySQL offers a convenient method to dump table data into individual files, which can be useful for backup purposes. However, manually specifying each table name in the dump command becomes impractical in dynamic environments where the number of tables can change over time. This article explores a solution to automate the dumping process, ensuring all existing tables are captured in separate files.

Automagic Dumping

While MySQL's default syntax requires the knowledge of table names beforehand, there is no direct command to dump all tables automatically. However, a combination of commands and scripting can accomplish this task.

Script-fu Approach

One approach involves creating a script that queries the database to retrieve all table names. Subsequently, it can iterate through the list and dump each table individually. Various scripting languages can access MySQL databases, including Python, Perl, and PHP.

Sample Bash Script

Below is a sample Bash script that dumps table data as SQL commands into separate, compressed files:

<code class="bash">#!/bin/bash

# Descr: Dump MySQL table data into separate SQL files for a specified database.
# Usage: Run without args for usage info.

[ $# -lt 3 ] && echo "Usage: $(basename ) <db_host> <db_user> <db_name> [<dir>]" && exit 1

DB_host=
DB_user=
DB=
DIR=

[ -n "$DIR" ] || DIR=.
test -d $DIR || mkdir -p $DIR

echo -n "DB password: "
read -s DB_pass
echo
echo "Dumping tables into separate SQL command files for database '$DB' into dir=$DIR"

tbl_count=0

for t in $(mysql -NBA -h $DB_host -u $DB_user -p$DB_pass -D $DB -e 'show tables') 
do 
    echo "DUMPING TABLE: $DB.$t"
    mysqldump -h $DB_host -u $DB_user -p$DB_pass $DB $t | gzip > $DIR/$DB.$t.sql.gz
    tbl_count=$(( tbl_count + 1 ))
done

echo "$tbl_count tables dumped from database '$DB' into dir=$DIR"</dir></db_name></db_user></db_host></code>

Usage and Benefits

This script prompts for the database password and dumps the data into compressed files within the specified directory. It eliminates the need to manually update the dump script when new tables are added. Additionally, it can be easily extended to dump multiple databases by providing a comma-separated list of database names as the third argument.

The above is the detailed content of How to Automate Database Table Dumping in MySQL Without Specifying Table Names?. For more information, please follow other related articles on the PHP Chinese website!