构建大数据平台（十一）Mysql 多源数据同步

作者: Mr_ZhaiDK | 来源:发表于2018-08-14 13:50 被阅读0次

构建大数据平台（十一）Mysql 多源数据同步
在Windows中基于Oracle GoldenGate （O
如何保证消息的顺序性
很行领数据可视化平台，个性化、层次感、空间感的大屏
使用datax迁移cassandra数据
Mysql 复制架构
企业业务数据整合利器-异构数据同步系统Arkgate
mysql数据丢失，从库数据丢失分析
DataX3.0介绍
创建Spring项目使用DataX进行定时数据库备份

一、前提：

（1）系统环境：centos7.3

host	ip	software	port	system	role
master1	192.168.31.210	mysql-5.7.22	3306	centos7	m1
master2	192.168.31.211	mysql-5.7.22	3306	centos7	m2
slave1	192.168.31.212	mysql-5.7.22	3306	centos7	s1

（2）所有主机均按照《构建大数据平台（十）Mysql 搭建》中的方法，搭建完成。
（3）所有主机server-uuid均不相同，查看方法如下：

#查询机器server-uuid
cat /var/lib/mysql/auto.cnf
#显示如下，重点看server-uuid
[auto]
server-uuid=8254cbe3-9ed1-11e8-bacd-000c2919f874

如果server-uuid存在相同，按如下方法修改：

#备份原有auto.cnf
mv /var/lib/mysql/auto.cnf /var/lib/mysql/auto.cnf.bak
#重启mysql
systemctl restart mysqld

二、配置master：

已master1为例，master2操作基本相同

修改my.cnf

#编辑my.cnf
vi /etc/my.cnf

master1配置文件如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#====以上为系统原始配置，以下为新增配置====#

#server-id必须唯一，各主机不同
server-id=1
innodb_flush_log_at_trx_commit=2  #
sync_binlog=1

#log-bin=mysql-bin-*，*号一般是server-id
log-bin=mysql-bin-1

#binlog-do-db表示需要同步的数据库，不填表示全部同步
#binlog-do-db=xxxx

#binlog_ignore_db表示不需要同步的数据库，这里不同步mysql自带4个数据库
binlog_ignore_db=mysql
binlog_ignore_db=sys
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema

#以表的方式存错
master_info_repository=TABLE
relay_log_info_repository=TABLE

#开启GTID模式
gtid-mode=on
enforce-gtid-consistency=true

#假设需要将来可能需要10台服务器做备份， 所以auto-increment-increment 设为10
auto-increment-increment=10
#auto-increment-offset表示这台服务器的序号。
#从1开始，不超过auto-increment-increment。
auto-increment-offset=1

因为，每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。解决这个问题的办法就是让每个数据库的自增主键不连续。
这样做之后，我在这台服务器上插入的第一个id就是 1，第二行的id就是 11了，而不是2。同理，在第二台服务器上插入的第一个id就是2，第二行就是12，这个后面再介绍）这样就不会出现主键冲突了。

重启mysql服务

systemctl restart mysql

进入mysql，配置slave用户。

#输入密码后，进入mysql
mysql -u root -p
#创建slave同步账户，其中*.*表示同步所有数据库所有表
#'slave'是创建的同步账户
#'192.168.31.%'表示ip过滤
grant replication slave on *.* to 'slave'@'192.168.31.%' identified by '你的密码';
#刷新权限
flush privileges;
#查看master状态
mysql>show master status ;
#显示结果如下
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB                                | Executed_Gtid_Set                        |
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
| mysql-bin-1.000001 |      154 |              | mysql,sys,information_schema,performance_schema | ec8d4606-9ee4-11e8-a24d-000c2916b700:1-6 |
+--------------------+----------+--------------+-------------------------------------------------+------------------------------------------+
1 row in set (0.00 sec)

记录File和Position信息，设置slave时需用到

master1配置完成。master2配置基本相同，按照上述步骤配置，
master2配置文件如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#====以上为系统原始配置，以下为新增配置====#

#server-id必须唯一，各主机不同
server-id=2
innodb_flush_log_at_trx_commit=2  #
sync_binlog=1

#log-bin=mysql-bin-*，*号一般是server-id
log-bin=mysql-bin-2

#binlog-do-db表示需要同步的数据库，不填表示全部同步
#binlog-do-db=xxxx

#binlog_ignore_db表示不需要同步的数据库，这里不同步mysql自带4个数据库
binlog_ignore_db=mysql
binlog_ignore_db=sys
binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema

#以表的方式存错
master_info_repository=TABLE
relay_log_info_repository=TABLE

#开启GTID模式
gtid-mode=on
enforce-gtid-consistency=true

#假设需要将来可能需要10台服务器做备份， 所以auto-increment-increment 设为10
auto-increment-increment=10
#auto-increment-offset表示这台服务器的序号。
#从2开始，不超过auto-increment-increment。
auto-increment-offset=2

三、配置slave：

修改my.cnf

#编辑my.cnf
vi /etc/my.cnf

slave配置文件如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#====以上为系统原始配置，以下为新增配置====#

#server-id必须唯一，各主机不同
server-id=99
innodb_flush_log_at_trx_commit=2
sync_binlog=1
#log-bin=mysql-bin-*，*号一般是server-id
log-bin=mysql-bin-99

#以表的方式存错
master_info_repository=TABLE
relay_log_info_repository=TABLE

#开启GTID模式
gtid-mode=on
enforce-gtid-consistency=true

重启mysql服务

systemctl restart mysql

进入mysql，配置master与slave关系。

#输入密码后，进入mysql
mysql -u root -p
#停止slave
mysql>stop slave；
#设置master1同步通道
#master_host为同步的主机ip地址
#master_user为同步的账户名称
#master_password为步的账户密码
#master_log_file为刚刚记录的File字段值
#master_log_pos为刚刚记录的Position字段值
#master_1为该同步通道名称
mysql>change master to master_host='192.168.31.210', master_user='slave' ,master_password='你的密码', master_log_file='mysql-bin-1.000001' ,master_log_pos=154 for channel 'master_1';
#设置master2同步通道
mysql>change master to master_host='192.168.31.211', master_user='slave' ,master_password='你的密码', master_log_file='mysql-bin-2.000001' ,master_log_pos=154 for channel 'master_2';
#开启同步通道
mysql>start slave for channel 'master_1';
mysql>start slave for channel 'master_2';
#查看通道开启状态
mysql>show slave status\G;
#显示内容如下
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.31.210
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-1.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: centos75clean-relay-bin-master_1.000005
                Relay_Log_Pos: 411
        Relay_Master_Log_File: mysql-bin-1.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: 154
              Relay_Log_Space: 843
              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: 8254cbe3-9ed1-11e8-bacd-000c2919f874
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 8254cbe3-9ed1-11e8-bacd-000c2919f874:1-4
            Executed_Gtid_Set: 8254cbe3-9ed1-11e8-bacd-000c2919f874:1-4,
a1848801-9ee1-11e8-a5cb-000c2932cfc9:1,
ec8d4606-9ee4-11e8-a24d-000c2916b700:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master_1
           Master_TLS_Version: 
*************************** 2. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.31.211
                  Master_User: slave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin-2.000001
          Read_Master_Log_Pos: 154
               Relay_Log_File: centos75clean-relay-bin-master_2.000002
                Relay_Log_Pos: 1678
        Relay_Master_Log_File: mysql-bin-2.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: 1510
              Relay_Log_Space: 3221
              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: 
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: ec8d4606-9ee4-11e8-a24d-000c2916b700
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 180814 11:45:04
     Last_SQL_Error_Timestamp: 180814 11:43:54
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: ec8d4606-9ee4-11e8-a24d-000c2916b700:1-6
            Executed_Gtid_Set: 8254cbe3-9ed1-11e8-bacd-000c2919f874:1-4,
a1848801-9ee1-11e8-a5cb-000c2932cfc9:1,
ec8d4606-9ee4-11e8-a24d-000c2916b700:1-5
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: master_2
           Master_TLS_Version:

如果显示如下内容，则配置成功。

 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

四、检验是否同步成功：

使用Navicat，检验是否成功。

至此，Mysql多源数据同步全部搭建完成！！！

构建大数据平台（十一）Mysql 多源数据同步
一、前提：（1）系统环境：centos7.3 （2）所有主机均按照《构建大数据平台（十）Mysql 搭建》中的方...
在Windows中基于Oracle GoldenGate （O
Oracle GoldenGate Windows平台MySQL->MySQL单向数据同步，试验环境：一、MyS...
如何保证消息的顺序性
大数据团队以前做过一个 mysql binlog 同步的系统，压力非常大，日同步数据要上亿，数据从一个 mysql...
很行领数据可视化平台，个性化、层次感、空间感的大屏
【很行领】数据可视化平台是基于SpringBoot+Vue3+mysql构建的数据可视化开发平台，数据库方面，基于...
使用datax迁移cassandra数据
DataX 是阿里巴巴集团内被广泛使用的离线数据同步工具/平台，实现各种异构数据源之间高效的数据同步功能。最近，阿...
Mysql 复制架构
Mysql 复制架构数据库复制的其实就是数据的同步，Mysql数据库基于binary log日志进行数据增量同步...
企业业务数据整合利器-异构数据同步系统Arkgate
Arkgate源起随着数据库、大数据生态的普遍应用和数据联动的重要性，MySQL数据库运维过程中关于数据同步需求...
mysql数据丢失，从库数据丢失分析
看到不错的文章mysql数据丢失怎办《MySQL数据库丢失数据场景分析》《MySQL数据库导入或者同步大量数据...
DataX3.0介绍
概览 datax是一个异构数据源离线同步工具，主要实现包括关系型数据库(MySQL、Oracle等)、MongoD...
创建Spring项目使用DataX进行定时数据库备份
DataX 是一个异构数据源离线同步工具，致力于实现包括关系型数据库(MySQL、Oracle等)、HDFS、...