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.
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.
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.
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.
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.
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.
A simple, reliable, cost-efficient backup service for continuous data protection. Performs incremental backups in real time and lowers RPOs to several seconds.
An on-demand database hosting service for MySQL with automated monitoring, backup and disaster recovery capabilities
Migrate to the Cloud with 450K Database Instances Running | Get up to $100 Rebate and Free Backup Instances
AlenaS - February 8, 2022
Alibaba Clouder - August 2, 2019
francisndungu - August 2, 2018
Alibaba Clouder - August 1, 2019
Alibaba Clouder - May 28, 2019
Alibaba Clouder - July 31, 2019
More Posts by Alibaba Clouder