Summary of various methods for MySQL database backup and recovery

Method one, directly package MySQL database under the data directory in the database you want to back up the name of the directory, and then restore can be directly replaced

Method Two, using Xcopy timed backup, the following code

Save the. bat file with the following code

The code is as follows	Copy Code
@echo off Echo Performs a backup task ECHO is stopping the database ... Echo is stopping the Web site database ... net stop MySQL Echo performs backup database xcopy "D:/mysql/data/*.*" d:/mysqlbak/webbak%date:~0,10%//e/y/C net start MySQL Echo is starting the Web site database ... Echo Quits backup task Echo. Exit

Save As Db_back.bat. The bat file means to stop the MySQL service first, and then use the Xcopy command to copy the entire folder of the source database to D:/db_backup, and name the file on today's date.

Let's test whether this bat can run under CMD. "Run"->cmd, enter the CD c:/the directory where the bat file is located. Enter Back_job.bat. The results are as follows

Method III, using phpMyAdmin Backup to restore, here I do not introduce.

Method four, using the mysqldump command for backup

I usually use the following SQL to back up the MyISAM table:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false --triggers-r--hex-blob-x db_name > Db_name.sql

Use the following SQL to back up the Innodb table:

/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false
--triggers-r--hex-blob--single-transaction db_name > Db_name.sql

1.2 Restore
A file backed up with Mysqldump is a SQL script that can be poured directly, and there are two ways to import the data.

• Use MySQL client directly
For example:

The code is as follows	Copy Code
/usr/local/mysql/bin/mysql-uyejr-pyejr Db_name < Db_name.sql

• Use SOURCE syntax
In fact, this is not a standard SQL syntax, but the functionality provided by the MySQL client, for example:

Source/tmp/db_name.sql;
Here you need to specify the absolute path to the file, and it must be a file that mysqld run the user (for example, nobody) has permission to read

Commands for backing up the MySQL database

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword databasename > Backupfile.sql

Backing up the MySQL database for a deleted table format

Backing up the MySQL database is a format with a deleted table that allows the backup to overwrite the existing database without having to manually delete the original database.

The code is as follows	Copy Code
Mysqldump-–add-drop-table-uusername-ppassword databasename > Backupfile.sql

Directly compress MySQL database to backup

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword DatabaseName | gzip > backupfile.sql.gz

Backing up a MySQL database (some) tables

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword databasename specific_table1 specific_table2 > Backupfile.sql

Backup multiple MySQL databases at the same time

The code is as follows	Copy Code
Mysqldump-hhostname-uusername-ppassword–databases databasename1 databasename2 databasename3 > Multibackupfile.sql

Just back up the database structure

The code is as follows	Copy Code
Mysqldump–no-data–databases databasename1 databasename2 databasename3 > Structurebackupfile.sql

Back up all databases on the server

The code is as follows	Copy Code
Mysqldump–all-databases > Allbackupfile.sql

command to restore MySQL database

The code is as follows	Copy Code
Mysql-hhostname-uusername-ppassword DatabaseName < Backupfile.sql

Restoring a compressed MySQL database

The code is as follows	Copy Code
Gunzip < backupfile.sql.gz | Mysql-uusername-ppassword DatabaseName

Transferring a database to a new server

The code is as follows	Copy Code
Mysqldump-uusername-ppassword DatabaseName | Mysql–host=*.*.*.*-C DatabaseName

Use PHP to back up the MySQL database

The code is as follows

Copy Code

<?php       mysql_connect (' localhost ', ' test ', ' 123456 ');        mysql_select_db (' Test ');       if (copy_table (' products ', ' Products_bak ')) {     echo success /n ";      }       Else {     echo "failure/n";       }       function copy_table ($from, $to) {     if (table_exists ($to)) {       $success = false;    }     Else {       mysql_query ("CREATE TABLE $to like $from");        mysql_query ("INSERT into $to SELECT * from $from");       $success = true;        }         return $success;      }       function table_exists ($tablename, $database = False) {     if (!$ Database) {       $res = mysql_query ("Select Database ()");     & nbsp;     $database = mysql_result ($res, 0);        }         $res = mysql_query ("             SELECT COUNT (*) as COUNT            from Information_schema.tables            WHERE table_schema = ' $ Database '            and table_name = ' $tablename '          ");         return mysql_result ($res, 0) = = 1;      } ?