This paper mainly introduces mysqldump and cron command of Linux to realize database backup every day. Mysqldump is often used for logical backup of MySQL database, and cron is the planned task of linux, which is to perform the planned work at the agreed time.

1. Creating shell scripts

#!/bin/bash
#The database backup path needs to be created first
backupdir=/home/mysql-data-bak
#Database Account
dbuser=myBak
#Database password
dbpasswd=myBak123abc
#Database Port
dbportal=3306
#Database Port
dbip=127.0.0.1
#Name of data to be backed up
dbname=my-test
#Backup time
time=`date+%Y%m%d`
#Official start of backup database
/usr/bin/mysqldump\
-u${dbuser}\
-p${dbpasswd} \
-P${dbportal}-h${dbip} \
--default-character-set=utf8 \
--databases${dbname} \
>$backupdir/${dbname}-$time.sql;
#To save hard disk space, compress the backup database
tarczvfP $backupdir/${dbname}-$time.sql.tar.gz$backupdir/${dbname}-$time.sql;
echo 'tardone';
#Delete backups 7 days ago, that is, save backups within 7 days only
find$backupdir -name "${dbname}-*.sql.tar.gz" -type f -mtime +7 -exec rm {} \; >/dev/null 2>&1
#Delete the original sql file, leaving only compressed files
rm-f $backupdir/${dbname}-$time.sql;
echo 'remove done'
echo'dump done';

When backing up the shell script, you should pay attention to:
1. To grant executable privileges to the shell script, the relevant commands are chmod+x***.sh

2. Test can be executed directly in sh directory.

./***.sh

After execution, see if there are any backup files in the database backup directory. If there is one, it means that the script has been executed successfully, and if not, it means that it has not been executed successfully.

3. It is suggested that mysql account permissions for mysqldump should be all permissions of 127.0.0.1, password number + letter combination.
Attach the statement to create mysql account:

CREATE  USER  'myBak'@'%'  IDENTIFIED BY  'myBak123abc';
GRANT  ALL ON  *.* TO  'myBak'@'127.0.0.1'  identified by  'myBak123abc';
flushprivileges;

4. Running the script will prompt:

Warning:Using a password on the command line interface can be insecure.

It is not safe to use passwords on the command line interface.
The warning looks bad. Is there any way to solve it? Yes, it's actually a simple way to add mysql configuration file my.cnf
Add mysqldump's username and password, and then remove the information related to the backup script's username and password.
The relevant processes are as follows:
a.vim /etc/my.cnf
b. Add mysqldump account and password configuration as follows [ps: fill in the account password of your own database]

[mysqldump]
user=your_backup_user_name
password=your_backup_password

c. Restart the database

service mysql restart

d. Modifying scripts
Remove the user name and password information from the backup script

II. Configuration of Planned Tasks
There are two ways to configure linux planning tasks:
1,
Executing crontab-e
Enter the following:

30 1 *  *  *  sh  /home/mysql-data-bak/my-test.sh

2,
Open Auto Execution File

vi /etc/crontab

Add the following to the etc to enable it to perform tasks automatically.

30 1 *  *  *  sh  /home/mysql-data-bak/my-test.sh

The above time of 301 * ** indicates that the / home/mysql-data-bak/my-test.sh script is executed automatically at 1:30 a.m. every day.

The meaning of the Planned Task Execution Order is as follows:

#Example of job definition:
#.---------------- minute (0 - 59)
#| .------------- hour (0 - 23)
#| | .---------- day of month (1 - 31)
#| | | .------- month (1 - 12) OR jan,feb,mar,apr ...
#| | | | .---- day of week (0 - 6) (Sunday=0 or 7) ORsun,mon,tue,wed,thu,fri,sat
#| | | | |
#* * * * * user-name command to be executed