High Availability MySQL Cookbook phần 8 doc

Chapter 5

155

In a busy server, this may take some time. Wait for the command

to complete before moving on.

Create a snapshot volume in window 2, passing a new name (mysql_snap), and pass the

size that will be devoted to keeping the data that changes during the course of the backup,

and the path to the logical volume that the MySQL data directory resides on:

[root@node1 lib]# lvcreate --name=mysql_snap --snapshot --size=200M \

/dev/system/mysql

Rounding up size to full physical extent 224.00 MB

Logical volume "mysql_snap" created

Return to window 1, and check the master log position:

mysql> SHOW MASTER STATUS;

+--------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+--------------+----------+--------------+------------------+

| node1.000012 | 997 | | |

+--------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Only after the lvcreate command in window 2 gets completed, unlock the tables:

mysql> UNLOCK TABLES;

Query OK, 0 rows affected (0.00 sec)

The next step is to move the data on this snapshot to the slave. On the master, mount

the snapshot:

[root@node1 lib]# mkdir /mnt/mysql-snap

[root@node1 lib]# mount /dev/system/mysql_snap /mnt/mysql-snap/

On the slave, stop the running MySQL server and rsync the data over:

[root@node2 mysql]# rsync -e ssh -avz node1:/mnt/mysql-snap /var/lib/

mysql/

root@node1's password:

receiving file list ... done

mysql-snap/

mysql-snap/ib_logfile0

mysql-snap/ib_logfile1

High Availability with MySQL Replication

156

mysql-snap/ibdata1

...

mysql-snap/world/db.opt

sent 1794 bytes received 382879 bytes 85482.89 bytes/sec

total size is 22699298 speedup is 59.01

Ensure the permissions are set correctly on the new data, and start the MySQL slave server:

[root@node2 mysql]# chown -R mysql:mysql /var/lib/mysql

[root@node2 mysql]# service mysql start

Starting MySQL. [ OK ]

Now carry out the CHANGE MASTER TO command in the Setting up slave with master having

same data section of this recipe to tell the slave where the master is, by using the position

and logfile name recorded in the output from window 1 (that is, log name node1.000012 and

Position 997).

Replication safety tricks

MySQL replication in anything but an extremely simple setup with one master handling every

single "write". A guarantee of no writes being made to other nodes is highly prone to a couple

of failures. In this recipe, we look at the most common causes of replication failure that can

be prevented with some useful tricks.

This section shows how to solve auto increment problems in multi-master setups, and also

how to prevent the data on MySQL servers, which you wish should remain read-only, from

being changed (a common cause of a broken replication link). Auto-increment is the single

largest cause of problems.

It is not difficult to see that it is not possible to have more than one server handling

asynchronous writes when auto-increments are involved (if there are two servers, both will

give out the next free auto-increment value, and then they will die when the slave thread

attempts to insert a second row with the same value).

Getting ready

This recipe assumes that you already have replication working, using the recipes discussed

earlier in this chapter.