MySQL is a popular database management tool used by millions of users and businesses worldwide. It is the most widely used free relational database and the second most popular database mentioned on the Internet after Oracle. Nevertheless, performing a MySQL DB extraction may be necessary when you want to migrate essential data from a legacy system to a new one.
The method you use depends on your familiarity with the MySQL database, the volume and complexity of data, the tools available, the destination database, and the sensitivity of the data. Here are five different ways to perform a MySQL database extraction.
1. Use phpMyAdmin
PhpMyAdmin is the popular free database admin tool used for MySQL and MariaDB. It is widely used because it can export data in different formats like CSV, XML, SQL, PDF, MS Word, Excel, and many more.
To export or extract data from a MySQL database with phpMyAdmin, take these steps:
- Load your phpMyAdmin
- Select the database to export
- Click on the Export tab
- Select the Custom option to have complete control of the data your export
- Specify the destination file on your computer
- Click Save, and the export process will start
2. Work With the MySQLdump Program
MySQLdump is essentially a backup utility for your database. It can extract database data by:
- Producing a set of SQL statements to recreate the database
- Generating output in a text file
- Creating output in CSV format
- Producing XML data
MySQLdump is easy to use once you are comfortable with the command line. Here are the steps to take to export a complete database in the form of SQL commands:
- Open up a command line or terminal window on your computer
- Connect to the server hosting the database
- Switch to the directory where your database is stored
- Enter this command:
mysqldump -u username -p password databasename > exporteddb.sql
Substitute “username” with your username, “password” with your password and
“databasename” with the name of your database. You may also replace “exporteddb.sql” with a more descriptive name.
To export a single table, use this version:
mysqldump -u username -p password databasename tablename > exporteddb.sql
MySQLdump has other options for exporting data, and you can check them out on help file pages.
3. Create a SELECT Query
Suppose you need to extract data into a CSV file by joining data from different tables. In that case, you can use a simple select query and specify parameters such as the output file, characters to separate fields, terminate fields, and lines.
A good example is:
SELECT *
INTO OUTFILE ‘/tmp/patients.csv’
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
ESCAPED BY ‘\\’
LINES TERMINATED BY ‘\n’
FROM patients
You can replace “*” with the join expression for your query.
After executing the query successfully, you may copy the file into another directory to have full permission/privileges to work on it.
4. Use MySQL Workbench
MySQL Workbench is a comprehensive database creation, development, and administration tool for working with MySQL databases. It’s a visual tool with a well-designed user interface.
To extract data with this tool, you need to:
- Connect to your database
- Click “Server” in the toolbar
- Choose Data Export
- Pick the tables you want to export
- If you don’t want to save the exported data file in your default directory, choose another one under “Export Options”
- Click the “Start Export” button to tell Workbench to start extracting the data
5. Use a Proprietary Application
The tools and methods discussed above are helpful for simple data extraction from your MySQL database.
To extract a considerable volume of data from a legacy information system without any hitches, you need to plan your extraction and map the data to fields in the new database before loading it. Then you must manually review records that do not map to the new database.
This process requires the help of an experienced data migration expert who uses specialized tools.
Learn More About MySQL DB Extraction
Contact MediQuant at 844.286.8683 to see a free demo of our data extraction tool and process. You can also reach us through our contact page to discuss all your data archiving and migration needs.