CODEWITHSUNDEEP
mysqlbash
Dr Jay
Dr Jay

Reputation: 425

A simple script to backup all mysql databases into separate folders

Hello i have 10 databases each database have 3 table with same name users

I'm trying to dump each database to a separated folder by name of database include in that directory all my tables dumped.

So i started with this sample code.

mysql -uroot -p -e 'show databases' | while read dbname; do mysqldump -uroot -p -T/backup/$dbname/ --fields-terminated-by="|" "$dbname" > "$dbname".txt; done

Then i get this error.

mysqldump: Can't create/write to file '/home/database/users.sql' (Errcode: 2 - No such file or directory)

I dont have much skills with bash scripting Please help me!!

My second Script

#!/bin/bash

echo "MySQL backup"
mysql -u root -pPwd -e "show databases" \
    | grep -Ev 'Database|information_schema' \
    | while read dbname;
do
  echo "Dumping $dbname"
  mysqldump -u root -pPwd --fields-terminated-by="|" $dbname > /var/lib/mysql-files/$dbname/ > "$dbname".txt; done
done

Upvotes: 2

Views: 2507

Answers (2)

alpha027
alpha027

Reputation: 359

Just for reader ease, here is a full working script to do that:

#!/bin/bash

# MySQL database credentials
DB_USER="your_username"
DB_PASS="your_password"

# Dump folder path
DUMP_FOLDER="/path/to/your/dump/folder"

# List of databases
DATABASES=("database1" "database2" "database3")

# Create dump folder if it doesn't exist
mkdir -p "$DUMP_FOLDER"

# Loop through each database and export data
for DB_NAME in "${DATABASES[@]}"; do
    echo "Exporting data from $DB_NAME..."
    mysqldump -u"$DB_USER" -p"$DB_PASS" "$DB_NAME" > "$DUMP_FOLDER/$DB_NAME.sql"
    echo "Export completed for $DB_NAME"
done

echo "All databases have been exported to $DUMP_FOLDER"

Upvotes: 2

Dmytro Kh.
Dmytro Kh.

Reputation: 130

You specified folders to store dumps, but do not create them in script, thus, add a mkdir prior to mysqldump command:

do
  echo "Dumping $dbname"
  mkdir -p /var/lib/mysql-files/${dbname} 
  mysqldump ... > /var/lib/mysql-files/${dbname}/${dbname}.sql
done

Upvotes: 1

Related Questions