|NO.Z.00002|——————————|Deployment|——|Hadoop&EbProjec电商实时数仓项目.v02|——|EbProject.v02|canal部署&配置MySQL的binlog|
一、配置MySQL的binlog
### --- 常见的binlog命令
~~~ # 是否启用binlog日志
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+~~~ # 查看binlog类型
mysql> show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+~~~ # 查看详细的日志配置信息
mysql> show global variables like '%log%';
+--------------------------------------------+-----------------------------------------+
| Variable_name | Value |
+--------------------------------------------+-----------------------------------------+
| back_log | 80 |
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+--------------------------------------------+-----------------------------------------+~~~ # mysql数据存储目录
mysql> show variables like '%dir%';
+-----------------------------------------+----------------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------------+
| basedir | /usr/ |
| binlog_direct_non_transactional_updates | OFF |
| character_sets_dir | /usr/share/mysql/charsets/ |
| datadir | /var/lib/mysql/ |
| ignore_db_dirs | |
| innodb_data_home_dir | |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| lc_messages_dir | /usr/share/mysql/ |
| plugin_dir | /usr/lib64/mysql/plugin/ |
| slave_load_tmpdir | /tmp |
| tmpdir | /tmp |
+-----------------------------------------+----------------------------+~~~ # 查看binlog的目录
mysql> show global variables like "%log_bin%";
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+--------------------------------+~~~ # 查看当前服务器使用的biglog文件及大小
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 11630243 |
+------------------+-----------+~~~ # 查看最新一个binlog日志文件名称和Position
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 11630243 | dwshow | | |
+------------------+----------+--------------+------------------+-------------------+~~~ # 查询binlog 变动信息
mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.36-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 219 | Query | 1 | 400 | CREATE USER 'canal'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E3619321C1A937C46A0D8BD1DAC39F93B27D4458' |
| mysql-bin.000001 | 400 | Anonymous_Gtid | 1 | 465 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 465 | Query | 1 | 606 | GRANT ALL PRIVILEGES ON `canal`.* TO 'canal'@'%' |
| mysql-bin.000001 | 606 | Anonymous_Gtid | 1 | 671 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 671 | Query | 1 | 918 | GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*E3619321C1A937C46A0D8BD1DAC39F93B27D4458' |
| mysql-bin.000001 | 918 | Anonymous_Gtid | 1 | 983 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000001 | 983 | Query | 1 | 1070 | flush privileges |
+------------------+-----+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+二、在mysql中配置binlog
### --- 在mysql中配置binlog
~~~ # 在mysql中创建dwshow数据库
mysql> CREATE DATABASE dwshow DEFAULT CHARACTER SET utf8mb4;~~~ # 在MySQL中 , 需要先开启 binlog 写入功能，配置 binlog-format 为 ROW 模式。 在/etc/my.cnf 中配置如下:
[root@hadoop03 ~]# vim /etc/my.cnf
~~~ # 行尾写入如下配置参数：
[mysqld]
server-id=1 # 配置 MySQL replaction 需要定义，不要和 Canal 的 slaveId 重复
log-bin=mysql-bin # 开启 binlog
binlog-format=ROW # 选择 ROW 模式
binlog-do-db=dwshow # dwshow是数据库的名称### --- 重启MySQL
~~~ # 重启mysql服务：修改后需要重启MySQL，只有重启之后配置才能生效。
[root@hadoop03 ~]# ps -ef|grep mysql
mysql 8672 1 1 14:16 ? 00:00:00 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
~~~ # MySQL重启之后，到下面路径中看有没有mysql-bin.*****文件
[root@hadoop03 ~]# ll /var/lib/mysql/mysql-bin.
mysql-bin.000001 mysql-bin.index三、授权设定
### --- 创建canal用户
~~~ # 修改mysql设置密码规则
mysql> set global validate_password_policy=0;
mysql> set global validate_password_length=5;
mysql> SHOW VARIABLES LIKE 'validate_password%';~~~ # 创建数据库及用户
mysql> CREATE DATABASE dwshow DEFAULT CHARACTER SET utf8mb4;
mysql> CREATE USER 'canal'@'%' IDENTIFIED BY 'canal';
mysql> GRANT ALL PRIVILEGES ON canal.* TO 'canal'@'%';### --- 在mysql中授权canal用户
~~~ # 在MySQL中执行
~~~ # 授权 Canal 链接 MySQL账号具有作为 MySQL slave 的权限, 如果已有账户可直接 grant。
mysql> GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' IDENTIFIED BY 'canal' ;~~~ # 保存刷新配置
mysql> flush privileges;### --- 查看用户授权情况
~~~ # 允许任意的ip地址去连接canal
mysql> select Host,User from user;
+-----------+---------------+
| Host | User |
+-----------+---------------+
| % | canal |
+-----------+---------------+~~~ # 查看canal用户的权限
mysql> show grants for 'canal'@'%';
+---------------------------------------------------------------------------+
| Grants for canal@% |
+---------------------------------------------------------------------------+
| GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%' |
| GRANT ALL PRIVILEGES ON `canal`.* TO 'canal'@'%' |
+---------------------------------------------------------------------------+四、导入数据并测试
### --- 导入数据并测试
~~~ 接下来往dbshow数据库中导入业务数据进行测试。
~~~ 导入数据之后，观察/var/lib/mysql目录中是否有mysql-bin.***文件，如下所示：
[root@hadoop03 ~]# ll /var/lib/mysql/mysql-bin.000001
-rw-r----- 1 mysql mysql 11630243 Nov 28 16:16 /var/lib/mysql/mysql-bin.000001Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart
——W.S.Landor
分类:
dov002-EB实时数仓
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」