Export MySQL Database Using Command Line

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

The 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 customer.sql .

$ 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

Leave a Comment