MySQL Timed Backup Database

In the process of data operation, data errors may occur, even the database may run into disruption. Effective timed backup can protect the database well. This article mainly describes several methods of MySQL timed backup database.

1.mysqldump command backup data

Mysqldump, a convenient tool for exporting database data and files from the command line, is provided in MySQL. We can export dump directly from the command line. First, let's briefly understand the use of mysqldump command:

#MySQLdump Common
mysqldump -u root -p --databases Database 1 Database 2 > xxx.sql

2.Common examples of mysqldump operations

  1. Back up the data and structure of all databases
mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2. Back up the structure of all databases (with -d parameter)

mysqldump -uroot -p123456 -A -d > /data/mysqlDump/mydb.sql
  1. Back up data from all databases (with -t parameter)
mysqldump -uroot -p123456 -A -t > /data/mysqlDump/mydb.sql

4. Back up the data and structure of a single database (, database name mydb)

mysqldump -uroot-p123456 mydb > /data/mysqlDump/mydb.sql
  1. Back up the structure of a single database
mysqldump -uroot -p123456 mydb -d > /data/mysqlDump/mydb.sql
  1. Back up data from a single database
mysqldump -uroot -p123456 mydb -t > /data/mysqlDump/mydb.sql
  1. Back up data and structure for multiple tables (data, structure backup separately is the same as above)
mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql
  1. Back up multiple databases at once
mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

3.Restore MySQL backup content

There are two ways to restore, one from the MySQL command line and the other from the SHELL line

  1. On the system command line, enter the following to restore:
mysql -uroot -p123456 < /data/mysqlDump/mydb.sql
  1. When you log in to the mysql system, restore the files in the corresponding system by using the source directive:
mysql> source /data/mysqlDump/mydb.sql

On Linux, BASH scripts are often used to write what needs to be executed, along with the periodic execution command crontab to automate log generation.

The following code function is to make a backup of mysql with crontab to make the backup content a daily mysql database record within the last month (31 days).

Write BASH maintenance fixed number backup files

On Linux, use vi or vim to write the script content and name it mysql_dump_script.sh

#!/bin/bash

#Save number of backups, backup data for 31 days
number=31
#Backup Save Path
backup_dir=/root/mysqlbackup
#date
dd=`date +%Y-%m-%d-%H-%M-%S`
#Backup tool
tool=mysqldump
#User name
username=root
#Password
password=TankB214
#The database to be backed up
database_name=edoctor

#Create folder if it does not exist
if [ ! -d $backup_dir ];
then     
    mkdir -p $backup_dir;
fi

#Simple notation mysqldump-u root-p123456 users >/root/mysqlbackup/users-$filename.sql
$tool -u $username -p$password $database_name > $backup_dir/$database_name-$dd.sql

#Write Create Backup Log
echo "create $backup_dir/$database_name-$dd.dupm" >> $backup_dir/log.txt

#Find Backups to Delete
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#Determine if the number of backups is greater than $number
count=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | wc -l`

if [ $count -gt $number ]
then
  #Delete the earliest backups, leaving only the number of backups numbered
  rm $delfile
  #Write Delete File Log
  echo "delete $delfile" >> $backup_dir/log.txt
fi

The main meaning of the code above is as follows:

1. Set various parameters first, such as the maximum number of backups needed for numbers, backup path, user name, password, etc.

2. Execute the mysqldump command to save the backup file and print the operation to the log.txt in the same directory to mark the operation log.

3. Define the file that needs to be deleted: get the ninth column, the file name column, through ls command, and define the file that needs to be deleted by implementing the definition operation last.

4. Define the number of backups: add via ls command

Count the number of rows of files ending in sql.

5. If the file exceeds the limit size, delete the sql file that was first created.

Tags: Database MySQL Big Data

Posted on Sun, 19 Sep 2021 21:05:43 -0400 by dantone