MySQL / MariaDB: backing up and restoring a database
What's in the box?
The aim of this tutorial is to show you several practical examples of how to back up and restore a MySQL or MariaDB database, using a utility native to these tools: mysqldump. For the sake of completeness, we'll also look at two other commands on the Linux server (Debian in my case).
Before getting started, a few words about mysqldump: this is a command-line program used to dumps MySQL databases, either locally or remotely, by creating a flat file in .SQL format.
I assume that you have already installed MySQL (or MariaDB) on the Linux system, and that you have administrative privileges on this instance. I had fun putting lots of different examples to cover as many needs as possible.
1. How do I back up a MySQL database under Linux?
Before showing you some examples, please note that the mysqldump command is used in the following way:
mysqldump -u -p >Here is the meaning, to be more precise:
- Nom d’utilisateur : un utilisateur MySQL valide, avec des droits sur la base de données à sauvegarde
- Mot de passe : le mot de passe de cet utilisateur (sans espace entre le mot de passe et l’option « -p »)
- Nom de la base de données : nom de la base de données à sauvegarde
- Fichier de sortie : le nom du fichier de sortie en .sql
I. How do I back up a single MySQL database?
To backup a single database, use the following command. The command will save the structure of the "wordpress" database (i.e. tables, etc.) and its data in a file called wordpress.sql. Authenticate with the MySQL "root" account and the password "Password".
mysqldump -u root -pPassword wordpress > wordpress.sqlII. How do I back up several MySQL databases?
If you want to back up several databases, run the following command. The example below backs up the structure and data of two databases: wordpress1 and wordpress2. However, there will be only one output file: wordpress1_wordpress2.sql. Note the addition of the " -databases "compared with the previous example.
mysqldump -u root -pPassWord --databases wordpress1 wordpress2 > wordpress1_wordpress2.sqlIII. How do I back up all MySQL databases?
If you want to make a backup of all the databases in a MySQL / MariaDB instance, the " -all-database The output file will simply be named "all-databases.sql". The output file will simply be named "all-databases.sql", but you can customize its name.
mysqldump -u root -pPassWord --all-databases > all-databases.sqlIV. How do I save only the structure of a MySQL database?
Sometimes, you want to save the structure of a database without actually recovering the data. For example, this is a way of cloning a database (restoring the backup afterwards) without having to purge the data. Here again, mysqldump offers a suitable option: " -no-data" . The command below exports the structure of the "wordpress" database to a file wordpress_withoutdata.sql.
mysqldump -u root -pPassWord --no-data wordpress > wordpress_withoutdata.sqlV. How do I back up only MySQL database data?
Conversely, we can save only the data at the time of dumping. In this case, we need to combine two options: " -no-create-info "and " -no-create-db" . This prevents the output .SQL file from including the SQL queries used to create the database and its tables.
The command below saves the "wordpress" database data in the "wordpress_data.sql" file.
mysqldump -u root -pPassWord --no-create-db --no-create-info wordpress > wordpress_data.sqlVI. How to save a single database table?
The mysqldump tool can save a single specific table in your database. If we take the example of the "wordpress" database, which may correspond to a WordPress site, we can save the table corresponding to posts. It's called "posts", and there's always a prefix, so in this example "wp2022_posts". We specify the database name, then the table name.
mysqldump -u root -pPassword wordpress wp2022_posts > wordpress_articles.sqlVII. How do I back up several tables in a database?
In the same style, you can select several tables in the same database. For example, the items table "wp2022_posts" and the users table "wp2022_users". In the command, separate the name of each table with a space.
mysqldump -u root -pPassword wordpress wp2022_posts wp2022_users > wordpress_tables.sqlVIII. How do I back up a MySQL database remotely?
Before moving on to restoring a database, let's look at the ultimate example! This time, we're backing up a database located on a remote server (which must allow remote MySQL connections!). Up to now, all examples have involved local backup. Let's imagine we want to back up the "wordpress" database on machine "10.10.10.10".
mysqldump -h 10.10.10.10 -u root -pPassword wordpress > wordpress.sql2. How do I restore a MySQL database?
So far, we've seen how to back up, back up and back up again a database. When you have a "SQL" file, i.e. a dump, like those generated above, how do you restore it? That's what we're going to look at right now... With the "mysql" command, here's the syntax that applies:
mysql -u -p <In the example above, you see the direction of the arrow (redirector) is different, because here we restore, so we import.
There are several ways of restoring a database. I recommend that you create an empty database on the target MySQL / MariaDB server (to have the right rights), and restore the database using the msyql command. For example, the following command will restore the wordpress.sql file to the wordpress database.
mysql -u root -pPassword wordpress < wordpress.sqlIf you want to restore a database that already exists on the machine and want to overwrite it, then you'll need to use the mysqlimport command.
mysqlimport -u root -pPassword wordpress < wordpress.sqlAfter reading this article from the tutorial box, you'll be able to back up and restore a MySQL / MariaDB database from the command line!
Resources :
