Pluggable Authentication is one of the many new MySQL 5.6 features. Pluggable authentication adds a capability to use external authentication services (PAM, Windows login IDs, LDAP, Kerberos, …) but also introduces built-in strong SHA-256 hashing for passwords. The SHA-256 Authentication Plugin uses encryption to protect the password from being sniffed during authentication. Read a live report from teaching the PHP mysqlnd library the new secure authentication method.

Your choices

Early MySQL versions have had very poor password hashing. The PHP mysqlnd library has never supported the old, insecure password hashing that was used until MySQL 4.1. …

Never trust user input! Injection is a threat . You are the new web developer, aren’t you?. Never trust user input is the first rule I had to learn as a web developer in anchient times. Injection can happen whenever user input is interpreted or used to compose new data. A quick recap of the #3 mistake from todays Top 10 MySQL Tips and Mistakes for PHP Developers web presentation. A webinar recording should be available in a couple of days.

Don’t!

Your probation period as a PHP developer may come to an immediate end if you write code like this:

mysqli_query(
  $link, "SELECT actor, rating FROM movies WHERE name = '" .
  $_GET['movie_name'] . "'"
);

mysql> SELECT actor, rating FROM movies
    WHERE name = '
      Self-made Sauerkraut' UNION ALL
           SELECT user, password FROM mysql.user …

It is a common question: what is the lifespan of a persistent, pooled, cached, however-you-call-it PHP MySQL connection? What about CGI, FastCGI, web server module? The answer is always the same. Standard I/O file handles, including socket connections, are bound to processes. The lifespan of a persistent, pooled, cached, however-it-is-to-be-called-in-context-x connection is that of the PHP process. Depending on the web server deployment model, a PHP process handles one or multiple web requests. Read on only if puzzled.

A process

Do you have any colleguages around? Please, ask them for "APUX". In case of no reply, get a copy and study. To become a master PHP developer you will have to master todays frameworks (Zend Framework, Symfony, Dojo, …) and yesterdays. Yesterdays framework is still dominant. Yesterdays framework is your operating system. "APUX" stands for …

Here comes the next open source mysqlnd plugin prototype: PECL/mysqlnd_mux. The PHP mysqlnd connection multiplexing plugin shares a connection to MySQL between multiple user connection handles. Connection multiplexing aims to reduce connect time and lowers the total number of concurrenly opened connections. The first is primarily a benefit for the client, the latter is great for the server. Multiplexing may be most desired in environments with many short-lived connections and weak client-side pooling - such as a PHP web application.

PHP mysqlnd connection multiplexing plugin . More about PHP …

An INSERT does not delay me much. At least, it does not necessarily block a PHP MySQL script immediately. The asynchronous query feature of the mysqlnd library helps out. A walkthrough what mysqlnd can do today and could do in theory.

Traditional synchronous API

$ret = $handle1->query("INSERT ...");
$ret = $handle1->query("INSERT ...");

By default, mysqli_query() is a blocking API call. It sends the query to MySQL and waits for MySQL to reply. Let’s see what happens in general when executing two INSERT statements. PHP offers no parallel processing language primitives such as threads and thus, the two INSERT statements run in a serial fashion. Execution times add up.

Some people speak incredibly fast, others make you fall asleep listening. If you have a heterogenous MySQL cluster with differently sized machines, some will be able to answer questions faster than others. PECL/mysqlnd_ms 1.4, the PHP MySQL replication plugin, allows you to assign more requests to the faster ones than to the others. Cluster nodes can be given a weight, a priority for load balancing. For example, a node given a weight of two will get twice as many requests in the average as a node assigned the default weight of one.

Load Balancing adaptive to hardware size

The mysqlnd replication plugin features round robin and random load balancing. Random can be combined with a sticky option which means the
plugin picks a random node at the beginning of the web requests and uses it until the end for all requests that the node can fulfill. For example, …

Clustering databases is a mainstream technology. MySQL PHP users can take many approaches to load balancing, pooling and redirection. PECL/mysqlnd_ms is one solution. How does it handle transactions? Transactions are a challenge for every MySQL load balancer.

MySQL? Load? Clustering! Balancing! PECL/mysqlnd_ms 1.4
View more presentations from Ulf Wendel Load Balancer and transactions

A load balancer must not switch from one cluster node to another in the middle of a transactions. Unfortunately, the MySQL Protocol …

Users of MySQL Replication sometimes throttle client requests to give slaves time to catch up to the master. PECL/mysqlnd_ms 1.4, the current development version, features some throttling through the quality-of-service filter and global transaction identifier (GTID). Both the plugins client-side GTID emulation and the MySQL 5.6 built-in GTID feature can be used to slow down PHP MySQL requests, if wanted.

How its done

The replication plugin has a neat feature called quality-of-service filter. If, for example, the quality of service you need from a MySQL Replication cluster is "read your writes", you call …

Tweaking is the motto - what an easy release PECL/mysqlnd_ms 1.4 will be! The first tweak for the next stable version of the mysqlnd replication and load balancing plugin solves pitfalls around charsets. String escaping now works on lazy connection handles (default) prior to establishing a connection to MySQL. A new server_charset setting has been introduced for this. The way it works also prevents you from the risk of using a different charset for escaping than used later on for your connection.

Lazy connections and server_charset

PECL/mysqlnd_ms is a load balancer. A users connection handle can point to different nodes of a replication cluster over time. For example, if using MySQL Replication, the connection handle may point to the master for running writes and, later on, to one of the slaves for reads. At the very moment a user opens a …

Why do we have to bother about built-in GTID support in MySQL 5.6 at all? Sure, it is a tremendous step forward for a lazy primary copy system like MySQL Replication. Period. GTIDs make server-side failover easier (slides). And, load balancer, including PECL/mysqlnd_ms as an example of a driver integrated load balancer, can use them to provide session consistency. Please, see the slides. But…

MySQL 5.6 Global Transaction IDs - Use case: (session) consistency
View more presentations on MySQL and …