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 '' | head -1` #Determine if the number of backups is greater than $number count=`ls -l -crt $backup_dir/*.sql | awk '' | 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.

Add new comment

0 comments