PlanetMysql.ru — информация о СУБД MySQL

The Issue

I haven't blogged in a while an I have a long TODO list of things to publish: The repository for the SNMP Agent, video and slides of my OSCON talk and a quick overview of MHA master-master support. In the meantime, here's a little fact that I didn't know from MySQL CREATE VIEW documentation:

Although it is possible to create a view with a nonexistent DEFINER account, an error occurs when the view is referenced if the SQL SECURITY value is DEFINER but the definer account does not exist.

How can this be possible?

The Problem

For a number of reasons we don't have the same user accounts on the master than we have on the slaves (ie: developers shouldn't be querying the master). Our configuration files include the following line:

replicate-ignore-table=mysql.user

So if we create a user on the master, the user definition doesn't go through the replication chain.

So a VIEW can be created in the master, but unless we run all the proper GRANT statements on the slave as well, the VIEWs won't be effective on the slaves. Example from our slave (output formatted for clarity):

show create view view3\G
*************************** 1. row ***************************
                View: view3
         Create View: CREATE ALGORITHM=UNDEFINED 
               DEFINER=`app`@`192.168.0.1` 
               SQL SECURITY DEFINER VIEW `view3` AS select 
[...]

show grants for `app`@`192.168.0.1`;
ERROR 1141 (42000): There is no such grant defined 
for user 'app' on host '192.168.0.1'

The Solution

Once again, Maatkit's to the rescue with mk-show-grants on the master:

mk-show-grants | grep 192.168.0.1
-- Grants for 'app'@'192.168.0.1'
GRANT USAGE ON *.* TO 'app'@'192.168.0.1' 
IDENTIFIED BY PASSWORD '*password_hash';
GRANT DELETE, EXECUTE, INDEX, INSERT, SELECT, 
SHOW VIEW, UPDATE ON `pay`.* TO 'app'@'192.168.0.1';

A simple copy from the master and paste onto the slave fixed it.

Conclusion

Every now developers come to me with unusual questions. In this case it was: How come I can access only 2 out of 3 views?. In cases like these, it usually pays off to not overthink the issue and look into the details. A SHOW CREATE PROCEDURE on the 3 views quickly showed that one had a different host for the DEFINER. A quick read through the documentation and an easy test confirmed the mistake. That's why I have 3 mantras that I keep repeating to whomever wants to listen:

• Keep it simple
• Pay attention to details
• RTFM (F is for fine)

It constantly keeps me from grabbing some shears and going into yak shaving mode.
PlanetMySQL Voting: Vote UP / Vote DOWN

At Percona, we’re now using sphinx for our documentation. We’re also using Jenkins for our  continuous integration. We have compiler warnings from GCC being parsed by Jenkins using the built in filters, but there isn’t one for the sphinx warnings.

Luckily, in the configuration page for Jenkins, the Warnings plugin allows you to specify your own filters. I’ve added the following filter to process warnings from sphinx:

For those who want to copy and paste:

Regex: ^(.*):(\d+): \((.*)\) (.*)

Mapping Script

import hudson.plugins.warnings.parser.Warning

String fileName = matcher.group(1)
String lineNumber = matcher.group(2)
String category = matcher.group(3)
String message = matcher.group(4)

return new Warning(fileName, Integer.parseInt(lineNumber), "sphinx", category, message);

Example log message: /home/stewart/percona-server/docs-5.1/doc/source/release-notes/Percona-Server-1.0.2-3.rst:67: (WARNING/2) Inline literal start-string without end-string.

Then I can select this filter from the job that builds (and publishes) our documentation and it shows up like any other compiler warnings. Neat!

TODO: get the intersphinx warnings also in there

TODO: fix the linkcheck target in Sphinx so that it’s easily parseable and can also be integrated.

PlanetMySQL Voting: Vote UP / Vote DOWN

Why This Post

While testing Yoshinori Matsunobo's MHA agent I found that although the wiki has a very complete documentation, it was missing a some details. This article intends to close that gap and bring up some issues to keep in mind when you do your own installation. At the end of the article I added a Conclusions section, if you're not interested in the implementation details, but to read my take on the project, feel free to jump straight to the end from here.

My Test Case

Most of our production environments can be simplified to match the MHA's agent most simple use case: 1 master w/ 2 or more slaves and at least one more slave in an additional tier:

Master A --> Slave B

         +-> Slave C --> Slave D

As noted in the documentation, in this case the MHA agent will be monitoring A, B & C only. I found that unless you have a dedicated manager node, a slave on the 3rd tier (Slave D above) is suitable for this role. All 4 servers were setup as VMs for my evaluation / tests. It makes it easier to simulate hard failure scenarios in a controlled environment. Once this is in place the fun begins.

1st Step: User Accounts

In all the examples in the documentation it uses root to login into MySQL and the OS. I prefer to create specific users for each application, so I created a specific MySQL user for the MHA agent and used the linux' mysql user (UID/GID = 27/27 in RedHat / CentOS).

MySQL Credentials

Reviewing the code, I was able to determine that the agent requires to run some privileged commands like: SET GLOBAL variable, CHANGE MASTER TO ..., FLUSH LOGS ..., SHOW SLAVE STATUS, etc. and creates internal working tables to be used during the master fail over. The easiest way to set it up was using:

GRANT ALL PRIVILEGES ON *.* TO mha_user@'ip address'  

IDENTIFIED BY password;

This should be repeated on all 4 servers using the IP addresses for all the potential manager nodes. Yes, it would be possible to use wildcards, but I consider restricting access from specific nodes a safer practice.

The MySQL replication user needs to be set up to connect from any other server in the cluster, since any of the slaves in the group could be promoted to be master, and have the rest of them connecting to it.

Linux User

As I mentioned before I use the default RedHat / CentOS definition for the mysql user. Keep in mind that if you installed from the official Oracle packages (ie: RPMs), they may not follow this criteria and could result in mismatching UID/GIDs between servers. The UIDs/GIDs for the mysql user and group have to be identical on all 4 servers. If this is not the case, you may use the following bash sequence/script as root to correct the situation:

#!/bin/bash 

# stop mysql

/etc/init.d/mysql stop

 

# Change ownership for all files / directories

find / -user mysql -exec chown -v 27 {} \;

find / -group mysql -exec chgrp -v 27 {} \;

 

# remove old user / group and rename the new ones

# might complain about not being able to delete group.

groupdel mysql

userdel mysql 



# Add the new user / group

groupadd -g 27 mysql

useradd -c "MySQL User" -g 27 -u 27 -r -d /var/lib/mysql mysql

 

# restart MySQL

/etc/init.d/mysql start

Once the mysql user is properly setup, you'll have to create password-less shared keys and authorize them on all the servers. The easiest way to do it is to create it in one of them, copy the public key to the authorized_keys file under the /var/lib/mysql/.ssh directory and then copy the whole directory to the other servers.

I use the mysql user to run the scripts since for most distributions it can't be used to login directly and there is no need to worry about file permissions, which makes it a safe and convenient user.

2nd Step: Follow The Documentation to Install and Configure

Once all the users have been properly setup, this step is straight forward. Check the Installation and Configuration sections of the wiki for more details.

For the placement of the configuration files I deviated a little bit from documentation, but not much:

1. Used a defaults file: /etc/masterha_default with access only for user mysql since it includes the MHA agent password:
   

   -rw------- 1 mysql mysql 145 Aug 11 16:36 masterha_default.cnf

2. The application settings were placed under /etc/masterha.d/ this way they're easy to locate and won't clutter the /etc directory.

For simplicity, I didn't include any of the optional scripts and checks (ie: secondary check) in the configurate. You may want to check the documentation and source code of these scripts. Some of them are not even code complete (ie: master_ip_failover). Unless you are implementing some of the more complicated use cases, you won't even need them. If you do, you'll need to write your own following the examples provided with the source code.

Once you have everything in place, run the following checks as the mysql user (ie: sudo su - mysql):

1. masterha_check_ssh: Using my configuration files the command line looks like:
   

   masterha_check_ssh --conf=/etc/masterha_default.cnf --conf=/etc/masterha.d/test.cnf

2. masterha_check_repl: This test will determine whether the agent can identify all the servers in the group and the replication topology. The command line parameters are identical to the previous step.

Both should show and OK status at the end. All utilities have verbose output, so if something goes wrong it's easy to identify the issue and correct it.

3rd Step: Run the Manager Script

If everything is OK, on the MHA node (Server D in my tests) run the following command as user mysql (ie: sudo su - mysql):

masterha_manager --conf=/etc/masterha_default.cnf --conf=/etc/masterha.d/test.cnf

You have to keep in mind that should the master fail, the agent will fail over to one of the slaves and stop running. This way it'll avoid split brain situations. You will either have to build the intelligence in the application to connect to the right master when failing or use a virtual IP. In both cases you'll might need to use customized IP failover scripts. The documentation provides more details.

Read the section about running the script in the background to choose the method that best fits your practice.

You will have to configure the notification script to get notified of the master failure. The failed server will have to be removed from the configuration file before re-launching the manager script, otherwise it will fail to start.

You can restart the failed server and set it up as a slave connected to the new master and reincorporate it to the replication group using masterha_conf_host.

Conclusion

This tool solves a very specific (and painful) problem which is: make sure all the slaves are in sync, promote one of them and change the configuration of all remaining slaves to replicate off the new master and it does it fairly quickly. The tool is simple and reliable and requires very little overhead. It's easy to see it is production ready.

The log files are pretty verbose, which makes it really easy to follow in great detail all the actions the agent took when failing over to a slave.

I recommend to any potential users to start with a simple configuration and add the additional elements gradually until it fits your infrastructure needs.

Although the documentation is complete and detailed, it takes some time to navigate and to put all the pieces of the puzzle together.

I would like the agent to support master-master configurations. This way it would minimize the work to re-incorporate the failed server into the pool. Yoshinori, if you're reading this, know that I'll volunteer to test master-master if you decide to implement it.
PlanetMySQL Voting: Vote UP / Vote DOWN

I spent my day doing updates to the automysqlbackup script.  Here is some of what I’ve added over the last year.

The bug number fixes are from SourceForge.  https://sourceforge.net/tracker/?atid=628964&group_id=101066&func=browse

# 2.5.5 MTG – (2011-07-21)
#    – Bug – Typo Ureadable Unreadable config file line 424 – ID: 3316825
#    – Bug – Change “#!/bin/bash” to “#!/usr/bin/env bash” – ID: 3292873
#    – Bug – problem with excludes – ID: 3169562
#    – Bug – Total disk space on symbolic links – ID: 3064547
#    – Added DEBUG option to only print the commands that will be executed.
#    – Bug – WHICH command didn’t work if there was a WHICH alias.
# VER 2.5.4: MTG – (2011-01-28)
#    – fixed bug in rsync process.
#    – Added the ability to backup only a single table by naming the table like
#      database.table
# VER 2.5.2-02:  MTG – (2010-12-29)
#    – Added file promission settings (chmod) to directory and file creation points.
# VER 2.5.2-01:  MTG – (2010-11-06)
#    – Added ‘-R’ to the mysqldump options to include stored procedures in the backup
#      by default. (suggested by Zack Evans)
# VER 2.5.2:  MTG – (2010-11-04)
#    – Added option to archive (rsync) the local backup files to a remote locations
#      using the COPYDIR varaible.
#    – Added option to copy files into a directory based on the hostname using the
#      variable HOSTNAME.  This allows the script to be run from a shared storage directory
#      ( SBM, NFS, NetApp) the data to be kept seperate places.
#    – Added option to backup all database schemas only using variable FULLSCHEMA.
#    – Added option to backup MySQL configuration file, my.cnf and remove files older then seven
#      days from the BACKUPDIR directory.
#    – Added –master-data=2 and –single-transaction to include a comment with the master server’s
#      the binary log coordinates. If used the CHANGE_MASTER_TO line must be uncommented.

The project is at https://sourceforge.net/projects/automysqlbackup/

You can download my version at http://www.mysqlfanboy.com/Files/automysqlbackup.sh

Email me or leave a comment if you have any trouble.

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

In my last two posts I installed the HandlerSocket plugin into MariaDB and showed how to use it with Perl.  That’s good, but if you are thinking of using HandlerSocket  I’m guessing you have a very high traffic website and it’s written in PHP.  In this post I’m going to connect HandlerSocket with PHP.  In the next post I’ll discuss using HandlerSocket on a production system.

There are a couple of HandlerSocket php modules projects.  I tried each of them and I found PHP-HandlerSocket was the best.  Both of them are still rough and neither of them have documentation beyond their source code.  Maybe this will move things forward.

Here are the applications you need to have installed that where not installed in my last two posts.  Run this to check your system.

 yum install php-devel re2c php php-mysql

Start in the a Downloads directory and wget the newest tarball.

 cd ~
 mkdir Downloads
 cd Downloads
 wget http://php-handlersocket.googlecode.com/files/php-handlersocket-0.0.7.tar.gz

Compile and install the module.

 phpize
 ./configure
 make
 make install

With everything in place, you need to teach PHP to load the module my editing the PHP configuration file  and add the extension.

 vi /etc/php.ini

Search for the Dynamic Extensions section and add this line.

 extension=handlersocket.so

Run php to test that the module is loading correctly.  If nothing happens, your good.

I had some trouble at the start getting the libraries in the right directory.   This is bad:

 PHP Warning:  PHP Startup: Unable to load dynamic library  '/usr/lib64/php/modules/hadlersocket.so'
 -  /usr/lib64/php/modules/hadlersocket.so: cannot open shared object file:
  No such file or directory in Unknown on line 0

To do more testing we need a database and some data.  Here is what I created. I’ve colored the sections of code as they correspond to the output.

CREATE TABLE `user` (
 `user_id` INT (10) UNSIGNED NOT NULL,
 `user_name` VARCHAR (50),
 `user_email` VARCHAR (255),
 `created` DATETIME DEFAULT NULL,
 PRIMARY KEY (`user_id`),
 KEY `NAME` (`user_name`)
 ) ENGINE = INNODB ;

INSERT INTO `user`  VALUES
 ( 1, 'mark', 'mark@mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 2, 'linda', 'linda@mysqlfanboy.com', '0000-00-00 00:00:00' ),
 ( 3, 'mark', 'test@mysqlfanboy.com', NULL ) ;

I created this program to read this data:  (mytest.php)  HandlerSocket can write data to the database.  I’ve left writing as an exercise to the reader. (For now.)

<?php
 $host = 'db';
 $port = 9998;
 $dbname = 'test';
 $table = 'user';
 $index = 'NAME';
 $columns = 'user_id,user_name,user_email,created';

 $hs = new HandlerSocket($host, $port);
 if (!($hs->openIndex(0, $dbname, $table, $index, $columns)))
 {
 echo $hs->getError(), PHP_EOL;
 die();
 }

 $retval = $hs->executeSingle(0, '=', array('mark'), 10, 0);
 echo "data > ",$retval[0][1], " \n";
 var_dump($retval);

 $retval = $hs->executeMulti(
 array(array(0, '=', array('mark'), 1, 0),
 array(0, '=', array('linda'), 1, 0)));
 var_dump($retval);

 unset($hs);

 ?>

Here is the output of running mytest.php.

# php mytest.php
data > mark 
array(2) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "mark@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 [1]=>
 array(4) {
 [0]=>
 string(1) "3"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "test@mysqlfanboy.com"
 [3]=>
 NULL
 }
}
array(2) {
 [0]=>
 array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "1"
 [1]=>
 string(4) "mark"
 [2]=>
 string(20) "mark@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 }
 [1]=>
 array(1) {
 [0]=>
 array(4) {
 [0]=>
 string(1) "2"
 [1]=>
 string(5) "linda"
 [2]=>
 string(21) "linda@mysqlfanboy.com"
 [3]=>
 string(19) "0000-00-00 00:00:00"
 }
 }
}
 

So it works, but do you really want to use it?  There are a lot more to questions to investigate.   Is it writing data any faster then SQL? Is writing faster then a bulk file load?  What if you mix SQL and HandlerSocket calls?  What does buffering do to these processes?

You you have any questions about about what I’ve written or that you would like be to investigate, email me – mark @ Grennan.com.

Tweet

PlanetMySQL Voting: Vote UP / Vote DOWN

While it’s great that MySQL 5.5 is GA with the 5.5.8 release (you can download it here), I’m rather disappointed that the bzr repositories on launchpad aren’t being kept up to date. At time of writing, it looked like this:

Yep – nothing for five weeks in the 5.5 repo – nothing since the 5.5.7 release :(

There hasn’t been zero changes either – the changelog has a decent number of fixes.

PlanetMySQL Voting: Vote UP / Vote DOWN

Okay! So here we will use Load Data syntax to load file into MySQL Server in a Stored procedure. Yep! It’s a workaround. Download MySQL UDF: [root@localhost kedar]# wget http://www.mysqludf.org/lib_mysqludf_sys/lib_mysqludf_sys_0.0.3.tar.gz... Related posts:

1. Ideas for select all columns but one mysql stored procedure Assume we’ve a table with 100 rows and we need...
2. MySQL Stored procedure to Generate-Extract Insert Statement A lot of places I saw people asking for ways...
3. Stored procedure to add-remove prefix by rename table mysql Here is one more procedure – (this time) for mass...

Related posts brought to you by Yet Another Related Posts Plugin.
PlanetMySQL Voting: Vote UP / Vote DOWN

I wonder if this comes under “Code Style” or not…

Anyway, Monty and I finished getting Drizzle ready for adding “-Wframe-larger-than=32768″ as a standard compiler flag. This means that no function within the Drizzle source tree can use greater than 32kb stack – it’s a compiler warning – and with -Werror, it means that it’s a build error.

GCC is not perfect at detecting stack usage, but it’s pretty good.

Why have we done this?

Well, there is a little bit of recursion in the server… and we can craft queries to blow a small stack (not so good). On MacOS X, the default thread stack size is only 512kb. This gives not many frames if 32kb stack is a even remotely common.

I found some interesting places to throw a lot of things on the stack too – that would be rather far down on a callchain – leading to the possibility of blowing up in really strange ways.

We’d love to make it 16kb…. but that’s a fair bit more work, so something for the future.

We’ve used the Boost scoped_ptr to address a bunch of these situations as it provides pretty much minimal code change for the same effect (except that memory is dynamically allocated instead of as part of the stack frame).

PlanetMySQL Voting: Vote UP / Vote DOWN

Following on from my post yesterday on the various states of a Storage Engine, I said I’d have a go with the Cursor object too. A Cursor is used by the Drizzle kernel to get and set data in a table. There can be more than one cursor open at once, and more than one per thread. If your engine cannot cope with this, it is its responsibility to figure it out and return the appropriate errors.

Let’s look at a really simple operation, inserting a couple of rows and then reading them back via a full table scan.

Now, this graph is slightly incomplete as there is no doEndTableScan() call. But you can see in which order things are meant to happen. In this case, “store_lock()” means that store_lock() has been called, so when coming back from doInsertRecord() we do not call store_lock() again, rather, we’re just in a state where it has already been executed.

For MySQL handler, think ::write_row() for doInsertRecord() and ::rnd_init() for doStartTableScan().

This diagram was again auto-generated from my test engine.

PlanetMySQL Voting: Vote UP / Vote DOWN

Following on from my post yesterday on the various states of a Storage Engine, I said I’d have a go with the Cursor object too. A Cursor is used by the Drizzle kernel to get and set data in a table. There can be more than one cursor open at once, and more than one per thread. If your engine cannot cope with this, it is its responsibility to figure it out and return the appropriate errors.

Let’s look at a really simple operation, inserting a couple of rows and then reading them back via a full table scan.

Now, this graph is slightly incomplete as there is no doEndTableScan() call. But you can see in which order things are meant to happen. In this case, “store_lock()” means that store_lock() has been called, so when coming back from doInsertRecord() we do not call store_lock() again, rather, we’re just in a state where it has already been executed.

For MySQL handler, think ::write_row() for doInsertRecord() and ::rnd_init() for doStartTableScan().

This diagram was again auto-generated from my test engine.

PlanetMySQL Voting: Vote UP / Vote DOWN