MySQL High Availability and Scalability - Replication

MySQL Replication enables users to cost-effectively deliver application performance, scalability and high availability. Many of the world's most trafficked web properties like eBay, Facebook, Tumblr, Twitter and YouTube rely on MySQL Replication to elastically scale-out beyond the capacity constraints of a single instance, enabling them to serve hundreds of millions of users and handle exponential growth.

By mirroring data between instances, MySQL replication is also the most common approach to delivering High Availability (HA) for MySQL databases. In addition, the MySQL replication utilities can automatically detect and recover from failures, allowing users to maintain service in the event of outages or planned maintenance.

With the release of MySQL 5.6, a number of enhancements have been made to MySQL Replication, delivering higher levels of data integrity, performance, automation and application flexibility.

With the introduction of GTIDs and MySQL Replication Utilities2 in MySQL 5.6, configuration, monitoring and management of replication becomes much easier and robust – for example, it introduces the automated promotion of a slave to master in the event that the original Master fails.

When you are using GTIDs for replication, you do not need (or want) to include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement used to direct a slave to replicate from a given master; in place of these options, it is necessary only to enable the MASTER_AUTO_POSITION option introduced in MySQL 5.6.5.

The following are for old version before 5.6

Things to do for database replication during backup

1.      Start a session on the master by connecting to it with the command-line client, and flush all tables and block write statements by executing the FLUSH TABLES WITH READ LOCK statement:

2.           mysql> FLUSH TABLES WITH READ LOCK;

Warning
Leave the client from which you issued the FLUSH TABLES statement running so that the read lock remains in effect. If you exit the client, the lock is released.

3.      In a different session on the master, use the SHOW MASTER STATUS statement to determine the current binary log file name and position:

4.           mysql > SHOW MASTER STATUS;

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

           | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

           | mysql-bin.000003 | 73       | test         | manual,mysql     |

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

            Keep a copy of this status for replication

4. backup as before using mysqldump (in a different session from the locking session)
5. after  backup is done, in the client where you acquired the read lock, release the lock:

           mysql> UNLOCK TABLES;

           Alternatively, exit the first session to release the read lock.

***********

Steps to restart MySQL replication slave

replicate-do-db=yourDatabase
in /etc/my.cnf

(for convenience next time)

make sure max_allowed_packet the same as master to avoid trouble
mysql>show variables like "max%";

You can

mysql>set GLOBAL max_allowed_packet=1G;
or just put
max_allowed_packet=1G
in /etc/my.cnf
(for convenience next time)
(sometimes need to exit mysql client and enter mysql client again to avoid cache problem)

mysql>stop slave;
to make sure slave is not running

drop database i88;
create database i88;
use i88;

SET foreign_key_checks = 0;

restore your data such as:
source slave.sql (in mysql client)
or
mysql  < slave.sql

SET foreign_key_checks = 1;



change master to MASTER_HOST='192.168.1.5', MASTER_USER='replication_account', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000638', MASTER_LOG_POS=308586121;

start the slave in the end:

mysql>start slave;

mysql> show slave status;

( just to confirm it is running)

---Done