The author selected the Free and Open Source Fund to receive a donation as part of the Write for DOnations program. Show
IntroductionImporting and exporting databases is a common task in software development. You can use data dumps to back up and restore your information. You can also use them to migrate data to a new server or development environment. In this tutorial, you will work with database dumps in MySQL or MariaDB (the commands are interchangeable). Specifically, you will export a database and then import that database from the dump file. PrerequisitesTo import or export a MySQL or MariaDB database, you will need:
Note: As an alternative to manual installation, you can explore the DigitalOcean Marketplace’s MySQL One-Click Application. Step 1 — Exporting a MySQL or MariaDB DatabaseThe Use
The command will produce no visual output, but you can inspect the contents of Run the following command:
The top of the file should look similar to this, showing a MySQL dump for a database named
If any errors occur during the export process, Step 2 — Importing a MySQL or MariaDB DatabaseTo import an existing dump file into MySQL or MariaDB, you will have to create a new database. This database will hold the imported data. First, log in to MySQL as root or another user with sufficient privileges to create new databases:
This command will bring you into the MySQL shell prompt. Next, create a new database with the following command. In
this example, the new database is called
You’ll see this output confirming the database creation.
Then exit the MySQL shell by pressing
If the command runs successfully, it won’t produce any output. If any errors occur during the process, ConclusionIn this tutorial you created a database dump from a MySQL or MariaDB database. You then imported
that data dump into a new database. To learn more about MySQL, check out our MySQL resource page. To learn more about MySQL queries, check out our tutorial, “An Introduction to Queries in MySQL”. How do I export MySQL database from MySQL command line?Command Line. Log into your server via SSH.. Use the command cd to navigate to a directory where your user has write access. ... . Export the database by executing the following command: mysqldump --add-drop-table -u admin -p`cat /etc/psa/.psa.shadow` dbname > dbname.sql. ... . You can now download the resulting SQL file.. How do I export an entire database?Method 1: Using phpMyAdmin. Step 1: Logging in to cPanel. Log in to cPanel & select the phpMyAdmin option from the databases section. Select the database you want to export and click on the Export tab. ... . Step 2: Exporting Using cPanel. In the export tab, you can select a particular column or an entire table to export.. How do I export and import MySQL database?Method #1: Use phpMyAdmin. Log in to cPanel. ... . In the DATABASES section of the cPanel home screen, click phpMyAdmin: ... . In the left pane of the phpMyAdmin page, click the database that you want to export.. Click the Export tab.. Under Export method, confirm that Quick is selected. ... . Under Format, confirm that SQL is selected.. How do I export a MySQL database to another server?To copy a MySQL database from a server to another, you use the following steps: Export the database on the source server to a SQL dump file. Copy the SQL dump file to the destination server. Import the SQL dump file to the destination server.
|