MySQL is an excellent choice for a low cost embedded database. Over 3,000 ISVs, OEMs and VARs choose MySQL for this purpose and benefit from significant cost savings when compared to using a competing database product.

The MySQL for Developers training course teaches you how to plan, design and implement applications using MySQL. Expert Oracle University instructors teach you through realistic examples, interactive instruction and hands-on exercises.

This course brings you lots of interesting facts. For example, the 'libmysqld' library is effectively a MySQL server in its own right. it is shipped as part of the standard MySQL installation. Developers with C/C++ ability can embed this library easily within their applications and it can do most of what a …

In MySQL 5.5 metadata locks were introduced to ensure consistency for some operations that previously could cause problems. For example if a transaction SELECTed from a table and another connection changed the structure of the table before the transaction was committed; in that case the two operations would be written in reverse order to the binary log which could prevent replaying the binary log.

However there was no way to see who held metadata locks. SHOW PROCESSLIST would show who were waiting for metadata locks, and often you could guess who held it, but it wasn’t so always. Consider for example the following:

mysql> SELECT Id, db, Command, Time, State, Info FROM information_schema.PROCESSLIST; …

On last Wednesday morning I gave my talk at linux.conf.au 2014. You can now view and download the recording of it here:

http://mirror.linux.org.au/linux.conf.au/2014/Wednesday/28-Past_Present_and_future_of_MySQL_and_variants_-_Stewart_Smith.mp4

(hopefully more free formats will come soon, the all volunteer AV team has been absolutely amazing getting things up this quickly).

My old team here at Oracle looking for talented, experienced MySQL experts to join the amazing MySQL Support Team.  I was lucky enough to join this great group of people over seven years ago, and while my responsibilities have shifted to other areas with the MySQL team at Oracle, I still join the team’s chat channel daily (where they’ve revoked my vowel and address me as “t*dd” to highlight my treachery in leaving the team).   I don’t know that it’s possible to find a better group of colleagues to work with than can be found in the MySQL Support Team.

The work itself is exciting – if you like solving problems, this is an incredible job.  I’m not talking about finding and quoting applicable sections of the MySQL reference manual.  I’m talking about owning customer problems like they are your own, taking the time to understand what’s really needed, and investing the (sometimes considerable) energy …

Anders Karlsson wrote about Some myths on Open Source, the way I see it a few days ago.  Anders' article is mostly focused on exploding the idea that open source magically creates high quality code.  It is sad to say you do not have to look very far to see how true this is.

While I largely agree with Anders' points, there is far more that could be said on this subject, especially on the benefits of open source. I love working on open source software. Here are three reasons that are especially important to me.

1.) Open source is a great way to disseminate technology to users.  In the best cases, it is this easy to get open source products up and running:

$ sudo apt-get install software-i-want-to-use

A lot of software companies ( …

Sequel Pro

I often use Sequel Pro when I'm getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It's a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in …

The performance of the mysqldbcopy, mysqldbexport, and mysqldbimport utilities has been optimized in MySQL Utilities 1.3.6. In the case of export/import there have been significant improvements. In particular, multiprocessing support has been added to these utilities and can be enabled with the new --multiprocess option. The option permits concurrent execution and makes the most of the CPU resources available (number of cores).

Multiprocessing is applied at different levels according to the operating system. For non-POSIX systems, multiprocessing is limited to the database-level whereas POSIX systems can make multiprocess at the table level.

More specifically, the mysqldbcopy and mysqldbexport utilities allow multiprocessing at the table level for non- Windows systems and database level for Windows system. The mysqldbimport utility allows multiprocessing at the file level independently from the OS.

…

Most technologies achieving high-availability for MySQL need a load-balancer to spread the client connections to a valid database host, even the Tungsten special connector can be seen as a sophisticated load-balancer. People often use hardware load balancer or software solution like haproxy. In both cases, in order to avoid having a single point of failure, multiple load balancers must be used. Load balancers have two drawbacks: they increase network latency and/or they add a validation check load on the database servers. The increased network latency is obvious in the case of standalone load balancers where you must first connect to the load balancer which then completes the request by connecting to one of the database servers. Some workloads like reporting/adhoc queries are not affected by a small increase of latency but other workloads like oltp processing and real-time logging are. Each load balancers must also check regularly if the database …

As we know, one of the most important config for InnoDB is the innodb_buffer_pool_size, it basically store the innodb data and indexes in memory, when MySQL receives a query and the InnoDB pages involved on that query are stored in the buffer, it does not need to go to the disk to return the result, which is much faster (memory speed vs disk speed).

As it is stored in memory, every time you restart your MySQL server it starts with a clean/empty buffer pool and usually it take some time to warm-up the buffer.
To speed up this process, we can configure 2 variables that will dump and reload the pages reference stored in the buffer, this is a new functionality added on MySQL 5.6 (it was presented on previous versions of Percona Server and MariaDB).

If you have your production server already running, we are going to set it to dump the content every time it shutdown:

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1; …

This is part of the ongoing work on improving the transaction life cycle management. In 5.7.2 we split the transaction list into two. The read-only transaction list and the read-write transaction list. There was another “virtual” list, the auto-commit non-locking read-only (AC-NL-RO) transaction list. The change in 5.7.2 was that by default a transaction was treated as read only and added to the read-only transaction list. Only when it was determined that the transaction was going to do an update we removed the transaction from the read-only list and moved it to the read-write transaction list. This initial add to the the read-only list forced the acquisition of the trx_sys_t::mutex. Acquiring the mutex during transaction start/begin has a cost. Promoting a transaction from read-only to read-write we had to acquire the trx_sys_t::mutex to add to the read-write transaction list and so that is not too expensive and is unavoidable. There is another …