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.

Web user and group settings in nginx+php-fpm env.

I have a VPS box with Nginx and php-fpm installed. It seems to be more stable than another box with Nginx and FastCgi-php installed. Noted that php-fpm is just a FastCGI Process Manager for PHP, so it still uses FastCgi-php.

What Happened

I had one issue, I set Web (nginx process) user/group as “nginx nginx”, and set the web folder’s owner/group as “nginx nginx”.

drwxr-xr-x. 10 nginx nginx      4096  03:18 caiweb

And the mod of sub folders has the same settings. However, when I tried to upload the file to the web folders via web interface, for example, install WordPress Plugin, I got permission denied. Again, my another box with Nginx and FastCgi-php installed with the same settings has no issue at all.

Why

What could that be? Finally, I figured out that there is another web user/group settings at /etc/php-fpm.d/www.conf, both of them were set to “php”. And it overrides the settings in nginx.conf.

Solution

The solution is simple, just need to change user/group settings in “/etc/php-fpm.d/www.conf” to be “nginx” in my case, and restart the php-fpm.

Duplicate Key Problem in Memcache PHP

I wrote an article about using PHP memcache as session handler. Recently, I experienced some duplicate key issue and would like to share what I learn from it.

Use PHP Memcache to handle PHP session, the session data is saved in Memcached server other than a plain file. The data in the Memcached is in the format of key-value pair. The session id is key and session data is value. If you have multiple servers running Memcached server, then the memcached key-value pair data could be saved evenly on any server without any order.

Everytime a visitor visits your website, PHP Memcache handler will try to get data using the session id as key from Memcached servers, if found it, it will use it, if not it will create one. So ideally, there should not be duplicate key. But think of the following situations,

1. There are two Memcached servers, m1 and m2. A pair of session data (sid1=>sdata1) is saved on m2, but for some reason, the m2 is down, because Memcached server cannot find sid1 anymore, so it creates another pair of data (sid1=>sdata2) on m1. In a very short time period, m2 is up, and the old pair of session data (sid1=>sdata1) is still on m2 having not expired yet, so in the cluster, we have duplate key. Because Memcache handler get data from the cluster without any order, there is a chance that the different value being retrieved. The general solution could be flushing data on the m2 before add it back to the cluster.

2. Another case is due to mis-configuration. As I mentioned in my previous article. We should have the same order of session.save path setup on different server, like

on m1 (php.ini): session.save_path="tcp://m1:11211, tcp://m2:11211"
on m2 (php.ini): session.save_path="tcp://m1:11211, tcp://m2:11211"

What about on m1, you only provide one Memcached server or you forget to add another server?  It becomes,

session.save_path="tcp://m1:11211";

If you use stickysession, then you may be fine. But if the session is not sticky, then there will be a problem. Says a visitor first hits m2, the session data is saved on m2, and then during the same session, this visitor hits m1 (because it is non stickysession), the Memcache handler on m1 will not able to find the session data that is saved on m2 because the m2 is not on the save_path, so it will create a new data saved on m1 and the duplicate key is created. This situation should be avoided by carefully confiugrating your Memcached servers.

Anyway, there is always a risk to use Memcache as session handler. If the session data is critical to you, I would suggetsion to use database to your session data when you do server load balancing.

MySQL replication fetal error

The power was off in the Data Center (disaster!!!,) both MySQL master server and slave server were shut down. After servers reboot, the master saved the replication log to a new file, and the slave still made request to the old replication log, so then I got the following fetal error:

[ERROR] Got fatal error 1236: ‘Client requested master to start replication from impossible position’ from master when reading data from binary log

The solution is the following,

1. Logon master server to check master status,

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
| master_replog.000101   | 20895412 |              |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2. Logon slave server to slave status;

mysql> show slave status;
| Slave_IO_State | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File        | Read_Master_Log_Pos | Relay_Log_File         | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB     | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master |

|                | webdb.your.com | repl        |        3306 |            60 | master_replog.000100   |            61537332 | xxxxx-relay-bin.001009 |            98 | master_replog.000100 | No               | Yes               |                 | phpmyadmin,xxxxx,mysql   |                    |                        |                         |                             |          0 |            |            0 |            61537332 |              98 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                  NULL |

3. You see the replication log file is ‘00100’ on the slaver, but on the master it is ‘00101’. So I ran the following command to change the replication log file on the slave to the next file and starting with position 4.

mysql > STOP SLAVE;
mysql > CHANGE MASTER TO MASTER_LOG_FILE='master_replog.000101', MASTER_LOG_POS=4;
mysql > SLAVE START;

Note: In original solution I found at here. The author mentioned to issue “Change Mater” if the value of Exec_Master_Log_Pos on the slave is greater then the File Size on the master. I could not find File Size on the master by running “show master status”, so I just assume it is true.

Memcached for Load Balancing Sessions

A few days ago, I wrote a article to introduce Apache Load balancing. At the end of that article, I mentioned using memcached to accomplish sharing session among load balanced machines. Today, I will get into this.

First you need have memcached installed and running on all load balanced machines. This’s not that hard, you can download memcached and find some usefully information at memecached.org

After that, edit your php.ini file on all load balanced machines,
1. use memcache as session save handler instead of files (default)

;session.save_handler = files
session.save_handler = memcache

2. memcached is installed identically on all load balanced servers, you can use one of them to save session data. So the server which hosts session data, you can set session save_path to localhost,

session.save_path="tcp://localhost:11211"

3. On other servers, you can set session save path to the server which hosts session data,

session.save_path="tcp://192.168.8.1:11211"

Here, 192.168.8.1 is the IP of the server, you can also use server name.

Important

  1. memcache is just a handler of php session in this case, the php still control other activities of session, like session cookie’s lifetime, garbage collection, etc.
  2. you have to turn off session auto start in order to use memcache.

Advanced

As you see, in the above example, we only use one server to save session data. If that server is down, then there will be no place to save session data. So the better solution will be using multiple servers to save session data.
On server1 (ex: IP: 192.168.8.1), add the following line into php.ini,

session.save_path="tcp://192.168.8.1:11211, tcp://192.168.8.2:11211"

On server2 (ex: IP: 192.168.8.2), add the following line into php.ini,

session.save_path="tcp://192.168.8.1:11211, tcp://192.168.8.2:11211"

If you have more balanced servers, just need to add server IP to save_path and add same line to each server. You would also add the following into your php.ini, in order to turn on failover support.

memcache.hash_strategy = consistent
memcache.allow_failover = 1

Important

  1. The order of save_path list must be the same on each server, however the first one on the list does not have the highest priority,  the session data is saved evenly on each member.
  2. In this case,  “failover” is not transparent. What it support is that  when one server goes down, session can be saved to another server, but all the data saved on the ‘dead’ server will completely lost.
  3. It’s our experience that we didn’t set memcache hash startegy, and seems everything also worked fine. Based on phpslacker (his site is down by this writing), memcached. hash_strategy should be set to consistent to use failover. Regarding php.net, Controls which strategy to use when mapping keys to servers. Set this value to consistent to enable consistent hashing which allows servers to be added or removed from the pool without causing keys to be remapped. Setting this value to standard results in the old strategy being used.

Above is pretty much what we did on our load balanced websites, and it works very well. If it’s critical to you that one server down causes session data lost, you may try to add the following to your php.ini file,

memcache.session_redundancy=2

This makes sessions data being saved to 2 servers. If you have multiple servers, you may change this number accordingly. Because every server actually hosts the same session files all the time, when one memcache daemon failure, the session files will not lost. You may need a recent version of the PHP memcache client to enable memcache redundancy. More details of this method, you can find at this grate post by Tom.

Reference/credit: Tom, phpslacker

Apache Load Balancing

Every server has limitation. Depend on your server’s specification (memory, cpu, etc.), it might only handle 384 or 512 clients at the same time. If your site is very busy, vistors will not be able to get to your site after the server hits its maximum capability. When one server does not meet your requirement, you might try load balancing. In this article, I will show you how to set up load balancing under Apache server.

First, you need to install your site on multiple servers. For example, you have website named www.busysite.com on server1, and then you can replicate your site to another server, says server2. After that, set up your DNS, and then www1.busysite.com points to your site on server1, and www2.busysite.com points to your site on server2.

Secondly, set up load balancing on server1. What you need is installing Apache proxy balaner moudle named mod_proxy_balancer.so. And then, server1 will be a load balancer manarger, the incoming traffics will be distributed from server1 to server2. You also need to create a load balance configuration file to be loaded when Apache server starts. The following is a example,

<VirtualHost your-load-balanced-domain-ip:80>
  ServerName your-load-balanced-domain

  #load apache mod_proxy_balancer module
  LoadModule proxy_balancer_module modules/mod_proxy_balancer.so

  # stickysesion let you stay on the same server
  # so connection doesn't have to be re-established on each access during the session.
  ProxyPass   /balancer-manager !
  ProxyPass  / balancer://cluster/ lbmethod=byrequests stickysession=BALID
  ProxyPassReverse / balancer://cluster/

  # set up balancer memebers
  <Proxy balancer://cluster>
  BalancerMember http://www1.busysite.com route=www1 loadfactor=50
  BalancerMember http://www2.busysite.com route=www2 loadfactor=50
  </Proxy>

  #preven balancer-manager to be access outsite
  <Location /balancer-manager>
    SetHandler balancer-manager
    Order Deny,Allow
    Deny from all
    Allow from .busysite.com
  </Location>

</VirtualHost>

You can setup balance factor of each server in the cluster in the load balance configuration file, it will take effect when the server restarts. Or you can use balancer manager, usually at http://yourdomain/balancer-manage, to change the balance factor or put a member in the offline mode on fly. Note that your change on balancer manager will not change your configuration file, after you reboot your server, it will be back to whatever settings in the load balance configuration file.

After that, You may add some rules to prevent www1.busysite.com and www2.busysite.com from being accessed publicly.

Pretty much that’s it. Note that in this setup we use stickysession, so the subsequent request from the user who has cookies enable will be sticked on the same balanced server. On the other hand, if you use non-sticky session, then every requests no matter new and old, will be randomly assigned to a balanced server. You know by default PHP session handler saves session files in the local machine, so in the case of non-sticky, in order to have multiple servers running without crash visitors’ session, you have to share PHP sessions among load balanced machines. You may save your session in database or using memcached. I will show you how to use memcached to accomplish share sessions among different machines in the next tutorial.