This article shows you several practical examples on how to perform various backup operations of MySQL/MariaDB databases using the mysqldump command and also we will see how to restore them with the help of mysql and mysqlimport command in Linux.
mysqldump is a command-line client program, it is used to dump local or remote MySQL databases or collections of databases for backup into a single flat file.
We assume that you already have MySQL installed on the Linux system with administrative privileges and we believe that you already have a small amount of knowledge of MySQL.
If you don’t have MySQL installed or don’t have any exposure to MySQL then read our articles below.
- How to Install MySQL on RHEL-based Distributions
- How to Install MySQL on Rocky Linux and AlmaLinux
- How to Install MySQL in Ubuntu Linux
- How to Install MySQL on Debian
- 20 MySQL (Mysqladmin) Commands for Database Administration in Linux
- 12 MySQL/MariaDB Security Best Practices for Linux
How to Backup MySQL Database in Linux?
To take a backup of MySQL databases or databases, the database must exist in the database server and you must have access to it. The format of the command would be.
# mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
The parameters of the said command are as follows.
- [username] : A valid MySQL username.
- [password] : A valid MySQL password for the user.
- [database_name] : A valid Database name you want to take backup.
- [dump_file.sql]: The name of the backup dump file you want to generate.
How to Backup a Single MySQL Database?
To take a backup of a single database, use the command as follows. The command will dump the database [rsyslog] structure with data onto a single dump file called rsyslog.sql.
# mysqldump -u root -ptecmint rsyslog > rsyslog.sql
How to Backup Multiple MySQL Databases?
If you want to take backup of multiple databases, run the following command. The following example command takes a backup of databases [rsyslog, syslog] structure and data into a single file called rsyslog_syslog.sql.
# mysqldump -u root -ptecmint --databases rsyslog syslog > rsyslog_syslog.sql
How to Backup All MySQL Databases?
If you want to take a backup of all databases, then use the following command with the option –all-database. The following command takes the backup of all databases with their structure and data into a file called all-databases.sql.
# mysqldump -u root -ptecmint --all-databases > all-databases.sql
How to Backup MySQL Database Structure Only?
If you only want the backup of the database structure without data, then use the option –no-data in the command. The below command exports database [rsyslog] Structure into a file rsyslog_structure.sql.
# mysqldump -u root -ptecmint -–no-data rsyslog > rsyslog_structure.sql
How to Backup MySQL Database Data Only?
To backup database data without structure, use the option –no-create-info with the command. This command takes the database [rsyslog] Data into a file rsyslog_data.sql.
# mysqldump -u root -ptecmint --no-create-db --no-create-info rsyslog > rsyslog_data.sql
How to Backup a Single Table of Database?
With the below command you can take a backup of a single table or specific tables of your database. For example, the following command only takes a backup of the wp_posts table from the database wordpress.
# mysqldump -u root -ptecmint wordpress wp_posts > wordpress_posts.sql
How to Backup Multiple Tables of Database?
If you want to take a backup of multiple or certain tables from the database, then separate each table with space.
# mysqldump -u root -ptecmint wordpress wp_posts wp_comments > wordpress_posts_comments.sql
How to Backup Remote MySQL Database
The below command takes the backup of the remote server [172.16.25.126] database [gallery] into a local server.
# mysqldump -h 172.16.25.126 -u root -ptecmint gallery > gallery.sql
How to Restore MySQL Database?
In the above tutorial, we have seen how to take the backup of databases, tables, structures, and data only, now we will see how to restore them using the following format.
# # mysql -u [username] –p[password] [database_name] < [dump_file.sql]
How to Restore Single MySQL Database
To restore a database, you must create an empty database on the target machine and restore the database using msyql command. For example, the following command will restore the rsyslog.sql file to the rsyslog database.
# mysql -u root -ptecmint rsyslog < rsyslog.sql
If you want to restore a database that already exists on the targeted machine, then you will need to use the mysqlimport command.
# mysqlimport -u root -ptecmint rsyslog < rsyslog.sql
In the same way, you can also restore database tables, structures, and data. If you liked this article, then do share it with your friends.
49 thoughts on “MySQL Backup and Restore Commands for Database Administration”
how to take only incremental mysql database backup…
Incremental backup can be done in two ways, one is using script and another way is doing replication. To know more on how to setup mysql replication you can referrer our guide at How to Setup MySQL Replication.
I am using follwoing command, but it generates #1049 unknown database error.
mysql -u [username] –p[password] [database_name] < [dump_file.sql]
Do you have any idea why?
Thanks in adavnce!!
This means you’re trying to connect to a database that does not present in the MySQL. Please check your database name and try again.
HI Ravi ,
I backup all the DBes using this command mysqldump -u root -ptecmint –all-databases > all-databases.sql
, How to sliptup this file now .
Please advice .
You can’t and there is no option to split it..
Hello I am new to mysql i Have to create the mysql user with proper privileges to restore the dump what privileges should i give for that user. Can you please help me.
There´s yet a backup that include: tables, procedures, functions, etc… all of estructures:
mysqldump -u user name_of_database –routines > backupFullStructures.sql
I´ve used this
How to restore only data to existing database….
i tried following…bt does’t worked…
mysqlimport -u root -proot ml < c:\ml.sql
this was nice
thanks ravi it’s very helpfull…
can u pls tell me how to give the access to other host??
From the mysql shell type the following command to give access to other or any remote machine.
i wnt to restore database backed up from windows in fedora can i do this?
Do I must change folder in linux before mysqldump (maybe /var/..)
# mysqldump -u username -p –all-databases > dump.sql
And then restore them all using:
# mysql -u username -p < dump.sql
No need to change any folder location before dumping databases.
how to restore whole database(including all the databases) in one go. because i took backup of all database in single .sql file ,now how to restore them via command line (linux)…
Please check comments, already answered..