Sony AK Knowledge Center

We will talk about how to do backup and restore on MySQL database using mysqldump utility. Sometimes you need to copy or backup your MySQL database from one machine to another. Fortunately you can do backup easily on MySQL using mysqldump utility.

Let’s assume you have database called ‘your_database_1′ on machine A and you want to backup and restore it on machine B. We will dump your database using mysqldump. Dumping here means extract your database into lines of SQL statement including all structures and data.

First you must know username and password that have access to your database on machine A. Let’s assume your username is ‘dbuser’ and your password is ‘dbpass’. Now get ready to dump/backup your database using mysqldump. The mysqldump utility available on your MySQL /bin folder. Login to machine A, then go to mysql /bin folder and you can type the command like below to backup/dump database.

mysqdump -udbuser -pdbpass your_database_1 > /home/sonyakcom/your_database_1_dump.sql

It means your database will be dumped to file ‘your_database_1_dump.sql’ on /home/sonyakcom/ folder. You can adjust with your current environment.

If your environment on Windows then your command will like this.

mysqdump -udbuser -pdbpass your_database_1 > c:\home\sonyakcom\your_database_1_dump.sql

File ‘your_database_1_dump.sql’ above will contains SQL statements about your database structure and data.

Here is the sample of contents of my your_database_1_dump.sql.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52

-- MySQL dump 10.13  Distrib 5.1.41, for Win32 (ia32)
--
-- Host: localhost    Database: your_database_1
-- ------------------------------------------------------
-- Server version	5.1.41
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
 
--
-- Table structure for table `tbl_users`
--
 
DROP TABLE IF EXISTS `tbl_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) DEFAULT NULL,
  `password` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
 
--
-- Dumping data for table `tbl_users`
--
 
LOCK TABLES `tbl_users` WRITE;
/*!40000 ALTER TABLE `tbl_users` DISABLE KEYS */;
INSERT INTO `tbl_users` VALUES (1,'sonyak','c0b137fe2d792459f26ff763cce44574a5b5ab03');
/*!40000 ALTER TABLE `tbl_users` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
-- Dump completed on 2010-02-08 15:41:29

Restore the database
Now we will learn how to restore the database to machine B. First you must login to the machine B and create database called ‘your_database_1′.

Next, you have to use mysql command to do the restore. Of course you must know the login and password of the database server on machine B. Suppose the username is ‘dbuser’ and password is ‘dbpass’.

mysql -udbuser -pdbpass < /home/data/your_database_1_dump.sql

Basically that command will tell MySQL to load file your_database_1_dump.sql from specified folder and run all SQL statements on that file. Now you will have your database fully restored on machine B.

If you have any difficulties you can send comment below or send us e-mail to info@sony-ak.com.