Commands, Database

How to import and export a MySQL database?

How to import and export a MySQL database?

This blog describes how to import MySQL databases and export MySQL databases. So You can import and export databases for a variety of scenarios, including:

- Transferring a MySQL database from one web hosting account or provider to another.
- Importing a third-party MySQL database.
- Backing up a MySQL database.

How to export a MYSQL database?

You can export a MySQL database to a file by using phpMyAdmin or the mysqldump database command line program.

Export : To Export a database, open up terminal, making sure that you are not logged into MySQL and type,

mysqldump -u [username] -h [hostname] -p [database name] > [database name].sql;

Ex : mysqldump -u root -h localhost -p magento233 > magento233dump.sql;

Once you execute this command, you will be prompted for your database password and type in the password and hit enter. Your database will now start exporting. When it is done, you will see the command prompt again. If it is a large database, this may take a few minutes.

The database that you selected in the command will now be exported to your path.

Import : To import a database, first create a new blank database in the MySQL shell to serve as a destination for your data.

CREATE DATABASE newdatabase;

Ex : CREATE DATABASE magento234;

Then log out of the MySQL shell and type the following on the command line:

mysql -u [username] -p newdatabase < [database name].sql;

Ex : mysql -u root -p magento234 < magento233dump.sql;

Once you execute this command, you will be prompted for your database password. Type it in and hit enter. Your database will now import. It may take a few minutes if you have a large database. When the import is done, you will be returned to the command prompt.

Now, your chosen database has been imported into your destination database in MySQL.

Check all existing databases size in MB : To check the existing databases size run below command after Login MYSQL Shell.

SELECT table_schema AS "Database",ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES GROUP BY table_schema;

🙂 Happy Coding. Keep Liking & Sharing 💻
DevDocs

So Home
Tagged , , , , , , ,