Way To Set Up Replication In MySQL

Rationale

When it comes to talking about replication in MySQL, there should be one master node (in the sense that one slave could have only one master, although you may set multiple master nodes globally) and multiple slaves nodes.

Master node will write replication events to a special log called binary log, which will be read by slave nodes via IO thread and stores in a file called relay log. Eventually, SQL thread in slave nodes will reads events from relay log and then applies them to current slave MySQL server. As shown below:

As we can see, though multiple nodes in MySQL cluster there are, it is still single point of failure (SPOF) given its semantics that "when master node breaks down, the service supplied by MySQL is down", which is not virtually the same as a decentralized distributed system.

Configuration Process

Find the valid my.ini or my.cnf used by MySQL server:

[mysql@644v3 mysql]$ $MYSQL_HOME/libexec/mysqld --verbose --help | grep -B 1 -E "my.(cnf|ini)" --color
Default options are read from the following files in the given order:
/etc/my.cnf ~/.my.cnf /usr/local/mysql/etc/my.cnf 

Edit one of the configuration files listed above (in my case, "/usr/local/mysql/etc/my.cnf" is used) on master node. The following configuration enables binary logging using a log file name prefix of mysql-bin, and configure a server ID of 1:

[mysqld]
log-bin=mysql-bin
server-id=1

Likewise, append the following configuration to my.cnf on slave nodes, in which, server-id should be unique in each slave node:

[mysqld]
server-id=2

read_only=1

After setting, we need to restart master and slave node respectively.

shell> $MYSQL_HOME/share/mysql/mysql.server stop
shell> $MYSQL_HOME/share/mysql/mysql.server start

As we can see from the rationale section, only operations via SQL on master node from the time binary log is on will be synchronized to slave nodes, thus we have to sync existing MySQL data to slave nodes ourselves.

Before exporting current data, we have to make the whole process as a transaction for consistency and integrity. Therefore, the following command is executed on master node to disable statements like insert/update/delete/replace/alter.

mysql> FLUSH TABLES WITH READ LOCK;

Then we can dump current data out to local filesystem and record current binary log's name and position:

# Syntax of mysqldump
# shell> mysqldump [options] db_name [tbl_name ...]
# shell> mysqldump [options] --databases db_name ...
# shell> mysqldump [options] --all-databases

shell> $MYSQL_HOME/bin/mysqldump -u repl -p test > ./mysql_snapshot.sql


#show current binary log's name and position
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |     2114 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

When the above oprations done, remember to release the read lock via `UNLOCK TABLES;`.

Scp the dump file "mysql_snapshot.sql" to slave nodes and restore it via command, in which, test is the name of database:

mysql -uroot -p test < mysql_snapshot.sql

Next, we are going to add a MySQL user on master node especially for slave node connection with the only replication privilege.

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

There's something beside the main topic: When specifying '%', it stands for accepting login sessions from any host, EXCEPT from 'localhost'. Actually, 'localhost' is special in mysql, it means a connection over a unix socket (or named pipes on windows I believe) as opposed to a TCP/IP socket. using '%' as the host does not include 'localhost'. Thus, create a user named 'repl'@'localhost' if necessary.

Eventually, we have to specify the start position of synchronizing from master node, which is recorded above (in my case, the position is 2114 and filename is mysql-bin.000001), and start slave threads for replication:

mysql> CHANGE MASTER TO
        MASTER_HOST='master_host',
        MASTER_USER='repl',
        MASTER_PASSWORD='slavepass',
        MASTER_LOG_FILE='mysql-bin.000001',
        MASTER_LOG_POS=2114;

mysql> START SLAVE;

If we intend to see the slave's status, we could simply invoke `show slave status;` in MySQL prompt, which will show information like the progress of synchronization, etc.

We could verify the replication in MySQL by executing an insert statement in master node, and the newly-added record appears in slave node synchronously.


Reference
1. MySQL Official Documentation - Replication Configuration
2. MySQL Replication Configuration - CSDN
3. what-exactly-does-flush-tables-with-read-lock-do
4. creating-a-mysql-user-without-host-specified
5. MySQL Official Documentation - mysqldump



© 2014-2017 jason4zhu.blogspot.com All Rights Reserved 
If transfering, please annotate the origin: Jason4Zhu