Simon Krenger

In the past few months, I worked closely with Packt Publishing to create a new video course for MySQL 8. Today, the video course was released into the world. I am pround to present to you the new video course “MySQL 8 Recipes“:

In this video course, I tried to include the most common tasks for database administrators while focussing on the new features of MySQL 8. I included the following main chapters:

• Install and configure a new MySQL 8 database
• Upgrade existing databases to MySQL 8
• Perform typical administration tasks
• Master the new querying features in MySQL 8
• Perform performance-tuning tasks
• Optimize your database
• Access databases with PHP, Python, or Java

The video course was released under ISBN-13 9781788393638 and is available per now. Of course, since MySQL 8 is still under development, there will hopefully be many other new features in the new release. But I hope you enjoy this video course. Purchase it here.

Anyone who has worked with Oracle has encountered the Oracle “hr” schema in one way or another. The Oracle example schema provides a few simple tables with example data to test out some queries or learn SQL.

So when working with MySQL, I like to have the same schema as well. Luckily, Andrei Ciobanu feels the same way and he provides a wonderful adaptation of this sample schema for MySQL on his website: HR Schema for MySQL and Maria DB.

The SQL script for the schema can be found on GitHub: nomemory/hr-schema-mysql (or in my fork: simonkrenger/hr-schema-mysql).

Thank you Andrei!

In another post I explained how to create a full backup using MySQL Enterprise Backup. Of course, the most important part of the backup is not “making the backup” but the restore of said backup! A backup is worthless if you cannot restore it.

Since Google shut down its Reader service, I am a regular user of the Tiny Tiny RSS reader. Having my own RSS reader installation gives me more power regarding my privacy and the services I am using. Consider me a happy user.

However, there are some issued regarding full UTF8 support when using MySQL. When a feed uses UTF8 emoijs, tt-rss will throw up and report an error. Unfortunately, this only manifests itself with log entries like this:

As you might know, I am primarily an Oracle guy. This means that for all my backup needs, I am using Oracle Recovery Manager (RMAN). I recently had the task to implement MySQL Enterprise Backup for a MySQL database (mysql-advanced-5.6.15-linux-glibc2.5-x86_64). My most important resource for this task was the MySQL Enterprise Backup User’s Guide.

When writing an application in PHP, I like to use Justin Vincents ezSQL for database access. ezSQL allows you to write your query and get your results as a PHP object, as shown in the following example:

// Select multiple records from the database and print them out..
$users = $db->get_results("SELECT name, email FROM users");
 
foreach ( $users as $user )
{
            // Access data using object syntax
            echo $user->name;
            echo $user->email;
}

So one common task you might want to do is to present the retrieved result as a table. While the ezSQL library features a debug() method, this should obviously only be used during development. So if you want to display your result as a table, you’ve got to do it yourself and I hereby provide a snippet of code on how to do it…

So after you’ve fetched your result from the database you can use the following method to print a HTML table with your results:

For habere.ch, we launched a contest where we award a restaurant voucher to a random person that wrote a review of a restaurant on our site. For this, we will be using the following query:

SELECT DISTINCT comment_author,comment_author_email
FROM wp_comments
ORDER BY RAND()
LIMIT 1;

What this query does is, that it selects all distinct rows, orders them randomly and shows the first entry of this selection.

Note that this query is relatively slow and should not be used in a program. When you use EXPLAIN on this query, MySQL will explain to you that it will create a temporary table and run a sort. Then only one entry is returned. But when you just use it once to determine a winner, this will work just fine!

Today I had to import some data from a CSV file into a table on a MySQL server.
So here is how to do it:

LOAD DATA LOCAL INFILE '/importfile.csv' 
INTO TABLE test_table 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n' 
(field1, filed2, field3);

Source

Of course, you will need to have access to the machine where the database is running. As an alternative, I am sure there are developer tools that can enter the data remotely.

Today I wondered how my newly purchased Virtual Server performs under load. I am currently using lighttpd as a webserver and MySQL as the database for WordPress. I already knew that MySQL features the Query Cache, which stores the result of issued queries in memory (Oracle’s equivalent of this would be the Result Cache in Oracle 11g). I wondered how much this feature could improve performance on a normal WordPress blog.

I quickly wrote a small Java program to query my website (source code available here: TestWebPerfo.java) and retrieve the performance metrics from the HTML source code. I then ran it 500 times to get my metrics without the Query Cache turned on. Average time for building the website: 0.115 seconds.

Having security in mind, I had some concerns granting all privileges to the WordPress MySQL user (see the instructions from WordPress):

GRANT ALL PRIVILEGES ON databasename.* TO "wordpressusername" [..]

After all, with these privileges the WordPress user would be able to access other databases on this server and do whatever he likes. WordPress has become very popular and is a known target for exploits and the like (as a quick search on milw0rm.com will confirm). I didn’t like that idea.

So here is what I did:

GRANT SELECT, INSERT, UPDATE, CREATE, DELETE ON wordpress.* TO 'wordpress' IDENTIFIED BY 'mypass';

This works fine so far and I don’t think my WordPress installation needs more privileges. Note that the ALTER and DROP statements are missing from my list, which could interfere with future updates. But we’ll see…