Diamond Notes

This tip is a follow-up to yesterday’s post about setting up a new slave server with a new master (one that has no current data on it). Many times you need to add a slave to a master server that already has data on it. This is a little trickier, but it is possible.

There are other ways of doing this. For me, this is typically the fastest way to do it and the one I use most often.

Assumptions I am making: your operating system is Linux and you are using what is called LVM (logical volume manager). The lvm programs are used to manage the partitions and filesystems on your server. You can use it to, among other things, resize partitions and most importantly, in this case, take “snapshots” of your partitions. WARNING: before performing this on production systems you NEED to research LVM and test it out on test servers.

step one - set up your slave server with the operating systems and MySQL. Your master server MUST have LVM on it (and I would recommend you set up LVM on the slave also).

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 - open up two terminal screens on the master server.

step four - Issue a ‘FLUSH TABLES WITH READ LOCK’ on the master server’s mysql client. This will block all database activity on the master server.

step five - As soon as the ‘FLUSH TABLES’ command returns issue a ‘SHOW MASTER STATUS’ command. You need to save the information returned as you will need it in step eleven.

step six - In the second terminal screen you need to create the lvm snapshot. This is done by running the following command:

lvcreate -s -n snap-data -L 10G /dev/vg0/data

The exact format will vary somewhat depending on your setup. In this case it creates a snapshot of the data partition that is 10 gigabytes in size. This means that up to 10 gigabytes of changes can be recorded before the snapshot fills up.

step seven - Now back in the original terminal screen (the one that you issued the ‘FLUSH TABLES’ and ‘SHOW MASTER STATUS’ commands) you need to execute an ‘UNLOCK TABLES’ command. This will return your database to normal activity. You need to execute steps four through seven as quickly as possible.

step eight - Now you need to mount the snapshot partition. Do something like this (as root):

mount /dev/vg0/snap-data /mnt

step nine - Now you rsync the files to the slave server:

rsync -Wav --delete --progress /mnt/mysql slave:/data/mysql

step ten - Start the mysqld daemon on the slave server. It might spit and sputter a bit as it is starting up, but only once have I had the daemon not start up (and that appeared to be a completely unrelated issue). In the last year I have probably done this procedure fifty times so it is well tested!

step eleven - Now it is time to tell the slave server about the master. You will also need the IP address of the master server. On Linux run the ifconfig command to determine the IP address. To put in the information on the slave server:

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’;

SHOW SLAVE STATUS;

step twelve - Cleanup!! Now you need to remove the snapshot partition you made on the master:

umount /mnt

lvremove /dev/vg0/snap-data

That’s it. A little more complicated. But worth it!!

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!!

Here is a nice overview of solid information just released yesterday about the upcoming release of six core processors by Intel.  These should go into production this summer.  I just hope that by the time they get to eight cores (which this new platform seems to allow them to accomplish) that MySQL can support 32 cores (4 chips x 8 cores).

hmmm….

Hey everyone, allow me to put on my editor/publisher/janitor hat briefly for MySQL Magazine (http://www.mysqlzine.net). On April the 15th I will be putting out the fourth issue of the magazine. We have been growing in “subscribers” (downloads) since I began the magazine last spring. It is now time to begin seeking advertising.

MySQL Magazine is a online magazine produced four times a year that is targeted at both MySQL developers and database administrators. Some of the most well known names in the field write articles for the magazine. Given the highly specialized nature of the magazine it provides a phenomenal opportunity for companies interested in reaching an audience with interest in MySQL products and services.

Please contact me at bmurphy AT paragon-cs.com if you are interested. Our advertising rates will be very competitive and I will offer special 25% discounts to the first two advertisers that will last for the first year if you choose to advertise with us for that long. We can do either full or half-page ads.

Thanks for your time. Now we return to regularly scheduled programming

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 follow my blog you know I harp (some might say constantly) on how I think that MySQL needs to evolve the server code to support an extreme (currently) number of core processors.  Along those lines, I ran across this article linked to from Slashdot.  Basically, it is the rundown of a roundtable discussion by some top chip people.  It was interesting to me how they took different approaches to the coming problems.

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!!