First of all I should clarify why am I exporting data from MySQL and putting it in PHPMyAdmin. Well, I had a lot of data stored in MySQL on my Windows(hope Microsoft does no sue me for using the word “Windows” :P) box and I wanted to use all that data in a project which I wanted to on LAMP (Linux/Apache/MySQL/PHP). So now I had to move all that data from MySQL into a dump file on the Crash(Windows :P) Box and import it into PHPMyAdmin on my Linux box.
EXPORT DATA FROM MYSQL :-
There’s a program called “mysqldump” that gets installed when we install MySQL. This mysqldump tool is used to out all the data from the database into a dump file.
Major confusion that occurs is, that we try to execute this program from MySQL prompt. When we do that, it returns an error. The error statement says that there is an error in the syntax and refer the MySQL version manual for the correct syntax.
We need to execute the mysqldump program from the Command Prompt instead of the MySQL prompt.
mysqldump -a -u=username -p database_name > name_of_the_dumpfile.sql
Once you hit enter it asks for password and creates the dumpfile.
IMPORT DATA INTO PHPMYADMIN :-
After logging into the PHPMyAdmin, select the database in which you want to import the data.
After selecting the database, you’ll see an Import tab on the top, click on it. In the import panel specify the path of the dump file and say GO. It’s done, all the data is imported into PHPMyAdmin.
Many a times we are required to move data from one Database Management system to another. Unfortunately very little is done by the database vendors to make this an easier experience. I obviously understand that no vendor would want that people should move data from their product to another but that is something which customers deserve.
Well in the absence a good tool to do that we are left with only one option, write a script in our preferred language and move the data physically from one database to another. My preferred language is JAVA but you can use any other language. I’ll show how to connect to Oracle and MySQL and then we’ll move data from Oracle to MySQL. Piece of Cake. :P.
CONNECT TO ORACLE ##-> READ FROM ORACLE ##-> CONNECT TO MySQL ##-> WRITE INTO MySQL ##-> CLOSE CONNECTION
Connect to ORACLE (version 9i)
Prerequisite : Add the path of the following file in the CLASSPATH.
Class.forName(“oracle.jdbc.driver.OracleDriver”); //Loading the driver
String urlora=”jdbc:oracle:thin:@127.0.0.1:1521:home”; //URL for connecting to the database
//Creating Connection class object.
Statement stmtora=connora.createStatement(); //Statement class object to execute the query
ResultSet rsetora=stmtora.executeQuery(“QUERY inside double quotes”);
//Write the query here.
//ResultSet class object contains the retrieved data after the successful execution of the query.
So once you have the data retrieved from the Oracle database, you just need to write it into the MySQL database(Checkout the next part).
Connect to MySQL
Prerequisite : Download the drivers required to connect to the MySQL database from here. Extract it and add the path of the .jar file in your classpath.
stmtmy.executeUpdate(“Query inside quotes.”); //Write all the data read from Oracle into MySQL
And you are done!!! You can run this in loop and print the number of records of data moved from one database to other.
Note : The process can also be reverted to move data from MySQL to Oracle.