Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog How to Backup and Restore MySQL Database with mysqldump Command

How to Backup and Restore MySQL Database with mysqldump Command

In this article, you will get some information on how to use mysqldump command to backup and restore mysql database.

Use mysqldump Command for Logical Backups

To use 'mysqldump' tool, follow the syntax codes below:

For the sake of clarity, we will use two databases; 'sample_db_1' and 'sample_db_1'. We will also use 'james' as the MySQL user in all examples. Each sample database contains two tables ('table_1' and 'table_2').

Backing up a single MySQL table

$ mysqldump [options] db_name [tbl_name ...] > backup_file_name.sql

Backing up a Single MySQL Database

$ mysqldump [options] --databases db_name ... > backup_file_name.sql

Backing up All MySQL databases

$ mysqldump [options] --all-databases  > backup_file_name.sql

In the above examples, the output of the mysqldump command is directed to a file name 'backup_file_name.sql'.

To check if the backup was created successfully, use the command below to list the files:

$ ls -a

We have used the '-p' option to allow MySQL to prompt us for a password. This ensures that our password is not logged on the server for security purposes.

Restoring MySQL Database with mysqldump

You can restore any MySQL database created using mysqldump command using the syntax below:

$ mysql -u username -p database_name < backup_file_name.sql

Appending date and time on the backup file name

It is advisable to include the name of the database on the filename. For instance, to backup our 'sample_db_1' database we will use the syntax below:

$ mysqldump -u james -p --databases sample_db_1> $(date "+%Y_%m_%d_%H_%M_%S")_sample_db_1.sql

Run the command below to confirm the changes:

$ ls –a

Logical MySQL backups using mysqldump only works when a database server is up and running. Also, mysqldump utility can be slow for large databases. So in case you are managing a bulky database or you can't access a MySQL server, taking physical backups is the ideal choice. For details about Physical MySQL Backups, please go to How to Back Up MySQL Database on Alibaba Cloud ECS Ubuntu 16.04.

Related Blog Posts

Using XtraBackup for Physical Backup and Restoration

This article mainly introduces the principles of MySQL database backup and restoration, so that you can better understand the ApsaraDB for RDS backup and restoration mechanisms. Specifically, we will be exploring how to create physical and logical backups on ApsaraDB for RDS using mysqldump, Percona XtraBackup, and innobackupex.

ApsaraDB for RDS uses mysqldump to logically back up data to the MySQL database. XtraBackup can be used for a full physical backup at the instance level.

Implementing Effective MySQL Database Backup Mechanisms

This document covers a variety of ways for you to synchronize data, focusing on restoring data from ApsaraDB for RDS for MySQL to a local self-built database.

  1. If there are no add, delete and edit operations to data on the RDS, you can generate a physical backup or logical backup, and then import the physical backup or logical backup data to your self-built database through Xtrabackup or mysqldump.
  2. If there are add, delete and edit operations to the database after the physical backup has been generated, you need to append the subsequent binlog file after restoring the physical backup. You can get the binlog file through the API/console to append the incremental data.

Related Documentation

Backup Mode - Database Backup

DBS offers features such as full backup, incremental backup, and data recovery. This section describes the different types of backup and how to select a backup type that best suits your needs.

Use mysqldump to migrate MySQL data

mysqldump can be used to migrate MySQL data. The disadvantage of mysqldump is that the service downtime is long. Use mysqldump if the data volume is small or if a long service downtime is allowed.

As RDS is fully compatible with MySQL, the procedure for migrating local databases to an RDS instance is similar to the procedure for migrating data from one MySQL server to another.

Related Products

Database Backup

A simple, reliable, cost-efficient backup service for continuous data protection. Performs incremental backups in real time and lowers RPOs to several seconds.

ApsaraDB RDS for MySQL

An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities

Database Celebration Offers

Migrate to the Cloud with 450K Database Instances Running | Get up to $100 Rebate and Free Backup Instances

mysqldump backup database mysqldump restore all databases mysqldump database
0 0 0
Share on

Read previous post:

How to Set up an OpenConnect VPN Server

Read next post:

How to Backup and Restore RDS Database

Alibaba Clouder

2,599 posts | 764 followers

You may also like

Comments

Alibaba Clouder

2,599 posts | 764 followers

Related Products

More Posts by Alibaba Clouder

See All