managing mysql database

Managing MySQL Database Using phpMyAdmin


Most of the small Internet web sites and projects use a MySQL database to store data. You can manage the MySQL database from the command line interface (like a bash), but this is not very convenient. One of the most popular graphical tools for managing MySQL databases is phpMyAdmin. PHPMyAdmin is an open source project (distributed under the GPL license), written in PHP, which allows you to perform almost 100% of the tasks of administering and managing MySQL databases through a simple and convenient graphical interface. PHPMyAdmin is available on most popular web hosting services by default.

You can also install phpMyAdmin on any server or computer. This can be any Windows or Linux computer on which you have an installed Apache or IIS web server with PHP support.
We won’t describe in detail the process of installing phpMyAdmin in different OSs. In most Linux distros you can install it from the repository. For example, in Ubuntu and Debian distributions you can install PHPMyAdmin using the command:

apt-get install phpmyadmin

You can also manually download and install PHPMyAdmin from the site:

https://www.phpmyadmin.net/downloads/

You can remotely manage multiple MySQL databases from your computer. The main thing is to properly configure the MySQL server.

By default, the MySQL server allows only local connections (from the address 127.0.0.1).

To allow remote connections to the MySQL server, you need to edit the my.cnf configuration file (/etc/mysql/my.cnf). The bind-address line contains the address 127.0.0.1 (localhost), which prevents the MySQL server from accepting external connections.

bind-address=127.0.0.1

bind-address=localhost

Delete or comment these lines (in this case, you can access your MySQL server from anywhere in the world), or specify the IP address of your server/computer from which to allow the management connection:

bind-address= x.x.x.x

Restart the MySQL server:

sudo service mysql restart

The phpMyAdmin settings located in the config.inc.php file, the syntax of which is rather complicated. To add the ability to connect from phpMyAdmin to your remote MySQL server, add the following lines in the config.inc.php file:

/* Remote mySQL Server1 */

 $i++;

 $cfg['Servers'][$i]['verbose'] = 'MirandaDB';

 $cfg['Servers'][$i]['host'] = 'x.x.x.x';

 $cfg['Servers'][$i]['connect_type'] = 'tcp';

 $cfg['Servers'][$i]['extension'] = 'mysqli';

 $cfg['Servers'][$i]['auth_type'] = 'cookie';

 $cfg['Servers'][$i]['AllowNoPassword'] = false;

Where ‘x.x.x.x’ is the IP address of your MySQL server.

Now open the phpMyAdmin web interface (http://127.0.0.1/phpmyadmin) and logon to your MySQL server using mysql credentials (if you specified several servers in the config.inc.php configuration, select the server from the list).

Manage MySQL Database with phpMyAdmin

Tip. You can specify the username mysql and password in the configuration file config.inc.php, in this case, the connection to the database server will be carried out automatically (without entering the password).

On the initial screen of phpMyAdmin you can see the list of databases, the encoding (server connection collation) and the version of the program.

Managing MySQL Database phpMyAdmin

The sidebar displays a list of available databases and tables in them. You can select a database or table.
Select any table. You can view its contents (Browse tab), structure (Structure tab), add new rows, perform data export/import, etc.

Manage MySQL Database through phpMyAdmin

In addition, you can execute console MySQL commands from the phpMyAdmin GUI. Select the SQL tab and enter the MySQL query code.

Manage MySQL Database

The phpMyAdmin interface is quite simple and straightforward, so you need to quickly figure it out.

 

Add Your Comment