Creating a Mysql Database and Uploading Data

Introduction

MySQL is a popular Linux-based database plan. As a database, MySQL is a versatile application. It tin be used for something every bit elementary every bit a product database, or as circuitous as a WordPress website.

This tutorial will walk you through how to consign a MySQL database and import it from a dump file in MySQL.

Prerequisites

• A organization with MySQL installed and configured
• An existing database (a examination or empty database will work)
• A root user business relationship and password for the MySQL database
• A terminal window or command-line
• (optional) the phpMyAdmin utility

How to Export MySQL Database

Option 1: Utilize mysqldump Tool

Exporting a database puts information technology in a dump file that tin be transferred to another system.

The MySQL database application includes a command mysqldump to create a dump file of your database. This file can be used every bit a backup or copied to another system.

one. To export your MySQL database to a dump file, enter the following in a concluding window:

          mysqldump –u username –p db_name > dump_file.sql        

Supersede username with the actual username for the MySQL database, and db_name with the proper noun of the database. You can rename dump_file.sql to annihilation y'all'd like but keep the .sql extension.

2. Ostend the export by inbound:

          head –n 5 dump_file.sql        

The organization should display the first 5 lines of the dump file you lot accept just created. You should recognize the database name that's listed.

Notation: The dump file is saved to the working directory you run it from. This is typically the dwelling house directory for the system's electric current user account.

Option two: Use phpMyAdmin

PhpMyAdmin is a handy utility for managing web servers. It includes powerful tools for graphically managing files.

To consign your MySQL database using phpMyAdmin:

1. Log in to cPanel
2. In the Databases department, select phpMyAdmin

3. In the column on the left, choose the database to export
4. One time it loads, click the tab on top to export
5. The export method should be Quick
6. The format should be SQL
7. Click Go, and then utilise the Save File dialog to save a re-create of the exported database.

How to Import MySQL Database

Option 1: Import MySQL Using mysqldump Tool

Importing a database requires setting upwardly a bare database first.

i. To create a blank database, launch the MySQL crush by entering:

          mysql –u root –p        

2. Enter the root password when prompted. The command prompt should change to show that you lot're working in MySQL.

three. Next, create a new database by entering the following:

          CREATE DATABASE new_db_name;        

The system should return with Query OK, 1 row affected (0.00 sec). Note: The command won't run if the semicolon isn't entered at the stop of the command.

four. Exit the MySQL trounce by pressing Ctrl-D. The command prompt should return to normal.

5. Enter the following command to import the dump file:

          mysql –u username –p new_db_name < dump_file.sql        

You lot'll but meet a response if there are errors. A successful import won't display any comments on the screen.

half-dozen. To check the database, log back into the MySQL shell:

          mysql –u root –p        

vii. To load the database, enter:

          USE new_db_name        

8. Display the contents of the database by typing:

          Show TABLES;        

Choice 2: Importing with phpMyAdmin

Footstep 1: Create New MySQL Database and Assign User

Before you can import a database using phpMyAdmin, you'll need to create a bare database starting time:

1. Log into cPanel
2. Under Databases navigate to MySQL Databases
3. Enter the name of the new database in the Create New Database field, and so select Create Database

4. Once it finishes, click Go Dorsum, and so click Add User to Database
5. Select the correct user in the User box, select the new database in the Database listing box, and then Add
6. Select All Privileges (unless you have a reason or policy that specifies account privileges)
7. Click Make Changes

Step two: Import MySQL Database with phpMyAdmin

To import a dump file into the new database using the phpMyAdmin web interface:

1. Log into cPanel
2. In the Databases section, select phpMyAdmin

3. In the left column, select the newly-created database as seen in the image beneath:

4. Click the Import tab (next to the Consign tab)
5. Next to File to Import, select Browse, then cull the file you downloaded from the consign performance and select Go

Note: These commands can all be run with a root database user account. If you already have some other user account prepare, you can employ information technology instead. Earlier you begin, make sure that the account has the correct privileges. Or, you lot may add together a new MySQL user account if you prefer.

Conclusion

In reading this guide, you now know two means to export and import a MySQL database.

One uses a command line to create a local dump file, which you can copy or transfer to a new system to import. The other uses a graphical utility to generate the dump file.

Also, check out our article on how to easily consign from MySQL database table to CSV and how to import CSV into MySQL tabular array.

Was this commodity helpful?

Yep No

fisherthermand49.blogspot.com

Source: https://phoenixnap.com/kb/import-and-export-mysql-database

Share this post