Want to develop and use applications with PHP and the MySQL database? Consider taking the MySQL and PHP: Developing Dynamic Web Applications training course.

Before taking this course you should:

• Understand how HTML files are assembled
• Understand fundamental PHP syntax
• Have some programming experience (preferably PHP)
• Have some experience with relational databases
• Have some knowledge of Object-Oriented Programming

This 4-day live, instructor-led course is perfect for developers who use PHP and MySQL to build and maintain their websites and who want to learn how PHP and MySQL can be used to rapidly prototype and deploy dynamic websites.

You can take this course as a: …

If you install a MySQL Database Server with the default options, your data are insecure and your server is in risk of invasion and some performance issues will appear shortly. With some best practices, your MySQL database becomes secure and the performance goes well. 1 – Set root password and change its login name As... Read More

The post MySQL Security Best Practices appeared first on Devops for Dummies.

The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu.

So here is how it works on Ubuntu (and probably also on other Debian based systems).

Please note that the PAM authentication plugin is an enterprise feature.

1. Make sure the plugin is loaded

This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart.

plugin-load=authentication_pam.so

2.  Add a user which will use the plugin

mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)

3. Add a pam config file for 'mysql':
Create /etc/pam.d/mysql with the following …

A Proxy is a software that acts as an intermediary between clients seeking resources from servers.

In the MySQL world, the most common proxies are:
- MySQL Proxy
- HAProxy

While the first one is able to understand the MySQL protocol and provides a lot of functionality, it is often not used in production as tagged as very slow, unstable and CPU hungry.
HAProxy is widely used with MySQL setups as it is said to be very efficient in term of performance and very stable, although it acts as a Layer 7 proxy and doesn’t recognize the MySQL protocol.

The adjectives slow and efficient are meaningless words if not supported by numbers and fact, so let’s try to make some real comparison.



We are going to tests performance of mysqld vs MySQL Proxy vs HAProxy running 2 very simple benchmark :
1) sysbench OLTP read-only;
2) mysqlslap …

I concluded my previous post stating that what is really missing is a proxy that:- is stable as HAProxy- scales like MySQL Proxy- is rich of features

That is what is driving my development for ProxySQL : a high performance proxy for MySQL.


Some background first.In the past I have worked with customers that, after providing them a detailed SQL review on how to improve performance rewriting queries, answer with the usual "we can't modify the queries" , and the most common reasons for this are, among others:- the queries are generated by ORM ;- they don't own the application ;- they don't have the time to dig the code and rewrite the queries .
It is a quite spread false believe that adding indexes and tuning MySQL can magically improve performance of badly written queries, but the truth is far from that. …

I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP(6)       |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:

mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6));
ERROR 1067 (42000): Invalid …

MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps?

 …

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

• Create and utilize table partitioning

• Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
    -> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
    -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name   |
+----+--------+
|  4 | test04 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 …

For the MySQL DBA Exam, I wrote that I needed to brush up on Security and High Availability techniques. So let’s take a look at these two in more detail.

Security

There were quite a lot of enhancements to security introduced in MySQL 5.6, and I want to make sure I’m not caught off guard by new questions. The major changes (with links to manual pages) are:

• Password expiration support
• Enforce password policy such as minimum length or …

People really don’t like Oracle. Enough so that SkySQL just got $20 Million in funding from Intel to help it continue to build a MySQL alternative. Now personally I don’t have the hatred that a lot of people do for Oracle, but when I look at the pricing and service offerings around Oracle’s database, Solaris operating system, and even things like ATG, I know that we can offer them comperable solutions at half the price, with far better service, so I get why people want to try to find alternatives to Oracle.

But here is what I don’t get. This week I went to the All Things Open conference and while I was there, I happened to catch the tail end of a SkySQL talk on new MariaDB features. One of the features that he was describing apparently has …