MySQL is a popular database management tool that millions of individuals and businesses use worldwide. As an open-source relational database, it boasts unrivalled popularity, second only to Oracle regarding mentions on the internet. Nevertheless, performing a MySQL database extraction may be necessary to migrate essential data from a legacy system to a new one.
Whether you prefer the user-friendly interface of phpMyAdmin or the command line efficiency of mysqldump, this guide will provide you with the knowledge and confidence to use both methods effectively.
Note: 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.
[Option 1] Using phpMyAdmin
phpMyAdmin represents a no-cost administrative utility employed in managing MySQL and MariaDB databases. It has become one of the most widely adopted and favoured tools for MySQL administration, attributed to its user-friendly web application built using PHP.
1. Access cPanel directly through your Z.com account.
2. Once you have reached the cPanel dashboard, locate phpMyAdmin under Databases.
3. In phpMyAdmin, on the left menu, select the database you want to export.
4. On the top menu, click Export.
5. Under the Export method, you can proceed in two ways – ‘Quick’ to download the SQL file immediately or ‘Custom’ to have more data control.
6. Once done, click Export at the bottom of the page.
[Option 2] Using the mysqldump program
Mysqldump serves as a program for creating database backups. Its primary function involves performing logical backups by generating a series of SQL statements capable of recreating the primary or original database. Additionally, mysqldump offers the flexibility to generate output in various formats such as CSV, other restricted text formats, or XML.
1. Access the command line interface on the computer where the database is located. If the database resides on a different web hosting account or with another hosting provider, you can log in to the respective account using SSH. Alternatively, in cases where you have physical access to the computer, open a DOS or terminal window to establish a connection with the command line.
2. Type the following command and click Enter.
| mysqldump –routines -u username -p dbname > dbexport.sql |
* Substitute ‘username’ with your username and ‘dbname’ with your database name.
3. Enter your password 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 located on a remote computer, proceed by downloading the file to your local computer.