How to use MySQL automatic backup script under Linux server

Problem Description

---

How to automatically back up MySQL deployed in the ECS Linux system.

Solution

---

The MySQL service is built in the ECS Linux system. Users can use the following script to implement regular automatic backup of MySQL.

The usage method is as follows:

1. Copy the following script to the local, upload to the server, and the name is "autoback.sh"

#!/bin/bash
#-----------------------------------------------#
#This is a  free GNU GPL version 3.0 or abover
#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 name
all_database=`/usr/bin/mysql -u$user -p$passwd -Bse 'show databases'`
#in the table from the database backup
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

The database name and database password in the script are based on the database information that the user needs to back up and need to be modified by the user.

2. Run crontab -e and write the following content:

30 5 * * * root sh /root/autobackup.sh >/dev/null 2>&1

Save Exit, and then the database will be automatically backed up at 5:30 every morning. .

Note: Backup will occupy disk space, clean up unnecessary data or expand disk space in time.

The above is the detailed content of How to use MySQL automatic backup script under Linux server. For more information, please follow other related articles on the PHP Chinese website!