Linux implements automatic and scheduled backup of MySQL database every day

Overview

Backup is the basis of disaster recovery. It refers to the process of copying all or part of the data set from the hard disk or array of the application host to other storage media to prevent data loss due to system operation errors or system failures. For some websites and systems, the database is everything, so it is crucial to back up the database!

What is backup?

Why backup?

Disaster recovery plan construction

Storage Media

CD

Tape

harddisk

Disk Array

DAS: Direct Attached Storage

NAS: Network Attached Storage

SAN: Storage Area Network

Cloud Storage

Here we mainly use the local disk as the storage medium to explain the addition and use of scheduled tasks and basic backup scripts. The only difference between other storage media is that the access method of the media may be slightly different.

1. Check the disk space:

Since it is a scheduled backup, you must choose a disk with sufficient space to avoid backup failure and data loss due to insufficient space!

Storing to the current disk is the simplest, but the least recommended. If the server has multiple hard disks, it is best to store the backup on another hard disk. If conditions permit, choose a better and more secure storage medium.

# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root 50G 46G 1.6G 97% /
tmpfs 1.9G 92K 1.9G 1% /dev/shm
/dev/sda1 485M 39M 421M 9% /boot
/dev/mapper/VolGroup-lv_home 534G 3.6G 503G 1% /home

2. Create a backup directory:

We can see from the command above that there is enough space in /home, so we can consider saving the backup files in /home;

cd /home
mkdir backup
cd backup

3. Create a backup shell script:

Note that you should replace DatabaseName in the following commands with the actual database name;

Of course, you can also use your own naming conventions!

vi bkDatabaseName.sh

Type/paste the following:

#!/bin/bash
mysqldump -uusername -ppassword DatabaseName > /home/backup/DatabaseName_$(date +%Y%m%d_%H%M%S).sql

To compress the backup:

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

Notice:

Replace username with the actual username;

Replace password with your actual password;

Replace DatabaseName with the actual database name;

4. Add executable permissions:

chmod u+x bkDatabaseName.sh

After adding executable permissions, execute the script first to see if there are any errors and whether it can be used normally;

./bkDatabaseName.sh

5. Add scheduled tasks

Detect or install crontab

Confirm whether crontab is installed:

If the crontab command reports command not found, it means that it is not installed.

# crontab
-bash: crontab: command not found

If crontab is not installed, you need to install it first. For specific steps, please refer to:

Use yum command to install crontab, a scheduled task program, in CentOS

Use the rpm command to install the scheduled task program crontab from the CentOS system disk

Adding a scheduled task

Execute the command:

crontab -e

At this time, you can edit the scheduled task just like using the vi editor.

Enter the following and save:

*/1 * * * * /home/backup/bkDatabaseName.sh

What does it mean specifically?

This means that the shell script "/home/backup/bkDatabaseName.sh" is executed once every minute.

6. Test whether the task is executed

It’s very simple, we just execute the “ls” command several times and see if the file is created after one minute!

If the task execution fails, you can view the task log by running the following command:

# tail -f /var/log/cron

The output is similar to the following:

Sep 30 14:01:01 bogon run-parts(/etc/cron.hourly)[2503]: starting 0ancron
Sep 30 14:01:01 bogon run-parts(/etc/cron.hourly)[2512]: finished 0anacron
Sep 30 15:01:01 bogon CROND[3092]: (root) CMD (run-parts /etc/cron.hourly)
Sep 30 15:01:01 bogon run-parts(/etc/cron.hourly)[3092]: starting 0anacron
Sep 30 15:01:02 bogon run-parts(/etc/cron.hourly)[3101]: finished 0anacron
Sep 30 15:50:44 bogon crontab[3598]: (root) BEGIN EDIT (root)
Sep 30 16:01:01 bogon CROND[3705]: (root) CMD (run-parts /etc/cron.hourly)
Sep 30 16:01:01 bogon run-parts(/etc/cron.hourly)[3705]: starting 0anacron
Sep 30 16:01:01 bogon run-parts(/etc/cron.hourly)[3714]: finished 0anacron
Sep 30 16:15:29 bogon crontab[3598]: (root) END EDIT (root)

Summarize

The above is the editor's introduction to the daily automatic backup of MySQL database in Linux. I hope it will be helpful to everyone. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!