Linux can backup MySQL database regularly and delete the backup files 30 days ago

Time:2020-9-27

1. MySQL version 5.6 or above

2. Modify / etc/ my.cnf file

# vim /etc/my.cnf
[client]
host=localhost
User = your database user
Password ='your database password '

3. Write database script mysql- backup.sh

# vim mysql-backup.sh
#!/bin/bash
Backupdir = database backup directory
backupTime=`date +%Y%m%d%H%M%S`
Mysqldump your database | gzip > $backupdir / your database-$ backupTime.sql.gz
Echo "1." $BackUpTime "backup complete" > > "backupdir"/ mysql.log
cd $backupDir
rm -rf `find . -name '*. sql.gz ' -mtime +30` >> #backupDir/ mysql.log  2> & 1 ා delete backup files 30 days ago
Echo "2. Delete backup files 30 days ago" > > ා backupdir/ mysql.log

4. Add execution permission to script


# chmod +x mysql-backup.sh

5. Test execution

./mysql-backup.sh

6. Add timing plan

#Crontab - E (same as VIM editor)
#Crontab configuration file format: time sharing day month week command
#In the last line, add:
0 3 * * *  /usr/sbin/mysql- bakup.sh  #It means that the backup is performed at 3:00 every day
0 */6 * * * /usr/sbin/mysql- bakup.sh  #Indicates that the backup is performed every 6 hours

7. Restart crontab

# /etc/rc.d/init.d/crond restart

8. Restore database backup files

#SQL backup file recovery:
MySQL - U root - p your database < backup file name.sql
#Compressed file recovery:
Gzip < backup file name sql.gz  |MySQL - U root - p your database

9. Attachment: if there is a problem: “mysqldump: command not found”, the solution is as follows:


ln -fs /usr/local/mysql/bin/mysqldump /usr/bin
ln -fs /usr/local/mysql/bin/mysql /usr/bin

PS: let’s take a look at the MySQL database backup in Linux

To back up MySQL database in Linux, the command is as follows:

[root] # mysqldump - U user name - P password -- all Databases > / home / MySQL/ bak.sql

If an error is reported as follows:


mysqldump -h localhost -u root -p psword [email protected] --all-tablespaces --master-data=2 --events --routines --all-databases > /home/mysql/mydatas.sql

Simple version: (saved locally first)


mysqldump -u root -p databaseName > data.sql

Query MySQL database:whereis mysql

Enter database (local to current database file)MySQL - H localhost - U database account - P password

To view the database:show databases;

View file size:ls -lht

summary

The above is the Linux implementation of regular backup MySQL database and delete the backup files 30 days ago. I hope it will be helpful to you. If you have any questions, please leave me a message, and the editor will reply you in time. Thank you very much for your support to the developeppaer website!
If you think this article is helpful to you, welcome to reprint, please indicate the source, thank you!

Recommended Today

PHP clear HTML code, space, carriage return newline function

Copy codeThe code is as follows: function DeleteHtml($str) { $str = trim($str); $str = strip_tags($str,””); $str = ereg_replace(“\t”,””,$str); $str = ereg_replace(“\r\n”,””,$str); $str = ereg_replace(“\r”,””,$str); $str = ereg_replace(“\n”,””,$str); $str = ereg_replace(“&nbsp;”,” “,$str); return trim($str); } This function can be extended and modified as needed.