How to use crontab to backup MYSQL database regularly under Linux
Just follow the following 3 steps, everything is under your control:
Step one: Configure the backup directory code on the server:
mkdir /var/lib/mysqlbackup cd /var/lib/mysqlbackup
Step two: Write backup script code:
vi dbbackup.sh 粘帖以下代码,务必更改其中的username,password和dbname。 #!/bin/sh mysqldump -uuser -ppassword dbname | gzip > /var/lib/mysqlbackup/dbnamedate +%Y-%m-%d_%H%M%S.sql.gz cd /var/lib/mysqlbackup rm -rf find . -name '*.sql.gz' -mtime 10 #删除10天前的备份文件
Step 3: Change backup script permissions
chmod +x dbbackup.sh
Step 4: Use crontab to execute backup script code regularly:
crontab -e
If you back up at 21:00 every night, add the following code
00 21 * /var/lib/mysqlbackup/dbbackup.sh
Problems you will encounter:
1.mysqldump is the command to back up the database. If you don’t understand, just Baidu it .
2. Crontab is a command for scheduled tasks. If you don’t understand, you can visit http://www.thinkphp.cn/code/1...
3. When backing up data, you must first make sure you have set a password for root. Otherwise, Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect
will be reported when mysqldump is executed. Solution:
Log in to mysql client
mysql -hserverip -uroot -p mysql> use mysql; Database changed mysql> update user set password=password('new password') where user='root'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) //这个命令是给用户赋予了新的权限或者密码,直接读到内存中不需要重启数据库防止出错 mysql> quit