It happened - you need to restore a database from a backup made earlier. Well, it happens.
There are two methods:
- from a database dump - if the backup was made via mysqldump
- from a database archive - if the backup was made by simply archiving the database directory
Method 1: from a dumpThis method assumes you have a backup file made with the mysqldump utility. This file is, no more no less, in SQL format - i.e. it simply contains the commands to recreate the database using regular MySQL commands.
1) If you backed up all databases on the MySQL server - open the backup file and delete all data that doesn't relate to the database being restored. If your backup is of a specific database - i.e. the one you're going to restore, and there's nothing else inside it - move on.
2) Open the backup file and make sure that at its beginning there's a definition of the database into which the data will be restored.
CREATE DATABASE IF NOT EXISTS `mydatabase`;
USE mydatabase;
-- MySQL dump 10.13 Distrib 5.1.56, for debian-linux-gnu (i486)
--
-- Host: localhost Database: mydatabase
-- ------------------------------------------------------
-- Server version 5.1.56-0.dotdeb.0
/*!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 `mytable`
--
DROP TABLE IF EXISTS `mytable`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `mytable` (
...
...
...
Pay attention to the first line: add it (most likely there's no such line in your file), replacing the database name with the one you need:
CREATE DATABASE IF NOT EXISTS `mydatabase`;
This line lets you recreate the database if it doesn't exist at all.
Do you see "USE mydatabase;" as the second line? Make sure such a command is present (most likely it isn't, so add it) and instead of "mydatabase" specify the name of the database being restored.
Now save the file and go back to the shell.
3) Restoring:
$ mysql -u root -p < mydatabase.bak
where instead of "mydatabase.bak" you specify the name of the backup file (the one you just edited).
That's basically it.
Method 2: from an archiveThis works if you backed up the database by simply copying its directory and archiving it into a tar file (or without archiving).
1) Stop the MySQL server.
for Debian/ubuntu:
$ sudo /etc/init.d/mysql stop
for CentOS/redhat:
$ sudo /etc/init.d/mysqld stop
for FreeBSD:
$ sudo /usr/local/etc/rc.d/mysql-server stop
2) Extract the database from the tar file (if it's packed into a tar archive)
$ tar -xf mydatabase.bak.tar
3) Copy the extracted archive (along with all the files inside it) to the path where all MySQL databases are stored. It's better to delete the old directory first (the database directory, not the whole MySQL directory, of course).
4) Check the attributes - to make sure the database owner is MySQL. It's easier to just run the command to apply the attributes:
$ sudo chown -R mysql:mysql mydatabase
where mydatabase is the name of the database directory (assuming you're in the MySQL databases directory).
5) Start the MySQL server back up.
for Debian/ubuntu:
$ sudo /etc/init.d/mysql start
for CentOS/redhat:
$ sudo /etc/init.d/mysqld start
for FreeBSD:
$ sudo /usr/local/etc/rc.d/mysql-server start
That's it - the database is restored.
Applies to: MySQL 5.x+
Comments