MySQL Error: error reconnecting to master

Error message:

Slave I/O thread: error reconnecting to master
Last_IO_Error: error connecting to master

Diagnosis:

Check that the slave can connect to the master instance, using the following steps:

  1. Use ping to check the master is reachable. eg ping master.yourdomain.com
  2. Use ping with ip address to check that DNS isn’t broken. eg. ping 192.168.1.2
  3. Use mysql client to connect from slave to master. eg mysql -u repluser -pREPLPASS –host=master.yourdomain.com –port=3306 (substitute whatever port you are connecting to the master on)
  4. If all steps work, then check that the repluser (the SLAVE replication user has the REPLICATION SLAVE privilege). eg. show grants for ‘repl’@’slave.yourdomain.com’;

Resolution:

  • If step 1 and 2 fail, you have a network or firewall issue. Check with a network/firewall administrator or check the logs if you wear those hats.
  • If Step 1 fails but Step 2 works, you have a DNS or names resolution issue. Check that the slave can connect and resolves names using mysql client or ssh/telnet/remote desktop.
  • If Step 3 fails, you need to check the error reported, it will either be a authentication issue (login failed/denied) or an issue with the TCP port the master is listening on. A good way to verify that port is open is to use: telnet master.yourdomain.com 3306 (or the port the master is listening on) if that fails then there is a firewall(s) in the network which are blocking that port.
  • If you get to step 4 and everything looks fine and the slave does reconnect fine on retrying. Then you have probably had either temporary, network failure, names resolution failure, firewall failure or any of the prior together.

Continuing Sporadic issues:

Get hold of the network and firewall logs.
If this is not possible, setup a script to periodically ping, connect, mysql connect and log that over
time to prove to your friendly network admin that there is an problem with the network.

How MySQL deals with it:

MySQL will try and reconnect by itself after a network failure or query timeout.

The process is governed by a few variables:

master-connect-retry
slave-net-timeout
master-retry-count

In a nutshell, a MySQL slave will try to reconnect after getting a timeout (slave-net-timeout) after waiting the number of seconds in master-connect-retry but only for the number of times
specified in master-retry-count.
By default, a MySQL slave waits one hour before retry, and will then retry every 60 seconds for 86,400 times. That is every minute for 60 days.

If the one hour slave-net-timeout is too long for your DR/Slave read strategy you will need to adjust it accordingly.

Edit: 2011/02/02

Thanks to leBolide. He discovered that there is a 32 character limit on the password for replication.

Have Fun

Paul

P.S. If you liked this post you might be good enough to try these challenges

https://dbadojo.com/2016/07/29/mysql-challenges-part-one/

Advertisements

Top 9 Posts for the last 12 months

If you were ever wondering what other people check out on this site, here are the most popular articles by pageviews for the last 12 months.

Seems most people like the LVM snapshots article, articles about running multiple MySQL instances and the various benchmark articles.

  1. mysql backups using lvm snapshots
  2. oracle 11g on ec2 using silent install
  3. mysql multi master master replication on ec2
  4. mysql master master replication table sync
  5. multiple mysql instances on ec2
  6. mysql dbt2 benchmark on ec2
  7. mysql 51 ndb cluster replication on ec2
  8. sysbench vs mysql on ec2
  9. bonnie io benchmark vs ec2

Have Fun

Paul

OurDelta MySQL on EC2 – updating binaries

Given the amount of time since my last post on installing OurDelta MySQL on EC2. It allowed me to show quickly how to get your OurDelta MySQL install up-to-date.

Prerequisites:

You have already installed the OurDelta Repository as per this documentation
http://ourdelta.org/centos

To update:

Now just yum update to get the latest version:
http://ourdelta.org/release-5077-d8

yum update MySQL-OurDelta*

It is as simple as that.

Comments:

Thanks for the feedback from the last post.

Some people requested the Amazon Machine Image (AMI). The main issue with this is, once you bundle an AMI image it is going to start with those binaries (including mysql) and require you to constantly run yum update each time you launch an instance. So if I had bundled an AMI back in February, anyone using that AMI now would be way behind on the latest updates for OurDelta and also any other CentOS packages.

It is better to go down the path of learning how to bundle an AMI yourself, then getting an base CentOS 4 or CentOS 5 AMI up-to-date once month and using yum update afterwards, when you launch the instance.
You can even pass a script which runs after the instance has launched, or use a configuration tool like Puppet.
Believe me when I say that whilst bundling AMIs is straight-forward you do not want large numbers of old/obsolete AMIs floating around to manage later.

Upcoming stuff:

I am going to use this AMI with MySQL Sandbox to show how easy it is to have a test environment if you want to take your existing MySQL 5.0.x versions to OurDelta MySQL or any other version on MySQL.

Have Fun

Paul

Screen log:



[root@domU-12-31-39-04-71-D3 ~]# yum update MySQL-OurDelta*
Setting up Update Process
Setting up repositories
Reading repository metadata in from local files
Resolving Dependencies
--> Populating transaction set with selected packages. Please wait.
---> Package MySQL-OurDelta-client.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-shared.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-test.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-server.i386 0:5.0.77.d8-54.el4 set to be updated
---> Package MySQL-OurDelta-devel.i386 0:5.0.77.d8-54.el4 set to be updated
--> Running transaction check

Dependencies Resolved

=============================================================================
Package Arch Version Repository Size
=============================================================================
Updating:
MySQL-OurDelta-client i386 5.0.77.d8-54.el4 ourdelta 6.0 M
MySQL-OurDelta-devel i386 5.0.77.d8-54.el4 ourdelta 7.5 M
MySQL-OurDelta-server i386 5.0.77.d8-54.el4 ourdelta 17 M
MySQL-OurDelta-shared i386 5.0.77.d8-54.el4 ourdelta 1.7 M
MySQL-OurDelta-test i386 5.0.77.d8-54.el4 ourdelta 6.7 M

Transaction Summary
=============================================================================
Install 0 Package(s)
Update 5 Package(s)
Remove 0 Package(s)
Total download size: 39 M
Downloading Packages:
(1/5): MySQL-OurDelta-cli 100% |=========================| 6.0 MB 00:03
(2/5): MySQL-OurDelta-sha 100% |=========================| 1.7 MB 00:01
(3/5): MySQL-OurDelta-tes 100% |=========================| 6.7 MB 00:03
(4/5): MySQL-OurDelta-ser 100% |=========================| 17 MB 00:09
(5/5): MySQL-OurDelta-dev 100% |=========================| 7.5 MB 00:03
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Updating : MySQL-OurDelta-client ####################### [ 1/10]
Updating : MySQL-OurDelta-shared ####################### [ 2/10]
Updating : MySQL-OurDelta-test ####################### [ 3/10]
Giving mysqld 5 seconds to exit nicely
Updating : MySQL-OurDelta-server ####################### [ 4/10]
090608 20:19:24 [Warning] option 'sync-mirror-binlog': unsigned value 18446744073709551615 adjusted to 4294967295
090608 20:19:24 [Warning] option 'sync-mirror-binlog': unsigned value 18446744073709551615 adjusted to 4294967295
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

MySQL bug reports should be submitted through http://bugs.mysql.com/
Issues related to the OurDelta patches or packaging should be
submitted in the OurDelta project on Launchpad, simply follow the
links via http://ourdelta.org/

The latest information about MySQL is available on the web at
http://dev.mysql.com/
Other information sources are
- the MySQL Mailing List archives (http://lists.mysql.com/);
- the MySQL Forums (http://forums.mysql.com/).


Notes regarding SELinux on this platform:
=========================================

The default policy might cause server startup to fail because it is
not allowed to access critical files. In this case, please update
your installation.

The default policy might also cause inavailability of SSL related
features because the server is not allowed to access /dev/random
and /dev/urandom. If this is a problem, please do the following:

1) install selinux-policy-targeted-sources from your OS vendor
2) add the following two lines to /etc/selinux/targeted/src/policy/domains/program/mysqld.te:
allow mysqld_t random_device_t:chr_file read;
allow mysqld_t urandom_device_t:chr_file read;
3) cd to /etc/selinux/targeted/src/policy and issue the following command:
make load


Starting MySQL.[ OK ]
Giving mysqld 2 seconds to start
Updating : MySQL-OurDelta-devel ####################### [ 5/10]
Cleanup : MySQL-OurDelta-client ####################### [ 6/10]
Cleanup : MySQL-OurDelta-shared ####################### [ 7/10]
Cleanup : MySQL-OurDelta-test ####################### [ 8/10]
Cleanup : MySQL-OurDelta-server ####################### [ 9/10]
Cleanup : MySQL-OurDelta-devel ####################### [10/10]

Updated: MySQL-OurDelta-client.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-devel.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-server.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-shared.i386 0:5.0.77.d8-54.el4 MySQL-OurDelta-test.i386 0:5.0.77.d8-54.el4
Complete!


Just make sure the mysql instance is secure

[root@domU-12-31-39-04-71-D3 ~]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] n
... skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

OurDelta MySQL on EC2 – install

Summary:

Arjen would give me an earful if I got this wrong or poorly worded.

“OurDelta produces enhanced builds for MySQL, with OurDelta and third-party patches, for common production platforms” from http://ourdelta.org/about

Over the next series of articles I am going to put the many additions to the MySQL 5.0 baseline through their paces on Amazon EC2.

Using a base CentOS 4.4 I had lying around on Amazon S3 I had Ourdelta installed and secure in no time flat.

This is outline for the installing Ourdelta onto CentOS 4

Install:


yum install yum-plugin-protectbase
rpm --import http://mirror.ourdelta.org/deb/ourdelta.gpg
rpm -Uvh http://mirror.ourdelta.org/yum/CentOS/OurDelta-release-0.0.4-1.noarch.rpm
mkdir downloads
cd downloads/
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-server-5.0.67.d7-44.el4_7.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-shared-5.0.67.d7-44.el4_7.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-test-5.0.67.d7-44.el4_7.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-client-5.0.67.d7-44.el4_7.i386.rpm
wget http://mirror.ourdelta.org/yum/CentOS/4/i386/RPMS/MySQL-OurDelta-devel-5.0.67.d7-44.el4_7.i386.rpm
yum localinstall MySQL-OurDelta-*
/usr/bin/mysql_secure_installation
ps -ef|grep mysql
mysql -u root -p

Comments:

Thats it. Straightforward install.
I am getting lazy in my old age I used a pipe combo to generate the install commands using this:

history |grep -v history|awk ‘{ print $2″ “$3” “$4 }’

The next article will cover the upgrade of any existing MySQL 5.0 release to OurDelta.
I have saved this Ourdelta MySQL install as an AMI.

If people are interested in making this a public AMI please post a comment.

Have Fun

Paul

Is EC2 useful as a database server

Plenty of people have been excited by the prospect of Amazon EC2 and the ability to scale out your databases as load increases from your original configuration. I noticed Morgan Tocker and Carl Mercier are going to be presenting on this topic at the upcoming MySQL Conference

However almost immediately people are worried about the lack of persistent of data across instance terminations.
In a sense people are wanting dedicated hosting services instead of what EC2 really is.

You need to think of Amazon EC2 in a electricity generation metaphor.
Coal, Nuclear Fission and Gas provide the base load electricity which is on 24×7.
Gas and Hydro can act as peak load generation as well. So at peak periods when the requirement for more electricity is higher, electricity generators can switch on these extra resources to cope.

Amazon EC2 is the same, it is there to service peak load.
Either you are using Amazon EC2 as a base load server or you are using a dedicated hosting service to provide base load. You add additional server resources during peak periods as required.
As a dedicated hosting service EC2 is not actually the cheapest option out there. There are plenty of dedicated hosting providers who will give you and your application and database, cheaper base load capacity. That said, many people choose to run both application and database servers on EC2 as base load servers and the uptime of these instances is good.

What this means is that using EC2 as base load means you must implement additional protections for your data to provide persistence. This may be in the form of clustering technologies and replication technologies or both. So running EC2 as a base load database server adds complexity. This is why numerous companies have sprung up as a result of this complexity. They are essentially providing a method for companies to pay for someone else to deal with it.

The hidden value here is, in adopting a more thorough attitude to data persistence and redundancy, your database is more robust. So if or when your dedicated hosting provider has an outage, your architectural design is already in a position to handle it.

The danger is, you see any ongoing performance issue (a demand for addition base load) as solved by throwing hardware at it. Rather than reviewing whether the demand is justified or whether it can be reduced through tuning the application, database or architecture.

Update: Added Carl as co-presenter at the MySQL conference.

Have Fun

Paul

Sysbench fileio vs EC2 Part 1

Overview:

Peter Zaitsev’s recent article about Evaluating IO subsystem performance for MySQL spurred my interest in doing something similar on EC2.

I have covered running sysbench against MySQL on EC2 however not specifically used sysbench to test IO. Rather I had used bonnie++ and iozone to do that.

I don’t have a lot of respect for the EC2 small instance. Whilst it was reasonable in the middle of 2006 when Amazon EC2 was launched, you can (even in Australia) pick up Dual and Quad Core CPUs with enough memory for 32 bit OS for a reasonable amount nowadays.
The main problem as I see it for my specific interest is the lack of grunt in network and IO bandwidth. Having the idea of Grid or Utility or the latest “Cloud” computing falls apart when you have to hit disk or worse need to synchronize across a slow network.

So I was mildly surprised when the small instance was able to match and out-perform Peter’s benchmarks on the local disk at least.

Summary:

Using Peter’s benchmark description as a key, I reran his tests on a small EC2 instance. The local disk matched and outperformed his benchmark with no changes to any settings.
Unfortunately the mounted filesystem /mnt was much much worse.

If you are using MySQL or any IO intensive application, I would do the following:

  1. Make your image specify as large a local mount as possible. You will need the raw IO speed for anything which requires fast reads and fast writes.
  2. If you can take the risk use the Linux file cache. Especially if you have to use /mnt.
  3. Treat the raw IO performance of /mnt as slow disk, even in some respects like a backup location. It has a requests/sec and io transfer rates little better than USB 1.0 High speed. Certainly not even in the realm of Firewire.
  4. Get as much into memory as possible. This means narrowing table and indexes sizes or going with the more expensive large and extra large instances.

Install:

  1. wget sysbench from sourceforge.
  2. yum install gcc (or apt-get gcc) if not already installed
  3. configure, make and install sysbench
  4. Run this simple shell script sysbench_fileio.sh to replicate Peter’s benchmark.
  5. Free feel to publish your results, either here as a comment or on your own blog. Link or drop me a note and I will link to your benchmark results and we can tally up and see for whom the IO tolls.

Documentation (explains the various options)

http://sysbench.sourceforge.net/docs/#fileio_mode

Configuration:

Amazon small EC2 instance, running CentOS 4.4



[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 9.9G 855M 8.6G 9% /
/dev/sda2 147G 188M 140G 1% /mnt
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/sda1 / ext3 defaults 1 1
/dev/sda2 /mnt ext3 defaults 1 2
/dev/sda3 swap swap defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# uname -a
Linux domU-12-31-38-00-20-02 2.6.16-xenU #1 SMP Mon May 28 03:41:49 SAST 2007 i686 athlon i386 GNU/Linux
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/redhat-release
CentOS release 4.4 (Final)
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /proc/cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 65
model name : Dual-Core AMD Opteron(tm) Processor 2218 HE
stepping : 3
cpu MHz : 2600.000
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm cr8legacy ts fid vid ttp tm stc
bogomips : 5201.75

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# ipcs

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status

------ Semaphore Arrays --------
key semid owner perms nsems

------ Message Queues --------
key msqid owner perms used-bytes messages

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# free
total used free shared buffers cached
Mem: 1740944 533224 1207720 0 217020 263060
-/+ buffers/cache: 53144 1687800
Swap: 917496 0 917496

Results:

Raw Results:



wget http://optusnet.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz

yum install gcc sysstat

tar -xzvf sysbench-0.4.8.tar.gz
cd sysbench-0.4.8
./configure --without-mysql
make
make install

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 9.9G 855M 8.6G 9% /
/dev/sda2 147G 188M 140G 1% /mnt
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/fstab
# This file is edited by fstab-sync - see 'man fstab-sync' for details
/dev/sda1 / ext3 defaults 1 1
/dev/sda2 /mnt ext3 defaults 1 2
/dev/sda3 swap swap defaults 0 0
none /proc proc defaults 0 0
none /sys sysfs defaults 0 0

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# uname -a
Linux domU-12-31-38-00-20-02 2.6.16-xenU #1 SMP Mon May 28 03:41:49 SAST 2007 i686 athlon i386 GNU/Linux
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /etc/redhat-release
CentOS release 4.4 (Final)
[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# cat /proc/cpuinfo
processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 65
model name : Dual-Core AMD Opteron(tm) Processor 2218 HE
stepping : 3
cpu MHz : 2600.000
cache size : 1024 KB
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext fxsr_opt lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm cr8legacy ts fid vid ttp tm stc
bogomips : 5201.75

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# ipcs

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status

------ Semaphore Arrays --------
key semid owner perms nsems

------ Message Queues --------
key msqid owner perms used-bytes messages

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# free
total used free shared buffers cached
Mem: 1740944 533224 1207720 0 217020 263060
-/+ buffers/cache: 53144 1687800
Swap: 917496 0 917496

http://sysbench.sourceforge.net/docs/#fileio_mode

Sysbench FileIO test options

[root@domU-12-31-38-00-20-02 sysbench-0.4.8]# sysbench --test=fileio help
sysbench v0.4.8: multi-threaded system evaluation benchmark

fileio options:
--file-num=N number of files to create [128]
--file-block-size=N block size to use in all IO operations [16384]
--file-total-size=SIZE total size of files to create [2G]
--file-test-mode=STRING test mode {seqwr, seqrewr, seqrd, rndrd, rndwr, rndrw}
--file-io-mode=STRING file operations mode {sync,async,fastmmap,slowmmap} [sync]
--file-extra-flags=STRING additional flags to use on opening files {sync,dsync,direct} []
--file-fsync-freq=N do fsync() after this number of requests (0 - don't use fsync()) [100]
--file-fsync-all=[on|off] do fsync() after each write operation [off]
--file-fsync-end=[on|off] do fsync() at the end of test [on]
--file-fsync-mode=STRING which method to use for synchronization {fsync, fdatasync} [fsync]
--file-merged-requests=N merge at most this number of IO requests if possible (0 - don't merge) [0]
--file-rw-ratio=N reads/writes ratio for combined test [1.5]


Peter Zaitsev's 120M prepare file command

sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=1 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=128M prepare

sysbench v0.4.8: multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...

Running the test with the 120M file

sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=128M --file-test-mode=rndrd run

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 590164 Read, 0 Write, 0 Other = 590164 Total
Read 9.0052Gb Written 0b Total transferred 9.0052Gb (153.69Mb/sec)
9836.03 Requests/sec executed

Test execution summary:
total time: 60.0002s
total number of events: 590164
total time taken by event execution: 59.3742
per-request statistics:
min: 0.0001s
avg: 0.0001s
max: 0.0980s
approx. 95 percentile: 0.0001s

Threads fairness:
events (avg/stddev): 590164.0000/0.00
execution time (avg/stddev): 59.3742/0.00

Rerunning with 64 threads

sysbench --num-threads=64 --test=fileio --max-time=60 \
--max-requests=1000000 --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=128M --file-test-mode=rndrd run
sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 745086 Read, 0 Write, 0 Other = 745086 Total
Read 11.369Gb Written 0b Total transferred 11.369Gb (194.02Mb/sec)
12417.46 Requests/sec executed

Test execution summary:
total time: 60.0031s
total number of events: 745086
total time taken by event execution: 3837.4047
per-request statistics:
min: 0.0003s
avg: 0.0052s
max: 0.1228s
approx. 95 percentile: 0.0325s

Threads fairness:
events (avg/stddev): 11641.9688/8.86
execution time (avg/stddev): 59.9594/0.02

Running in single thread with random write i.e. --file-test-mode=rndwr


sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=1 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=128M --num-threads=1 --file-test-mode=rndwr run

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 0 Read, 93104 Write, 0 Other = 93104 Total
Read 0b Written 1.4207Gb Total transferred 1.4207Gb (24.227Mb/sec)
1550.55 Requests/sec executed

Test execution summary:
total time: 60.0457s
total number of events: 93104
total time taken by event execution: 59.9639
per-request statistics:
min: 0.0000s
avg: 0.0006s
max: 1.8278s
approx. 95 percentile: 0.0001s

Threads fairness:
events (avg/stddev): 93104.0000/0.00
execution time (avg/stddev): 59.9639/0.00

with 64 threads --num-threads=64

sysbench --test=fileio --num-threads=64 --max-time=60 \
--max-requests=1000000 --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=128M \
--file-test-mode=rndwr run

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 109175 Write, 0 Other = 109175 Total
Read 0b Written 1.6659Gb Total transferred 1.6659Gb (24.115Mb/sec)
1543.37 Requests/sec executed

Test execution summary:
total time: 70.7379s
total number of events: 109175
total time taken by event execution: 4329.6293
per-request statistics:
min: 0.0000s
avg: 0.0397s
max: 70.7328s
approx. 95 percentile: 0.0001s

Threads fairness:
events (avg/stddev): 1705.8594/6223.80
execution time (avg/stddev): 67.6505/3.06

Cleaning up for test with multiple files

sysbench --test=fileio --num-threads=64 --max-time=60 \
--max-requests=1000000 --file-num=1 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=128M --file-test-mode=rndwr cleanup
sysbench v0.4.8: multi-threaded system evaluation benchmark

Removing test files...

Preparing files

sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=128M --num-threads=64 --file-test-mode=rndwr prepare

sysbench v0.4.8: multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...

rerunning with 128 files to total of 128M

sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=128M --num-threads=64 --file-test-mode=rndwr run

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 126633 Write, 0 Other = 126633 Total
Read 0b Written 1.9323Gb Total transferred 1.9323Gb (25.607Mb/sec)
1638.85 Requests/sec executed

Test execution summary:
total time: 77.2695s
total number of events: 126633
total time taken by event execution: 4561.7451
per-request statistics:
min: 0.0002s
avg: 0.0360s
max: 28.6922s
approx. 95 percentile: 0.0069s

Threads fairness:
events (avg/stddev): 1978.6406/28.82
execution time (avg/stddev): 71.2773/5.32

Preparing the 128 files to a total of 2Gig test

sysbench --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=2G --num-threads=1 --file-test-mode=rndwr prepare

The run

sysbench --init-rng=1 --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=2G --num-threads=1 --file-test-mode=rndrd run

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 14094 Read, 0 Write, 0 Other = 14094 Total
Read 220.22Mb Written 0b Total transferred 220.22Mb (3.6701Mb/sec)
234.89 Requests/sec executed

Test execution summary:
total time: 60.0034s
total number of events: 14094
total time taken by event execution: 59.9868
per-request statistics:
min: 0.0001s
avg: 0.0043s
max: 0.2521s
approx. 95 percentile: 0.0129s

Threads fairness:
events (avg/stddev): 14094.0000/0.00
execution time (avg/stddev): 59.9868/0.00

2Gig file test with 64 threads

sysbench --init-rng=1 --test=fileio --max-time=60 --max-requests=1000000 \
--file-num=128 --file-extra-flags=direct --file-fsync-freq=0 \
--file-total-size=2G --num-threads=64 --file-test-mode=rndrd run

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 24639 Read, 0 Write, 0 Other = 24639 Total
Read 384.98Mb Written 0b Total transferred 384.98Mb (6.3875Mb/sec)
408.80 Requests/sec executed

Test execution summary:
total time: 60.2719s
total number of events: 24639
total time taken by event execution: 3847.3337
per-request statistics:
min: 0.0023s
avg: 0.1561s
max: 0.5804s
approx. 95 percentile: 0.3490s

Threads fairness:
events (avg/stddev): 384.9844/0.94
execution time (avg/stddev): 60.1146/0.08

2 Gig file test single thread random writes --file-test-mode=rndwr

sysbench --init-rng=1 --test=fileio --max-time=60 \
--max-requests=1000000 --file-num=128 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=2G --num-threads=1 \
--file-test-mode=rndwr run

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 0 Read, 52960 Write, 0 Other = 52960 Total
Read 0b Written 827.5Mb Total transferred 827.5Mb (13.748Mb/sec)
879.89 Requests/sec executed

Test execution summary:
total time: 60.1893s
total number of events: 52960
total time taken by event execution: 60.1396
per-request statistics:
min: 0.0001s
avg: 0.0011s
max: 2.1815s
approx. 95 percentile: 0.0001s

Threads fairness:
events (avg/stddev): 52960.0000/0.00
execution time (avg/stddev): 60.1396/0.00


2 Gig file test 64 threads random writes --file-test-mode=rndwr

sysbench --init-rng=1 --test=fileio --max-time=60 \
--max-requests=1000000 --file-num=128 --file-extra-flags=direct \
--file-fsync-freq=0 --file-total-size=2G --num-threads=64 \
--file-test-mode=rndwr run

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 38767 Write, 0 Other = 38767 Total
Read 0b Written 605.73Mb Total transferred 605.73Mb (10.038Mb/sec)
642.45 Requests/sec executed

Test execution summary:
total time: 60.3424s
total number of events: 38767
total time taken by event execution: 3861.3838
per-request statistics:
min: 0.0001s
avg: 0.0996s
max: 4.8871s
approx. 95 percentile: 0.8721s

Threads fairness:
events (avg/stddev): 605.7344/14.70
execution time (avg/stddev): 60.3341/0.00

Rerunning the whole test set on /mnt

cd /mnt
vi sysbench_fileio.sh
chmod 700 sysbench_fileio.sh
./sysbench_fileio.sh

sysbench v0.4.8: multi-threaded system evaluation benchmark

1 files, 131072Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!


Time limit exceeded, exiting...
Done.

Operations performed: 9782 Read, 0 Write, 0 Other = 9782 Total
Read 152.84Mb Written 0b Total transferred 152.84Mb (2.547Mb/sec)
163.01 Requests/sec executed

Test execution summary:
total time: 60.0092s
total number of events: 9782
total time taken by event execution: 59.9964
per-request statistics:
min: 0.0003s
avg: 0.0061s
max: 0.1835s
approx. 95 percentile: 0.0100s

Threads fairness:
events (avg/stddev): 9782.0000/0.00
execution time (avg/stddev): 59.9964/0.00

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!

Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 10799 Read, 0 Write, 0 Other = 10799 Total
Read 168.73Mb Written 0b Total transferred 168.73Mb (2.7943Mb/sec)
178.83 Requests/sec executed

Test execution summary:
total time: 60.3856s
total number of events: 10799
total time taken by event execution: 3852.0646
per-request statistics:
min: 0.0098s
avg: 0.3567s
max: 0.9908s
approx. 95 percentile: 0.6044s

Threads fairness:
events (avg/stddev): 168.7344/4.29
execution time (avg/stddev): 60.1885/0.11

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 0 Read, 15274 Write, 0 Other = 15274 Total
Read 0b Written 238.66Mb Total transferred 238.66Mb (3.9774Mb/sec)
254.55 Requests/sec executed

Test execution summary:
total time: 60.0029s
total number of events: 15274
total time taken by event execution: 59.9853
per-request statistics:
min: 0.0003s
avg: 0.0039s
max: 0.3117s
approx. 95 percentile: 0.0106s

Threads fairness:
events (avg/stddev): 15274.0000/0.00
execution time (avg/stddev): 59.9853/0.00

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
1 files, 128Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 13360 Write, 0 Other = 13360 Total
Read 0b Written 208.75Mb Total transferred 208.75Mb (3.4615Mb/sec)
221.54 Requests/sec executed

Test execution summary:
total time: 60.3062s
total number of events: 13360
total time taken by event execution: 3847.7447
per-request statistics:
min: 0.0003s
avg: 0.2880s
max: 60.2694s
approx. 95 percentile: 0.0175s

Threads fairness:
events (avg/stddev): 208.7500/1157.46
execution time (avg/stddev): 60.1210/0.07

sysbench v0.4.8: multi-threaded system evaluation benchmark

Removing test files...
sysbench v0.4.8: multi-threaded system evaluation benchmark

128 files, 1024Kb each, 128Mb total
Creating files for the test...
sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 1Mb each
128Mb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 14728 Write, 0 Other = 14728 Total
Read 0b Written 230.12Mb Total transferred 230.12Mb (3.8192Mb/sec)
244.43 Requests/sec executed

Test execution summary:
total time: 60.2541s
total number of events: 14728
total time taken by event execution: 3846.4353
per-request statistics:
min: 0.0004s
avg: 0.2612s
max: 1.5574s
approx. 95 percentile: 0.5732s

Threads fairness:
events (avg/stddev): 230.1250/10.67
execution time (avg/stddev): 60.1006/0.03

sysbench v0.4.8: multi-threaded system evaluation benchmark

128 files, 16384Kb each, 2048Mb total
Creating files for the test...
sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 8496 Read, 0 Write, 0 Other = 8496 Total
Read 132.75Mb Written 0b Total transferred 132.75Mb (2.2124Mb/sec)
141.60 Requests/sec executed

Test execution summary:
total time: 60.0016s
total number of events: 8496
total time taken by event execution: 59.9901
per-request statistics:
min: 0.0003s
avg: 0.0071s
max: 0.2733s
approx. 95 percentile: 0.0107s

Threads fairness:
events (avg/stddev): 8496.0000/0.00
execution time (avg/stddev): 59.9901/0.00

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random read test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 9497 Read, 0 Write, 0 Other = 9497 Total
Read 148.39Mb Written 0b Total transferred 148.39Mb (2.4566Mb/sec)
157.22 Requests/sec executed

Test execution summary:
total time: 60.4044s
total number of events: 9497
total time taken by event execution: 3854.1290
per-request statistics:
min: 0.0094s
avg: 0.4058s
max: 1.1867s
approx. 95 percentile: 0.7207s

Threads fairness:
events (avg/stddev): 148.3906/3.90
execution time (avg/stddev): 60.2208/0.12

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
Done.

Operations performed: 0 Read, 12236 Write, 0 Other = 12236 Total
Read 0b Written 191.19Mb Total transferred 191.19Mb (3.1862Mb/sec)
203.91 Requests/sec executed

Test execution summary:
total time: 60.0056s
total number of events: 12236
total time taken by event execution: 59.9917
per-request statistics:
min: 0.0003s
avg: 0.0049s
max: 0.3623s
approx. 95 percentile: 0.0117s

Threads fairness:
events (avg/stddev): 12236.0000/0.00
execution time (avg/stddev): 59.9917/0.00

sysbench v0.4.8: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 64

Extra file open flags: 16384
128 files, 16Mb each
2Gb total file size
Block size 16Kb
Number of random requests for random IO: 1000000
Read/Write ratio for combined random IO test: 1.50
Calling fsync() at the end of test, Enabled.
Using synchronous I/O mode
Doing random write test
Threads started!
Time limit exceeded, exiting...
(last message repeated 63 times)
Done.

Operations performed: 0 Read, 12251 Write, 0 Other = 12251 Total
Read 0b Written 191.42Mb Total transferred 191.42Mb (3.1792Mb/sec)
203.47 Requests/sec executed

Test execution summary:
total time: 60.2109s
total number of events: 12251
total time taken by event execution: 3847.4821
per-request statistics:
min: 0.0039s
avg: 0.3141s
max: 1.8187s
approx. 95 percentile: 0.6672s

Threads fairness:
events (avg/stddev): 191.4219/8.33
execution time (avg/stddev): 60.1169/0.04

sysbench v0.4.8: multi-threaded system evaluation benchmark

Removing test files...

MySQL Master-Master replication table sync

I saw a post by Baron mentioning that his tool maatkit is best for handling situations where a master-master replication setup has got out of sync.

If you think Baron was blowing his own trumpet he has good reason to. I have used his mk-archiver tool as part of the Maatkit to make the problem of archiving and purging data much easier. This was much easier than rolling my own solution.

Anyhow. I have a master-master replication just lying around to test this kind of stuff after finishing the multi-instance master-master replication pair last week. Plus I have already had some past experience using table-checksum tool which is part of Maatkit (or MySQL toolkit as it used to be known). Amazing how you publish some stuff and then you get the next idea for an article almost immediately.

Overview:

To test Baron’s assertion that Maatkit mk-table-sync is the best tool to re-sync a master-master replication pair.

Install:

  1. Setup a master-master replication pair.
  2. Download and install Maatkit.
  3. Load the Sakila sample database.
  4. Make some changes on one master which are not replicated to the other.
  5. Verify that the tables are out-of-sync using mk-table-checksum
  6. Use mk-table-sync to re-sync the tables.

Summary:

Again Maatkit lives up to my expectations. Both mk-table-checksum and mk-table-sync discovered and re-synced the tables without any issues.
On a side note, without this toolkit, you could still have rebuilt the other master from the dedicated slave, but this is so much faster and easier.

Ideas for the motivated reader:

  1. As always be careful when using set global sql_slave_skip_counter = 1;
  2. Download and test the Maatkit tools for yourself.
  3. Run mk-table-checksum periodically to discover if your slaves are actually consistent with your master.
  4. Having multiple recovery methods is good, you should be experienced and confident to use them all.

Detailed Screen Dump with comments:



Maatkit Dependencies

yum install perl-DBI perl-DBD-MySQL gcc
Time::HiRes

wget http://search.cpan.org/CPAN/authors/id/J/JH/JHI/Time-HiRes-1.9712.tar.gz

download maatkit-1753 from Sourceforge

perl Makefile.PL
make install

Break the master-master replication

On master2:

stop slave;

On master1:

mysql> use sakila
Database changed

mysql> insert into film values(1001,'Welcome to DBA Dojo','A place on the way towards being a DBA',2007,1,1,30,45,25.00,90000.00,'G','Behind the Scenes',now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


mysql> select * from film where film_id = 1001;
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| film_id | title | description | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features | last_update |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
| 1001 | Welcome to DBA Dojo | A place on the way towards being a DBA | 2007 | 1 | 1 | 30 | 45.00 | 25 | 999.99 | G | Behind the Scenes | 2008-03-03 05:42:58 |
+---------+---------------------+----------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+-------------------+---------------------+
1 row in set (0.00 sec)

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

On master 2

mysql> set global sql_slave_skip_counter = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from film where film_id = 1001\G
Empty set (0.00 sec)

On Master 1 run a simple table checksum


mk-table-checksum h=master1,u=root,p=$PASSWD h=master1 --databases=sakila

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 1 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL

Check the local slave


mk-table-checksum h=master1,u=root,p=$PASSWD h=slave1 --databases=sakila
DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
sakila actor 0 master1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila actor 0 slave1 MyISAM NULL 3596356558 0 0 NULL NULL
sakila address 0 master1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila address 0 slave1 MyISAM NULL 3083097758 0 0 NULL NULL
sakila category 0 master1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila category 0 slave1 MyISAM NULL 2281594170 0 0 NULL NULL
sakila city 0 master1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila city 0 slave1 MyISAM NULL 1881669182 0 0 NULL NULL
sakila country 0 master1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila country 0 slave1 MyISAM NULL 3658016321 0 0 NULL NULL
sakila customer 0 master1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila customer 0 slave1 MyISAM NULL 1332169016 0 0 NULL NULL
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 slave1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film_actor 0 master1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_actor 0 slave1 MyISAM NULL 3128610213 0 0 NULL NULL
sakila film_category 0 master1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_category 0 slave1 MyISAM NULL 3646644932 0 0 NULL NULL
sakila film_text 0 master1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila film_text 0 slave1 MyISAM NULL 2391883145 0 0 NULL NULL
sakila inventory 0 master1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila inventory 0 slave1 MyISAM NULL 3471334076 0 0 NULL NULL
sakila language 0 master1 MyISAM NULL 19972916 0 0 NULL NULL
sakila language 0 slave1 MyISAM NULL 19972916 0 0 NULL NULL
sakila payment 0 master1 MyISAM NULL 684052380 0 0 NULL NULL
sakila payment 0 slave1 MyISAM NULL 684052380 0 0 NULL NULL
sakila rental 0 master1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila rental 0 slave1 MyISAM NULL 2658764859 0 0 NULL NULL
sakila staff 0 slave1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila staff 0 master1 MyISAM NULL 1172551672 0 0 NULL NULL
sakila store 0 master1 MyISAM NULL 1107595282 0 0 NULL NULL
sakila store 0 slave1 MyISAM NULL 1107595282 0 0 NULL NULL


Creating the CHECKSUM table

mysql> use mysql
Database changed
mysql> CREATE TABLE checksum (
-> db char(64) NOT NULL,
-> tbl char(64) NOT NULL,
-> chunk int NOT NULL,
-> boundaries char(64) NOT NULL,
-> this_crc char(40) NOT NULL,
-> this_cnt int NOT NULL,
-> master_crc char(40) NULL,
-> master_cnt int NULL,
-> ts timestamp NOT NULL,
-> PRIMARY KEY (db, tbl, chunk)
-> ) ENGINE=InnoDB;

Forget the port and you will get this error

mk-table-checksum h=master1,u=root,p=$PASSWD h=master2,u=root,p=$PASSWD \
--databases=sakila

DBI connect(';host=master2;mysql_read_default_group=mysql','root',...) failed: Can't connect to MySQL server on 'master2

And we have a winner, mk-table-checksum has found the issue with the film table

mk-table-checksum h=master1,u=root,p=$PASSWD \
h=master2,u=root,p=$PASSWD,P=3308 --databases=sakila

DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG
...
sakila film 0 master1 MyISAM NULL 1490639089 0 0 NULL NULL
sakila film 0 master2 MyISAM NULL 1421174266 0 0 NULL NULL
...

Now use Baron's mk-table-sync example... testing first.

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--test

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 0 0 0 Chunk sakila.film

Run the real thing

mk-table-sync --synctomaster h=master2,u=root,p=$PASSWD,P=3308,D=sakila,t=film \
--verbose --execute

# Syncing D=sakila,P=3308,h=master2,p=...,t=film,u=root
# DELETE REPLACE INSERT UPDATE ALGORITHM DATABASE.TABLE
# 0 1 0 0 Chunk sakila.film


On master2

mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)

On Slave 2, which is a cascade slave of master 1

mysql -u root -p$PASSWD -S /tmp/mysql2.sock
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.1.20-beta-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sakila
Database changed
mysql> select * from film where film_id = 1001\G
*************************** 1. row ***************************
film_id: 1001
title: Welcome to DBA Dojo
description: A place on the way towards being a DBA
release_year: 2007
language_id: 1
original_language_id: 1
rental_duration: 30
rental_rate: 45.00
length: 25
replacement_cost: 999.99
rating: G
special_features: Behind the Scenes
last_update: 2008-03-03 05:42:58
1 row in set (0.00 sec)