mysql主从复制集群搭建

mysql主从复制集群搭建（基于日志点的复制）

安装mysql

【注意】防火墙开启的情况下默认的mysql的3306端口会被阻止，所以需要我们手动设置3306端口在防火墙列表中的列外。

mysql配置步骤

• 在主master端建立复制用户（此用户是slave端用来读取master端binarylog日志所使用）
• 备份master端的数据，并在slave端恢复。
• 使用change master命令配置复制。

配置示例

• 集群有三个节点，节点A、节点B、节点C
  其中A、B互为主从，C为从节点，B为C的主节点；

节点A配置my.cnf文件

配置文件中增加的内容如下：

#table name as lowercase
  lower_case_table_names=1
  max_allowed_packet = 200M

  #master conf
  server-id=154
  log-bin=mysql-bin
  log-bin-index=mysql-bin.index
  binlog-do-db = softcentric
  binlog-ignore-db = mysql
  binlog-ignore-db=information_schema
  binlog-ignore-db=performance_schema
  log-slave-updates
  sync_binlog = 1
  auto_increment_offset = 1
  auto_increment_increment = 2
  replicate-do-db = softcentric
  replicate-ignore-db = mysql,information_schema,performance_schema

节点B配置my.cnf文件

lower_case_table_names=1
max_allowed_packet = 200M
#slave conf
server-id=157
log-bin=mysql-bin
log-bin-index=mysql-bin.index
binlog-do-db = softcentric
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
replicate-do-db = softcentric
replicate-ignore-db = mysql,information_schema,performance_schema
log-slave-updates
sync_binlog = 1
auto_increment_offset = 2
auto_increment_increment = 2

节点C配置my.cnf文件

lower_case_table_names=1
max_allowed_packet = 200M
#slave conf
server-id=158
log-bin=mysql-bin
log-bin-index=mysql-bin.index
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
binlog-do-db = softcentric
binlog-ignore-db = mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

节点A上进入mysql 命令行：

• 第一步：创建用于复制的用户

mysql> create user 'repl_user'@'192.168.31.15%' identified by '123456';

• 赋予用户复制的权限

# 在节点A的mysql上授权，让节点B（157）主机使用的repl_user用户有复制的权限

mysql> grant replication slave,replication client on *.* to 'repl_user'@'192.168.31.157';
mysql> flush privileges;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 154
     Binlog_Do_DB: softcentric
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

在节点B（157）上指定master通过设置参数，其中master_log_file， master_log_pos两个参数来自上一步的输出中的（File，Position）的值。

mysql> change master to master_host='192.168.31.154',master_port=3306,master_user='repl_user',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;

• 节点B启动slave并查看状态：

mysql>slave start;
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000003
         Position: 154
     Binlog_Do_DB: softcentric
 Binlog_Ignore_DB: mysql,information_schema,performance_schema
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.154
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: localhost-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: softcentric
          Replicate_Ignore_DB: mysql,information_schema,performance_schema

在master A 上导入数据库表，在节点B（slave）上查看同步的结果

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| softcentric        |
| sys                |
+--------------------+
5 rows in set (0.02 sec)

#这里多了一个softcentric数据库

mysql> use softcentric;
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> show tables;
+------------------------+
| Tables_in_softcentric  |
+------------------------+
| act_evt_log            |
| act_ge_bytearray       |
| act_ge_property        |
| act_hi_actinst         |
| act_hi_attachment      |
| act_hi_comment         |
| act_hi_detail          |
| act_hi_identitylink    |
| act_hi_procinst        |
| act_hi_taskinst        |
| act_hi_varinst         |
| act_id_group           |

上面我们看到，数据库中的表已经创建，数据已经复制完成。

在slave节点C上（节点B 157作为节点C的master）设置master的参数：

mysql> change master to master_host='192.168.31.157',master_port=3306,master_user='repl_user',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=154;

查看同步的结果：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.157
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 154
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| softcentric        |
| sys                |
+--------------------+
5 rows in set (0.07 sec)

mysql> use softcentric;show tables;
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
+------------------------+
| Tables_in_softcentric  |
+------------------------+
| act_evt_log            |
| act_ge_bytearray       |
| act_ge_property        |
| act_hi_actinst         |
| act_hi_attachment      |
| act_hi_comment         |
| act_hi_detail          |

参考内容
mycat+mysql集群:实现读写分离，分库分表
高可用mysql 书籍