linux/window backup mysql database regularly

Keywords: MySQL SQL PHP Database

It is relatively simple for Linux to back up the database regularly. crontab can be used for scheduled backup. If windows is used, it may be troublesome.


Step 1: configure the backup directory code on the server:

mkdir /var/lib/mysqlbackup 

cd /var/lib/mysqlbackup

Step 2: write backup script code:

Paste the following code and be sure to change the username,password, and dbname in it.


mysqldump -uuser -ppassword dbname | gzip > /var/lib/mysqlbackup/dbname`date +%Y-%m-%d_%H%M%S`.sql.gz

cd  /var/lib/mysqlbackup

rm -rf `find . -name '*.sql.gz' -mtime 10`  #Delete backup files 10 days ago

Step 3: change the permissions of the backup script
chmod +x

Step 4: execute the backup script code regularly with crontab
crontab -e
If you back up at 21:00 every night, add the following code
00 21 * * * /var/lib/mysqlbackup/

Problems encountered:
1.mysqldump is a backup database command, do not understand direct Baidu.

2.Crontab is the command of a scheduled task. If you don't understand it, you can access it

3. First of all, make sure that the password has been set for root before backing up data. Otherwise, mysqldump will be reported. Get error: 1045: access denied for user 'root' @'localhost '(using password: Yes) when trying to connect
terms of settlement:

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) //This command gives the user new permission or password, which can be read into memory directly without restarting the database to prevent errors
mysql> quit


Step 1: create a new one test.bat File and write the code

"D:\php\php.exe" -f "D:\wwwroot\demo\export_mysql\demo.php"

Note: find your own php.exe Path and your own project path.

Step 2: in my own project path (mine is "D:" wwwroot / demo / export_ New under MySQL \ ") demo.php File, code is

    // Set save file name   
//     $filename=date("Y-m-d_H-i-s")."-".$cfg_dbname.".sql";  
    // Get the current page file path, and the SQL file will be exported to this folder   
    $tmpFile = (dirname(__FILE__))."/".$filename;
    header("Content-Disposition: attachment; filename=" . $filename);
    header("Content-type: application/octet-stream");
    // Export database with mysqldump command   
    system("D:/MySQL/bin/mysqldump -h$cfg_host -u$cfg_dbuser -p$cfg_dbpwd $cfg_dbname --default_character-set=utf8 > $tmpFile");
    $file = fopen($tmpFile, "rb"); // Open the file   
    echo fread($file,filesize($tmpFile));   
    //Delete files 5 days ago


Step 3: use windows task plan to turn on the timer function. The specific task plan setting is relatively simple and can be google d.

Posted by jara06 on Tue, 14 Jul 2020 09:05:18 -0700