MySQL 5.6-DBA-Beta-Exam is challenging

I took the MySQL 5.6-Exam today and it really is not easy. Lets get straight to the facts i remember:

  • I had 130 questions and 180 minutes time
  • I needed 155 minutes but i was a bit exhausted at the end so went a bit quicker through the questions as in the beginning. Normally i am a fast exam taker and only need a third of the time.
  • The option to eliminate obviously wrong answers by CTRL-Rightclick is often really helpful

What you should learn in the following order:

  • Performance Schema
  • Replication Features, especially GTID, and mysqlbinlog-usage
  • Partitioning
  • Optimizing MySQL Queries
  • MySQL Enterprise Backup-Features
  • Anything else (command line options, Windows Cluster, mysqldump, mysqlshow, etc..)

I had 3 or 4 questions where i thought that the answers (or the question) were not correct or no unmistakable but maybe i just didn’t get it :)

 

Share/Bookmark

MySQL 5.6-DBA and Developer-Certification in Beta-Status (low price!)

Recently Todd  blogged about why you should register for MySQL 5.6 certification exams now: http://mysqlblog.fivefarmers.com/2013/10/04/why-you-should-register-for-mysql-5-6-certification-exams-now/ and i also think that now is a good opportunity to renew or take a new mysql certification.

The most interesting point in my opinion is that you can save a lot of money if you take the exam NOW! The exam price is 50 dollar (39 Euro) till 14.12.2013.

So, if you want to update your, probably years old, mysql certification now is the time!  You can search for a test center and register for the exam here:

http://pearsonvue.com/oracle/

Be aware that you won’t get your results directly but after a few weeks. Also there are about 150-220 questions! Phew! All information about the exam can be found here:

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=5001&get_params=p_exam_id:1Z0-883#

I’ll take the exam in November and will let you know my opinion about the exam.

New MySQL 5.6-Feature host_cache_size does not work

Today as i was learning for the new MySQL 5.6-certification (more about that in a later post) i stumbled again across the host_cache-Table that was added to MySQL in 5.6.5. Before that you had no chance to check which hosts are already known by the MySQL-server.

So thats a pretty good feature for us. And even better: you could resize the size of the host_cache now! Whoohoo, awesome! As we have a lot of servers that need to connect to one of our MySQL-server and we could not switch to ip-based-authentication we were really happy to tune the host_cache-size without recompiling MySQL.

Unfortunately i noticed that the performance_schema.host_cache table only holds 128 rows and that the content was changing everytime i checked. So i logged in to a server that wasn’t already in the host_cache-table, made a connection attempt to the mysql server  and checked again the host_cache-table:

The server was now in the host_cache-table but still the table held only 128 rows though the host_cache_size-variable was set to 2000. :/

You can see my bug report here: http://bugs.mysql.com/bug.php?id=70552

Hopefully it won’t take Oracle years to fix this ;)

Running out of Disk Space? Move innodb-tables to another partition (with MySQL 5.6)

Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.

Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.

First get the 15 biggest tables:

root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY round(sum(data_length+index_length)) DESC LIMIT 15;
 Query OK, 0 rows affected (0.00 sec)

+------------------+-------------------------------+-----------+
 | table_schema | Table Name | Size (GB) |
 +------------------+-------------------------------+-----------+
 | bigdb | really_big_table | 55.2498 |
 | foo | bar | 25.6894 |
 | foo | bar2 | 20.1347 |
 | bigdb | asdf | 14.0842 |
 [...]

So, “bigdb.really_big_table” is the biggest table. Lets check how it was created:


 root@mysqlserver [(none)]> use bigdb;
 root@mysqlserver [(none)]> show create table really_big_table \G
 *************************** 1. row ***************************
 Table: really_big_table
 Create Table: CREATE TABLE `really_big_table` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `foo` varchar(30) NOT NULL DEFAULT '0',
 `bar` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 ) ENGINE=InnoDB AUTO_INCREMENT=1231243249 DEFAULT CHARSET=utf8

Create a new identical table with the data directory on the different partition:

root@mysqlserver [bigdb]> create table really_big_table_new ( \
 -> `id` int(11) NOT NULL AUTO_INCREMENT, \
 -> `foo` varchar(30) NOT NULL DEFAULT '0',\
 -> `bar` int(11) NOT NULL, \
 -> PRIMARY KEY (`id`), \
 ) ENGINE=InnoDB AUTO_INCREMENT=1231243249 DEFAULT CHARSET=utf8 data directory='/path/to/big_partition/mysql-datadir2/';

And then select everything from the current into the new table (Remember to STOP SLAVE if you do that on a SLAVE or LOCK TABLE if you do that on a production server):

root@mysqlserver [bigdb]> insert into really_big_table_new select * from really_big_table ;
Query OK, 38164689 rows affected (15 min 43.07 sec)
Records: 38164689 Duplicates: 0 Warnings: 0

Then drop the current table and rename the new one:

root@mysqlserver [bidb]> drop table really_big_table;
 Query OK, 0 rows affected (0.71 sec)
 root@mysqlserver [bigdb]> rename table really_big_table_new to really_big_table;
 Query OK, 0 rows affected (0.01 sec)

You have successfully moved the biggest to a different partition.

Please keep in mind that if you use binary logging that your binary logs gonna grow very big so maybe you want to purge them afterwards:

http://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html

phpMyAdmin breaks Replication in MySQL 5.6

Recently i updated to MySQL 5.6 and we were really excited about the very good overall performance. But beside a major bug concerning wrong results when running a SELECT that includes a HAVING based on a function (see http://bugs.mysql.com/bug.php?id=69638) we also noticed that from time to time the replication breaks with the following error:

Last_SQL_Errno: 1590
Last_SQL_Error: The incident LOST_EVENTS occured on the master. Message: error writing to the binary log

After some investigation it seemed like this happens if one modifies some user privileges, so we stumbled upon http://bugs.mysql.com/bug.php?id=68892.

Essentially the bug report says that if you use the wrong syntax for GRANT-statements the replication will break. So far, so bad. I told everyone who had the privileges to modify user privileges that they should really watch what they are doing and inform us if they accidentially used the wrong syntax.

Unfortunately that didn’t help. Not because they didn’t inform us but because phpMyAdmin sends a

REVOKE GRANT OPTION ON `database`@`table_name` FROM `user`@`host`;

if you modify the user-privileges for a specific table for example. If the user has no GRANT Option on that table the replication also breaks. MySQL throws the Error:

ERROR 1141 (42000): There is no such grant defined for user 'user' on host 'host'

Just adds the following to the binary log:

# Incident: LOST_EVENTS
RELOAD DATABASE; # Shall generate syntax error
# at 177

And the SLAVEs go out of sync. This should really be fixed as soon as possible but MySQL-Developers marked the bug only as “S3 (Non-critical)” so it seems that we gonna have to fix the replication very often in the next months or give console access to everyone who can grant/revoke privileges.

Sigh!