Shulou(Shulou.com)05/31 Report--

This article introduces how to use Mysqldump to back up and restore mysql database, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Dump is an effective tool to backup MySQL database. It creates a *. Sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database, execute the *. Sql file on destination database. For MyISAM, use mysqlhotcopy method that we explained earlier, as it is faster for MyISAM tables.

Using mysqldump, you can backup a local database and restore it on a remote database at the same time, using a single command. In this article, let us review several practical examples on how to use mysqldump to backup and restore.

For the impatient, here is the quick snippet of how backup and restore MySQL database using mysqldump:

Backup: # mysqldump-u root-p [root _ password] [database_name] > dumpfilename.sql

Restore:# mysql-u root-p [root _ password] [database_name]

< dumpfilename.sql How To Backup MySQL database 1. Backup a single database: This example takes a backup of sugarcrm database and dumps the output to sugarcrm.sql # mysqldump -u root -ptmppassword sugarcrm >

Sugarcrm.sql

# mysqldump-u root-p [root _ password] [database_name] > dumpfilename.sql

The sugarcrm.sql will contain drop table, create table and insert command for all the tables in the sugarcrm database. Following is a partial output of sugarcrm.sql, showing the dump information of accounts_contacts table:

--

-- Table structure for table `accounts_ contacts`

--

DROP TABLE IF EXISTS `accounts_ contacts`; SET @ saved_cs_client = @ @ character_set_client

SET character_set_client = utf8

CREATE TABLE `accounts_ contacts` (

`id`varchar (36) NOT NULL

`contact_ id` varchar (36) default NULL

`account_ id` varchar (36) default NULL

`date_ modified` datetime default NULL

`deleted` tinyint (1) NOT NULL default'0'

PRIMARY KEY (`id`)

KEY `idx_account_ contact` (`account_ id`, `contact_ id`)

KEY `idx_contid_del_ accid` (`contact_ id`, `deleted`, `account_ id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8

SET character_set_client = @ saved_cs_client

--

-- Dumping data for table `accounts_ contacts`

--

LOCK TABLES `accounts_ contacts` WRITE

/ *! 40000 ALTER TABLE `accounts_ contacts` DISABLE KEYS * /

INSERT INTO `accounts_ contacts` VALUES ('6ff90374-26d1-5fd8-b844-4873b2e42091'

'11ba0239-c7cf-e87e-e266-4873b218a3f9' Magnum 503a06a8-0650-6fddmury22aeMuy4873b245ae53'

'2008-07-23 05PUBG 24RU 30mm Pol 1)

83126e77Mutual eedamurf335Mutual dc1bMui 4873bc805541' 7c525b1clic8a11Mutual d803-94a5-4873bc4ff7d2'

'80a6add6-81ed-0266-6db5-4873bc54bfb5)

('4e800b97-c09f-7896-d3d7-48751d81d5ee1') f241c222murb91aMuyd7a9a9a9Meif355-48751d6bc0f9'

'27060688-1f44-9f10-bdc4-48751db40009)

('c94917ea-3664-8430-e003-487be0817f41' recorder c564b7f3-2923-30b5-4861-487be0f70cb3'

'c71eff65 color b76b color cbb0 color d31a color 487be06e4e0b magic law 2008-07-23 05purse 24 charge 30charge 1)

('7dab11e1-64d3murea6alyc62cMuy487ce17e4e41') 79d6f6e5-50e5-9b2bMur034bMel 487ce1dae5af'

'7b886f23-571b Mui 595b Mui 19ddMui 487ce1eee867 copyright copyright 2008-07-23 05PUR 24 purl 30mm L1)

/ *! 40000 ALTER TABLE `accounts_ contacts` ENABLE KEYS * /

UNLOCK TABLES

2. Backup multiple databases:

If you want to backup multiple databases, first identify the databases that you want to backup using the show databases as shown below:

# mysql-u root-ptmppassword

Mysql > show databases

+-+

| | Database |

+-+

| | information_schema |

| | bugs |

| | mysql |

| | sugarcr |

+-+

4 rows in set (0.00 sec)

For example, if you want to take backup of both sugarcrm and bugs database, execute the mysqldump as shown below:

# mysqldump-u root-ptmppassword-- databases bugs sugarcrm > bugs_sugarcrm.sql

Verify the bugs_sugarcrm.sql dumpfile contains both the database backup.

# grep-I "Current database:" / tmp/bugs_sugarcrm.sql

-- Current Database: `mysql`

-- Current Database: `sugarcrm`

3. Backup all the databases:

The following example takes a backup of all the database of the MySQL instance.

# mysqldump-u root-ptmppassword-- all-databases > / tmp/all-database.sql

4. Backup a specific table:

In this example, we backup only the accounts_contacts table from sugarcrm database.

# mysqldump-u root-ptmppassword sugarcrm accounts_contacts\ > / tmp/sugarcrm_accounts_contacts.sql

4. Different mysqldump group options:

Opt isa group option, which is same as-add-drop-table,-add-locks,-create-options,-quick,-extended-insert,-lock-tables,-set-charset, and-disable-keys. Opt is enabled by default, disable with-skip-opt.

-compact is a group option, which gives less verbose output (useful for debugging). Disables structure comments and header/footer constructs. Enables options-skip-add-drop-table-no-set-names-skip-disable-keys-skip-add-locks

How To Restore MySQL database

1. Restore a database

In this example, to restore the sugarcrm database, execute mysql with

< as shown below. When you are restoring the dumpfilename.sql on a remote database, make sure to create the sugarcrm database before you can perform the restore. # mysql -u root -ptmppassword mysql>

Create database sugarcrm

Query OK, 1 row affected (0.02 sec)

# mysql-u root-ptmppassword sugarcrm < / tmp/sugarcrm.sql

# mysql-u root-p [root _ password] [database_name] < dumpfilename.sql

2. Backup a local database and restore to remote server using single command:

This is a sleek option, if you want to keep a read-only database on the remote-server, which is a copy of the master database on local-server. The example below will backup the sugarcrm database on the local-server and restore it as sugarcrm1 database on the remote-server. Please note that you should first create the sugarcrm1 database on the remote-server before executing the following command.

[local-server] # mysqldump-u root-ptmppassword sugarcrm | mysql\-u root-ptmppassword-- host=remote-server-C sugarcrm1 [Note: There are two-- (hyphen) in front of host]

On how to use Mysqldump backup and restore mysql database to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.