How to Properly Upload a Deleted Database in Phpadmin

How to import and consign a MySQL database

This commodity describes how to import MySQL databases and export MySQL databases. You can import and export databases for a multifariousness of scenarios, including:

• Transferring a MySQL database from ane spider web hosting business relationship or provider to some other.
• Importing a 3rd-party MySQL database.
• Backing up a MySQL database.

Table of Contents

• How to consign a MySQL database
  • Method #i: Utilise phpMyAdmin
  • Method #ii: Use the mysqldump table program
• Creating a new MySQL database and assigning a user
• How to import a MySQL database
  • Method #one: Use phpMyAdmin
  • Method #2: Employ the mysql program
  • Troubleshooting a MySQL database import
• More than Data

How to consign a MySQL database

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

Method #ane: Utilise phpMyAdmin

Y'all 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 business relationship, delight come across this article.

2. In the DATABASES department of the cPanel habitation 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 consign.
4. Click the Export tab.

5. Nether Consign method, confirm that Quick is selected.
   

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

   • In the Export section, click Select All.
   • Select the Relieve as file checkbox, and then click Go. The export process runs.
6. Under Format, confirm that SQL is selected.
7. Click Get.
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 procedure runs.

Method #two: Use the mysqldump table program

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

1. Admission 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 business relationship using SSH. If you accept physical access to the computer, y'all tin open up a DOS or terminal window to access the command line.
2. Type the following command, then press Enter. Supervene upon 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 example uses the dbexport.sql filename for the exported database, but you tin proper noun the file whatever you want.

3. Type your countersign 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 reckoner.

Creating a new MySQL database and assigning a user

Earlier y'all tin 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 lot do not know how to log in to your cPanel business relationship, please come across this commodity.

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 Get Back.
6. Under Add together User to Database, in the User list box, select the user that you want to add together.
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 cheque 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

Subsequently you take created a new database in cPanel, you tin import the database'southward contents by using phpMyAdmin or the mysql control line program.

If the exported database file contains any CREATE DATABASE statements, yous must remove them or annotate them out. Otherwise, the import process volition fail.

Method #one: Use phpMyAdmin

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

1. Log in to cPanel.

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

2. In the DATABASES section of the cPanel abode 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 yous desire to import the data into.
4. Click the Import tab.
5. Under File to Import, click Browse, and so select the dbexport.sql file on your local estimator.
6. Click Become. The import process runs.
7. The database should at present incorporate the data that is in the dbexport.sql file.

Method #2: Apply the mysql program

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

1. Transfer the dbexport.sql file to your A2 Hosting business relationship using SCP, SFTP, or FTP.
2. Log in to your A2 Hosting account using SSH.
3. Modify to the directory where yous uploaded the dbexport.sql file. For example, if y'all uploaded the dbexport.sql file to your dwelling house directory, type cd ~.
4. Type the following control, so press Enter. Supplant username with your username and dbname with the proper noun of the database that you want to import the information into:

                 mysql -u                username                -p                dbname                < dbexport.sql            

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

Troubleshooting a MySQL database import

Y'all may receive one of the following error messages when you endeavour to import a MySQL database using either phpMyAdmin or the mysql programme:

• Error 1044: Access denied for user 'username1'@'localhost' to database 'username2_database'
  This error bulletin occurs when the import file contains an SQL statement that attempts to access a database for the wrong username. Annotation in this example that username2 in username2_database does not match username1 in 'username1'@'localhost'. You lot must edit the import file and change username2 to your new username1.
• Error 1049: Unknown database 'username_database'
  This error bulletin occurs when the target database does not exist. Brand sure yous create the database first as described above, and then try to it import again.
• Fault 1064: Y'all have an error in your SQL syntax; check the transmission that corresponds to your MySQL server version for the right syntax to use near 'username_database' at line x.
  This mistake bulletin occurs when the import file does non contain backup information for a database or there is a MySQL syntax mistake in the file. Alternatively, the import file may be altered, decadent, or in an unsupported format. (Import files must comprise SQL statements; other file formats such as CSV do not work with the mysql program.) Try exporting the database once more, then try to import it.
• ERROR 1227: Access denied; you need (at to the lowest degree one of) the SUPER privilege(due south) for this operation
  This error message occurs when the import file contains one or more than SQL statements that require superuser privileges (such equally SET GLOBAL or CREATE DEFINER statements). In some cases, yous tin just delete these statements from the .sql file and rerun the import process. For example, CREATE DATABASE statements tin be safely removed, because you should have already created the database in cPanel. If you crave MySQL superuser access, all the same, you should consider migrating your business relationship to a VPS or Dedicated server, where you have complete control over the surround.

  For more information about MySQL user privileges on shared servers, delight run into this article.

More Data

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

cronineader1959.blogspot.com

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

Share this post