MySQL database comes with backup and recovery tools: MySQLdump.exe and Mysql.exe

database backup work is the key to protect the database, the following article is mainly about the MySQL database backup of some small coup, we all know that using MySQL dump backup database users need relatively small permissions, Only the permissions on select and lock table are sufficient.
so you can create a user who backs up the database, specifically for backing up the database, which avoids accidental damage to the database.
mysqldump is a good tool for backing up MySQL databases. It is relatively fast with backup tools such as phpMyAdmin, and avoids limitations such as php.ini, and can be scheduled to remotely back up the database in conjunction with scheduled tasks under the Windows system.
before backing up the database we have to do the preparation, first create the database user--root to perform the backup operation, the password is: Hust. Next, determine which database we want to back up and where to put it. Then, determine where the backup files for the database are to be placed.
Suppose our task is to back up a database on the 192.168.1.2 server named Db_test and place the backup file in the D:\bak\ folder. So we can perform backup recovery operations in the following ways:

1. Backup of data

complete a backup of the data in command-line mode, using the mysqldump command. This command allows you to store data in the form of a text file under a specified folder.

Note: To operate the MySQL database in command-line mode, you must set the environment variables of the computer, right-click "My Computer",

Select the "Properties" command from the popup shortcut menu, select the "Advanced" tab in the Pop-up dialog box, and then in the new popup

dialog box, click Environment Variables button, locate the variable PATH in the text box of the user variable and select it, click Edit to

Add "D:\AppServ\MySQL\bin" to the Variable Value text box in the variable PATH (MySQL database

The installation path for the bin folder), and then click OK to press NYC. The path where the bin folder is added is based on your own installation of MySQL

Depends on the location of the library, (if you do not want to configure "environment variables", you want to locate the directory where MySQLdump.exe opens a command prompt, just hold the SHIFT key in your directory to find "Open command Prompt Here")

Attention:

If you use an integrated installation package to configure PHP's development environment, you do not need to do the above configuration, because

The integrated installation package has been configured to complete itself. However, if it is a standalone installation of MySQL, then the above configuration must be done,

To operate the MySQL database in command-line mode.

To back up the entire database with the mysqldump command, proceed as follows:

(1)we can use the command prompt that comes with Windows (there are several ways to invoke the command prompt, the most common is to click on the Start menu, select "Run", enter cmd command to start), and at the command prompt, enter the following command:

Mysqldump-uroot-phust-h 192.168.1.2 db_test >d:\bak\bakcup.sql

In this section of the command:

MySQLdump is the program name of Myqldump; (Here I'm using the integrated development Environment D:\AppServ\MySQL\bin directory)

-U is the user name of the database, followed by the user name root;

-P is the password of the database, followed by the same password, note that there is no space between-p and password;

-H is the address of the database,If this is not the case, the local database is backed up;

Db_test the name of the database to be backed up;

Backup.sql file after backup, suffix can also be. txt

> indicates that the MySQL database is backed up to a later file, followed by the address of course the backup file, note that to use the absolute file path, the file suffix can also be used in. txt.

Once the command is executed, Mysqldump immediately performs the backup operation. Remotely backing up a database it would take a while for a typical remote backup to look like a network.

If you write this command as a batch file, you can implement scheduled backup data through Windows Scheduled tasks.

Back up the database with mysqldump under Windows, because there is no gzip compression engine, so you cannot compress the backup database, so if you have more backup files, you should often clean up outdated MySQL database backup files.

(2) backing up the database in PHP

The previous article is about backing up the database from the Windows command prompt, so how do you do it in PHP?

Back up the database in PHP and execute the system commands primarily through the EXEC () function

The format of the EXEC () function is:

Stringexec (String command)

The parameter command is the system command to execute. Here is the command to prepare the data table, as illustrated below:

<?php

$filename =$_POST[filename];//这里是用户输入的数据库的备份名

 $mysqlstr = "mysqldump -uroot -phust db_wms >sql/".$_POST[filename];//这里使用的是相对路径，（我将mysqldump.exe 复制到了本php文件路径下）
//这里未指定主机，默认本机
 //$mysqlstr = "mysqldump -uroot -phust db_wms >D:/AppServ/www/wms/system/sql/".$_POST[filename];

 exec($mysqlstr);//
 echo "<script>alert(‘数据备份成功!‘);window.location=‘../desk.php‘;</script>";
?>

2. Recovery of data

(1) using a command prompt that comes with Windows

The MySQL command is used. Its command format is as follows:

Mysql-uroot-proot db_database <f:\db_database.sql "

Where MYSQ 丨 is the command used, after-u root on behalf of the user name,-p root on behalf of the password, DB

One

Database represents the number of library names (or table names), and the "F:\db_database.txt" after the "<" is the location where the backup file is stored.

Here are the steps to implement database recovery:

(1) Select "Start"/"Run" command.

(2) Enter "cmd" in the popup dialog and click "OK" button to enter command line mode.

(3) Enter "Mysql-uroot-proot db_database<f:\db_database.txt" directly in the command line mode , and then "enter"

(2) Recovering the database in PHP

<?php

    $mysqlstr = "mysql -uroot -phust db_wms<sql/".$_POST[r_name];//$_POST[r_name]//为用户选择的用来恢复的文件名

    //echo $mysqlstr;

    exec($mysqlstr);

    echo "<script>alert(‘恢复成功‘);location=‘../desk.php‘</script>";

?>

From for notes (Wiz)

MySQL database comes with backup and recovery tools: MySQLdump.exe and Mysql.exe