In this post I will explain about backing up and restoring mysql from the command line using mysqldump. You can use the mysqldump utility to transfer your MySQL database to another MySQL server. If you don’t back up your database, if that server dies, it’s a disaster. To save you a lot of time, I recommend backing up your MySQL database.
Mysqldump Command Syntax
mysqldump looks like this:
1 2 | mysqldump [options] > file.sql |
- options you can see here
- file.sql file name we will backup To use mysqldump, the MySQL server must be running.
Backup a Single MySQL Database
The most common use case of the mysqldump tool is to back up a database. We will backup the database “ex_database” with the root user and store it with the file name ex_database.sql. You use the following command:
1 2 | mysqldump -u root -p ex_database > ex_database.sql |
After entering the password and successful authentication, the backup process starts to execute, the time it takes depends on the size of your database.
Backup Multiple MySQL Databases
Back up multiple MySQL databases with a command you need to use –database option followed by the list of databases you want to backup. Each database must be separated by space.
1 2 | mysqldump -u root -p --databases database_name_a database_name_b > databases_a_b.sql |
The above command will contain both databases in the databases_a_b.sql file.
Backup All MySQL Databases
The –all-databases option will back up all databases.
1 2 | mysqldump -u root -p --all-databases > all_databases.sql |
The above command will contain all databases troing file all_databases.sql.
Backup all MySQL databases to separate files
mysqldump does not support backing up all databases into files, but we can do that using the bash for loop.
1 2 3 4 | for DB in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $DB > "$DB.sql"; done |
The above command will create separate files for each database by using the database name as the file name.
Create a Compressed MySQL Database Backup
If the database size is too large, the best idea is to compress it. Use the gzip option to compress.
1 2 | mysqldump database_name | gzip > database_name.sql.gz |
Create a Backup with Timestamp
Use timestamp to avoid duplicate file backup names.
1 2 | mysqldump database_name > database_name-$(date +%Y%m%d).sql |
Restoring a MySQL dump
You can restore the MySQL dump using mysql. The general syntax of the command is as follows:
1 2 | mysqld database_name < file.sql |
You will need to create a database to import. If the database already exists, you need to delete it first, after creating the database you can start to restore the database.
1 2 3 | mysql -u root -p -e "create database database_name"; mysql -u root -p database_name < database_name.sql |
Restore a Single MySQL Database from a Full MySQL Dump
If you have backed up all your databases with the -all-database option and you want to restore the database from a backup file that contains multiple databases, use the –one-database option like down here:
1 2 | mysql --one-database database_name < all_databases.sql |
Export and Import a MySQL Database in One Command
Instead of dumping a database and then importing the backup into another MySQL database, you can use the following:
1 2 | mysqldump -u root -p database_name | mysql -h remote_host -u root -p remote_database_name |
Before using the above command, make sure that the database exists on the remote server.
Automate Backups with Cron
Automating the database backup process is as simple as creating a cron job , which will run the mysqldump command at the specified time.
- Create .my.cnf file in the home user directory
1 2 | sudo nano ~/.my.cnf |
Add the following text to my fiel my.cnf:
1 2 3 4 | [client] user = dbuser password = dbpasswd |
- Restrict file permissions so that only your users have access to it:
1 2 | chmod 600 ~/.my.cnf |
- Create backup folder:
1 2 | mkdir ~/db_backups |
- Open crontab file:
1 2 | crontab -e |
Adding the following routine job will create a backup copy of the mydb database name every day at 3 am:
1 2 | 0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +%Y%m%d).sql |
This tutorial covers the basics, but it should be a good start for anyone who wants to learn how to create and restore a MySQL database from the command line using the mysqldump utility.
See you !!!
You can refer here: https://linuxize.com/post/how-to-back-up-and-restore-mysql-databases-with-mysqldump/#backup-all-mysql-databases-to- separate-files