How to automatically backup mysql remotely under Linux

Preface:

Basically, whether it is for our own use or deploying servers for customers, MySQL backup is an indispensable part. Here I will share with you how I achieve automatic off-site backup on Linux.

• mysql_backup.sh is used to back up data
• mysql_restore.sh is used to restore data
• remove_backup.sh is used to delete backup files from a period of time ago

1. Data backup preparation

1.1 Create a backup directory

Note: Here I put the backup file under /data/backup/mysql and the script under /data/backup

Create a folder (if it has been created, please ignore it)

mkdir -p /data/backup/mysql
cd /data/backup

1.2 Create a script file

Create mysql_backup.sh Script

vi mysql_backup.sh

Paste the following script content

#!/bin/bash
# If necessary, change this yourself #db_user='root'
#db_password=`cat /data/www/mysql_password`
db_name='wuqilong'
backup_dir='/data/backup/mysql/'
current_time=$(date +'%Y-%m-%d_%H%M%S')
filepath=$backup_dir$current_time'.sql.gz'
#$db_password $db_user is not used here, it has been written into the configuration file echo 'Start exporting the database...'
mysqldump --defaults-extra-file=/data/backup/my_mysql.cnf $db_name | gzip > $filepath
echo 'Export successful, file name: '$filepath

Create a new configuration file in our current directory, i.e. /data/backup

vi my_mysql.cnf

The following is the file content. Here you can set the upper limit of the file size allowed to be exported

The host here is the server you need to back up from.

[mysqldump]
max_allowed_packet = 400M
host=ip address (192.168.1.**)
user=root
password='root'
[mysql]
host=ip address (192.168.1.**)
user=root
password='root'

The exported shell script is now ready. Next, add permissions to it.

chmod +x ./mysql_backup.sh

Now you can use the following command to export

sh ./mysql_backup.sh
# Check the results ll ./mysql 

Summarize

This is the end of this article about MySQL automatic remote backup under Linux. For more information about MySQL automatic remote backup under Linux, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!