Taking backup of databases is imperative to keep up with the successful operations. If you seem to have a problem backing up and restoring your MySQL database, then it is time to have the best program and utility in use. The most popular command for the backup of MariaDB is mysqldump. A utility tool with lots of features inside for backup and it costs nothing much. The working of the mysqldump is not that hard, but to be more efficient, it is good to understand the complete procedure.
Introducing mysqldump
Included with MariaDB server (an open-source database), you do not need to resume your MariaDB services while taking backup. It takes backup of databases and recovers the schema in tables; further, generate a data text file at the exterior of MariaDB. A text file consists of structured query language statements that are vital to reconstruct the complete database. Inside the file that is produced by mysqldump, there are CREATE TABLE alongwith a myriad of INSERT statements of a structured query language.
This guide contains the instructions that need to apply to both MySQL and MariaDB. Each step is described in depth so that one can make efficient backups. Before you start with this, you need to have MariaDB’s MySQL and database user installed and get them in working.
For database export, start with a syntax
mysqldump -u website_backup -p -x -A > /databases/backups/web_db1.sql
The insertion of (-u website_backup -p) indicates MariaDB that the utility is to be run through the user website_backup. Before the process begins, a user further induced to enter the secret code. That password is required to be added thereafter. By the way, you might entice to use the root, which is the username by default, it’s recommended to create a special user just like we are using in this. In case, if core dump executes through a script, then you can change it to -pmypwd. Make sure there is no need to give a gap in between -p and password mypwd. Moving on the next, the -x containing MariaDB starts locking up all the tables. This lock will not be set free until the whole process accomplish.
There is an addition of -e option to hold together all INSERT declarations for tables. With this add on insert option, the size of the dump file will become compact and helps make the upcoming restores run faster. On the other side, -A option defines that the databases are ready for export. At last, the greater-than is diverted through the standard output to the file mentioned after it.
The above example for the backup of MariaDB’s MySQL is not longhand. The protocol is to be moving with longer letters, not the single. The reason is that the single letter code has a chance to deprecate and will no longer accessible later.
So, the syntax is
mysqldump –user=website_backup –password –lock-tables –all-databases >/databases/backups/web_db1.sql
Whereas the longer letters are simple to recall. If you want the execution of the backup by a shell script once more time, then enter –password=mypwd
Single Database Backup
Backing up numerous databases simultaneously might lead to a dump file of large size. This further takes time to finish the backup process and make restoration difficult to manage later. Instead, a user can prefer to make a series of backups for multiple files of compact size based on each database. Thereafter, you can take a backup of larger and critical databases and especially ones that change frequently so that your interaction with the user cannot be depreciated.
Syntax
mysqldump –user= website_backup –password –lock-tables –databases web_db1 > /databases/backups/web_db1.sql
The syntax for exporting one database is mentioned above. The difference you see in this syntax is the replacement of the -A option with the -B. On the other hand, if you are supposed to export the multiple databases, then enter the names of the databases after the option.
Sharing the example like, -B database1 database2
Dump Tables in MySQL
A user may require taking a backup of data containing in many tables instead of the whole database, especially for the large databases. You could go for the backup of specific individual tables in which the data changes frequently. The syntax that is used to take a backup of a single table is given below.
Syntax
mysqldump –user=website_backup –password –lock-tables web_db1 table1 > /databases/backups/web_db1.sql
Note that the –databases is no required to be entered, only place the name of the database and table. Also, if you would like to take a backup of multiple tables, then you must enter table names just after the name of the database.
Sharing the example like, db1 t1 t2
Final Words
We can say that mysqldump is simple to manage. Start thinking about the importance of your data if you do not want it to be lost and end up costing you a lot. As a beginner, you can consider using a test database for practice until you become confident in taking a backup of your data inside the database. It is time to enhance your skills or take the help of an expert who is proficient in working with mysqldump.
