Backup and Restore MySQL Database with mysqldump!File Repair Tool Blog

To backup the MySQL database using the mysqldump is very efficient as it helps you to create a *.sql file with the CREATE table, DROP table, and INSERT into sql-statements of the source database. For restoring the database you need to execute the *.sql file on destination database.   

With the help of the mysqldump you can backup your local database and restore it on the remote database at the same time with the help of single command. Here are some of the reviews by which you can backup and restore the database using the mysqldump.

How to backup and restore the 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

Backup MySQL database

1. Single database backup:

Here is an example of how to take the backup of sugarcrm database and dumps of output on sugarcrm.sql

# mysqldump -u root -ptmppassword sugarcrm > sugarcrm.sql # mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql

Sugarcrm.sql contains create table, drop table and insert command for all the tables present in the sugarcrm database. Here is the partial output of sugarcrm.sql that shows you the dump information of account_contact table here:

-- -- Table structure for table `account_contact` -- 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 `account_contact` -- LOCK TABLES `account_contact` WRITE; /*!40000 ALTER TABLE `account_contact` DISABLE KEYS */; INSERT INTO `account_contact` VALUES ('6ff90374-26d1-5fd8-b844-4873b2e42091', '11ba0239-c7cf-e87e-e266-4873b218a3f9','503a06a8-0650-6fdd-22ae-4873b245ae53', '2008-07-23 05:24:30',1), ('83126e77-eeda-f335-dc1b-4873bc805541','7c525b1c-8a11-d803-94a5-4873bc4ff7d2', '80a6add6-81ed-0266-6db5-4873bc54bfb5','2008-07-23 05:24:30',1), ('4e800b97-c09f-7896-d3d7-48751d81d5ee','f241c222-b91a-d7a9-f355-48751d6bc0f9', '27060688-1f44-9f10-bdc4-48751db40009','2008-07-23 05:24:30',1), ('c94917ea-3664-8430-e003-487be0817f41','c564b7f3-2923-30b5-4861-487be0f70cb3', 'c71eff65-b76b-cbb0-d31a-487be06e4e0b','2008-07-23 05:24:30',1), ('7dab11e1-64d3-ea6a-c62c-487ce17e4e41','79d6f6e5-50e5-9b2b-034b-487ce1dae5af', '7b886f23-571b-595b-19dd-487ce1eee867','2008-07-23 05:24:30',1); /*!40000 ALTER TABLE `account_contact` ENABLE KEYS */; UNLOCK TABLES;

2. Single database backup:

If you need to backup multiple databases then you need to first identify the database of which you want to create backup using the show database:

 # mysql -u root -ptmppassword
 mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bugs               |
| mysql              |
| sugarcr            |
+--------------------+

4 rows in set (0.00 sec)

Example: If you want to create a backup of the both sugarcrm and bugs databases then you need to execute the mysqldump as demonstrated below:

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

Then you need to verify that there are no bugs in database backup with the help of bugs_sugarcrm.sql dumpfile

# grep -i "Current database:" /tmp/bugs_sugarcrm.sql
-- Current Database: `mysql`
-- Current Database: `sugarcrm`

3. All Databases Backup:

Here is the following example that you need to take for the backup of all the database of the MySQL instance.

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

4.  Specific table backup:

Here is the by which you can backup only the account_contact table with the help of sugarcrm database.

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

5. Group options for different mysqldump:

• –opt is a group option, which is same as–add-drop-table,–create-options, –quick, –add-locks,  –lock-tables, –set-charset,  –extended-insert, and –disable-keys, option is enabled by default, disable with –skip-opt.
• –compact is a group option, that gives less verbose output (useful for debugging). It let you to disables structure comments and header/footer constructs. Enables options –skip-add-drop-table –no-set-names –skip-disable-keys –skip-add-locks

Restore MySQL database

1. Database Restore:

Here is the example for restoring the sugarcrm database, execution of mysql with < as shown below: Restoring of the dumpfilename.sql on a remote database makes sure that you can create the sugarcrm database before performing the restoration.

# 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. You can back-up the local database and restore them to remote server using single command.

If you want to keep a read only database on your remote server then you need to copy the master database on the local server. For example: here you can back-up the sugarcrm database on the local-server and restore them as sugarcrm1 database on your remote server. For this firstly you need to 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]

Apart from backing up and Restoring MySQL Database with the mysqldump you can make use of the MySQL Repair Tool which is powerful tool that helps you in recovery of corrupt MySQl Server database files. It is best for sudden database fails or reliable recovery of essential database. It saves the data retrieved information from Transact-SQL script for quick database recreation. It lets you recover the table structure and data.

Steps to Restore MySQL Database

Step 1: Download, install and launch the MySQl Database repair tool.

Step 2: Click ‘Select’ button to find and add corrupt MySQL database to the application and then select OK. Here you have the option of ‘Repair corrupt database which is stored at default location of MySQL’ and ‘Manual option to selct your database from default location’.

Step 3: After the scanning process you can now see the preview of the file in the left panel of the software.

Step 4: Now you can start the reairing process, just click on Repair button. A dialoguie box will appear. Click Yes.

Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.