The Geek Diary

CONCEPTS | BASICS | HOWTO

How to backup and restore MySQL database

By

Backing up MySQL database

Backup and restoration of MySQL databases play a very important role in a production environment, so here is a simple method to backup a MySQL database.

To backup MySQL database(s), the following command can be used:

# mysqldump --user=username --password=password --opt DatabaseName > database.sql
Note: The above command will only backup the table structure and the data from the existing database. The filename “database.sql” can be changed to suit desired needs.

The following command can be used to ensure that database(s) and tables are created and listed:

# mysql -u root -p
Enter Password:
mysql> SHOW DATABASES;
mysql> SHOW TABLES;

The following example will list the MySQL databases with SHOW DATABASES, then backup the mysql database.

# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \\g.
Your MySQL connection id is 3 to server version: 3.23.58

Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.06 sec)
mysql> exit;
# mysqldump --user=root --password=redhat --opt mysql > /backup/12042017_mysql.sql

Restoring the MySQL database

To restore a saved MySQL database, the following sequence can be used.

Note: Before the restoration, ensure the database is created first.

The following command can be used to ensure the database(s) are created and listed:

# mysql -u root -p
Enter Password :
mysql> SHOW DATABASES;
mysql> exit;

If the database is created, restore the database using:

# mysql --user=username --password=password DatabaseName < backup_filename.sql

The following example will list the MySQL databases with SHOW DATABASES, then restore the test database from a directory called backup (created under / ) and a filename 12042017_mysql.sql.

# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \\g.
Your MySQL connection id is 3 to server version: 3.23.58

Type 'help;' or '\\h' for help. Type '\\c' to clear the buffer.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.06 sec)
mysql> exit;
# mysqldump --user=root --password=redhat test < /backup/12042017_mysql.sql
-- MySQL dump 8.23
--
-- Host: localhost    Database: test
---------------------------------------------------------
-- Server version       3.23.58

If you need to create the database and tables first, the following commands can help you. Note - The database structure at your environment can be very different. So the example given below is just a basic example and can not be used for every restoration out there.

# mysql -uroot -p
Enter Password :
mysql> CREATE DATABASE DatabaseName;
mysql> USE DatabaseName;
mysql> CREATE TABLE TableName (ColumnName1 Attributes1, ColumnName2 Attributes2);

You May Also Like