How can I automate the process of dumping MySQL tables into separate files?

Automating MySQL Table Dumps into Separate Files

Manually dumping tables into separate files can be tedious, especially when you have a large database with numerous tables. To automate this process and avoid missing dumps for newly added tables, there are several approaches you can consider.

Automating Table Dumps

The starting point is a script that can iterate through all existing tables and dump them into separate files. Here's an example script that uses the mysqldump command for this purpose:

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

# dump-tables-mysql.sh
# Descr: Dump MySQL table data into separate SQL files for a specified database.

[ $# -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"

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

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

This script will prompt you for the database password, then iterate through all tables in the specified database and dump them into compressed .sql.gz files in the specified directory (--DIR).

Other Dumping Options

If the mysqldump command alone is not flexible enough for your needs, you can use other scripting languages to access MySQL and automate the dumping process. Some popular languages that can connect to MySQL are:

• Python (using the mysql library)
• Ruby (using the mysql2 gem)
• PHP (using the mysqli extension)

These languages provide more control over the dumping process, allowing you to customize the output format, handle errors, and perform other operations as needed.

The above is the detailed content of How can I automate the process of dumping MySQL tables into separate files?. For more information, please follow other related articles on the PHP Chinese website!