How to automatically back up the mysql database regularly

We all know that data is priceless. If we don’t back up the data, it is equivalent to leaving the data naked. Once the server has problems, we can only cry. The following will introduce the automatic backup of the MySQL database. You can choose where to store the backup data according to the situation.

The first one is that the data runs on the server. If you only want to regularly backup the database on the server (the server system here is centos7), you can set it as follows:

First, write the sh file: /usr/local/backup/bkDatabase.sh:

#!/bin/bash

mysqldump -uusername -ppassword DatabaseName | gzip > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql.gz

Add permissions: chmod u+x bkDatabase.sh

Test whether the execution is ./bkDatabase.sh

Add scheduled tasks to the system

Execute the command crontab -e

Edit the content and add the example of executing the script file regularly at 23:00 every day for backup:

00 23 * * * /usr/local/backup/bkDatabaseName.sh

Check whether the scheduled task is effective: crontab -l

Restart the crontab service: service crond restart

The second method is to automatically and regularly back up the MySQL database of the remote server and store it locally (the local operating system is Windows 7). This method is safer and is therefore recommended.

First, write the backup.bat file.

@echo off
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
md D:\mysite-backup\%Ymd%\
C:\"Program Files"\"mysql-server 5.7.14"\bin\mysqldump --opt -hx.xxx database > D:\database-backup\%Ymd%\database_%Ymd%.sql
echo "Database backup completed"

Then modify the my.ini file to add the following content and restart the mysql service.

[mysqldump]
user=root
password=xyq

Click backup.bat to test whether the backup file is generated successfully.

Create a scheduled task locally to back up the remote database

1. Open Control Panel > Administrative Tools > Task Scheduler and create a task:

2. Fill in the trigger and set the execution time:

3. Add execution script file:

Now you are done. The system will regularly back up the MySQL database on the remote server at the specified time.

This is the end of this article about the method and steps of MySQL scheduled automatic database backup. For more relevant MySQL scheduled automatic backup content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!