Import MySQL database by command line and phpMyAdmin
If you are a web developer, then you have to work on the web application. When we work on a web application, we will also launch it. You have to migrate the application to launch it, and everyone knows this. I am primarily talking here about the PHP Powered Websites, which is using the MySQL database. So when migrating, we have to migrate the database as well. We have two ways to migrate or import databases. Either we import from phpMyAdmin or from MySQL command line. Today in this post I will tell you how to import the database in both these ways. So this post is about how to import the databases from phpMyAdmin, and how to import the database from the MySQL command line.
Let us first know the method based on how to import the database from phpMyAdmin.
How to import the database from phpMyAdmin?
Importing the database from phpMyAdmin is much easier. If you want to import the database into the local machine, then you have to enter the URL http://localhost/phpMyAdmin/. There can be two options for browsing this URL.
Firstly, if you are using this on a Windows machine and you do not have a password set for phpMyAdmin, then you will see the index page of phpMyAdmin. Whereas, if you are using the Linux system or you have a set password for your windows platform then you will get a login screen. You will have to log-in with your username and password. This will take you to the index page.
On the index page, you will find two panels, i.e., a navigation/sidebar and the main panel where you will get the options for database operations. There is an option ‘New’ in the sidebar panel. By clicking on New, you will have to create a new database, like I have created a database with name ‘my_db_imported’.
The database will be created as soon as you click on the Create button. And now whatever operation you do will be on the same database. As, the phpMyAdmin switches to the database, as soon as it is created.
Now you will see the name of the server and the database being used at the top of the panel. So you can know which database you are working on. Below that, you will see many other options such as Structure, SQL, and Searches, etc. In the same row, you will see an option with the name Import. You have to click on it. As soon as you click, you will get a form for uploading a file. This form page will have a heading, I am mentioning below.
Importing into the database “my_db_imported”
You have to browse your data file by clicking on the Choose File button. The file can be in SQL format or in any other supported format. Drop-down of the format will be changed as soon as the file is browsed (only if the file is of a format other than SQL), SQL is selected by default. Now, you just need to click on the Go button to import the data in that file. In a few seconds, your database will be imported and you can connect it with your PHP application, JSP application or any other application.
Now let us see another option which is to import the database from the command prompt into the MySQL.
How to import the database from the MySQL command
To import the database from a MySQL command prompt, you have to access MySQL from the command line itself. For this, you have to see the folder named mysql inside the folder of your web server. Inside the mysql folder, there is a folder with the name bin. This bin folder contains mysql.exe file. You have to go to that bin folder from the command line. Or if you go into that folder in Windows, you can do a shift+right-click. This action will open the command prompt at the same place as the directory path. To navigate through the command prompt, I am giving below example command.
Command to change path in windows command line
|
1 2 3 4 |
C:\> cd web_server_folder/mysql/bin C:\> cd C:\xampp\mysql\bin |
Below is the command to go to the mysql directory. If your mysql directory is placed in another directory, then you have to follow the exact path.
|
1 |
$ cd /var/lib/mysql/bin |
After reaching here, you have to log-in to MySQL. I am giving below command to get log-in to MySQL. This is the same for both Linux and Windows.
How to Login to MySQL with command line
|
1 2 3 |
mysql> -u username -p [press enter] Enter password: |
If your user is root and password is mypassword then for such case, the command will be:
|
1 2 3 |
mysql> -u root -p [press enter] Enter password: |
After pressing enter, you will have to enter the password, which will not be visible while you type. After entering the password, you have to press enter again. If you have entered your correct password then you will be logged in.
As soon as you are logged in, you will see a welcome message of MySQL which will consist of a few lines. Now you have to run the command to import the data into the database. This time, I am using the same database name as above, i.e, with name ‘my_db_imported‘.
How to change or use MySQL database with command line
|
1 |
mysql> use database_name;[press enter] |
Consider that the database name is my_db_imported
|
1 |
mysql> use my_db_imported;[press enter] |
As soon as you run these commands, you will get the message “Database Changed”. Now you have to run the command of database import in MySQL.
Before this, you should know the path of your data import file. Or you can copy the file in the bin folder itself. If you have copied the file in your bin folder, instead of entering the full path, you only have to use the file name. The command to import the database with the MySQL command line is given below.
Import MySQL database with command line
MySQL database import command for windows
If file placed in the bin folder
|
1 2 |
mysql> source my_db_file.sql;[press enter] |
If file placed in any other folder:
|
1 2 3 4 |
mysql> source path_to_file/file_name;[press enter] mysql> source C:/mydbfiles/my_db_file.sql;[press enter] |
MySQL database import command for Linux (Ubuntu)
If file placed in the bin folder
|
1 2 |
mysql> source my_db_file.sql;[press enter] |
If file placed in any other folder
|
1 2 3 4 |
mysql> source path_to_file/file_name;[press enter] mysql> source /var/www/html/mydbfiles/my_db_file.sql;[press enter] |
After pressing enter, you will see a lot of messages running for a few seconds or even for minutes if the database is so large. After successful import, you will get the blank mysql> prompt again.
If you do not want to navigate to the bin folder every time, you can set that path to ENVIRONMENT VARIABLES.
This was the easy way to import the database in MySQL by phpMyAdmin and MySQL command.