MySQL主从复制及生产如何采集至大数据

一、本次课程主题

二、MySQL单节点部署

• 2.1、MySQL主从复制
• 2.2、MySQL主从架构
• 2.3、MySQL故障案例
• 2.4、MySQL如何落地到大数据平台
• 2.5、MySQL拓展思考
• 2.6、主从复制拓展思考

一、本次课程主题

• 数据库：MySQL、PostgreSQL、Cassandra
  1、搭建部署单节点
  2、主从架构（读写分离）及部署
  3、故障案例
  4、MySQL如何落地到大数据平台
  5、拓展思考：

二、MySQL单节点部署

• https://blog.csdn.net/SparkOnYarn/article/details/104876632

2.1、MySQL主从复制

• 前提条件：阿里云上购买2台同一区域的机器，都部署好mysql5.7

sz-5i5i01做master 主
sz-5i5j02做slave 从

如何操作：

1、在主库上创建一个复制用户，进入到mysql数据库中操作：
grant replication slave on . to repluser@’%’ identified by ‘960210’;
flush privileges;

2、主从复制的注意点：

• 两台机器的server_id不能保持一致，在/etc/my.cnf文件中进行修改；
• 设定MySQL主库的binlog的过期时间为7天，expire_logs_day=7，binlog_format=ROW，

3、pos

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

4、在从库中输入如下去尝试连接主库：
change master to
master_host='master_host=‘47.103.60.162’,
master_port=‘3306’,
master_url=‘repluser’,
master_password=‘960210’,
master_log_file=‘mysql-bin.000002’,
master_log_pos=1458;

mysql> change master to
    -> master_host='47.103.60.162',
    -> master_port=3306,
    -> master_user='repluser',
    -> master_password='960210',
    -> master_log_file='mysql-bin.000002',
    -> master_log_pos=1458;
ERROR 29 (HY000): File '/usr/local/mysql/relay_log/relay-log.index' not found (Errcode: 2 - No such file or directory)
//提示没有这个目录，直接去创建一个即可。
[root@sz5i5j-02 mysql]# mkdir relay_log

mysql> change master to master_host='47.103.60.162',master_port=3306,master_user='repluser',master_password='960210',master_log_file='mysql-bin.000002',master_log_pos=1458;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

强调：MySQL在生产上的调优参数：根据机器内存调整MySQL的内存：innodb_buffer_pool_size = 2G

在从库中启动slave，然后查看状态：mysql> show slave status \G;
关注两个参数即可：
Slave_IO_Running: No
Slave_SQL_Running: Yes

测试：
在主库上创建一个表：createt database ruozedata;在从库上查看是否有主库创建的这个ruozedata;

故障排查：

1、在/etc/my.cnf中配置的两台服务器的server_id不能是一致的，如启动后修改的my.cnf文件，需要重启mysql服务：
赘述：CentOS下如何使得MySQL5.7开机自启动：

vi /etc/rc.local
添加如下这句话：
su - mysqladmin -c "/etc/init.d/mysql start --federated"
意思是切换为mysqladmin用户的一瞬间就启动mysq服务；

注意/etc/init.d目录下的这个mysql脚本是

2、出现报错：
mysql> change master to master_host=‘47.103.60.162’,master_port=3306,master_user=‘repluser’,master_password=‘960210’,master_log_file=‘mysql-bin.000002’,master_log_pos=1458;
ERROR 3021 (HY000): This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first.

那就按照它提示的进行操作：STOP SLAVE IO_THREAD；运行这个之后show slave status \G; 发现还是有报错：Slave_IO_Running: No

3、重启slave从节点还是出现问题：还是显示：Slave_IO_Running: No

• 参考博客：https://www.cnblogs.com/l-hh/p/9922548.html#_label1

操作如下：
mysql> stop slave;  
Query OK, 0 rows affected (0.00 sec)

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
Query OK, 0 rows affected (0.00 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status \G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

主要是SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; 这一步得到了解决。

测试：我们在sz5i5j-01机器上创建ruozedata_hive数据库，再去到sz5i5j-02机器上查看数据库过来了；
在ruozedata_hive数据库中创建表，再去sz5i5j-02机器上去查看，表也在：

1、在sz5i5j-01机器上的主库创建表：
mysql> create table stu(id int,name varchar(20),age int);
ERROR 1046 (3D000): No database selected
mysql> use ruozedata_hive;
Database changed
mysql> create table stu(id int,name varchar(20),age int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into stu values(1,'john',24);
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
+--------------------------+
| Tables_in_ruozedata_hive |
+--------------------------+
| stu                      |
+--------------------------+
1 row in set (0.00 sec)

2、再去sz5i5j-02机器上的从库去查看到如下：
mysql> use ruozedata_hive;
Database changed
mysql> show tables;
+--------------------------+
| Tables_in_ruozedata_hive |
+--------------------------+
| stu                      |
+--------------------------+
1 row in set (0.00 sec)

mysql> select * from stu;
+------+------+------+
| id   | name | age  |
+------+------+------+
|    1 | john |   24 |
+------+------+------+
1 row in set (0.00 sec)

• 至此，MySQL的主从部署完成。

2.2、MySQL主从架构

• 当我们的业务数据流转到MySQL主库中，这些数据以二进制的binlog文件落地到磁盘上，binlog文件目录：/usr/local/mysql/arch；
• slave有两个进程：Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  IO进程通过网络去读这个binlog文件，读过来之后把这个文件写到Relay_log（中继日志），从库中单独有个SQL进程，把中继日志的binlog文件解析成SQL.

专业术语：重演；小弟把binlog文件拿过来存到Relay_log，再有另外一个SQL Thread进程拿过来解析重新作用在slave从库。

读和写到底应该怎么做呢？一般都是JDBC连接，指定从库IP；市面上也有很多读写分离的组件，比如：Proxy；正常生产上就采用如下这个图。

1、relay_log文件存储位置：
[root@sz5i5j-02 relay_log]# pwd
/usr/local/mysql/relay_log
[root@sz5i5j-02 relay_log]# ll
total 12
-rw-r----- 1 mysqladmin dba  367 Apr 17 21:03 relay-log.000006
-rw-r----- 1 mysqladmin dba 1174 Apr 17 21:15 relay-log.000007
-rw-r----- 1 mysqladmin dba   88 Apr 17 21:03 relay-log.index

2、我们怎么知道这个relay_log文件呢，配置在/etc/my.cnf中如下：
[root@sz5i5j-02 relay_log]# cat /etc/my.cnf|grep relay
relay-log  = /usr/local/mysql/relay_log/relay-log
relay-log-index = /usr/local/mysql/relay_log/relay-log.index
relay_log_info_file= /usr/local/mysql/relay_log/relay-log.info
relay_log_info_repository=TABLE
relay_log_recovery=ON

3、在生产上/etc/my.cnf中只要配置一个参数即可：
innodb_buffer_pool_size = 2G
J总公司正常写一个8G

2.3、MySQL故障案例

关于主从架构，在生产上会出现什么样的一个问题呢？

1、如果出现Slave_SQL_Running: NO这个问题，就是在show slave status \G;

解决方案：在从库中进行如下操作：
stop slave;

去到MySQL主库中去查看这个binlog命令：

su - mysqladmin

导出bug前后200行：
sz5i5j-01:mysqladmin:/usr/local/mysql/arch:> mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/arch/mysql-bin.000003 | grep -A 200 '294204964' > debug20170820.log

或者全部导：
sz5i5j-01:mysqladmin:/usr/local/mysql/arch:> mysqlbinlog --no-defaults --base64-output=decode-rows -v -v /usr/local/mysql/arch/mysql-bin.000003 > 2.sql

cat2.sql进行查看，就能看见明文的执行SQL

• 检查mysql数据库从库发现这条记录已经不存在，故同步抛错，我们只需要执行set global sql_slave_skip_counter=1; 跳过这个事物即可
  
• 比如在主库执行一条数据：delete from t where id=1; 主库执行这条数据，从库没有这条数据，就会抛error，手工检查找不到；主要是因为主从同步是一个异步的操作，先关闭slave，set global sql_slave_skip_counter=1; 跳过这个事物即可。

然后show slave status \G; 看两个是否都是yes

2.4、MySQL如何落地到大数据平台

离线架构：B站生产上从0-1
MySQL --> SQOOP/DataX --> Hive/HBase
MySQL --> spark bulkload --> HBase

J总生产上实时增量数仓架构，线下班项目：
MySQL（正常生产上使用从库） --> Maxwell --> Kafka --> Spark Streaming+Phoenix --> HBase

Maxwell的B站视频：

• https://www.bilibili.com/video/BV1zb411N7jj?from=search&seid=12687533310067328535

MySQL --> streamsets --> kudu + impala

2.5、拓展思考

• 针对于库、表做白名单进行过滤，一些不需要的表发到kafka加大开销，没有必要。
• 如何做全量数据，平台搭建好后，HBase是空的，MySQL积累了很多的数据，如何全量刷新到HBase，假设今天晚上跑的，又如何增量刷新？
• 又如何去保证高可靠，数据零丢失？数据质量（MySQL的数据量和HBase的数据量是否相同呢）
• 业务库数据是有顺序的 insert update delete，Spark Streaming消费的时候是一个partition，先delete，再update，再delete，如何保证有序？正常DML语句同步数据，但是DDL语句，比如MySQL表结构增加字段，修改字段，删除字段，HBase如何跟上进行相应的变化呢，这一块的机制该如何保证呢？
• 如何保证整条链路的断点还原（Maxwell）、断批还原（Spark Streaming）？

2.6、主从复制拓展思考

• 生产上存在的情况：主1和从1，如何搭建一个新的从库挂载在从1保证数据okay，叫做主从从；做主1的从库，做从1的从库；
• 从2和从1并行的库

还有个业务场景：主1 --> 从1；此时另外一个主2也要写到从1；也要保证不能少数据。

业务场景：
主库的oms库–>从库oms库已经同步好了，此时主1中又有wms01、wms02库，要把这两个库同步到从库中去，Maxwell此时又要采集wms01+wms02的库来进行需求分析。