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