Today I was doing some tests with XA transactions in MySQL 5.6. The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column: The good news is that 5.7 has transaction information in performance_schema: mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode -> from performance_schema.events_transactions_current; +-----------------+-----------------+--------+--------+----------+-------------+ | trx_id | isolation_level | state | xid | xa_state | access_mode | +-----------------+-----------------+--------+--------+----------+-------------+ | NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE | | 421476507015704 | REPEATABLE READ | ACTIVE | NULL | NULL | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | foo-1 | ACTIVE | READ WRITE | | NULL | REPEATABLE READ | ACTIVE | NULL | NULL | READ ONLY | | NULL | ...
Posts
Today I encountered a situation where MySQL Enterprise Backup caused to much load on the I/O subsystem of the server to cause the application to be so slow that it wasn't usable any longer. So I wanted to limit the mysqlbackup process so it wouldn't cause any more issues. The mysqlbackup command has settings to for the number of read, write and process threads. The defaults are 1 read, 1 write and 6 process threads. So that isn't really useful for throttling as I was using the defaults. Using the ionice utility wouldn't work as that requires the CFG I/O scheduler. I found a solution in this blog post . It is to use cgroups on Linux. I had used cgroups before to test how a galera setup works when one of the three servers had a much slower CPU. # mkdir /cgroup/blkio # mount -t cgroup -o blkio non /cgroup/blkio # cgcreate -g blkio:/mysqlbackup # ls -lh /dev/mapper/vgdb01-lvdb01 lrwxrwxrwx 1 root root 7 Sep 26 14:22 /dev/mapper/vgdb01-lvdb01 -> ../dm-2 # ls -lh /...
In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions. In this post I will show you how to get even more information about what is locked by a transaction. As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says: "The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction." So if would like to know all locks held by a transaction this doesn't help. There is lots of information in the output of " SHOW ENGINE INNODB STATUS\G " in the section about TRANSACTIONS. ------------ TRANSACTIONS ------------ Trx id counter 8991 Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle History list length 50 ...
In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema. But what are the possible solutions? The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution. The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution. The third solution is to change the transaction isolation level of the blocking transaction to READ COMMITTED. mysql [test] > set transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql [test] > start transaction; Query OK, 0 rows affected (0.00 sec) mysql [test] > insert into t2 select * from t1; Query OK, ...
When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state. Example: mysql [information_schema] > select trx_id,trx_state -> from information_schema.innodb_trx; +--------+-----------+ | trx_id | trx_state | +--------+-----------+ | 7974 | LOCK WAIT | | 7962 | RUNNING | +--------+-----------+ 2 rows in set (0.00 sec) Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful. Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the server. The first session: mysql [test] > create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql [test] > insert...
It is possible to disallow users from using old_passwords=1. This can be done by adding ' maximum-old_passwords=0 ' to your my.cnf This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier. Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes. mysql> select @@old_passwords; +-----------------+ | @@old_passwords | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> set old_passwords=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect old_passwords value: '1' | +---------+------+----------------------------------------------+ 1 r...
Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax. We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again. There are two methods of downgrading: Dump/Restore with mysqldump. This is easy and reliable, but can take more time. In place (replace binaries, don't change data). This fast, but won't work if file formats have changed. As expected this is documented in the MySQL Reference Manual. I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for InnoDB. All tables used the A ntelope format. As both MySQL versions support A ntelope and B arracuda this shoud be fine. I don't know why Oracle didn't introduce a new C heeta format with the introduction of the fulltext indexes. The i...
I've been working on the CIS security benchmark for Oracle MySQL 5.6. There already is a benchmark document for earlier versions of MySQL. The benchmark document helps you to ensure you have a secure configuration for MySQL. Center for Internet Security (CIS) is a nonprofit organization. The Call For Participation is now open. If you are already a CIS member then you can download the document here . If you don't have an account you can register for free.
To get more familiar with docker and to create a test setup for MySQL Cluster I created docker images for the various components of MySQL Cluster (a.k.a. NDB Cluster) At first I created a Fedora 20 container and ran all components in one container. That worked and is quite easy to setup. But that's not how one is supposed to use docker. So I created Dockerfile 's for all components and one base image. The base image: contains the MySQL Cluster software has libaio installed has a mysql user and group serves as a base for the other images The management node (ndb_mgmd) image: Has ndb_mgmd as entrypoint Has a config.ini for the cluster config Should be started with " --name=mymgm01 " The data node (ndbmtd) image: Has ndbmtd as entrypoint Uses the connect string: " host=${MGM01_PORT_1186_TCP_ADDR}:1186 " Should be started with " --link mymgm01:mgm01 " to allow it to connect to the management node. You should create 2 container...
MySQL Enterprise Backup 3.10 support backups to the cloud. The only supported cloud service is Amazon S3. When the cloud destination is used mysqlbackup will upload the backup as an image file. You can specify all options on the commandline: mysqlbackup --cloud-service=s3 --cloud-aws-region=eu-west-1 \ --cloud-access-key-id=AKIAJLGCPXEGVHCQD27B \ --cloud-secret-access-key=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 \ --cloud-bucket=meb_myserver --cloud-object-key=firstbackup --cloud-trace=0 \ --backup-dir=/tmp/firstbackup --backup-image=- --with-timestamp backup-to-image But you can also put the settings in the my.cnf [mysqlbackup_cloud] cloud-service=s3 cloud-aws-region=eu-west-1 cloud-access-key-id=AKIAJLGCPXEGVHCQD27B cloud-secret-access-key=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 cloud-bucket=meb_myserver cloud-trace=0 backup-dir=/data/cloudbackup backup-image=- with-timestamp The with-timestamp option is important as the backup won't start if the backup-dir already...
This Tuesday Markus Winand will talk at the MySQL User Group NL meetup about "Indexes: The neglected performance all-rounder" . Markus is known for the http://use-the-index-luke.com website and the SQL Performance Explained book. Date: Tuesday August 12 Location: Marktplaats/eBay Office Amsterdam Registration: http://www.meetup.com/MySQL-User-Group-NL/events/196440532/
In a comment on my post about Using SSL with MySQL xiaochong zhang asked if it is possible to decode SSL/TLS encrypted MySQL traffic. The short answer is: It depends. To test this we need a MySQL server which is SSL enabled. I used MySQL Sandbox to create a sandboxed 5.6.19 server. Then I used mysslgen to create the config and the certificates. $ make_sandbox 5.6.19 $ ./mysslgen.py --config=sandboxes/msb_5_6_19/my.sandbox.cnf --ssldir=sandboxes/msb_5_6_19/ssl This assumes there already is a extracted tarball of MySQL 5.6.19 in ~/mysql/5.6.19 The mysslgen.py script will return a message with the changes you should make in your mysqld and client sections of the my.sandbox.cnf file. Then restart the server to make it active. For SSL to work we need to connect using TCP/IP instead of over a UNIX socket. So we connect with "./my sql -h 127.0.0.1". Now execute "\s" or "status" to see if we're indeed using SSL. It probably looks like this: mys...
Oracle has released the Critical Patch Update (CPU) for July 2014. The Oracle MySQL Risk Matrix lists 10 security fixes. It took me some time to understand the subcomponent names. So here is the list with the full name of each subcomponent: Subcomponent Full name SRFTS Server: Full Text Search SRINFOSC Server: INFORMATION_SCHEMA SRCHAR Server: Character sets ENARC Engine: Archive SROPTZR Server: Optimizer SRREP Server: Replication SRSP Server: Stored Procecure ENFED Engine: Federated I don't think there is anything really important in the list, but it might be a good trigger to update to the latest release. Upgrading should be easy especially if you're using the APT or YUM repositories from Oracle. If you're upgrading to a new major release (e.g. from 5.5 to 5.6) then you should read the instructions in the reference manual.
After MySQL 5.1 was released work started on MySQL 5.2, but then this was renamed to MySQL 6.0. There were many new features introduced in 6.0. But then stabilizing this branch became as huge task. Eventually the decision was made to start of with a stable branch and backport the new features from 6.0. This is how many of the 6.0 features landed in 5.5 and 5.6. So let's see which features 6.0 brought and were they landed. I'll use the What Is New in MySQL 6.0 section of the MySQL 6.0 Reference Manual for this. The Falcon storage engine . This never landed anywhere as far as I know. It's not even included in the list of storage engines in the MariaDB knowledgbase. As both InnoDB and MySQL are now part of Oracle I don't see any reason for Falcon to exist anymore. 4-byte utf8 and support for utf16 and utf32 . This is included in MySQL 5.5 together with many other Unicode enhancements. Database backup with SQL . This allows you to make backups by executing 'BA...
I was recently asked about if MySQL Enterprise Backup would be able to restore single databases. My initial answer was that this was complicated, but might be doable with the Transportable Table Space (TTS) option. But first let's go back to the basics. A common way of working with mysqldump is to get a list of databases and then loop through the databases and dump the data and schema to a SQL file. But both backups and restores will take a lot of time if the size of the database grows. And it's a luke-warm backup at best instead of a hot backup. So that's why we have MySQL Enterprise Backup. MySQL Enterprise Backup allows you to make a hot backup of InnoDB tables by copying the datafiles while watching the InnoDB redo log files. On disk the data from the InnoDB storage engine consists of a system tablespace (one of more ibdataX files), the redo log files (iblogfileX) and zero or more table-specific tablespace files (*.ibd). The data dictionary data is located in ...
MySQL Bugs On Dolphins, Panda's and Bugs Like any good OpenSource project the MySQL Bugs website is open for anyone to search through. This ofcourse doesn't include the security bugs. There is a second collection of bugs in the My Oracle Support and these bugs are only accesseble by customers with a support contract. Even when I have access to MOS I still prefer to use the community bugs site. For service requests etc. I would use MOS. The openness of the bugs database is one of the topic the IOUG MySQL Council discusses with Oracle. The bugs database has more to offer than just information about initial bugs: Bugs Statistics : This has a big matrix with components and states, some per developer stats and some totals (Did you known the fastest bug closure was 9 seconds?). Bugs Tide This gives you per month statistics. For both there are some filter option for version and whether to include feature requests. ...
