What is the command to export mysql database?

6.5.2 SQL Data Export and Import Wizard

Use this wizard to either export or import SQL generated from MySQL Workbench or with the mysqldump command.

Access these wizards from either the Navigator area of the sidebar, or by selecting from the main menu, and then either or .

Data Export

This tab allows you to export your MySQL data. Select each schema you want to export, optionally choose specific schema objects/tables from each schema, and generate the export. Configuration options include exporting to a project folder or self-contained SQL file, optionally dump stored routines and events, or skip table data.

Note

Alternatively, use Export a Result Set to export a specific result set in the SQL editor to another format such as CSV, JSON, HTML, and XML.

Select the schema objects to export and then configure the related options. The figure that follows shows the sakila database ready for export.

Note

Click Refresh to load the current objects.

Figure 6.19 Navigator Administration: Data Export: Object Selection

Optionally open the Advanced Options tab that allows you to refine the export operation. The next figure shows an example that adds table locks, uses replace instead of insert statements, quotes identifiers with backtick characters, and so on.

Figure 6.20 Navigator Administration: Data Export: Advanced Options

Click Start Export to begin the export process. As the next figure shows, status information indicates when the export is finished.

Figure 6.21 Navigator Administration: Data Export: Export Progress

This functionality uses the mysqldump command.

Data Import/Restore

Restore exported data from the Data Export operation, or from other exported data from the mysqldump command.

Choose the project folder or self-contained SQL file, choose the schema that the data will be imported to, or choose New to define a new schema. The following figure shows an example of an import from a dump project folder.

Figure 6.22 Navigator Administration: Data Import: Import From Disk

Note

You may only select specific data objects (tables) to import if the data export operation used project folders instead of a self-contained SQL file.

Click Start Import to begin the import process. Use the Import Progress tab to monitor the progress. Status information indicates when the import is finished and displays the log.

This article describes how to import MySQL databases and export MySQL databases. 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.

Table of Contents

• How to export a MySQL database
  • Method #1: Use phpMyAdmin
  • Method #2: Use the mysqldump table program
• Creating a new MySQL database and assigning a user
• How to import a MySQL database
  • Method #1: Use phpMyAdmin
  • Method #2: Use the mysql program
  • Troubleshooting a MySQL database import
• More Information

How to export a MySQL database

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

Method #1: Use phpMyAdmin

You can export a MySQL database using the phpMyAdmin web interface. To do this, follow these steps:

1. Log in to cPanel.

   If you do not know how to log in to your cPanel account, please see this article.

2. In the DATABASES section of the cPanel home screen, click phpMyAdmin:
   

   The phpMyAdmin administration page appears in a new window.

3. In the left pane of the phpMyAdmin page, click the database that you want to export.
4. Click the Export tab.

5. Under Export method, confirm that Quick is selected.
   

   If you are using an older version of phpMyAdmin that does not have the Quick option, follow these steps instead:
   

   • In the Export section, click Select All.
   • Select the Save as file checkbox, and then click Go. The export process runs.

6. Under Format, confirm that SQL is selected.
7. Click Go.
8. In the Save File dialog box, type the filename and select the directory where you want to save the exported database on your local computer.
9. Click Save. The export process runs.

Method #2: Use the mysqldump table program

You can export a MySQL database from the command line using the mysqldump table/database program. To do this, follow these steps:

1. Access the command line on the computer where the database is stored. For example, if the database is on another web hosting account or with another web hosting provider, log in to the account using SSH. If you have physical access to the computer, you can open a DOS or terminal window to access the command line.
2. Type the following command, and then press Enter. Replace username with your username, and dbname with the name of the database that you want to export:

   mysqldump --routines -u username -p dbname > dbexport.sql
   

   This example uses the dbexport.sql filename for the exported database, but you can name the file whatever you want.

3. Type your password at the Enter password prompt.
4. The dbexport.sql file now contains all of the data for the dbname database. If the dbexport.sql file is on a remote computer, download the file to your local computer.

Creating a new MySQL database and assigning a user

Before you can import the database, you must create a new database in cPanel and assign a user to it. To do this, follow these steps:

1. Log in to cPanel.

   If you do not know how to log in to your cPanel account, please see this article.

2. In the DATABASES section of the cPanel home screen, click MySQL® Databases:
   

3. Under Create New Database, in the New Database text box, type the name of the database.
4. Click Create Database. cPanel creates the database.
5. When the database is created, click Go Back.
6. Under Add User to Database, in the User list box, select the user that you want to add.
7. In the Database list box, select the new database.
8. Click Add.
9. Select the check boxes to grant the user specific privileges, or select the ALL PRIVILEGES check box to grant the user all permissions to the database.
10. Click Make Changes. cPanel adds the user to the database.

How to import a MySQL database

After you have created a new database in cPanel, you can import the database's contents by using phpMyAdmin or the mysql command line program.

If the exported database file contains any CREATE DATABASE statements, you must remove them or comment them out. Otherwise, the import process will fail.

Method #1: Use phpMyAdmin

You can import a MySQL database using the phpMyAdmin web interface. To do this, follow these steps:

1. Log in to cPanel.

   If you do not know how to log in to your cPanel account, please see this article.

2. In the DATABASES section of the cPanel home screen, click phpMyAdmin:
   

   The phpMyAdmin administration page appears in a new window.

3. In the left pane of the phpMyAdmin page, click the database that you want to import the data into.
4. Click the Import tab.
5. Under File to Import, click Browse, and then select the dbexport.sql file on your local computer.
6. Click Go. The import process runs.
7. The database should now contain the data that is in the dbexport.sql file.

Method #2: Use the mysql program

You can import a MySQL database from the command line using the mysql program. To do this, follow these steps:

1. Transfer the dbexport.sql file to your A2 Hosting account using SCP, SFTP, or FTP.
2. Log in to your A2 Hosting account using SSH.
3. Change to the directory where you uploaded the dbexport.sql file. For example, if you uploaded the dbexport.sql file to your home directory, type cd ~.
4. Type the following command, and then press Enter. Replace username with your username and dbname with the name of the database that you want to import the data into:

   mysql -u username -p dbname < dbexport.sql
   

5. The dbname database should now contain the data that is in the dbexport.sql file.

Troubleshooting a MySQL database import

You may receive one of the following error messages when you try to import a MySQL database using either phpMyAdmin or the mysql program:

• ERROR 1044: Access denied for user 'username1'@'localhost' to database 'username2_database'
  This error message occurs when the import file contains an SQL statement that attempts to access a database for the wrong username. Note in this example that username2 in username2_database does not match username1 in 'username1'@'localhost'. You must edit the import file and change username2 to your new username1.
• ERROR 1049: Unknown database 'username_database'
  This error message occurs when the target database does not exist. Make sure you create the database first as described above, and then try to it import again.
• ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'username_database' at line x.
  This error message occurs when the import file does not contain backup data for a database or there is a MySQL syntax error in the file. Alternatively, the import file may be altered, corrupt, or in an unsupported format. (Import files must contain SQL statements; other file formats such as CSV do not work with the mysql program.) Try exporting the database again, and then try to import it.
• ERROR 1227: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
  This error message occurs when the import file contains one or more SQL statements that require superuser privileges (such as SET GLOBAL or CREATE DEFINER statements). In some cases, you can just delete these statements from the .sql file and rerun the import process. For example, CREATE DATABASE statements can be safely removed, because you should have already created the database in cPanel. If you require MySQL superuser access, however, you should consider migrating your account to a VPS or Dedicated server, where you have complete control over the environment.

  For more information about MySQL user privileges on shared servers, please see this article.

More Information

• For more information about the mysqldump table/database command line program, please visit http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html.
• For more information about the mysql command line program, please visit http://dev.mysql.com/doc/refman/5.1/en/mysql.html.
• For more information about phpMyAdmin, please visit http://www.phpmyadmin.net.

How do I export a database in MySQL using terminal?

How do I export and import MySQL database?

How do I export MySQL results?

How do I export a database in Linux?