The Two Essential MySQL/MariaDB Shell Commands You Need to Know!

In the realm of database management, particularly with MySQL and MariaDB, there are countless shell commands, each serving unique functions. However, two commands stand out for their critical roles in database administration: mysqldump for data backup and the mysql command for data restoration or import (and all the other fancy things).

You like what you find here? A donation helps to keep this project running.

The mysqldump Command for Data Backup

The mysqldump command is a powerful utility for creating a backup of database(s). This tool generates an SQL script containing the necessary commands to recreate the database's structure and data.

For instance, the command:

mysqldump -h 127.0.0.1 -u user -p&quot;password&quot; --default-character-set=charset table --result-file=dump.sql

This command performs a backup of the table database. The parameters include:

-h 127.0.0.1: Specifies the host address.
-u user: The username for database access.
-p"password": The password (presented here as a hashed value for security).
--default-character-set=charset: Ensures the character set compatibility. utf8mb4 is often a secure choice!
--result-file=dump.sql: Directs the output to a file named backup.sql.

The mysql Command for Data Restoration

The mysql command is used to execute SQL statements, manage databases, or restore data from a dump file. The command outlined here is particularly used for restoring the table database from a dump.sql file.

For example:

mysql -h 127.0.0.1 -u user -p&quot;password&quot; table &lt; dump.sql

-h 127.0.0.1: Similar to mysqldump, this specifies the host server.
-u user and -p: These options are for providing the username and password, respectively, for database access.
table: The name of the database into which the data will be restored.
< dump.sql: This part of the command directs the mysql client to read the dump.sql file and execute the SQL statements inside. This effectively restores the data in the table database.

Conclusion

Both mysqldump and mysql are essential tools in a database administrator's toolkit. They play a crucial role in the backup and restoration process, ensuring data integrity and continuity in the event of data loss or when migrating data between servers. Using these commands effectively can safeguard critical data and streamline database management tasks.