MySQL database backup

Data backup is a very important task for database administrators. system crashes, disk corruption, and so on can lead to data loss, so you need to back up the database regularly. There are three ways to back up a database.

To maintain the consistency of the backup, perform a lock TABLES operation on the related table before the backup, and then flush TABLES the table. FLUSH tables statement to ensure that all active index pages are started to be written to disk.

I. Backing up the database with the mysqldump command

1. Back up all the tables in a single database

For example, backing up a MySQL database

Mysqldump-h localhost-u root-p mysql > Mysql_db_20141011_1412.sql

You can open Mysql_db.sql to see the following:

-- mysql dump 10.13  distrib 5.5.38, for debian-linux-gnu  (i686)- --- Host: localhost    Database: mysql-- ----------------------------- --------------------------- server version5.5.38-0ubuntu0.12.04.1/*!40101 set @[email  protected] @CHARACTER_SET_CLIENT  */;/*!40101 set @[email protected] @CHARACTER_SET_ Results */;/*!40101 set @[email protected] @COLLATION_CONNECTION  */;/*!40101 set  names utf8 */;/*!40103 set @[email protected] @TIME_ZONE  */;/*!40103  Set time_zone= ' +00:00 '  */;/*!40014 set @[email protected] @UNIQUE_CHECKS,  UNIQUE_ Checks=0 */;/*!40014 set @[email protected] @FOREIGN_KEY_CHECKS,  foreign_key_checks=0  */;/*!40101 set @[email protected] @SQL_MODE,  sql_mode= ' No_auto_value_on_zero '  * /;/*!40111 set&nBsp;@[email protected] @SQL_NOTES, sql_notes=0 */;---- Table structure for  table  ' Columns_priv '--drop table if exists  ' columns_priv ';/*!40101 SET @ saved_cs_client     = @ @character_set_client  */;/*!40101 SET  character_set_client = utf8 */; create table  ' Columns_priv '   (   ' Host '  char)  COLLATE utf8_bin  not null default  ',   ' Db '  char (UP)  COLLATE utf8_bin NOT  null default  ',   ' User '  char ( COLLATE utf8_bin NOT NULL  default  ',   ' table_name '  char ( COLLATE utf8_bin NOT NULL ) default  ',   ' column_name '  char ( COLLATE utf8_bin NOT NULL ) default  ',   ' Timestamp '  timestamp not null default current_timestamp on update current_timestamp,   ' Column_priv '  set (' Select ', ' Insert ', ' Update ', ' References ')  CHARACTER SET utf8 NOT NULL DEFAULT  ',   PRIMARY KEY  (' Host ', ' Db ', ' User ', ' table_name ', ' column_name ')  ENGINE=MyISAM  Default charset=utf8 collate=utf8_bin comment= ' Column privileges ';/*!40101 SET  character_set_client =  @saved_cs_client  */;

Back up a single data table in a single database such as a backup database under MySQL user table mysqldump-h localhost-u root-p mysql user > mysql_user_20141011_1412. SQL backup multiple database backup database MySQL and test, using parameter--databases

Mysqldump-h localhost-u root-p--databases mysql Test > Mysql_test_db_20141011_1412.sql

After you use--databases, you must specify at least one database, separated by a space between multiple databases. You can also use the--all-databases parameter to back up all the databases in the system.

Second, directly copy the entire database directory

The directory location of the database under Linux is usually/var/lib/mysql. Backing up the database directory directly is a simple, fast, and efficient way to backup, but this is not the best approach because it does not apply to InnoDB's storage engine tables. Databases backed up using this method are susceptible to version compatibility issues.

Third, use the Mysqlhotcopy tool to quickly backup

Mysqlhotcopy is a Perl script. He uses the lock Tables,flush TABLES and CP or SCP to quickly back up the database, the quickest way to back up a database and a single table, but he can only run on the same machine as the database directory, and can only back up tables of type MyISAM. Mysqlhotcopy runs on Unix systems. The mysqlhotcopy syntax format is as follows:

Mysqlhotcopy Db_name_1, Db_name_2......db_name_n/path/to/new_directory.

For example, back up the database test to the/usr/backup directory.

Mysqlhotcopy-u root-p Test/usr/backup

This article from "People self-improvement, dream endless" blog, please be sure to keep this source http://yongbird.blog.51cto.com/7554149/1562530

MySQL database backup