Category: Managing phpMyAdmin Linux Systems

Managing phpMyAdmin Linux Systems

Import Export MySQL Dumpfile MySQL Server on Terminal

This is an alternative way to restore or import MySQL Dumpfile data with mysql command on Terminal.
Best tool to restore or import MySQL Dumpfile is phpMyAdmin, here is the tutorial to Set up Apache phpMyadmin and Nginx phpMyAdmin

This is restore or import dbuser.sql dumpfile into dbuser MySQL database using user as username:

First we upload MySQL dumpfile to terminal or home folder of user
Easy simple way by log in via SFTP (SSH File Transfer Protocol), this is same way a  user log via ssh. We can use Bitvise, Filezilla, etc.

Assume database ‘dbuser’ has been created and MySQL dumpfile (dbuser.sql) has been uploaded to user home folder, user log in to terminal and in home directory position.

#ls -la
dbuser.sql

Import: To MySQL dump data file to MySQL Server type the following command:

#pwd
/home/user
#mysql -u user -p -h localhost dbuser < dbuser.sql

Export: To Export a database and create dump file from MySQL Server type the following command:

#mysqldump -u [username] -p [database name] > [database name].sql

Important to avoid Mysql::Error: Incorrect string value: ‘\xE2\x80\xA8\x09 while inserting data to the table

#mysql -u root
MariaDB [(none)]>use dbname;
MariaDB [(none)]>ALTER TABLE dbname.tablename CONVERT TO CHARACTER SET utf8;

Install phpMyadmin Nginx CentOS 6

Prior to installing phpMyAdmin, we should verify that NGINX MySQL PHP (LEMP) has been installed and all of the LEMP services are started and running.  We can grep look for their processes – “nginx”, “mysqld” and “php-fpm”:

#ps auxf | egrep "nginx|mysqld|php-fpm"

If you see processes similar to the ones below, you may proceed with the phpMyAdmin installation, otherwise you need to get back to the LEMP setup tutorial:

[root@vpshelpdesk phpMyAdmin]# ps auxf | egrep "nginx|mysqld|php-fpm"
root 1171 0.0 0.2 2340 588 pts/0 S+ 15:01 0:00 | \_ gr ep -E nginx|mysqld|php-fpm
root 574 0.0 0.5 3044 1324 ? S 14:41 0:00 /bin/sh /usr/bin/mysqld_sa fe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --pid-file=/var/run/mysqld/m ysqld.pid --basedir=/usr --user=mysql

Install phpMyadmin

#wget https://files.phpmyadmin.net/phpMyAdmin/4.1.7/phpMyAdmin-4.1.7-all-languages.zip
#unzip phpMyAdmin-4.1.7-all-languages.zip
#mv phpMyAdmin-4.1.7-all-languages.zip phpMyAdmin
#mv phpMyAdmin /usr/share/nginx/html
#cd /usr/share/nginx/html/phpMyAdmin
#service nginx restart
#service php-fpm restart

Check the result http://ipaddress/phpMyAdmin
phpMyadmin page will appear but this still not work, we should edit phpMyadmin config.ing.php

#cd /usr/share/nginx/html/phpMyAdmin/
#mv config.sample.inc.php config.inc.php
#vi /usr/share/nginx/html/phpMyAdmin/config.ing.php

Edit following lines:
$cfg['Servers'][$i]['auth_type'] = 'cookie';
With this:
$cfg['Servers'][$i]['auth_type'] = 'http';

#service nginx restart
#service php-fpm restart

Check the result again http://ipaddress/phpMyAdmin
It will work!

Install and Secure phpMyAdmin Apache

Step One Install phpMyAdmin
The EPEL repository can be made available to your server by installing a special package called epel-release. This will reconfigure your repository list and give you access to the EPEL packages. To install, just type:

#yum install epel-release

Now that the EPEL repo is configured, you can install the phpMyAdmin package using the yum packaging system by typing:

#yum install phpmyadmin

phpMyadmin is up, try to access at

http://server_domain_or_IP/phpMyAdmin
Forbidden

You don’t have permission to access /phpMyAdmin on this server. We will need to modify this a bit to get it to work correctly for our installation. Open the file in your text editor now so that we can make a few changes:

#vi /etc/httpd/conf.d/phpMyAdmin.conf
     # Apache 2.4
       #Require ip 127.0.0.1
       #Require ip ::1
       Require all granted
     # Apache 2.2
       #Order Deny,Allow
       #Deny from All
       #Allow from 127.0.0.1
       #Allow from ::1

Restart the Apache web server to implement your modifications by typing:

#service httpd restart

With that, our phpMyAdmin installation is now operational at http://server_domain_or_IP/phpMyAdmin

Changing the Application’s Access Location

We simply need to rename the alias. Open the phpMyAdmin Apache configuration file now:

#vi /etc/httpd/conf.d/phpMyAdmin.conf

We want to disable these specific aliases since they are heavily targeted by bots and malicious users. In our case, we’ll go with /dbadmin.
To apply our intended changes, we should remove or comment out the existing lines and add our own:

# Alias /phpMyAdmin /usr/share/phpMyAdmin
# Alias /phpmyadmin /usr/share/phpMyAdmin
Alias /dbadmin /usr/share/phpMyAdmin

To implement the changes, restart the web service:

#service httpd restart

Now, if you go to the previous location of your phpMyAdmin installation, you will get a 404 error: http://server_domain_or_IP/dbadminphpmyadmin