This article shows you several practical examples of 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.
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 a 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 [mydb] structure with data onto a single dump file called mydb.sql.
(We assume you have MySQL's username called root)
mysqldump -u root -p mydb > mydb.sqlHow to Backup Multiple MySQL Databases
If you want to take a backup of multiple databases, run the following command. The following example command takes a backup of databases [mydb_one, mydb_two] structure and data into a single file called backup_db.sql.
mysqldump -u root -p --databases mydb_one mydb_two > backup_db.sqlHow 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 -p --all-databases > all_databases.sqlHow 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 [mydb] Structure into a file mydb_structure.sql.
mysqldump -u root -p -–no-data mydb > mydb_structure.sqlHow 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 [mydb] data into a file mydb_data.sql.
mysqldump -u root -p --no-create-db --no-create-info mydb > mydb_data.sqlHow 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 posts table from the database mydb into a file posts.sql.
mysqldump -u root -p mydb posts > posts.sqlHow 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 -p mydb table_one table_two > mydb_table_one_and_two.sqlHow to Backup Remote MySQL Database
The below command takes the backup of the remote server [172.16.25.126] database [mydb] into a local server.
mysqldump -h 172.16.25.126 -u root -p mydb > mydb.sqlHow 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 mydb.sql file to the mydb database.
mysql -u root -p mydb < mydb.sqlIf you want to restore a database that already exists on the targeted machine, then you must use the mysqlimport command.
mysqlimport -u root -p mdb < mydb.sqlIn the same way, you can also restore database tables, structures, and data. If you liked this article, then do share it with your friends.