PHPMyAdmin connect to both Local and Remote Server

Add servers into config.inc.php, there may be two config.inc.php, one is under “/etc/phpmyadmin”, the other is under your web root folder. If so, update the one under your root.

$i=0;
$i++;
//server 1 - local
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['verbose']   = 'local';
$cfg['Servers'][$i]['host']      = 'localhost';//or ip:'10.9.8.1'
$cfg['Servers'][$i]['extension'] = 'mysqli';

//server 2 - remote
$i++;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['verbose']   = 'remote';
$cfg['Servers'][$i]['host']      = 'remote.com';//hostname or ip:'10.9.8.1'
// this server must allow remote clients, e.g., host 10.9.8.%
// not only in mysql.host but also in the startup configuration
$cfg['Servers'][$i]['extension'] = 'mysqli';

Then, you should see the two servers shown up on the PHPMyAdmin login screen.
If you receive the error when trying to connect to remote server, then keep on reading.

To access remote mysql database, you have to enable remote access on the remote server. Check this article.

The tricky part is to open TCP port 3306 using iptables. You have to add an rule via iptables

iptables -A INPUT -i eth0 -s 10.5.1.3 -p tcp --destination-port 3306 -j ACCEPT
##10.5.1.3 is client ip, where we try to connect to db server.

But you need to make sure there is no REJECT ALL rule before this new rule. For example, using command “iptables -L”, you see there is a REJECT ALL rule before the new rule we added, then the new rule will be ignored.

[root@leijinet resources]# iptables -L
Chain INPUT (policy ACCEPT)
target     prot opt source               destination
ACCEPT     tcp  --  anywhere             anywhere            tcp dpt:http
ACCEPT     all  --  anywhere             anywhere            state RELATED,ESTABLISHED
ACCEPT     icmp --  anywhere             anywhere
ACCEPT     all  --  anywhere             anywhere
ACCEPT     tcp  --  anywhere             anywhere            state NEW tcp dpt:ssh
REJECT     all  --  anywhere             anywhere            reject-with icmp-port-unreachable
ACCEPT     tcp  --  10.5.1.3             anywhere            tcp dpt:mysql

To fix this, we need to move the new rule up. I could not find the move command, so what I did was that remove the “REJECT ALL” RULE and then add it to the end. Like

### remove a rule, "6" is the index
iptables -D INPUT 6

### add a rule back
iptables -A INPUT -i eth0 -j REJECT

Some simple note here hope can help you.

MySql index is very important

In a project, we need to import data from a plain file into database and the file has about 55,000 rows.
What the script does is looping throught 55,000 rows, for each row, check the search term that is in the database table or not, if yes, update it, if not, insert it.

The script was running fine for a while, but some day we found that the script took more than 10 hours to run. So we check the script. It uses php json_decode(), serialize() when processing the data, but without connecting to the database, it loops quickly. So obviousely, the issue lies in the database part.

We checked the table, which has been growing into about 200,000 rows. As I mentioned, before inserting data, the script queries the search term, the “search_term” column was not indexed, so for 200,000 rows table, the query does not run as fast as you may think. Although for single query, it’s not notiable, but for about 55,000 loops, the different is significant. We added index to the column.

ALTER TABLE `io_search_recs` ADD INDEX ( `search_term` ) 

The perfomance is dramatically changed, See the benchmark below,

55029 loops total run? 89.491609096527 secs
55186 loops total run? 43986.108026981 secs

Some article mentions, “Indexes have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.”, but based on our testing, we do not see the downside.