Replication

Faster streaming backups using mariabackup and pigz

Posted by

1

Are you looking for a faster way to stream a backup from one db server to another?

Maybe you are rebuilding a replica from the master after someone wrote to the slave and now the data is out of sync.

I had a look at what was out there in terms of articles about streaming backups.

Most of them got part of the way but clearly haven’t had people asking in chat “are we there yet?” over and over, wondering when their replicas will be back so they can direct the read traffic at them.
The key here is to use the parallel options that come with mariabackup and use pigz to compress and decompress in parallel.

Do you want speed?
Do you want to saturate the 10Gigabit bonded link or InfiniBand (I wish…) ?
Do you have spare CPUs to use?

WARNING:

  • This command assumes the destination directory is empty. 
  • The command will 8 CPUs (4 for mariabackup and 4 for pigz) on the source
  • The command can use up to 8 CPUs (4 for pigz to decompress, 4 for mbstream)
  • Don’t run pigz without -p option. As by default it will use all CPUs available.

The command:

mariabackup --backup --slave-info --tmpdir=/tmp --stream=xbstream --parallel=4 --datadir=/var/lib/mysql 2>backup.log | pigz -p 4 | ssh -q replica -t "pigz -dc -p 4 | mbstream --directory=/var/lib/mysql -x --parallel=4"

Explanation of the command:

  1. This command will take a streaming backup (using xbstream) in parallel from the database in /var/lib/mysql.
  2. It will write output the backup.log (just in case you need to debug…)
  3. Then it will pipe that toq pigz (parellel gzip) which will compress in parallel.
  4. This stream of compressed xbstream will be sent encrypted via ssh to host called replica.
  5. On the replica, the compressed xbstream will be uncompressed by pigz, then piped thru mbstream into the /var/lib/mysql directory.

Next steps:

  1. Prepare the backup using the appropriate amount of RAM. 
    mariabackup --prepare --use-memory=16G --target-dir=/var/lib/mysql/
  2. Change the ownership of the destination datadir 
    chown -R mysql:mysql /var/lib/mysql
  3. Start the db 
    systemctl start mariadb
  4. Look at xtrabackup_slave_info to get the slave info 
    cat /var/lib/mysql/xtrabackup_slave_info
  5. Change the master using CHANGE MASTER using that info.

Until next time.

 

 

Prewarm your EBS backed EC2 MySQL slaves

Posted by

2

This is the story of cold blocks and mismatched instances and how they will cause you pain and cost you money until you understand why.

Most of the clients that we support run on the Amazon cloud using either RDS or running MySQL on plain EC2 instances using (Provisioned IOPS) PIOPS EBS for data storage.

As expected the common architecture is running a master with one or more slaves handling the read traffic.

A common problem is that after the slaves are provisioned (normally created from an EBS snapshot) they lag badly due to slow IO performance.

Unfortunately what tends to be lost in the “speed of provisioning new resources” fetish is some limitations in terms of data persistence layer (EBS).

If you are using EBS and you have created the EBS volume from snapshot or created a new volume you have to pre-warm the EBS volume otherwise you will suffer a bad (I mean seriously bad) first usage penalty.  Bad? I am talking up to 50% performance drop[1]. So that expensive PIOPS EBS volume you created is going to perform like rubbish every time it reads/writes a cold block.

The other thing which also tends to happen is mixing up the wrong instance (network performance) with the PIOPS EBS. This the classic networked storage, the network is the bottleneck. If your instance type has limited network performance, having a higher PIOPS than the network can handle means you are wasting money (on PIOPS) you can’t use. A bit like in the old days (of dedicated servers and SAN storage) where the SAN could deliver 200-300Mbytes per sec, but the 1 Gigabit network could only do 40-50Mbytes per sec.

Here is the real downside, using the cloud you can provision new resources to handle peak load (in the case more MySQL slaves to handle read load) as fast as you can click, or faster using API calls, or even automagically, if you have some algo forecast the need for additional resources. But… the EBS is all cold blocks, so these new instances will be up and available in minutes but the IO performance will be poor until you either pre-warm or the slave gets around to writing/reading all blocks.

So the common solution is to pre-warm the blocks using dd to read the EBS device (and warm the block) to /dev/null

eg: sudo dd if=/dev/xvdf of=/dev/null bs=1M

Consider how long this will take for any reasonable sized DB (200GBytes) using an instance with 1 Gigabit network.

200Gigabytes read at 50Mbytes/sec  = 200,000 Mbytes/50 = 4000 secs = 3600 (1hr) + 400 (6 mins 40 secs) =~ more than 1 hr.

So you or your algo provisioned a new EC2 instance for the database in minutes but either your IO will be rubbish for an extended period, or you wait more than 1 hr per 200GB to have the EBS pre-warmed.

What are the solutions?

  1. Forecast further in advance depending on the size of your db (or any other persistent storage layer eg NoSQL etc)
  2. Use ephemeral storage and manage the increased risk of data loss in the event of instance termination.
  3. Break your DB or your application into smaller pieces aka micro services.[2]
  4. Pay more $ and have your databases stay around longer so waiting for a instance to be ready in the beginning is not a problem.

As you can expect, most businesses are happy with option 4. Pay more, leave instances around like they were dedicated servers (base load). Amazon is happy too.

Option 3 whilst requiring some thought (argh) and additional complexity is where the real speed of provisioning, dare I say it, agile nature of the cloud will bear the most fruit.

[1] http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/ebs-prewarm.html

[2] http://martinfowler.com/articles/microservices.html

 

MySQL Error: error reconnecting to master

Posted by

5

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/

MySQL Multi Master-Master on EC2

Posted by

3

Background:

As I mentioned in the previous post on running multiple mysql instances on EC2 using mysqld_multi. This was the first step in running 2 or more EC2 Amazon Machine Image (AMI) as a Multi-instance Master-Master (MIMM) replication cluster.
The idea cames from a blog article from Apokalyptik.com
It is about improving the availability of your databases on EC2 and allowing easy backups without affecting either master instance.
The other benefit I see, is these two masters are read-write. So you can choose your load-balancer and not worry about what to do if one of the master instances goes down.
I am going to test using mysql-proxy to perform that role of load-balancing a multi master replication cluster next.
Then we are going to hammer the hell out of the configuration using mysqlslap, then sysbench and then the granddaddy of OLTP benchmarks DBT.

If you follow the screen dump of what I did I am using a bunch of previous work. Each of the previous steps and posts builds on the last.

Summary:

  1. The most complex part of this was getting the /etc/my.cnf right. The rest was plain vanilla mysql replication. You can view myMIMM.cnf
  2. Some of the links below never mention log-slave-updates. This is fine if your in a circular or one way replication setup. In this setup each master is a slave of the other, and each master has a dedicated slave. To propagate changes from one master to the dedicated slave needs log-slave-updates on each master instance.
  3. mysqld_multi stop does not seem to stop the instances. I had to use mysqladmin shutdown instead, connecting to specific sockets.

Other Resources:

http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
http://www.howtoforge.com/mysql_master_master_replication
http://capttofu.livejournal.com/1752.html
http://jayant7k.blogspot.com/2006/06/multi-master-replication-in-mysql.html
http://code.google.com/p/mysql-master-master/

Install:

Refer to the above links for more detailed setups. Essentially the high level steps are

  1. Setup your partitioning, I used LVM and my LVM script so I can use snapshots for backups in the future.
  2. Download and install MySQL.
  3. Load your data into that single instance, then shutdown.
  4. Map out your replication topology.
  5. Copy the data to form the basis of all the new master and slave instances.
  6. Modify the /etc/my.cnf so it has the setting for running mysqld_multi. For an example follow my post on Multiple instances or follow the example my.cnf below.
  7. Assign appropriate names, sockets, port numbers and even datafile names so that at 3am you don’t mistake one instance for another.
  8. Modify the /etc/hosts file. So you can use nicer names rather than DNS or IP addresses.

Session Output: Comments in bold




Run make_mnt_lvm.sh

[root@domU-12-31-38-00-29-35 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             4.0G  1.2G  2.7G  31% /
/dev/mapper/vg-myvmdisk1
                     30G   77M   28G   1% /data1/mysql
/dev/mapper/vg-myvmdisk2
                     30G   77M   28G   1% /data2/mysql
/dev/mapper/vg-myvmdisk3
                     30G   77M   28G   1% /backup/mysql

 Copy existing mysql data directory to new homes 

cp -R /usr/local/mysql/data/* /data1/mysql/data
cp -R /usr/local/mysql/data/* /data2/mysql/data
mv /usr/local/mysql/data /usr/local/mysql/data_old
chown -R mysql:mysql /data1/mysql/data
chown -R mysql:mysql /data2/mysql/data


[root@domU-12-31-38-00-29-35 mysql]# ls -l /data1/mysql/data
total 1504
-rw-r-----  1 root root  5242880 Feb 25 04:46 ib_logfile0
-rw-r-----  1 root root  5242880 Feb 25 04:46 ib_logfile1
-rw-r-----  1 root root 10485760 Feb 25 04:46 ibdata1
drwxr-x---  2 root root     4096 Feb 25 04:46 mysql
-rw-r-----  1 root root      263 Feb 25 04:46 mysql-bin.000001
-rw-r-----  1 root root      125 Feb 25 04:46 mysql-bin.000002
-rw-r-----  1 root root       38 Feb 25 04:46 mysql-bin.index
drwxr-x---  2 root root     4096 Feb 25 04:46 test

 Create the multi_mysql aware /etc/my.cnf 

[root@domU-12-31-38-00-29-35 mysql]# cat /etc/my.cnf
[client]
port            = 3306
socket          = /tmp/mysql1.sock

[mysqld_multi]
mysqld          = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin      = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user            = multi_root
password        = multi_pass

[mysqld1]
port            = 3306
socket          = /tmp/mysql1.sock
pid-file        = /data1/mysql/data/mysql1.pid
datadir         = /data1/mysql/data/
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


log-bin=mysql1-bin

server-id       = 1

# For Master-Master replication
auto_increment_increment      = 10
auto_increment_offset         = 1

# Slave configuration

# The replication master for this slave - required
master-host     =  master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user     = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port     = 3308

[mysqld2]
port            = 3307
socket          = /tmp/mysql2.sock
pid-file        = /data2/mysql/data/mysql2.pid
datadir         = /data2/mysql/data
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data2/mysql/data
innodb_data_file_path = ibdata2:10M:autoextend
innodb_log_group_home_dir = /data2/mysql/data
innodb_log_arch_dir = /data2/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

log-bin=mysql2-bin
log-slave-updates

server-id       = 2

# Slave configuration

# The replication master for this slave - required
master-host     =  master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user     = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port     = 3306


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

 Second master on separate machine

Notes:
I am using higher server_id 3 and 4.
higher mysqld configurations mysqld3 and mysqld4
Higher port numbers 3308 and 3309

Reason: At 3am in the morning you don't want to be working on the wrong instance.


[client]
port            = 3308
socket          = /tmp/mysql1.sock

[mysqld_multi]
mysqld          = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe
mysqladmin      = /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqladmin
user            = multi_root
password        = multi_pass

[mysqld3]
port            = 3308
socket          = /tmp/mysql1.sock
pid-file        = /data1/mysql/data/mysql1.pid
datadir         = /data1/mysql/data/
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


log-bin=mysql1-bin

server-id       = 3

# For Master-Master replication
auto_increment_increment      = 10
auto_increment_offset         = 2

# Slave configuration

# The replication master for this slave - required
master-host     =  master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user     = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port     = 3306
skip-slave-start

[mysqld4]
port            = 3309
socket          = /tmp/mysql2.sock
pid-file        = /data2/mysql/data/mysql2.pid
datadir         = /data2/mysql/data
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Innodb
innodb_data_home_dir = /data2/mysql/data
innodb_data_file_path = ibdata2:10M:autoextend
innodb_log_group_home_dir = /data2/mysql/data
innodb_log_arch_dir = /data2/mysql/data
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

log-bin=mysql2-bin
log-slave-updates

server-id       = 4

# Slave configuration

# The replication master for this slave - required
master-host     =  master2
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user     = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port     = 3308


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


 Starting up 

mysqld_multi start 1 --verbose --password=multi_pass
mysqld_multi start 2 --verbose --password=multi_pass


[root@domU-12-31-38-00-29-35 data]# ps -ef|grep mysqld_safe
root      3178     1  0 04:59 ttyp0    00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3306 --socket=/tmp/mysql1.sock

--pid-file=/data1/mysql/data/mysql1.pid --datadir=/data1/mysql/data/ --skip-locking --key_buffer=16M

--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K

--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --innodb_data_home_dir=/data1/mysql/data

--innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/data1/mysql/data

--innodb_log_arch_dir=/data1/mysql/data --log-bin=mysql1-bin --server-id=1
root      3669     1  0 05:21 ttyp0    00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3307 --socket=/tmp/mysql2.sock

--pid-file=/data2/mysql/data/mysql2.pid --datadir=/data2/mysql/data/ --log-error=/data2/mysql/data/ --skip-locking

--key_buffer=16M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K

--read_buffer_size=256K --read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M

--innodb_data_home_dir=/data2/mysql/data --innodb_data_file_path=ibdata2:10M:autoextend

--innodb_log_group_home_dir=/data2/mysql/data --innodb_log_arch_dir=/data2/mysql/data

--innodb_buffer_pool_size=128M --log-bin=mysql2-bin --server-id=2

 Checking connectivity 

[root@domU-12-31-38-00-29-35 data]# mysql -u root -p -S /tmp/mysql1.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> show variables like '%dir%';
+--------------------+-----------------------------------------------------------------------+
| Variable_name      | Value                                                                 |
+--------------------+-----------------------------------------------------------------------+
| basedir            | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/                      |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir            | /data1/mysql/data/                                                    |
| plugin_dir         | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql             |
| slave_load_tmpdir  | /tmp                                                                  |
| tmpdir             | /tmp                                                                  |
+--------------------+-----------------------------------------------------------------------+
6 rows in set (0.01 sec)

mysql> exit
Bye

[root@domU-12-31-38-00-29-35 data]# mysql -u root -p -S /tmp/mysql2.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> show variables like '%dir%';
+--------------------+-----------------------------------------------------------------------+
| Variable_name      | Value                                                                 |
+--------------------+-----------------------------------------------------------------------+
| basedir            | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/                      |
| character_sets_dir | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/share/mysql/charsets/ |
| datadir            | /data2/mysql/data/                                                    |
| plugin_dir         | /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/mysql             |
| slave_load_tmpdir  | /tmp                                                                  |
| tmpdir             | /tmp                                                                  |
+--------------------+-----------------------------------------------------------------------+
6 rows in set (0.00 sec)

 Fire up new machine and setup as slave on master1 (this will be master2 soon) 

[root@ip-10-251-75-150 ~]# mysqld_multi --verbose --password=multi_pass start 3
[root@ip-10-251-75-150 ~]# ps -ef|grep mysqld_safe
root      3006     1  0 05:34 ttyp0    00:00:00 /bin/sh

/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/bin/mysqld_safe --port=3308 --socket=/tmp/mysql1.sock

--pid-file=/data1/mysql/data/mysql1.pid --datadir=/data1/mysql/data/ --skip-locking --key_buffer=16M

--max_allowed_packet=1M --table_cache=64 --sort_buffer_size=512K --net_buffer_length=8K --read_buffer_size=256K

--read_rnd_buffer_size=512K --myisam_sort_buffer_size=8M --innodb_data_home_dir=/data1/mysql/data

--innodb_data_file_path=ibdata1:10M:autoextend --innodb_log_group_home_dir=/data1/mysql/data

--innodb_log_arch_dir=/data1/mysql/data --innodb_buffer_pool_size=256M --innodb_additional_mem_pool_size=20M

--innodb_log_file_size=64M --innodb_log_buffer_size=8M --innodb_flush_log_at_trx_commit=1

--innodb_lock_wait_timeout=50 --log-bin=mysql1-bin --server-id=3 --auto_increment_increment=10

--auto_increment_offset=2 --master-host=master1 --master-user=repluser --master-password=replpassword

--master-port=3306 --skip-slave-start
root      3235  2791  0 05:34 ttyp0    00:00:00 grep mysqld_safe


mysql -u root -p$PASSWD -S /tmp/mysql1.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'slave2' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges
  -> ;
Query OK, 0 rows affected (0.00 sec)

 Add privileges on Master1 

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master2' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

 Test connectivity from master2 to master 1 

[root@ip-10-251-75-150 ~]# mysql -u repluser -preplpassword --host=master1 --port=3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql>

 Check master position on master1 

mysql> show master status\G
*************************** 1. row ***************************
          File: mysql1-bin.000001
      Position: 475
  Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

 Start slave on master2 

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

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master1
                Master_User: repluser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql1-bin.000001
        Read_Master_Log_Pos: 475
             Relay_Log_File: mysql1-relay-bin.000003
              Relay_Log_Pos: 621
      Relay_Master_Log_File: mysql1-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 475
            Relay_Log_Space: 883
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error:
             Last_SQL_Errno: 0
             Last_SQL_Error:
1 row in set (0.00 sec)

 Setup master1 as slave on master2 

 Test connectivity for master1 to master2 

[root@ip-10-251-69-190 ~]# mysql -u repluser -preplpassword --host=master2 --port=3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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


 Check master status on master2 

mysql -u root -p$PASSWD -S /tmp/mysql1.sock

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> show master status\G
*************************** 1. row ***************************
          File: mysql1-bin.000003
      Position: 106
  Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

 On master1 start slave 

[root@ip-10-251-69-190 ~]# mysql -u root -p$PASSWD -P 3306
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

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

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master2
                Master_User: repluser
                Master_Port: 3308
              Connect_Retry: 60
            Master_Log_File: mysql1-bin.000003
        Read_Master_Log_Pos: 106
             Relay_Log_File: mysql1-relay-bin.000004
              Relay_Log_Pos: 252
      Relay_Master_Log_File: mysql1-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 106
            Relay_Log_Space: 554
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error:
             Last_SQL_Errno: 0
             Last_SQL_Error:
1 row in set (0.00 sec)

 Ok fire up the slaves on each master 


 Addition of replication settings 

# Slave configuration

# The replication master for this slave - required
master-host     =  master1
#
# The username the slave will use for authentication when connecting
# to the master - required
master-user     = repluser
#
# The password the slave will authenticate with when connecting to
# the master - required
master-password = replpassword
#
# The port the master is listening on.
# optional - defaults to 3306
master-port     = 3306
log-slave-updates
skip-slave-start


 setup replication user 

mysql -u root -p$PASSWD -S /tmp/mysql1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'slave1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* to repluser@'master1' identified by 'replpassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges
  -> ;
Query OK, 0 rows affected (0.00 sec)

 On Master mysqld 1 
mysql> show master status\G
*************************** 1. row ***************************
          File: mysql1-bin.000002
      Position: 320
  Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

 Test connectivity for slave replication user 

mysql -u repluser -preplpassword --host=master1 --port=3306

 Start slave if it isn't already 

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

 Check the status.

Note: The Last_IO_error is just that. The last recorded error



mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master1
                Master_User: repluser
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql1-bin.000002
        Read_Master_Log_Pos: 535
             Relay_Log_File: mysql2-relay-bin.000003
              Relay_Log_Pos: 681
      Relay_Master_Log_File: mysql1-bin.000002
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 535
            Relay_Log_Space: 983
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 1130
              Last_IO_Error: error connecting to master 'repluser@master1:3306' - retry-time: 60  retries: 86400
             Last_SQL_Errno: 0
             Last_SQL_Error:
1 row in set (0.00 sec)


 Load some sample data into the master1 instance 

http://dev.mysql.com/doc/sakila/en/sakila.html

[root@ip-10-251-69-190 ~]# cd /backup/mysql/
[root@ip-10-251-69-190 mysql]# wget http://downloads.mysql.com/docs/sakila-db.zip
--05:55:09--  http://downloads.mysql.com/docs/sakila-db.zip
         => `sakila-db.zip'
Resolving downloads.mysql.com... 213.136.52.53
Connecting to downloads.mysql.com|213.136.52.53|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 670,566 (655K) [application/x-zip]

100%[============================================================================>] 670,566      281.15K/s

05:55:13 (280.50 KB/s) - `sakila-db.zip' saved [670566/670566]



[root@domU-12-31-38-00-29-35 sakila-db]# mysql -u root -p$PASSWD -S /tmp/mysql1.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> source sakila-schema.sql

...

mysql> source sakila-data.sql
...

mysql> use sakila
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

 Checking that slave propagation 

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

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use sakila;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

 Checking master2 

[root@ip-10-251-75-150 ~]# mysql -u root -p$PASSWD -P 3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.20-beta-log MySQL Community Server (GPL)

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

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sakila             |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> use sakila
Database changed
mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

 Checking slave2 

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

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

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

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: master2
                Master_User: repluser
                Master_Port: 3308
              Connect_Retry: 60
            Master_Log_File: mysql1-bin.000003
        Read_Master_Log_Pos: 106
             Relay_Log_File: mysql2-relay-bin.000004
              Relay_Log_Pos: 252
      Relay_Master_Log_File: mysql1-bin.000003
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 106
            Relay_Log_Space: 554
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
              Last_IO_Errno: 0
              Last_IO_Error:
             Last_SQL_Errno: 0
             Last_SQL_Error:
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

 Where is the sakila database?!? 

This is related to log-slave-updates!
http://dev.mysql.com/doc/refman/5.1/en/replication-options.html#option_mysqld_log-slave-updates

Master 2 took the changes from Master 1, however it never recorded the changes in its binlog!

 Stopping the instances... 

I found mysqld_multi stop doesn't stop or shutdown anything. So you have to go and shutdown the
mysql instances using mysqladmin shutdown -S yourSocket -uroot -Pyourpassword

mysqladmin shutdown -S /tmp/mysql2.sock -uroot -p$PASSWD
mysqladmin shutdown -S /tmp/mysql1.sock -uroot -p$PASSWD

[root@ip-10-251-75-150 ~]# ps -ef|grep mysql
root     27356  2791  0 06:37 ttyp0    00:00:00 grep mysql