Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux

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 in order to prevent data loss caused by operating errors or system failures. This article mainly introduces the daily automatic backup and scheduled backup of mysql database in Linux. Friends who need it can refer to the following

Overview

Backup is the capacity The basis of disaster 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 in order to prevent data loss caused by operating 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 back up

Disaster recovery plan construction

##Storage mediaOptical Disc

Tape

Hard Disk

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 talk about the addition and use of scheduled tasks, basic backup scripts, and other storage media are just media access The method may be different.

1. Check the disk space: Since it is a scheduled backup, it is necessary to choose a disk space with sufficient space to avoid problems caused by space. Insufficiency leads to backup failure and data loss!

Store to the current disk This is the simplest, but the least recommended; the server has multiple hard disks, it is best to store the backup on another hard disk; if possible, choose better and safer storage Media;

# 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 used the command above to see /home There is sufficient space, so you can consider saving the backup file in /home;

cd /home
mkdir backup
cd backup

3. Create a backup Shell script: Please replace the DatabaseName in the following command with the actual database name;

Of course, you can also use the actual naming rules!

vi bkDatabaseName.sh

Enter/paste the following:

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

Compress the backup:

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

Note:

Replace username with the actual username;

Replace password with the actual password;

Replace DatabaseName with Actual database name;

4. Add executable permissions:

chmod u+x bkDatabaseName.sh

Add executable permissions Then execute it first to see if there are any errors in the script and whether it can be used normally;

./bkDatabaseName.sh

5. Add scheduled tasksDetect or install crontab

Confirm whether crontab is installed:

Execute the crontab command. If command not found is reported, it means 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 the yum command to install the scheduled task program crontab under CentOS

Use the rpm command from CentOS Install the scheduled task program crontab on the system disk

Add a scheduled task

Execute the command:

##

crontab -e

This is just like using the vi editor , you can edit scheduled tasks.

Enter the following content and save:

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

What does it mean specifically?

means that the shell script "/home/backup/bkDatabaseName.sh" is executed 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 there after one minute. It's ok without being created!

If the task execution fails, you can view the task log through 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 0anacron
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)

Summary

The above is the detailed content of Detailed example of how to implement daily automatic backup and scheduled backup of mysql database in Linux. For more information, please follow other related articles on the PHP Chinese website!