MySQL Single‑Database Backup and Automated Backup Script Guide

Note: If you are using the one‑click environment configuration from the help center, the MySQL installation directory is /alidata/server/mysql. If MySQL is installed elsewhere, provide the full installation path.

For a single‑database backup, run the following command on the server:

/alidata/server/mysql/bin/mysqldump -uroot -p<em>password</em> <em>database_name</em> > <em>backup_name</em>.sql

By default, mysqldump does not export the mysql.event table, which may generate a warning. To include MySQL events, use:

/alidata/server/mysql/bin/mysqldump -uroot -p<em>password</em> --events --ignore-table=mysql.event <em>database_name</em> > <em>backup_name</em>.sql

To import a backup .sql file, navigate to the directory containing the file and execute:

/alidata/server/mysql/bin/mysql -uroot -p<em>password</em> mysql < <em>backup_name</em>.sql

Alternatively, you can start the MySQL client and run the commands manually: /alidata/server/mysql/bin/mysql -uroot -p<em>password</em> Then inside the MySQL prompt:

mysql> use <em>database_name</em>;

mysql> source /root/<em>backup_name</em>.sql;

Note: /root/<em>backup_name</em>.sql should be the absolute path to the actual backup file.

Below is a Bash script ( autoback.sh) that performs periodic automatic backups of all databases:

#!/bin/bash

echo -e "[`date +"%Y-%m-%d %H:%M:%S"`] start"

# system time
time=`date +"%y-%m-%d"`
# host IP
host="192.168.20.85"
# database backup user
user="root"
# database password
passwd="123456"

# create a backup directory
mkdir -p /backup/db/"$time"

# list database names
all_database=`mysql -u$user -p$passwd -Bse 'show databases'`

# backup each database
for i in $all_database; do
    /usr/bin/mysqldump -u$user -p$passwd $i > /backup/db/"$time"/"$i"_"$time".sql
done

echo -e "[`date +"%Y-%m-%d %H:%M:%S"`] end"
exit 0

To schedule the script, edit the crontab with crontab -e and add: 59 23 * * * root sh /root/autobackup.sh >/dev/null 2>&1 Save and exit; the database will be automatically backed up each night at 23:59. Remember to monitor disk usage and clean up old backups or expand storage as needed.