In this post,I am going to explain the native methods that are used to generate the backup of the database.
Mysqldump is a command-line utility that is used to generate the logical backup of the MySQL database. It produces the SQL Statements that can be used to recreate the database objects and data.

By default, mysqldump command does not dump the information_schema database, performance_schema, and MySQL Cluster ndbinfo database.

For more interesting posts on MySQL, please click on INDEX

If you want to include the information_schema tables, you must explicitly specify the name of the database in the mysqldump command, also include the —skip-lock-tables option.

Backup Syntax :

mysqldump -u [username] -p [databaseName] > [filename]-$(date +%F).sql


1. Create a backup of an entire Database Management System (DBMS):

mysqldump -u root -p -h127.0.0.1 --all-databases --single-transaction --quick --lock-tables=false > full-backup-$(date +%F).sql


Create a compressed backup file

mysqldump -u root -p -h127.0.0.1 --all-databases --single-transaction --quick --lock-tables=false|gzip > full-backup-$(date +%F).sql.gz


–single-transaction: Issue a BEGIN SQL statement before dumping data from the server.

–quick: Enforce dumping tables row by row. This provides added safety for systems with little RAM and/or large databases where storing tables in memory could become problematic.

–lock-tables=false: Do not lock tables for the backup session.

2. Back up a specific database. Replace database_name with the name of the database you want to back up:

mysqldump -u username -p -h127.0.0.1 database_name --single-transaction --quick --lock-tables=false > database_name-backup-$(date +%F).sql


3. Backup more than 1 database

mysqldump -u username -p -h127.0.0.1 -databases database1 database2 --single-transaction --quick --lock-tables=false > database_name-backup-$(date +%F).sql


4. Back up a single table from any database. In the example below, table1 is exported from the database database_name:

mysqldump -u username -p -h127.0.0.1 --single-transaction --quick --lock-tables=false database_name table_name > database_name-table1-$(date +%F).sql


5. Generate Metadata of a database in MySQL

 mysqldump -u root -p -h127.0.0.1 --no-data INFRA > database_name-backup-$(date +%F).sql


Restore :

1. Restore whole instance :

/mysqldatabase/product/binary/5.7.35/bin/mysql -u root -p -h127.0.0.1 < full-backup-2021-10-03.sql


2. Restore a Single MySQL Database from a Full MySQL Dump

mysql --one-database database_name < all_databases.sql

/mysqldatabase/product/binary/5.7.35/bin/mysql -u root -p -h127.0.0.1 --one-database infra2 < full-backup-2021-10-03.sql


3. Export and Import a MySQL Database in One Command

mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name

mysqldump -u root -pmysql -h127.0.0.1 INFRA | /mysqldatabase/product/binary/5.7.35/bin/mysql -u root -pmysql -h172.168.190.35  INFRA

mysqldump -u root -pmysql -h127.0.0.1 INFRA | mysql -u root -pmysql -h172.168.190.35  INFRA


For this to happen, we need to create a user from this host with sufficient permissions

4. Restore a table from a full database dump

Extract table
sed -n -e '/DROP TABLE.*`table_name`/,/UNLOCK TABLES/p' dump.sql > mytable.sql

Import the new table
mysql -u [user] -p'password' database < mytable_restored.sql


5. Restore a table in mysql

mysql -uroot -pmysql -h127.0.0.1 INFRA < database_name-movies-2021-10-04.sql