How can I automatically dump MySQL tables into separate files without knowing table names beforehand?

Automating MySQL Table Dumps into Separate Files

Mysqldump offers a convenient way to dump MySQL tables into individual files. However, knowing the table names beforehand is a limitation. To address this, there are alternative approaches to automatically dump each table.

Script-Based Approach:

By leveraging script-fu, you can query the database to retrieve all table names and create a script that performs the dumps. Here are some scripting languages capable of accessing MySQL databases:

• Python with mysql-connector or sqlalchemy
• PHP with PDO or mysqli
• Ruby with mysql2
• Java with JDBC or JPA

Self-Contained Bash Script:

The provided Bash script streamlines the process further, eliminating the need for additional scripting knowledge:

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

# dump-tables-mysql.sh
[ $# -lt 3 ] && echo "Usage:  <DB_HOST> <DB_USER> <DB_NAME> [<DIR>]" && exit 1

DB_host=
DB_user=
DB=
DIR=

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

read -sp "DB password: " 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"</code>

This script prompts for the database password, dumps each table's data into a separate compressed SQL file, and stores them in a specified directory. It handles all necessary steps, from retrieving table names to creating the dump files.

The above is the detailed content of How can I automatically dump MySQL tables into separate files without knowing table names beforehand?. For more information, please follow other related articles on the PHP Chinese website!