How to Export, Import, and Automate MySQL Backups on Linux

MySQL backup export

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, you need to 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

mysqldump does not export event tables by default; you may see a warning: "Warning: Skipping the data of table mysql.event. Specify the --events option explicitly."

If you need to export 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

MySQL backup import

To import a .sql backup file, navigate to its directory and run:

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

Alternatively, you can start the MySQL client first: /alidata/server/mysql/bin/mysql -uroot -p<em>password</em> Then execute:

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.

Automated periodic MySQL backup script

1. Copy the following script to a local file named autoback.sh and upload it to the server:

#!/bin/bash
#-----------------------------------------------#
# This is a free GNU GPL version 3.0 or above
# Copyright (C) 2008 06 05
# mysql_backup Dedicated copyright by My
#-----------------------------------------------#
echo -e [`date +"%Y-%m-%d %H:%M:%S"`] start
# system time
time=`date +"%y-%m-%d"`
# host IP
host="127.0.0.1"
# database backup user
user="root"
# database password
passwd="yourpasswd"
# Create a backup directory
mkdir -p /backup/db/"$time"
# list database names
all_database=`/usr/bin/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

Modify the script to set the correct database names and passwords as needed.

2. Edit the crontab with crontab -e and add the following line to schedule the script: 30 5 * * * root sh /root/autobackup.sh >/dev/null 2>&1 Save and exit; the database will be automatically backed up each morning at 5:30 AM.

Note: Backups consume disk space, so clean up unnecessary files or expand storage as required.