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.

1.Linux:

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

mkdir /var/lib/mysqlbackup 

cd /var/lib/mysqlbackup

Step 2: write backup script code:

vi dbbackup.sh

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

#!/bin/sh

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 dbbackup.sh

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/dbbackup.sh

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

2.Windows

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

D:
"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

<?php  
    error_reporting(0);
    $cfg_host="localhost";
    $cfg_dbuser="root";  
    $cfg_dbpwd="root";  
    $cfg_dbname="demo";  
    // Set save file name   
//     $filename=date("Y-m-d_H-i-s")."-".$cfg_dbname.".sql";  
    $filename=date("Y-m-d")."-".$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");
    header("Pragma:no-cache");
    header("Expires:0");
    // 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
    $time=time();
    $rar=date("Y-m-d",$time-86400*5)."-".$cfg_dbname.".sql";

    if(file_exists($rar))
    {
        @unlink($rar);
    }
    ob_flush();
    flush();
    
    fclose($file);
    ob_end_flush();
    exit;

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.