• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to backup and restore MySQL database

By admin

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);

Filed Under: mysql

Some more articles you might also be interested in …

  1. “expect” script to provide password to mysql_config_editor
  2. How to Install MySQL Cluster on a single server for testing purposes
  3. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  4. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  5. MySQL Fails to Start Using systemctl On systemd Linux Distributions
  6. Recommended Configuration of the MySQL Performance Schema
  7. How to find the size of a MySQL database
  8. What is the purpose of “system user” in MySQL Replication
  9. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  10. MySQL Grants – Setting User Permissions On Different Tables

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary