MySQL database stores a different types of data. These data can be backed up or exported by using the
mysqldump command into an SQL file. The SQL files stores the database tables, and structure the data. Exported SQL files can be used to restore the database and data in other database servers. The exporting of MySQL database can be also used for simple backup methodology.
Export MySQL Database
mysqldump command can be used to export MySQL databases. The
-u is used to specify the user name which is generally
root . The
-p is used to ask for user passwords interactively. The database name is provided as the last parameter. The
> operator is used to redirect SQL output into an SQL file named
$ mysqldump -u root -p customer > customer.sql
Export Multiple MySQL Databases
We can use the mysqldump command to export multiple MySQL databases in a single execution. The database names are provided as the last parameter and separated with commas. In the following example, we export databases of customers, users, and products.
$ mysqldump -u root -p --databases customers,users,products > backup.sql
Export Only Specified Tables
The mysqldump command can be used to export specific tables from the specified database. The table names are provided after the database names by separating them with spaces. In the following example, we export tables pro and location tables from the database named customers.
$ mysqldump -u root -p --databases customers pro location > customers.sql