MySQL is the most widely used open source relational database management system (RDBMS) in the world that runs as a server providing multi-user access to a number of databases. The use of MySQL management clients or tools save us the stress and hardship of using MySQL commands line.
While facing the MySQL administration tasks, most of the developer rely on content managing tools for MySQL like phpMyAdmin, Adminer, SQLBuddy, etc. These tools are used to manage the contents of the MySQL databases.
They are written in php and hence they can be installed on the web server and accessed from the browser. The tasks like checking the data, alter few records, or backup the databases can be easily performed using these tools.
phpMyAdmin is a open source administration tool for MySQL that is available for free. It is primarily written in PHP as a portable web application and it has become one of the most popular MySQL administration tool over the web. It supports a wide range of operations on MySQL and MariaDB.
phpMyAdmin provides both user interface as well as directly execution of the MySQL statement. Frequently used operations like managing databases, tables, columns, etc can be performed via the user interface, while you still have the ability to directly execute any SQL statement
phpMyAdmin is being translated into 78 different languages and supports both LTR and RTL languages to make it easy for wide range of people. The features of phpMyAdmin are:
- Imports data from CSV and SQL
- Exports data to various formats like CSV, SQL, XML, PDF, etc
- Creating complex queries using query-by-example(QBE)
- Searching globally in a database or a subset of it
- Administering multiple servers
- Intuitive web interface
- Supports the most MySQL features
- browse and drop databases, tables, views, etc
- create, copy, drop, rename and alter databases, tables, etc
- maintenance server, databases and tables, with proposals on server configuration
- manage stored procedures and triggers
- Stored data can be transformed into any format using a set of predefined functions, like download-link or displaying BLOB-data as image
How to Export database tables using phpMyAdmin
Exporting data from the database using phpMyAdmin is easy and hardly it takes much time. Follow these steps to Export the data using phpMyAdmin :
- Select the database from the list available of the available databases.
- Click on Export tab present at the top
- Select the tables and the format of the file
- Choose the Structure and Data from the list
- Check the “Save File” and select the compression option from the given list
- Click on “Go”
How to Import database tables using phpMyAdmin
Importing data to the database is also easy and a small process in phpMyAdmin. To import data to phpMyAdmin, follow the following steps :
- Select the database you need (if not present, then create it)
- Choose Import tab on the top
- Click Browse, and Navigate to the file which you need to import
- Click “Go”
Adminer (formerly known as phpMinAdmin) is a tool for managing content in MySQL databases. It is light-weight database managing tool. Adminer also supports SQLite, PostgreSQL, Oracle, etc. and it is distributed under GPL v2 license in a form of a single PHP file(around 430KB in size).
Adminer prioritize : safety, user-friendliness, performance, functionality, and size (according to its author).
The features of Adminer are:
- It is light-weight tool – released in a form of a single file
- Searching and sorting via multiple column
- Text area for arbitrary SQL commands and storing these commands in command history
- Support of CSS “skins”, as well as many extensions
- Visual databases / E-R schema editing
- SQL syntax highlighting
- Export of databases and tables as a dump to output or a downloadable attachment
- Basic functions : select database, select/edit tables, browse/insert/edit table rows
- Users log in specifying the destination server and providing the user name and password (which is stored during whole session)
How to Export data from Adminer
Sometimes you need to export the data from the database to move the data from one place to another or create a local backup to be on a safer side. Adminer gives you functionality to export the data from the database using its UI. To export database or tables from the database using Adminer, follow these steps :
- Login to database using Adminer
- Navigate to the database whose table you want to export (If you want to export the databases then skip this step)
- Click Export button on the left
- Choose the tables or databases you want to export and also select the output type, format, etc
- Finally click on Export
How to Import data using Adminer
Importing data in the form of tables or the database is easy. Follow the steps below to import the data to the database using Adminer.
- Login to database using Adminer
- Choose Import option on the left
- Click Browse and navigate to the sql file which you need to import (Notice that file must be less than 2MB in size)
- Click Execute
Adminer and phpMyAdmin Comparision
Adminer and phpMyAdmin are tools for managing the contents of the databases. PhpMyAdmin is the one of the most famous tools for managing the MySQL database. On the other hand Adminer is also famous and it is also light-weight tool. But both have some pros and cons.
So, lets have the look on both of them:
Supported System : As the databases are concerned, phpMyAdmin supports MySQL as well as MariaDB. While Adminer supports MySQL,SQLite, PostgreSQL, MS SQL, Oracle(Adminer is also available in a MySQL only version)
Login : Adminer offers permanent login including user list, while phpMyAdmin only partial support through configuration
Creating Table : In phpMyAdmin creating table is not smart as you have to specify the number of fields prior to creating the table. The form design transposes based on the number of fields. While In Adminer, creating table is smart as Adminer adds fields automatically
Altering Table : phpMyAdmin can not move columns and can not add columns in different places at once (every alter table is very slow when applied to big tables). While Adminer can reorganize columns and add them in different places at once
Languages : Adminer offers 36 different languages while phpMyAdmin is available in 78 different languages
Foreign Keys : In phpMyAdmin, it is not possible to alter to create multi-column foreign keys. You have to create an index manually before you create the foreign key. Adminer offers a foreign keys interface directly in create as well as alter table
More Points to Compare
There are many other points to compare. Some of them are :
Export Formats : phpMyAdmin can export the file in 16 different formats including CSV, CodeGen, LaTeX, PDF, PHP array, SQL, XML, etc. while Adminer can only export the file in 3 formats i.e. SQL, CSV, TSV. Exploring database to LaTeX is a task that most developers solve on a daily basis
Configuration : phpMyAdmin usually requires some configuration, but Adminer do not, it just works
Syntax Highlighting : No syntax highlighting in phpMyAdmin while Adminer provides links to documentation for all SQL keywords
Variables : phpMyAdmin does not display any description for variables while Adminer links to the official documentation
Synchronization : Synchronize feature in phpMyAdmin does not work without remote access to the server which is usually disabled by a firewall while Adminer uses a different approach – it can create an ALTER export which generates alter commands to synchronize the target database with the source after running on the remote server.
Calling Routines : In phpMyAdmin, calling stored procedures and functions needs creating a SQL query. Adminer offers a friendly interface
Customization : Very simple customization is offered by phpMyAdmin through special tables whereas Advanced customization is offered by Adminer through a PHP extension class
Size : phpMyAdmin is heavy weighted as it consists of many files and the size of phpMydmin is large as compared to Adminer while Adminer consists of only 1 file and hence it is light weighted
Requirements : The requirements of Adminer is less than the basic requirements of the phpMyAdmin. phpMyAdmin requires php version 5.2+ while Adminer requires php version 4.3+ also phpMyAdmin requires MySQL 5+ while Adminer requires MySQL 4.1+
Overall Performance : The overall performance of Adminer is faster than that of the phpMyAdmin. According to independent test by Juraj Hajdúch, Adminer is 28% faster on average