Several methods for backing up mysql database

1. Copy date folder backup
   ==============================
   Imagine environment ：
   MySQL Installation location: C:\MySQL
   Forum database name is: bbs
   Database backup destination: C:\db_bak\
   ============== ===============
   Create a new db_bak.bat and write the following code
   
   ##************** *******************Code Start*******************************
   net stop mysql
   xcopy c:\mysql\data\bbs\*.* c:\db_bak\bbs\%date:~0,10%\ /S /I
   net start mysql
   * ******************************Code End ****************** ***********
   
   Then use Windows’ “Scheduled Tasks” to execute the batch script regularly. (For example: execute back_db.bat at 3 am every day)
   Explanation: The backup and recovery operations are relatively simple, the integrity is relatively high, and the backup cycle control is more flexible. For example, use %date:~0,10%. This method is suitable for users who have independent hosts but have no management experience in MySQL. The disadvantage is that it takes up a lot of space, and mysql will be disconnected for a short time during the backup (for example, it takes about 5 seconds for a database of about 30M). For the usage of %date:~0,10%, refer to .
   
   
   2. Mysqldump is backed up into a sql file
   ==============
   Imaginary environment:
   MySQL Installation location: C:\MySQL
   The forum database name is: bbs
   MySQL root Password: 123456
   Database backup destination: D:\db_backup\
   

# #3. Use WinRAR to perform regular backup of the MySQL database.

For MySQL backup, the best way is to directly back up the Data directory of the MySQL database. The following provides a method to use WinRAR to perform regular backup of the Data directory.

First of all, of course, WinRAR must be installed on the computer.

Write the following command into a text file

****************************** ****Code Start******************************
net stop mysql

c:\ progra~1\winrar\winrar a -ag -k -r -s d:\mysql.rar d:\mysql\data

net start mysql
************ ************************Code End****************************** *

Save, and then change the extension of the text file to CMD. Enter the control panel, open Scheduled Tasks, and double-click "Add Scheduled Task". Find the CMD file just now in the scheduled task wizard, and then specify a running time and the account password used when running for this task.
The disadvantage of this method is that it takes up more time, compression during backup takes time, and mysql disconnection takes more time than the first method, but it is good for file naming.

I recently needed to use mysql backup, so I wrote a shell script for backup.

#!/bin/bash
#定义备份的数据库名称
database=***#定义备份的时间
currTime=$(date +%Y%m%d)
#备份存储目录
bakUrl=/opt/$database/db/$currTime

#备份文件名
databaseBakName=$database.$currTime.sql

#备份文件路径
databaseBakUrl=$bakUrl/$databaseBakName

#判断备份的文件夹是否存在
#这里的-d 参数判断备份存储目录是否存在  
if [ ! -d $bakUrl ]; 
then
   mkdir -p $bakUrl  
fi #这里的-f参数判断备份文件是否存在  
if [ ! -f $databaseBakUrl ]; 
then  
    mysqldump -u*** -hIP -p***  $database > $databaseBakUrlelserm -rf $databaseBakUrl
    mysqldump -u*** -hIP -p***  $database > $databaseBakUrlfi

Name the shell script databasebak.sh

Give script permissions

chmod u+

x databasebak.sh

The above is the detailed content of Several methods for backing up mysql database. For more information, please follow other related articles on the PHP Chinese website!