Home > Database > Mysql Tutorial > How Can I Automate Dumping MySQL Tables into Separate Files?

How Can I Automate Dumping MySQL Tables into Separate Files?

Mary-Kate Olsen
Release: 2024-10-27 04:46:02
Original
732 people have browsed it

How Can I Automate Dumping MySQL Tables into Separate Files?

Automatically Dump MySQL Tables to Separate Files

The built-in mysqldump utility allows for selective table dumps, but requires pre-specified table names. For a dynamic approach that handles new table additions, an automated solution is necessary.

One such solution is a shell script that dynamically queries the database for all table names and dumps each table into a separate compressed file. This eliminates the need for manual table name maintenance in the dump script.

Here's a sample script that achieves this functionality:

<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.
# Author: @Trutane

[ $# -lt 3 ] && echo "Usage: $(basename ) <DB_HOST> <DB_USER> <DB_NAME> [<DIR>]" && exit 1

DB_host=
DB_user=
DB=
DIR=${4:-.}

[ -d $DIR ] || mkdir -p $DIR

echo -n "DB password: "
read -s DB_pass

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>
Copy after login

This script prompts for the database password, queries the specified database for table names, and dumps each table's data as SQL commands into individual compressed files within the specified directory (or the current working directory by default).

The above is the detailed content of How Can I Automate Dumping MySQL Tables into Separate Files?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template