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=1max_allowed_packet = 200M#slave confserver-id=157log-bin=mysql-binlog-bin-index=mysql-bin.indexbinlog-do-db = softcentricbinlog-ignore-db = mysqlbinlog-ignore-db=information_schemabinlog-ignore-db=performance_schemareplicate-do-db = softcentricreplicate-ignore-db = mysql,information_schema,performance_schemalog-slave-updatessync_binlog = 1auto_increment_offset = 2auto_increment_increment = 2

节点C配置my.cnf文件

lower_case_table_names=1max_allowed_packet = 200M#slave confserver-id=158log-bin=mysql-binlog-bin-index=mysql-bin.indexrelay-log=slave-relay-binrelay-log-index=slave-relay-bin.indexbinlog-do-db = softcentricbinlog-ignore-db = mysqlbinlog-ignore-db=information_schemabinlog-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_schemaExecuted_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_schemaExecuted_Gtid_Set: 1 row in set (0.00 sec)ERROR: No query specifiedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase 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 书籍