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

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • 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. System Tuning for MySQL Server
  2. “Access denied for user ‘username’@’hostname’ (using password: YES)” – Error while connecting MySQL with PHP
  3. How to Add Data Nodes to MySQL Cluster without Restarting the Cluster (version 6.4 and later)
  4. MySQL – How to Set Maximum Rates for Connections and Queries
  5. How To Reset MySQL 8.0 Root Password On Windows
  6. How to Install MySQL Cluster on a single server for testing purposes
  7. Understanding MySQL Query Cache
  8. Examples of mysqldump partial backups
  9. MySQL : How To Find the Slowest Queries
  10. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE

You May Also Like

Primary Sidebar

Recent Posts

  • “aws s3 presign” Command Examples
  • “aws s3 mv” Command Examples
  • “aws s3 mb” Command Examples
  • “aws s3 ls” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright