Diamond Notes

I set up a new server pair today. You know people complain sometimes that replication isn’t durable enough (and when it breaks at 3:00 AM I don’t think it is durable enough ), but no one complains that it is too complex to set up.

step one - set up your servers with their operating systems and MySQL

step two - on the master you need to add the replication user:

GRANT REPLICATION SLAVE ON *.* TO ‘repl_user’@‘ip_of_new_slave_server’

IDENTIFIED BY ‘repl_user_password’;

step three - you need to get your starting information about the master (we assume that the master isn’t in production yet)

SHOW MASTER STATUS;

This will display the binary log file currently being written to and the log position in the file.

step four - starting the slave. Plug in the information you determined above. You will also need the IP address of the master server. On Linux run the ifconfig command, on Winodows it would be ipconfig.

RESET SLAVE;

CHANGE MASTER TO master_log_file=‘master_log’,

master_log_pos=master_log_position, master_user=‘repl_user’,

master_password=‘repl_user_password’, master_host=‘ip_of_master’;

START SLAVE;

SHOW SLAVE STATUS;

That’s it. You should be up and running.

So set up a slave server just for fun!!

When working with databases that allow for transactions you will often see the term ACID compliant. ACID is an acronym for:

Atomic - all (SQL) code is successfully executed or is canceled as a unit

Consistent -a database in a consistent state when a transaction begins is left in a consistent state by the transaction

Isolated - one transaction does not affect another

Durable - all changes that finish successfully are recorded properly in the database. Changes are not lost.

Transactions are groups of one or more SQL statements that have a beginning (denoted by BEGIN or START TRANSACTION) and an ending (denoted by COMMIT).  As Roland points out in the comments transactional support can also be achieved by using autocommit mode.  This is important when dealing with things such as financial information.  MySQL provides transaction support on multiple storage engines and ACID compliance along with it.

A simple transactional example might be beneficial.

Suppose two people wanted to move $4,000 from one account to another for the purchase of a car. Also assume that the bank database had a simple account table structure like the following:

create table accounts
(
 account_id int not null auto_increment primary key,
 balance float
) type = InnoDB;

Now to move the money from the account of customer one to the account of customer two we might do something like this in my very bad pseudo-code:

select balance from accounts where account_id = id_of_customer_one;

make sure that balance is greater than 4,000;

update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;

update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;

The problem with this is that if this is an interruption between the two update statements there is a problem. Suppose the server crashes or the power goes off. Now there is a problem. One account has 4,000 less dollars, but the other account didn’t receive it. The solution is a transaction. Adding a transaction makes our code look like this:

select balance from accounts where account_id = id_of_customer_one;

make sure that balance is greater than 4,000;

begin;

update accounts set balance = balance - 4000 where account_id = account_id_of_customer_one;

update accounts set balance = balance + 4000 where account_id = account_id_of_customer_two;

commit;

Now with the addition of the transaction statements the two updates will either commit or fail (and do what is called rollback..going back to the original state). If the power fails or the server crashes in between the updates the rollback will be performed when the server comes back up and everything will be as it was when the transaction began.

It is important that the created table is of the type that supports transactions. Innodb (like this example table) does support transactions. MyISAM does not support transactions and would just ignore the BEGIN and COMMIT statements.

To finish the discussion on the mk-table-checksum tool I wanted to let you know about the custom udf (user defined function) that Baron programmed for use in the maatkit toolkit. Essentially the udf provides a replacement for the md5 hash function. This function provides an impressive speedup. On two servers I ran the mk-table-checksum tool with the default setup and then installed the udf and re-ran the checksum tool. Here are the results:

Clearly it is a great benefit to use this udf if you are running table checksums.  So how do you set it up?  The source code is included in the tar.gz file that is distributed from the maatkit website.  Just download and unzip/untar.  In the included files there is a udf directory with one file.  To compile “just”:

gcc -fPIC -Wall -I/usr/include/mysql -shared -o fnv_udf.so fnv_udf.cc

The instructions are included in the source code.  Once you have it compiled, just copy the resultant file (fnv_udf.so) to the /lib directory and then run the command:

mysql mysql -e “CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME ‘fnv_udf.so’”

This adds the function to your MySQL server setup.  To test, log into mysql and make this call:

mysql> SELECT FNV_64(’hello’, ‘world’);

This should returns some results if everything is done properly.  Once your MySQL server “sees” the new udf maatkit will begin using it automatically.

Baron has an excellent background post on the fvn udf here: xaprb post

Thanks for the great tools Baron!!

Most production environments have a master/slave setup of servers. This provides the ability to easily perform backups, have warm fail-over if you have a hardware failure on the master, etc. Replication between the master and slave is fairly easy to set up, however as Baron points out here, it is possible (and probably quite common) for for a master and slave to “drift” out of sync.

Fortunately, not only did he point out the problem, he provides a fix for the problem. Included in the maatkit toolkit is the mk-table-checksum tool (and the related tool mk-table-sync which we won’t be discussing this time). It can tell you which tables on the master and and slave are out of sync and even tell you within a fair degree what rows are out of sync. Here is an example run:

mk-table-checksum  –chunksize 500000 –replicate=test.checksum localhost

What are we doing here? Well, the chunksize option specifies that if tables are larger than 500,000 rows (in this case) that they should be chunked — broken apart — when processing. This will mean that a table doesn’t get blocked for too long a period of time. The replicate option specifies that you are wanting the checksum tool to use the test.checksum table to store results in. This can be very helpful in providing an accurate picture of your results. The create command for this table should be:

CREATE TABLE `checksum` (
`db` char(64) NOT NULL,
`tbl` char(64) NOT NULL,
`chunk` int(11) NOT NULL,
`boundaries` char(64) NOT NULL,
`this_crc` char(40) NOT NULL,
`this_cnt` int(11) NOT NULL,
`master_crc` char(40) default NULL,
`master_cnt` int(11) default NULL,
`ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`db`,`tbl`,`chunk`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It doesn’t have to be in the test database by the way. You can put it wherever you want. Your option. Just be sure to specify the right database when running the mk-table-sync command.

The final option is really easy. It just specifies the location of the master server. In this case, I ran the command on the master server itself so localhost was specified.

It will take a while for this command to run your databases are of any size. Once it is done executing and your slave has caught up (if it lagged behind the master) than you just run the following:

mk-table-checksum –replicate=test.checksum –replcheck 2 localhost

This will give you the results you want. Some sample output:

mk-table-checksum --replicate=test.checksum –replcheck 2 localhost

Differences on h=server.slave

DB          TBL                     CHUNK CNT_DIFF CRC_DIFF BOUNDARIES
database_1 table_1		      0        0        1 `clientid` < 184723
database_1 table_2		      0        0        1 `clientid` < 184721
mysql       db                        0      -19        1 1=1
mysql       user                      0       -1        1 1=1

This shows that there were some differences on the slave. In the mysql database, on the slave, there are 19 fewer rows in the db table and one less row in the user database. This is because I have different permissions on my master and slave servers.

On database_1 I have two tables that are out of sync. The two table_1 tables (on the master and slave) have the same row count (shown by the cnt_diff column). However, the crc_diff column shows that there are differences in the contents of the columns on the two tables. The boundaries column show you which chunk of the table that the differences are located in. You can use this to figure out the exact problem yourself..or you can use the mk-table-sync tool to do this for you. I will do another “Tip of the Day” on that tool.

Tomorrow I will wrap up the talk on the mk-table-sync tool by showing how to use the UDF function that Baron programmed. Using it speeds up the run time of mk-table-sync quite significantly!!

If you are running MySQL on Windows this really doesn’t concern you.  However, especially for Linux, this is a relevant discussion.

I guess this will be subject to much debate.  However, in my mind, it is clear.  I have for a long time used the compiled binary versions of MySQL (that MySQL AB provides) rather than .rpm files (on RedHat) or .deb files (on Debian).  Why? Simply because I am not tied down to a vendor’s idea of what version of MySQL I should be using.  I determine what version of MySQL we run in house.  And I promise you that my servers run a OS that includes an older package of MySQL than I am currently running in production.  I just checked.  It is quite a bit older.  Now, mind you, I have absolutely nothing against the operating system itself.  It is a fine operating system.  Using the compiled binaries of MySQL gives me control over what goes on and as a DBA I like that.

I suppose there is a downside to this.  The vendor could fix a security problem before MySQL makes a fix available.  Unlikely but theoretically possible.

Every spring MySQL puts on the MySQL Users Conference in the San Francisco area of the United States.  Almost 2,000 users gather for four days to listen to some of the experts of the MySQL world.  Consider it a Users Group meeeting on steroids.  There are other conferences in other countries, but this is the one that I am the most familiar and according to MySQL is the largest gathering of MySQL users and dbas.

This year the conference is April 14th through 17th and the home page is http://en.oreilly.com/mysql2008/public/content/home

I know there are many others who have talked about the MySQL conference — even recently - and more recently.   It is because we realize how important it is.

If you are MySQL professional you owe it to yourself and your company to go “join the pod” at the Users Conference.  You will learn more in four days than you thought possible.  Hope to see you there!!

 If you who don’t currently read planetmysql.org, add it to your favorite RSS reader.  It is one of the best sources of information about MySQL available on the Internet.  It is an aggregation of a large number of blogs by some of the best MySQL minds in the world.

Most importantly, the information that you get from these blogs is up-to-date and cutting edge.

The order that NULL is sorted in varies from database server to database server.  Some sort NULL first (in an ascending list), some sort NULL last in an ascending list.  As far as I know PostgreSQL, in the new version 8.3, is the only database server that allows you to specify which way the sort is applied.

When sorting your data it is important to know where NULL will fall when sorted.  Without further ado:

mysql> create table sort (id int not null, data varchar(5));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into sort (id,data) values (1,’a');
Query OK, 1 row affected (0.39 sec)

mysql> insert into sort (id,data) values (2,’b');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (3,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (4,’c');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (5,’d');
Query OK, 1 row affected (0.02 sec)

mysql> insert into sort (id,data) values (6,’e');
Query OK, 1 row affected (0.00 sec)

mysql> insert into sort (id,data) values (7,null);
Query OK, 1 row affected (0.01 sec)

mysql> select * from sort;
+—-+——-+
| id    | data     |
+—-+——-+
|  1    | a          |
|  2    | b          |
|  3    | c          |
|  4    | c          |
|  5    | d          |
|  6    | e          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql> select id, data from sort order by data asc;

+—-+——+
| id    | data   |
+—-+——+
|  7    | NULL |
|  1    | a          |
|  2    | b         |
|  3    | c          |
|  4    | c          |
|  5    | d         |
|  6    | e         |
+—-+——+
7 rows in set (0.24 sec)

mysql> select id, data from sort order by data desc;

+—-+——-+
| id    | data     |
+—-+——-+
|  6    | e          |
|  5    | d          |
|  3    | c          |
|  4    | c          |
|  2    | b          |
|  1    | a          |
|  7    | NULL |
+—-+——+
7 rows in set (0.00 sec)

mysql>

As you can see NULL is sorted before the other characters in the default (ascending) collation.  Just something to keep in mind!

Tonight here in the Raleigh/Durham/Chapel Hill “Triangle” area we are having our monthly MySQL Users Group meeting.  It is a great chance to meet other MySQL’ers, talk, network and learn something new.  This month we have a DBA coming up from Columbia, SC to talk about his experience with migrating to MySQL from Oracle.

If you don’t belong to a MySQL Users Group, join one.  The main page to see if you have one in your area is here:  http://forge.mysql.com/wiki/List_of_MySQL_User_Groups

If there isn’t one in your area consider starting one yourself.  The benefits are invaluable!

As a DBA you will develop a “bag of tools” that you use on a day-in and day-out basis.  I will outline some of the tools I use every day:

mytop (http://jeremy.zawodny.com/mysql/mytop/) - this is a tool that is very similar in use to the “top” tool of the Unix world.  In almost real time it provides information  such as what queries are running, how many queries per second and how many threads are being utilized.

bash (for shell programming) - while there are many choices I use tried and true bash for scripting mysql server installs and the hundred other  tasks a DBA ends up having to script to automate tasks and make life easier.

maatkit (http://maatkit.sourceforge.net) - If I had to have one tool in my bag — this would be it.  The collection of utilities is astonishing and cover everything from a dump and restore utility that runs multiple threads to a table sync tool that compares tables on two servers and can correct any problems between them.  The complete list of utilities is too long to list here.  Do yourself a favor and download it if you don’t have it already.

nagios (http://www.nagios.org) - I don’t enjoy getting paged at 2:00 a.m. but sometimes it is necessary.  Nagios can monitor everything from disk space to cpu usage and there are many plug ins written specifically for MySQL.  To keep on top of things you need monitoring.  Nagios is among the best for that task.

ganglia (http://ganglia.sourceforge.net/)  - ganglia is also a monitoring system, but it is of a slightly different bent.  Ganglia produces nice graphs that you can use to track various system statistics over time and see how your system loads are changing.

That’s all for now.   I am sure if you have been a DBA for a while you might have some more.  Feel free to comment.  If you aren’t using these tools already give them a try.