canal 开启MySQL binlog、准备测试数据、创建用户 作者:马育民 • 2022-06-21 21:30 • 阅读:10216 # 说明 使用 `ROW` 记录模式,能清楚记录每一个行数据的修改细节 如果使用 `STATEMENT`,记录 SQL 语句,对于 canal 来说,没有意义,不会执行该 SQL # 修改配置文件 ``` vim /etc/my.cnf ``` 在 `[mysqld]` 区块下添加下面内容: ``` # 开启binlog,必须指定server id,否则无法启动 server_id=1 # 设置日志路径、文件名,注意路经需要mysql用户有权限写 log-bin=mysql-bin # 设置记录模式 binlog_format=ROW # 指定scott数据库发生数据变化时,会生成 binlog 日志 binlog-do-db=scott # 可以配置多个项,记录不同数据库的binlog # binlog-do-db=test ``` **说明:** - binlog日志路径:默认在 `/var/lib/mysql` 目录下。在本数据库中在 `/program/mysql-5.7.32/data/` 路径下 - 重启mysql,会立即生成 `mysql-bin.00000x` 文件 # 重启 ``` systemctl restart mysql ``` ### 查看是否生效 登录 MySQL 客户端: ``` mysql -uroot -proot ``` 执行下面 SQL: ``` show variables like '%binlog%'; ``` 看到下面结果,说明成功: ``` | binlog_format | ROW | ``` # 查看binlog日志 ``` cd /program/mysql-5.7.32/data/ ``` ``` ll mysql-bin* ``` 看到下面文件: ``` mysql-bin.000001 mysql-bin.index ``` ### 查看 mysql-bin.index 文件内容 ``` cat mysql-bin.index ``` 结果如下: ``` ./mysql-bin.000001 ``` ### 查看 mysql-bin.000001 文件内容 ``` vim mysql-bin.000001 ``` 结果是二进制乱码 # 测试 ### 创建 scott 数据库 **注意:**数据库名必须是 `scott` ,因为上面指定 只记录 `scott` 数据库的 binlog日志 创建过程略 ### 创建表 dept ``` CREATE TABLE `dept` ( `deptno` INT(4) NOT NULL, `dname` VARCHAR(14) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', `loc` VARCHAR(13) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', PRIMARY KEY (`deptno`) ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB ; ``` ### 创建表 emp ``` CREATE TABLE `emp` ( `empno` INT(4) NOT NULL, `ename` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', `job` VARCHAR(9) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci', `mgr` INT(4) NULL DEFAULT NULL, `hiredate` DATE NULL DEFAULT NULL, `sal` DECIMAL(7,2) NULL DEFAULT NULL, `comm` DECIMAL(7,2) NULL DEFAULT NULL, `deptno` INT(2) NULL DEFAULT NULL, PRIMARY KEY (`empno`) ) COLLATE='utf8mb4_unicode_ci' ENGINE=InnoDB ; ``` ### 导入 dept 数据 ``` INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (3, '市场部', '北京'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (20, 'RESEARCH', 'DALLAS'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (30, 'SALES', 'CHICAGO'); INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (40, 'OPERATIONS', 'BOSTON'); ``` ### 导入 emp 数据 ``` INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600.00, 300.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250.00, 500.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250.00, 1400.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850.00, NULL, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450.00, NULL, 10); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000.00, NULL, 10); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500.00, 0.00, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950.00, NULL, 30); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000.00, NULL, 20); INSERT INTO `emp`(`empno`, `ename`, `job`, `mgr`, `hiredate`, `sal`, `comm`, `deptno`) VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300.00, NULL, 10); ``` ### 执行下面sql ``` INSERT INTO `scott`.`dept`(`deptno`, `dname`, `loc`) VALUES (NULL, NULL, NULL); ``` # 查看 mysql-bin.000001 文件内容 ``` vim mysql-bin.000001 ``` 内容变多了 # 创建用户 canal 需要连接MySQL,读取 bin log日志,这里指定与之相关的用户名 ### 登录 ``` mysql -uroot -proot ``` ### 创建用户 创建 canal 用户,并分配 `select` ,`replication slave` , `replication client` 权限: ``` grant select ,replication slave,replication client on *.* to canal@'%' identified by 'canal'; ``` ### 立即生效 刷新权限,立即生效 ``` flush privileges; ``` 原文出处:http://malaoshi.top/show_1IX3XUEnSeeo.html