and I Need to Upload a Db on the Server The sql File

How to import and consign a MySQL database

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 1 spider web hosting business relationship or provider to another.
• Importing a 3rd-party MySQL database.
• Bankroll upwards a MySQL database.

Table of Contents

• How to consign a MySQL database
  • Method #1: Utilize phpMyAdmin
  • Method #two: Use the mysqldump tabular array program
• Creating a new MySQL database and assigning a user
• How to import a MySQL database
  • Method #1: Use phpMyAdmin
  • Method #2: Apply 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: Utilize phpMyAdmin

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

1. Log in to cPanel.

   If you lot do not know how to log in to your cPanel business relationship, please encounter this article.

2. In the DATABASES department of the cPanel dwelling screen, click phpMyAdmin:

   

   The phpMyAdmin assistants folio 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 take the Quick selection, follow these steps instead:
   

   • In the Consign section, click Select All.
   • Select the Save as file checkbox, and and then click Become. The consign process runs.
6. Under Format, confirm that SQL is selected.
7. Click Become.
8. In the Save File dialog box, type the filename and select the directory where you desire to salve the exported database on your local computer.
9. Click Salvage. The consign process runs.

Method #2: Use the mysqldump tabular array programme

You tin consign a MySQL database from the command line using the mysqldumptable/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 spider web hosting account or with another web hosting provider, log in to the account using SSH. If you have physical admission to the computer, you can open a DOS or terminal window to access the control line.
2. Type the following command, and and then press Enter. Replace username with your username, and dbname with the name of the database that yous want to export:

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

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

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

Creating a new MySQL database and assigning a user

Before yous 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. Nether Create New Database, in the New Database text box, type the proper name of the database.
4. Click Create Database. cPanel creates the database.
5. When the database is created, click Become Back.
6. Under Add together 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 bank check box to grant the user all permissions to the database.
10. Click Brand Changes. cPanel adds the user to the database.

How to import a MySQL database

After yous accept created a new database in cPanel, you tin import the database'southward contents by using phpMyAdmin or the mysql command line program.

If the exported database file contains whatever CREATE DATABASE statements, y'all must remove them or comment them out. Otherwise, the import process volition neglect.

Method #1: Use phpMyAdmin

Y'all tin import a MySQL database using the phpMyAdmin web interface. To practice this, follow these steps:

1. Log in to cPanel.

   If yous do not know how to log in to your cPanel business relationship, please see this article.

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

   

   The phpMyAdmin administration folio appears in a new window.

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

Method #two: Use the mysql programme

You can import a MySQL database from the command line using the mysql plan. 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 business relationship using SSH.
3. Change to the directory where you uploaded the dbexport.sql file. For example, if yous uploaded the dbexport.sql file to your abode directory, blazon cd ~.
4. Type the post-obit command, and then printing Enter. Replace username with your username and dbname with the proper name of the database that you want to import the data into:

                 mysql -u                username                -p                dbname                < dbexport.sql            

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

Troubleshooting a MySQL database import

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

• ERROR 1044: Admission denied for user 'username1'@'localhost' to database 'username2_database'
  This mistake message occurs when the import file contains an SQL argument that attempts to access a database for the wrong username. Notation in this example that username2 in username2_database does non match username1 in 'username1'@'localhost'. You must edit the import file and change username2 to your new username1.
• Mistake 1049: Unknown database 'username_database'
  This mistake message occurs when the target database does non exist. Make sure you lot create the database offset as described higher up, and so try to it import again.
• Error 1064: Yous have an error in your SQL syntax; check the transmission that corresponds to your MySQL server version for the right syntax to use nearly 'username_database' at line x.
  This error message occurs when the import file does not contain fill-in data for a database or there is a MySQL syntax error in the file. Alternatively, the import file may be contradistinct, corrupt, or in an unsupported format. (Import files must contain SQL statements; other file formats such as CSV do not piece of work with the mysql program.) Try exporting the database again, and and then attempt to import it.
• Error 1227: Access denied; you need (at to the lowest degree ane of) the SUPER privilege(south) for this operation
  This error bulletin occurs when the import file contains i or more SQL statements that require superuser privileges (such as SET GLOBAL or CREATE DEFINER statements). In some cases, y'all can only delete these statements from the .sql file and rerun the import process. For example, CREATE DATABASE statements can be safely removed, because you should accept 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 nigh MySQL user privileges on shared servers, please run across this commodity.

More Information

• For more data virtually the mysqldump table/database command line plan, delight visit http://dev.mysql.com/dr./refman/v.i/en/mysqldump.html.
• For more data about the mysql command line program, please visit http://dev.mysql.com/doc/refman/5.ane/en/mysql.html.
• For more information most phpMyAdmin, please visit http://www.phpmyadmin.net.

stanfillsuname.blogspot.com

Source: https://www.a2hosting.co.id/kb/developer-corner/mysql/import-and-export-a-mysql-database

Share this post