Tuesday, September 24, 2013

MySQL a Global Community

I am encouraged by the response to this blog so thank you to all that have read it.

Since MySQL is a global community. I figured I would point out the global interest that I have tracked via this blog. This by no means can determine the only interest in MySQL overall per region. Yet, I have found it interesting to see the different topics that the different countries/languages are focused on. The topics actually do vary.  Maybe you can also find something helpful and maybe it can help direct more support to the non-English community.

I will not break it down by country but instead by language to reflect the different blogs.






MySQL YUM Repo (Oracle's, MariaDB's and Percona's)

Lots of people today prefer to stick with the yum package manager when installing their related software over downloading the latest RPM from MySQL for example.

While you can download RPMS from a vendor and install with yum (yum install *.rpm ) you also can update your yum repo to pull directly from the Vendor for MySQL packages. At the time of this post you will only get you up to MySQL 5.5.13 even though MySQL 5.6 GA was released 5 February 2013 via the Oracle repo. Now that MariaDB has released MariaDB-5.5.33 I would hope Oracle will get a move on and update their public repo.

Regardless of what you choose. Here is how to set up vendor repos so you can access what you would like.

All of the instances have pages that I have listed that are easy to follow and set up. I will go ahead and give examples as well.

I will use CentOS 6 64bit for these examples.

In all of the  cases you will be working from the yum.repos.d directory as root.
cd /etc/yum.repos.d

wget https://public-yum.oracle.com/public-yum-ol6.repo
# vi public-yum-ol6.repo
Locate the following and edit enabled to 1 from 0 then save the file.

name=MySQL for Oracle Linux 6 ($basearch)

yum list | grep MySQL
mysql.x86_64                              5.5.34-1.el6                ol6_MySQL
mysql-devel.x86_64                        5.5.34-1.el6                ol6_MySQL
mysql-embedded.x86_64                     5.5.34-1.el6                ol6_MySQL
mysql-embedded-devel.x86_64               5.5.34-1.el6                ol6_MySQL
mysql-libs.x86_64                         5.5.34-1.el6                ol6_MySQL
mysql-libs-compat.x86_64                  5.5.34-1.el6                ol6_MySQL
mysql-server.x86_64                       5.5.34-1.el6                ol6_MySQL
mysql-test.x86_64                         5.5.34-1.el6                ol6_MySQL

vi MariaDB.repo

MariaDB does offer you the choice to pick 5.5 OR 10, I used 5.5 for this example.

# MariaDB 5.5 CentOS repository list - created 2013-09-24 21:59 UTC
# http://mariadb.org/mariadb/repositories/
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64

MariaDB-Galera-server.x86_64              5.5.32-1                    mariadb  
MariaDB-client.x86_64                     5.5.33a-1                   mariadb  
MariaDB-common.x86_64                     5.5.33a-1                   mariadb  
MariaDB-compat.x86_64                     5.5.33a-1                   mariadb  
MariaDB-devel.x86_64                      5.5.33a-1                   mariadb  
MariaDB-server.x86_64                     5.5.33a-1                   mariadb  
MariaDB-shared.x86_64                     5.5.33a-1                   mariadb  
MariaDB-test.x86_64                       5.5.33a-1                   mariadb  
galera.x86_64                             23.2.6-1.rhel6              mariadb

 vi Percona.repo

name = CentOS $releasever - Percona
enabled = 1
gpgkey = file:///etc/pki/rpm-gpg/RPM-GPG-KEY-percona
gpgcheck = 1

percona-toolkit.noarch                    2.2.4-1                     @/percona-toolkit-2.2.4-1.noarch
percona-xtrabackup.x86_64                 2.1.3-608.rhel6             @/percona-xtrabackup-2.1.3-608.rhel6.x86_64
Percona-SQL-50-debuginfo.x86_64           5.0.92-b23.89.rhel6         percona  
Percona-SQL-client-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-devel-50.x86_64               5.0.92-b23.89.rhel6         percona  
Percona-SQL-server-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-50.x86_64              5.0.92-b23.89.rhel6         percona  
Percona-SQL-shared-compat.x86_64          5.0.92-b23.89.rhel6         percona  
Percona-SQL-test-50.x86_64                5.0.92-b23.89.rhel6         percona  
Percona-Server-51-debuginfo.x86_64        5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-55-debuginfo.x86_64        5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-56-debuginfo.x86_64        5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-client-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-client-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-client-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-devel-51.x86_64            5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-devel-55.x86_64            5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-devel-56.x86_64            5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-server-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-server-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-server-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-51.x86_64           5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-shared-55.x86_64           5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-56.x86_64           5.6.13-rc60.6.427.rhel6     percona  
Percona-Server-shared-compat.x86_64       5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-shared-compat-51.x86_64    5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-51.x86_64             5.1.71-rel14.9.589.rhel6    percona  
Percona-Server-test-55.x86_64             5.5.33-rel31.1.566.rhel6    percona  
Percona-Server-test-56.x86_64             5.6.13-rc60.6.427.rhel6     percona  
Percona-XtraDB-Cluster-client.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-debuginfo.x86_64   1:5.5.33-   percona  
Percona-XtraDB-Cluster-devel.x86_64       1:5.5.33-   percona  
Percona-XtraDB-Cluster-galera.x86_64      2.7-1.157.rhel6             percona  
                                          2.7-1.157.rhel6             percona  
Percona-XtraDB-Cluster-server.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-shared.x86_64      1:5.5.33-   percona  
Percona-XtraDB-Cluster-test.x86_64        1:5.5.33-   percona  
jemalloc.x86_64                           3.3.1-1.el6                 percona  
jemalloc-devel.x86_64                     3.3.1-1.el6                 percona  
percona-cacti-templates.noarch            1.0.4-1                     percona  
percona-nagios-plugins.noarch             1.0.4-1                     percona  
percona-playback.x86_64                   0.6-2.el6                   percona  
percona-playback-debuginfo.x86_64         0.6-2.el6                   percona  
percona-playback-devel.x86_64             0.6-2.el6                   percona  
percona-xtrabackup.x86_64                 2.1.5-680.rhel6             percona  
percona-xtrabackup-20.x86_64              2.0.8-587.rhel6             percona  
percona-xtrabackup-20-debuginfo.x86_64    2.0.8-587.rhel6             percona  
percona-xtrabackup-20-test.x86_64         2.0.8-587.rhel6             percona  
percona-xtrabackup-test.x86_64            2.1.5-680.rhel6             percona  
qpress.x86_64                             11-1.el6                    percona  
qpress-debuginfo.x86_64                   11-1.el6                    percona

Hopefully this will help you all be able to get updated beyond what might be in your standard repos at the moment.

 I should also point out that MariaDB and Percona also have additional repos for other Linux distros.

Monday, September 23, 2013

ERROR 1146 (42S02): Table doesn't exist

So some of you might have run across the following errors when installing MySQL 5.6 :
  • ERROR 1146 (42S02): Table 'mysql.innodb_index_stats' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.innodb_table_stats' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_master_info' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_relay_log_info' doesn't exist
  • ERROR 1146 (42S02): Table 'mysql.slave_worker_info' doesn't exist
You are likely amazed that you see this error on a fresh database install. You are not alone. The issue is fixable though.

The safest thing to do is to reinstall the mysql database via the following command: mysql_install_db
I recently had to do this on every fresh install (yes it happened more than once) of MySQL 5.6 on a Solaris Sparc environment.

You can try to use the following to create the missing tables but I found it best to keep everything clean and ensure all is set up with the mysql_install_db.
Some do recommend the launchpad fix I mentioned above but I like I said I prefer the mysql_install_db to ensure everything is linked installed correctly.

I have other blog posts that include examples on using this command :

Related posts on this topic:
 If you run across this from tables outside of the mysql_install_db scope see Peter's blog post to help get you started:

Tuesday, September 10, 2013


So I was recently working with mysqld_multi  and I realized that this was a feature that  I do not see in very many blog posts these days. They do exist and I have listed some at the bottom of this post for your reference.

Your reasons are likely to vary and also be debatable when it comes to the concept of: should run more than one MySQL instance on the same hardware.

To avoid any confusion, if you want to install another MySQL instance for testing purposes and not as a production instance, then you should just work with MySQL Sandbox.  If that for some reason that does not work, you can execute another server like many people typically do: create new my.cnf files and start the mysql server with the mysqld_safe and custom commands. 

Mysqld_multi makes it a lot easier for you to run multiple servers.

For example:
You have a secondary server running on port 3306. It is a read_only slave and you have a lot of hardware in place waiting to become the new primary server when the current primary fails.  You also would like to take advantage of the Percona toolkit and have a replicated secondary server that is running in a delayed mode. If you could update to MySQL 5.6 then you would not need pt-slave-delay but currently that is not an option.

In either case, you have budget limits and are not allowed another server. So do you give up? You have the disk space to hold another version of the server on your secondary box so why not? The idea of having to start and stop custom versions and etc can be off putting to some. So instead you can set up a new version of the my.cnf file but first you can do the following.

Pick you favorite editor (ie: vi )
vi /etc/multi_my.cnf
mysqld = /usr/local/bin/mysqld_safe
mysqladmin = /usr/local/bin/mysqladmin
user = mysql
log = /var/log/multi_mysql.log

# Port 3306 Server
>socket = /tmp/mysql_3306.sock
port = 3306
pid-file = /var/lib/mysql/mysql_3306.pid
datadir = /var/lib/mysql/
user = mysql
Now you can take the [mysqld] section from your my.cnf file and copy it into this location.

cat /etc/my.cnf >> /etc/multi_my.cnf
If you use the command above edit to clean up so you just have the  [mysqld] section copied over.

You can then create the 3307 port section.
# Port 3307 Server
socket = /tmp/mysql_3307.sock
port = 3307
pid-file = /var/lib/mysql2/mysql_3307.pid
datadir = /var/lib/mysql2/
user = mysql
And example of the configuration can be found here:

For this example I will assume you will create a backup of Port 3306 server with Percona Xtrabackup and place it into the new datadir.
innobackupex --defaults-file=/etc/my.cnf --user=root --password=<password> --port=3306 --no-timestamp  /var/lib/mysql2/
innobackupex --apply-log /var/lib/mysql2/
 Now you can test this now with the mysqld_multi binary (/usr/bin/mysqld_multi ) or set up at start and stop script. A template comes with your MySQL install: /usr/share/mysql/mysqld_multi.server

You can copy this to your init.d directory or test it from the current location.
The script will default to the /etc/my.cnf file. So to start test this with the --default_file=/etc/multi_my.cnf report

The report option is the similar  to the status argument to see if the server is running.  If you choose to run this as the default process you can symlink or copy the /etc/multi_my.cnf as the new /etc/my.cnf
/etc/init.d/mysqld_multi.server report 1,2
/etc/init.d/mysqld_multi.server report 1
/etc/init.d/mysqld_multi.server report 2

The above would then give you running status for each give argument that of course references to a different MySQL instance. You can do the same the all of the following options : {start|stop|report|restart}

If everything went well you can "start 2" which will start the instance on port 3307.  Then log in and change master with the binlog position information provided in the xtrabackup_binlog_info file.
MASTER_LOG_FILE='<log filename>',

Start slave;
By now you have a duplicate copy of your secondary slave server.  If using pt-slave-delay you can execute the following command, the default is an hour delay.
pt-slave-delay   --port=3307 --socket=/tmp/mysql_3307.sock  --host=localhost

Hope this can at least get you started.

Friday, September 6, 2013

MySQL access and replication blocked by secure_auth

ERROR 2049 (HY000): Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled)

If you have tried to connect to a MySQL database and you see this error then you need to have valid 41byte hash password.  If you are unsure which you have execute the SQL below. If you have 16 character passwords they are older passwords.

select Password from mysql.user;

The following is how I solved this as part of a migration from MySQL 5.0 to MySQL 5.6.

The MySQL 5.0 server had a mixture of the older pre 4.1 passwords and valid 41byte passwords.  Because the MySQL 5.0 server had some accounts with the older passwords I decided to not dump the MySQL table as part of setting up replication. I did dump all of the databases except the mysql database. This allow ensured that I would keep the valid MySQL 5.6 table enhancements.

The MySQL 5.6 server installed easily and was up and I loaded the dump data.  Part of the migration was to use replication while they evaluated the new database. While on the MySQL 5.6 server I tested the replication user account. The response I got was the error at the top of this page. Replication will not run of course without a valid user account. This is why the error logs was giving me this error:
[ERROR] Slave I/O: error connecting to master '<user>@<hostname>:3306' - retry-time: 10  retries: 68, Error_code: 2049

A quick review of the account on the MySQL 5.0 server showed that the new account was established with the pre 4.1 password.  So I needed to upgrade the account to a valid 41 byte password.

The following query showed that they did indeed have old passwords enabled. So I have to disable that and update the user account again to set the password as a valid 41 byte hash.

>SELECT @@session.old_passwords, @@global.old_passwords;
| @@session.old_passwords | @@global.old_passwords |
|                       1 |                      1 |
1 row in set (0.00 sec)

>SET @@session.old_passwords = 0;
Query OK, 0 rows affected (0.00 sec)

>GRANT REPLICATION SLAVE ON *.* TO '<user>'@'<ip_address>' IDENTIFIED BY '<Password>';
Query OK, 0 rows affected (0.00 sec)

A check of the password showed the password as the 41byte password now. I was this able to connect to the primary server from the secondary server and avoid the secure_auth error. replication connected easily and problem was solved.

Going forward I needed to get the MySQL 5.0 users accounts onto the MySQL 5.6 server. ( since I skipped them as part of building the secondary server. )

The client needed to set the grants again for each user regardless of valid password or not.
So I instructed them to execute the following sql. I could have done this but I would need to know all of their passwords and that was not needed.

For each user in their system. You do not have to do the root user because you already have a valid root account on the 5.6 system.

>SET @@session.old_passwords = 0;
>show grants for '<User>'@'<Host>';
To gather the sql needed for each user run the following :
SELECT CONCAT("SHOW GRANTS FOR '",User,"'@'",Host,"';") as sql_command from mysql.user;

For each result given execute the "show grants" statement and then execute the statement given.
The statements should be similar to the following:

GRANT USAGE ON *.* TO 'bob'@'%.example.org' IDENTIFIED BY 'cleartext password';

Replication then created and populated the MySQL table on the MySQL 5.6 server.

More can be found here:

Also as an update --

Check out http://dev.mysql.com/doc/refman/5.6/en/account-upgrades.html

Monday, September 2, 2013

MySQL Optimization Tip - thread_cache_size

Recently I encountered a MySQL database that easily was running with 300 to 600 rows in the processlist. The Max connections was set easily more than twice this amount as well. This was a set up that I just do not agree with. I was called in because it also proved itself not to be working out very well. So here are a few of my thoughts on the process I uncovered.

  1. In my opinion the majority of MySQL databases in use will not need a max connections or 1500 or more. The more connections you allow the more overhead you bring to your server. Use your connections efficiently. 
  2. Second, Understand the % of threads_created versus the connections used. You might consider this the threads created hit rate.  BTW.. This is not new  information, this is information that has been understood in the community for some time. I do not pretend to present this in any other way than trying to help others. So do the following to understand your current %
    •  show status like 'Threads_created';
      •  set @Threads_created=< result from query above>;
    • show status like 'Connections';
      •  set @Connections=< result from query above>; 
    • select 100 - (( @Threads_created / @Connections ) * 100) as "Threads_created % of Connections"\G

So if your execute the process above what is your percentage ? You want this to be as close to 100 as possible. So for example the server I recently encountered had a % under 10%.  So how do you fix this and raise your %?

 The variable thread_cache_size has a default of 0. If you start to notice your process grow and but queries are not blocked by deadlocks and etc, then you should check your "Threads_created % of Connections" as mentioned above. It is likely that your % will be low. You can raise the % and drastically improve performance of your database by finding the sweet spot that fit your server environment. The thread_cache_size can be changed in a live environment. So this allows you to set the variable then monitor the status value of the "Threads_created" (see above to get value). If this continues to increment in value then continue to raise the thread_cache_size. Typically I prefer to raise the value by 25 at a time for a few then move to 500 at a time. I often check the "Threads_created % of Connections" and the 'Threads_created'. Once you get close to the sweet spot you will notice the % to gain and the processlist to begin to drop in rows. Typically one more adjustment of the thread_cache_size will get you into the sweet spot.

Every server and environment is different.
Some servers might be 98% with a thread_cache_size of 50 while others have a 98% with the thread_cache_size set to 15000. The max is 16384.

So if nothing else... find out what your percentage is first then look into making adjustments.