Mysql 主从从级联复制

 需求：三个服务器A->B->C级联主从

版本： 数据库 mysql 5.6_64bit_binary_install                            操作系统： redhat 6.4_x86_64bit

Master ：  192.168.0.186        =>主节点

slave1   :    192.168.0.167     =>中继节点

slave2   :    192.168.4.123     

1:    管理iptables , selinux ，/etc/hosts  下主机IP 地址绑定

2：  配置 MY.CNF 相关参数

MAster 配置：  my.cnf

[mysql@master ~]$ cat /usr/local/mysql/my.cnf 

# For advice on how to change settings please see 
----------------
[mysqld] 
port=3308 
skip-name-resolve                           -- /*  dns 反向解析时间 *  grant 时，必须使用ip不能使用主机名  */
datadir=/cifpay/mysqldb 
basedir=/usr/local/mysql 
log-error=/cifpay/mysqldb/oracle11g.com.err 
pid-file=/cifpay/mysqldb/oracle11g.com.pid 
plugin-dir=/usr/local/mysql/lib/plugin 
socket=/tmp/mysql.sock 
# ----master-----# 

# replicate-do-db
server_id=1                                       --/* 唯一标示 */
log-bin=/cifpay/mysql-bin.log 
binlog-ignore-db=mysql                --/*  复制 排除 mysql 库 */ 
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION      --/*  可以添加 用户 */

-------------------

Slave1(中继节点)  配置：

[mysql@slave1 ~] $ cat /usr/local/mysql/my.cnf 

# For advice on how to change settings please see 

---------------
[mysqld] 
port=3308 
basedir=/usr/local/mysql 
datadir=/cifpay/mysqldb 
log-error=/cifpay/mysqldb/dominic.mysql1.err 
pid-file=/cifpay/mysqldb/dominic.mysql1.pid 
plugin-dir=/usr/local/mysql/lib/plugin 

# ----slave 1 -------# 

# replicate-do-db
server-id=2 
read_only=TURE 
binlog-ignore-db=mysql 
log_slave_updates=1                             /*  关键一步 */         
log-bin=/cifpay/mysql-bin.log 
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

------------------

Slave2 配置：

[mysql@slave2 ~]$ cat /usr/local/mysql/my.cnf

# For advice on how to change settings please see 
------------------------
[mysqld] 
port=3308 
server_id=3 
basedir=/usr/local/mysql 
datadir=/cifpay/mysqldb 
log-error=/cifpay/mysqldb/cifpay.rac1.err 
pid-file=/cifpay/mysqldb/cifpay.rac1.pid 
plugin-dir=/usr/local/mysql/lib/plugin 
read_only=TURE 

# replicate-do-db
sql-mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

------------------------

注意： 关于要复制多个数据库时，binlog-do-db和replicate-do-db选项的设置，网上很多人说是用半角逗号分隔，经过测试，这样的说法是错误的，MySQL官方文档也明确指出，如果要备份多个数据库，只要重复设置相应选项就可以了。
binlog-do-db=a
binlog-do-db=b
replicate-do-db=a
replicate-do-db=b

3 :    各mysql 数据库DB 创建对应的copy  user 以及授权：注意， 最好用户分开，并授予 REPLICATION SLAVE 权限。

mysql> grant all privileges on *.* to copy1@'%' identified by '123456'with grant option;

mysql> show databases;

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

| Database           |

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

| information_schema |

| dominic            |

| mysql              |

| performance_schema |

| test               |

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

5 rows in set (0.00 sec)

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select user,password,host from user;

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

| user  | password                                  | host          |

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

| root  |                                           | localhost     |

| root  |                                           | oracle11g.com |

| copy1 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %             |

| copy2 | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | %             |

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

4 rows in set (0.00 sec)

     

4 ： 重启Mster  库，使配置生效， 在主服务器上，设置读锁定有效，这个操作是为了确保没有数据库操作，以便获得 一个一致性的快照：

      mysql> flush tables with read lock;

5 ：通过mysqldump 或者其他方式： 释放master 锁  及 slave 库恢复（约）

       mysql> unlock tables;

 6：启动Slave 1 （中继节点） ，通过 --skip-slave-start参数   或者 正常启动后，通过mysql > stop salve ;

    

对从数据库服务器做相应设置，指定复制使用的用户，主数据库服务器的 IP、端 口以及开始执行复制的日志文件和位置等，具体语法如下： 

查看 master 节点 pos ：

mysql> show master status ;      --Master 

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000004 |      120 |              | mysql            |                   |

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

1 row in set (0.00 sec)

mysql> CHANGE MASTER TO         --Slave 1

-> MASTER_HOST='master_host_name',                           -->主机host(IP)

-> MASTER_USER='replication_user_name',                     -->复制的用户user( 如果是主从从，指定的复制用户必须不同）

-> MASTER_PASSWORD='replication_password',            -->复制用户的密码

-> MASTER_LOG_FILE='recorded_log_file_name',             -->对应第5步的 file 编号。

-> MASTER_LOG_POS=recorded_log_position,                 -->对应的POS编号

-> MASTER_PORT=3308;

eg： 中继节点 Slave1 上执行

mysql> change master to

    -> MASTER_HOST='192.168.0.186',

    -> MASTER_USER='copy1',

    -> MASTER_PASSWORD='123456',

    -> MASTER_LOG_FILE='mysql-bin.000004',

    -> MASTER_LOG_POS=120,

    ->  MASTER_PORT=3308;

Query OK, 0 rows affected, 2 warnings (0.06 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G                      --查看slave 1 状态

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.186

                  Master_User: copy1

                  Master_Port: 3308

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000004

          Read_Master_Log_Pos: 249

               Relay_Log_File: dominic-relay-bin.000002

                Relay_Log_Pos: 412

        Relay_Master_Log_File: mysql-bin.000004

              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: 249

              Relay_Log_Space: 587

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 1

                  Master_UUID: 1fb88a50-81b1-11e4-95e4-080027a9d0f9

             Master_Info_File: /cifpay/mysqldb/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.00 sec)

 

   7 :  启动slave 2 通过 --skip-slave-start参数   或者 正常启动后，通过mysql > stop salve ;

 

mysql> show master status \g     --查看Slave 1 对应pos 值：

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

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mysql-bin.000001 |      431 |              | mysql            |                   |

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

1 row in set (0.00 sec)

[mysql@slave2]$ /etc/init.d/mysqldb start --skip-slave-start

Starting MySQL... SUCCESS! 

[mysql@slave2]$ /usr/local/mysql/bin/mysql

Welcome to the MySQL monitor. Commands end with ; or \g. 
Your MySQL connection id is 1 
Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial) 

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. 

Oracle is a registered trademark of Oracle Corporation and/or its 
affiliates. Other names may be trademarks of their respective 
owners. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

mysql> change master to 
-> MASTER_HOST='192.168.0.167', 
-> MASTER_USER='copy2', 
-> MASTER_PASSWORD='123456', 
-> MASTER_LOG_FILE='mysql-bin.000001', 
-> MASTER_LOG_POS=302, 
-> MASTER_PORT=3308; 
Query OK, 0 rows affected, 2 warnings (0.19 sec)

   查看Slave2 节点状态：

mysql> show slave status \G                 --Slave2 节点

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.0.167

                  Master_User: copy2

                  Master_Port: 3308

                Connect_Retry: 60

              Master_Log_File: mysql-bin.000001

          Read_Master_Log_Pos: 302

               Relay_Log_File: cifpay-relay-bin.000002

                Relay_Log_Pos: 283

        Relay_Master_Log_File: mysql-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: 302

              Relay_Log_Space: 457

              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:

  Replicate_Ignore_Server_Ids:

             Master_Server_Id: 2

                  Master_UUID: 9b6fc45b-81b3-11e4-95f4-0800273e24cb

             Master_Info_File: /cifpay/mysqldb/master.info

                    SQL_Delay: 0

          SQL_Remaining_Delay: NULL

      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

           Master_Retry_Count: 86400

                  Master_Bind:

      Last_IO_Error_Timestamp:

     Last_SQL_Error_Timestamp:

               Master_SSL_Crl:

           Master_SSL_Crlpath:

           Retrieved_Gtid_Set:

            Executed_Gtid_Set:

                Auto_Position: 0

1 row in set (0.01 sec)