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 …

"But 63 slides is too much" has been the initial reply to the draft of tomorrows Top 10 MySQL Tips and Mistakes for PHP Developers web presentation (Tuesday, September 25, 2012: 10:00 CET). SQL injection, security and access control, monitoring for indexing and tuning, choosing adequate data types, character sets, how your web application speaks UTF8 correctly, types of connections and their performance properties as well as planning for tomorrow are too important to allow for less slides, Johannes Schlueter and I thought.

Beginners will be presented with a list of pitfalls to avoid. Advanced users profit from the presentation of the latest news put in context. And, of course, we will answer questions. Whoever of us is not speaking at a time is happy to answer your questions in the chat.

A popular NoSQL store claims to have a rich query language. A powerful, portable query language is most important. If not powerful, you have to send multiple commands to fetch the desired data. In the worst case a program must be written which contraditcs the idea of a query language. If not portable, you are back to the 1960th and vendor lock-in. Developing portable applications is impossible. Stepping up from one database to another means rewriting your application. SQL does not have any of these issues. Plus, it is widely known which means low training costs and high understanding of its properties.

SQL - a query language for the relational model

It may seem odd to compare a query language for the relational model (SQL) with access methods of a document store. However, the NoSQL store compared with explains how to map their concepts to SQL in their manual! In the following, I’ll do the same. I’ll consider tables as …

MySQL and NoSQL go together. In the data center, that’s a known. Inside MySQL, that’s lesser known. MySQL 5.6 is both a puristic key value store and a full-fledged relational database. MySQL could also be developed into a schema free document store supporting MapReduce as a search method. With regards to searching those are the extremes. What search methods should a NotOnlyMySQL offer? The extremes first.

Thanks, MySQL. Losing a key is no nightmare!

Memcached is a high performance key value store for volatile data. Memcached is part of a classic scaling story: first, we buy a bigger machine for MySQL, then we try replication, then we cache database results in Memcached.

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 …