1. Backup data with mysqldump command

MySQL provides a convenient tool mysqldump for exporting database data and files from the command line. We can directly export the database content through the command line. First, let's briefly understand the usage of mysqldump command:

mysqldump -u root -p --databases Database 1 database 2 > xxx.sql

2.mysqldump common operation examples

1. Back up the data and structure of all databases

mysqldump -uroot -p123456 -A > /data/mysqlDump/mydb.sql

2. Back up the data and structure of a single database (, database name test)

mysqldump -uroot-p123456 test> /data/mysqlDump/mydb.sql

3. Backup the data and structure of multiple tables (the separate backup method of data and structure is the same as above)

mysqldump -uroot -p123456 mydb t1 t2 > /data/mysqlDump/mydb.sql

4. Backup multiple databases at one time

mysqldump -uroot -p123456 --databases db1 db2 > /data/mysqlDump/mydb.sql

3. Restore MySQL backup content

There are two ways to restore. The first is in the MySQL command line, and the second is to use the SHELL line to complete the restore

1. On the system command line, enter the following to restore:

mysql -uroot -p123456 < /data/mysqlDump/mydb.sql

2. After logging into the mysql system, find the files in the corresponding system through the source command to restore:

mysql> source /data/mysqlDump/mydb.sql

In Linux, BASH script is usually used to write the content to be executed, and crontab is executed regularly to realize automatic log generation.

The following code function is to backup mysql. In cooperation with crontab, the backup content is the daily MySQL database records in recent one month (31 days).

Write BASH to maintain a fixed number of backup files

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

#!/bin/bash

#Save the number of backups and back up the 31 day data
number=31
#Backup save path
backup_dir=/root/mysqlbackup
#date
dd=`date +%Y-%m-%d-%H-%M-%S`
#Backup tools
tool=mysqldump
#user name
username=root
#password
password=TankB214
#Database to be backed up
database_name=edoctor

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

#Simply write 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 the backup that needs to be deleted
delfile=`ls -l -crt $backup_dir/*.sql | awk '{print $9 }' | head -1`

#Determine whether the current 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 generated backups and keep only the number of backups
  rm $delfile
  #Write delete file log
  echo "delete $delfile" >> $backup_dir/log.txt
fi

The main meanings of the above codes are as follows:

1. First set various parameters, such as number, the maximum number to be backed up, backup path, user name, password, etc.
2. Execute the mysqldump command to save the backup file and print the operation to log in the same directory Txt.
3. Define the file to be deleted: get the ninth column, that is, the file list, through the ls command, and then define the file to be deleted with the latest operation time.
4. Define the number of backups: add with ls command to count the number of lines of files ending in sql.
5. If the file size exceeds the limit, delete the sql file created the earliest

4. Use crontab to execute backup scripts regularly

0 2 * * * /root/mysql_backup_script.sh

Execute the script every Tuesday and Friday at 4 a.m

0 4 * * 2,5 /root/mysql_backup_script.sh

Reference:
1. MySQL dump command: www.cnblogs.com com/smail-bao/p/6402265. html
2. Use Shell script to backup mysql database: www.cnblogs.com com/mracale/p/7251292. html
3. Detailed explanation of cronab regular task execution command under Linux: www.cnblogs.com com/longjshz/p/5779215. html