MySQL5.5.42 主从复制架构搭建

1、异步复制构建

2、半同步复制构建

1、安装相同mysql数据版本5.5.42
   

     192.168.56.101  master
     192.168.56.102  slave
     192.168.56.103  vhost

主库配置：

2、在主库上创建一个用作复制使用的账户repl，授予REPLICATION SLAVE权限；

      同时授权限制从slave节点连接访问。在master主机上执行：

     mysql>grant REPLICATION SLAVE on *.* to 'repl'@'192.168.56.102' identified by 'repl';
     mysql>grant REPLICATION SLAVE on *.* to 'repl'@'192.168.56.103' identified by 'repl';

3、修改master主机my.cnf配置文件，开启 BINLOG,并设置server-id的值。这两个参数的修改需要重新启动数据库服务才生效。

vi my.cnf
[mysqld]
log-bin=/fsystem1/mylogbin/mysql-bin.log
server-id = 1

4、在master上设置读锁定有效，这是为了确保没有数据库操作，以便获得一个一致性快照。

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

5、从master获取当前二进制日志名称和偏移量值。这是为了在从数据库启动后，从这个点开始进行数据的恢复；

mysql> show master status ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

一致性备份：

cd /fsystem1/
tar -cvf data.tar data
scp data.tar  slave:/fsystem1/
scp data.tar  vhost:/fsystem1/

6、开锁
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

从库配置：

两个从库未开启。将data.tar恢复到指定的路径下：tar -xvf data.tar

7、配置从库配置文件,确保没个节点的server-id不重复。

#on slave
vi my.cnf
[mysqld]
server-id = 2
#on vhost
vi my.cnf
[mysqld]
server-id = 3

8、在从库上使用-skip-salve-start参数启动，这样不会立即启动从数据库上的复制进程，方便对从库进一步配置。

    或者直接启动 service mysql start

9、对slave库做相应设置，指定复制用户，主数据库服务器胡ip，端口，以及开始执行复制胡日志文件和位置等
      on slave/vhost执行：

mysql>
change master to
master_host='192.168.56.101',
master_port=3306,
master_user='repl',
master_password='repl',
master_log_file='mysql-bin.000001',
master_log_pos=107;

10、在从库启动slave IO线程。

mysql>start slave ;

mysql>show slave status \G;查看slave相关信息
mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.56.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: vhost-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Connecting
            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: 107
              Relay_Log_Space: 107
              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: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'repl@192.168.56.101:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: show processlist
*************************** 2. row ***************************
     Id: 2
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 114
  State: Connecting to master
   Info: NULL
*************************** 3. row ***************************
     Id: 3
   User: system user
   Host:
     db: NULL
Command: Connect
   Time: 114
  State: Slave has read all relay log; waiting for the slave I/O thread to update it
   Info: NULL
3 rows in set (0.00 sec)

ERROR:
No query specified

原因是由于master防火墙没有关闭。

配置半同步复制（在异步复制基础上配置）

mysql> select @@have_dynamic_loading ;
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set (0.00 sec)

[root@master plugin]# pwd
/mysql/mysql-5.5.42/lib/plugin
[root@master plugin]# ls -l | grep semisync_
-rwxr-xr-x. 1 root mysql 170522 Jan  7  2015 semisync_master.so
-rwxr-xr-x. 1 root mysql  88911 Jan  7  2015 semisync_slave.so

1、在主库上安装semisync_master.so，从库上安装semisync_slave.so
     语句成分是固定的。

MySQL>install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
mysql>install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

mysql> select * from mysql.plugin;
+---------------------+-------------------+
| name                | dl                |
+---------------------+-------------------+
| rpl_semi_sync_slave | semisync_slave.so |
+---------------------+-------------------+
1 row in set (0.00 sec)
mysql> select * from mysql.plugin;
+----------------------+--------------------+
| name                 | dl                 |
+----------------------+--------------------+
| rpl_semi_sync_master | semisync_master.so |
+----------------------+--------------------+
1 row in set (0.00 sec)


2、安装完成后，需要打开半同步复制开关，默认是关闭的。

mysql>set global rpl_semi_sync_master_enabled=1;
mysql>set global rpl_semi_sync_master_timeout=30000;
mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)

由于是在异步复制的基础上配置的，需要重新启动从库的I/O线程。

mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)
mysql>set global rpl_semi_sync_slave_enabled=1;

mysql>stop slave IO_THREAD ;start slave IO_THREAD;

配置完成。
mysql> show status like '%semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |----半同步复制打开。
+----------------------------+-------+
1 row in set (0.00 sec)


mysql> show status like '%semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 2     |------------从库数量
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |----半同步复制打开。
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+
14 rows in set (0.00 sec)