Although there are different methods for backing up MySQL and MariaDB databases, the most common and effective one is to use a native tool that both MySQL and MariaDB make available for this purpose: the mysqldump command. As its name suggests, this is a command-line executable program that allows you to perform a complete export (dump) of all the contents of a database or even all the databases in a running MySQL or MariaDB instance. Of course it also allows partial backups, i.e. only some specific tables, or even only only a subset of all the records in a table.
The mysqldump command offers a multitude of different parameters that make it very powerful and flexible. Since having so many options can be confusing, in this post I am going to collect several of the most frequent usage examples with the most common parameters and that are most useful in the day to day life of the system administrator.
Creating a MySQL or MariaDB user and granting permissions to him to access a specific database and be able to write data on it is a very usual task that is necessary to perform each time you install a new application based on any of these database engines, like web applications running on top of LAMP stack. Whether it is a simple WordPress, or a more complex application tailor made, one way or another you will always have to complete these steps at some point before its deployment.
After subscribing a new VPS server from my hosting provider, I found that although I could access to Plesk control panel properly, I was unable to access MySQL with the same admin’s user and password as used in Plesk. As a result, I couldn’t do anything with the database from the command line or in any other way. So I decided to manually change the admin user’s password via the mysql shell. After making such change I could perfectly log in to MySQL, but nevertheless the Plesk control panel stopped working, throwing the following exception: