The online environment server periodically backs up the database

The online environment server periodically backs up the database

This is the 10th day of my participation in Gwen Challenge

The environment that

Database: mysql (mariaD)

System: CentOs 7

Solution 1

Back up all database commands

Do not include the Database | information_schema | performance_schema | mysql | test the Database

mysql -e "show databases;"- h127.0.0.1 -u (username) - p (password) | grep - Ev"Database|information_schema|performance_schema|mysql|test"| xargs mysqldump - h127.0.0.1 -u (username) - p (password) - skip - lock - tables - databases > the SQLCopy the code

Mysqldump: Got error: 1044: Access denied for user 'database '@'%' to database' database 'when using LOCK TABLES Error cause: Common account permission related solution: Add --skip-lock-tablesCopy the code

annotation

-all-databases Indicates all databases

– the lock – tables not lock table = 0

Date “+%Y-%m-% D -%H-% m-% S” timestamp

Pay attention to

Database password with! @ needs to be escaped with single quotation marks

Solution 2

To back up the specified database, run the following command

Mysqldump -h127.0.0.1 --port=3306 -u -p --lock-tables=0 --skip-lock-tables --databases > databases /data/databaseBackup/db.sqlCopy the code

An error occurs when the preceding command is executed

Mysqldump: Got error: 1044: "Access denied for user 'database '@'%' to database' password "when selecting the databaseCopy the code

The reason:

-p cannot contain Spaces between the database password

Solutions:

Mysqldump -h127.0.0.1 --port=3306 -u -p --lock-tables=0 --skip-lock-tables --databases > /data/databaseBackup/db.sqlCopy the code

Restoring all databases

Mysql -h127.0.0.1 -uroot -proot test < db-xx.sql mysql -h127.0.0.1 -uroot -proot test < db-xx.sql

Periodically back up the shell script of the database. In the back-up-db.sh file in the /data/databaseBackup directory, the script content is as follows:

#! /bin/bashBckupdir =/data/databaseBackup DB_USER=" username "DB_PASS=" password"
#Backup file suffix timetime=_`date +%Y_%m_%d_%H_%M_%S` mysql -e "show databases;" - h127.0.0.1 -u (Database) - p (password) | grep - Ev "Database | information_schema | performance_schema | mysql | test" | xargs mysqldump -h127.0.0.1 -u (user name) -p(password) --skip-lock-tables -- Databases > /data/databaseBackup/db${time}.sql
#Delete backup files created seven days ago
find $backupdir -name "db*.sql" -type f -mtime +7 -exec rm -rf {} \; > /dev/null 2>&1

echo ${time}" ok"
Copy the code

Configure the shell script to grant the execute permission after the script is saved successfully

chmod +x back-up-db.sh              
Copy the code

The Timer is set on the Linux server

[root@mariadb databaseBackup]#crontab -e    
Copy the code

The timer configuration file is displayed

#The scheduled task is executed at 3:30 every day to redirect the scheduled task logs
30 3 * * * /bin/bash -x /data/databaseBackup/back-up-db.sh >>/data/databaseBackup/backupLog.log 2>&1
Copy the code

annotation

– x: Add parameters – x to get more detailed implementation process 2 > &1: according to the error log (file descriptor for 2) is redirected to standard output (file descriptor 1) > > / data/databaseBackup backupLog. Log: said to standard output (file descriptor 1) Log redirect file/data/databaseBackup/backupLog log, equivalent to 1 > > / data/databaseBackup/backupLog log

View scheduled task logs of the crontab

tail -f /var/log/cron              
Copy the code

View crontab execution logs

tail -f /var/spool/mail/root            
Copy the code