使用mac进行基于canal中间件对redis缓存进行处理
开启mysql binlog
问题点
由于我是安装的5.8版本以上的mysql，并没有my.cnf文件。于是需要自己去创建一个文件取各个my.cnf配置文件的先后顺序是：
-
/etc/my.cnf
-
/etc/mysql/my.cnf
-
/usr/local/mysql/etc/my.cnf
-
~/.my.cnf
我是直接配置在/etc环境下面，配置文件如下，配置好了之后 -
配置文件重点注意
log-bin=mysql-bin #添加这一行就ok
binlog-format=ROW #选择row模式
server_id=1
#配置好了并且重启后输入
show variables like 'log_bin';
看是否开启binlog
在navicat输入查看
重启mysql服务，具体操作如下
- 启动mysql服务
sudo /usr/local/MySQL/support-files/mysql.server start - 停止服务
== sudo /usr/local/MySQL/support-files/mysql.server stop == - 重启服务
== sudo /usr/local/MySQL/support-files/mysql.server restart ==
my.cnf文件配置
Last login: Sat Feb 27 22:44:06 on ttys003
mac@macdeMacBook-Pro ~ % cd /etc
mac@macdeMacBook-Pro /etc % ls
afpovertcp.cfg ntp_opendirectory.conf
aliases openldap
aliases.db pam.d
apache2 passwd
asl paths
asl.conf paths.d
auto_home periodic
auto_master pf.anchors
autofs.conf pf.conf
bashrc pf.os
bashrc_Apple_Terminal php-NOTICE-PLANNED-REMOVAL.txt
com.apple.screensharing.agent.launchd php-fpm.conf.default
csh.cshrc php-fpm.d
csh.login php.ini.default
csh.logout postfix
cups ppp
defaults profile
emond.d protocols
find.codes racoon
ftpusers rc.common
gettytab rc.netboot
group resolv.conf
hosts rmtab
hosts.equiv rpc
irbrc rtadvd.conf
kern_loader.conf security
krb5.keytab services
localtime shells
locate.rc snmp
mail.rc ssh
man.conf ssl
manpaths sudo_lecture
manpaths.d sudoers
master.passwd sudoers.d
my.cnf syslog.conf
nanorc ttys
networks uucp
newsyslog.conf wfs
newsyslog.d xtab
nfs.conf zprofile
notify.conf zshrc
ntp.conf zshrc_Apple_Terminal
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
Password:
mac@macdeMacBook-Pro /etc % cat my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[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
log_bin = /usr/local/mysql/logs/mysql-bin
binlog-format = ROW
# These are commonly set, remove the # and set as required.
# basedir = .....
# datadir = .....
# port = .....
# server_id = .....
# socket = .....
#服务端口号 默认3306
port = 3306
server_id = 1
# mysql安装根目录
basedir = /usr/local/mysql
# mysql数据文件所在位置
datadir = /usr/local/mysql/data
# pid
# 设置socke文件所在目录
socket = /usr/local/mysql/data/mysql.sock
# 跳过密码登录
# skip-grant-tables
# 数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
# character-set-server = utf8mb4
# 数据库字符集对应一些排序等规则，注意要和character-set-server对应
# collation-server = utf8mb4_general_ci
# 设置client连接mysql时的字符集,防止乱码
# init_connect='SET NAMES utf8mb4'
# 是否对sql语句大小写敏感，1表示不敏感，8.0需要在初始化时候设置
# lower_case_table_names = 1
# 最大连接数
max_connections = 1000
#最大错误连接数
max_connect_errors = 1200
# wait_timeout = 1814400
# 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
# 二进制日志目录
# 自动删除过期日志的天数
expire_logs_days = 10
# 限制单个文件大小
max_binlog_size = 100M
# 查询日志
general_log = 1
# 查询日志文件位置
general_log_file = /usr/local/mysql/logs/query.log
# 数据库错误日志文件
log_error = /usr/local/mysql/logs/error.log
# sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
mac@macdeMacBook-Pro /etc % sudo /usr/local/mysql/support-files/mysql.server restart
Shutting down MySQL
... SUCCESS!
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/mysql/data/macdeMacBook-Pro.local.pid).
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
mac@macdeMacBook-Pro /etc % sudo /usr/local/mysql/support-files/mysql.server restart
ERROR! MySQL server PID file could not be found!
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/mysql/data/macdeMacBook-Pro.local.pid).
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
mac@macdeMacBook-Pro /etc % sudo /usr/local/mysql/support-files/mysql.server restart
ERROR! MySQL server PID file could not be found!
Starting MySQL
. ERROR! The server quit without updating PID file (/usr/local/mysql/data/macdeMacBook-Pro.local.pid).
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
mac@macdeMacBook-Pro /etc % echo my.cnf
my.cnf
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
Password:
mac@macdeMacBook-Pro /etc % rm my.cnf
override rw-r--r-- root/wheel for my.cnf? y
rm: my.cnf: Permission denied
mac@macdeMacBook-Pro /etc % sudo rm my.cnf
mac@macdeMacBook-Pro /etc % ls
afpovertcp.cfg openldap
aliases pam.d
aliases.db passwd
apache2 paths
asl paths.d
asl.conf periodic
auto_home pf.anchors
auto_master pf.conf
autofs.conf pf.os
bashrc php-NOTICE-PLANNED-REMOVAL.txt
bashrc_Apple_Terminal php-fpm.conf.default
com.apple.screensharing.agent.launchd php-fpm.d
csh.cshrc php.ini.default
csh.login postfix
csh.logout ppp
cups profile
defaults protocols
emond.d racoon
find.codes rc.common
ftpusers rc.netboot
gettytab resolv.conf
group rmtab
hosts rpc
hosts.equiv rtadvd.conf
irbrc security
kern_loader.conf services
krb5.keytab shells
localtime snmp
locate.rc ssh
mail.rc ssl
man.conf sudo_lecture
manpaths sudoers
manpaths.d sudoers.d
master.passwd syslog.conf
nanorc ttys
networks uucp
newsyslog.conf wfs
newsyslog.d xtab
nfs.conf zprofile
notify.conf zshrc
ntp.conf zshrc_Apple_Terminal
ntp_opendirectory.conf
mac@macdeMacBook-Pro /etc % sudo vim my.cnf
mac@macdeMacBook-Pro /etc % sudo /usr/local/mysql/support-files/mysql.server restart
ERROR! MySQL server PID file could not be found!
Starting MySQL
.. SUCCESS!
mac@macdeMacBook-Pro /etc % vim my.cnf
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
character-set-server=utf8
init_connect='SET NAMES utf8
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 16M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
character-set-server=utf8
init_connect='SET NAMES utf8'
# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
"my.cnf" [readonly] 125L, 4743C
canal配置
1)下载canal
下载地址
原理介绍
1.和mysql主从复制的原理差不多，canal 就是相当于一个slave，会订阅master的binlog，当binlog发生改变的时候，matser会启用一个dumpthread 去下载，然后传递给slave
下好了之后 直接解压：
- 解压命令
- 我移了一下位置，命令为mv /Users/mac/Downloads/canal.deployer-1.1.5-SNAPSHOT.tar.gz /Users/mac/alibabacanal
- tar zxvf canal*.tar.gz
修改配置
// 这些其实里面都有备注看仔细点就好 重点配置下面的东西
修改conf/example/instance.properties
/ 修改mysql数据库的连接地址
canal.instance.master.address=127.0.0.1:3306
// 修改mysql的账号和密码
canal.instance.dbUsername=canal
canal.instance.dbPassword=canal
// 表示对mysql所有的表进行监听 实际项目中需要指定监听的库和表名
canal.instance.filter.regex=.*\\..*
修改之后去启动canal，执行语句如下
- sudo sh …/bin/startup.sh
踩坑点
我有一次停止后，再启用就报错，说端口占用了。记得查询一下端口，然后kill掉，再启动。 - sudo lsof -i :8080
- sudo kill -9 653
如何检查是否已经启动成功
去查看logs/canal/canal.log 日志进行查看
java代码实现canal客户端
public class CanalClient {
public static void main(String args[]) {
// 连接我们的CanalServer端
CanalConnector connector = CanalConnectors.newSingleConnector(new
InetSocketAddress("127.0.0.1",
11111), "example", "", "");
int batchSize = 100;
try {
connector.connect();
// 表示监听2020_order库下的tbl_order表 2020_order.*表示监听该库下所有表
connector.subscribe("study.student");
connector.rollback();
while (true) {
// 获取指定数量的数据
Message message = connector.getWithoutAck(batchSize);
long batchId = message.getId();
int size = message.getEntries().size();
System.out.println("batchId = " + batchId);
System.out.println("size = " + size);
System.out.println(message.getEntries());
if (batchId == -1 || size == 0) {
try {
Thread.sleep(1000);
} catch (InterruptedException e) {
e.printStackTrace();
}
} else {
printEntry(message.getEntries());
}
// 提交确认
connector.ack(batchId);
// connector.rollback(batchId); // 处理失败, 回滚数据
}
} finally {
connector.disconnect();
}
}
private static void printEntry(List<CanalEntry.Entry> entrys) {
for (CanalEntry.Entry entry : entrys) {
if (entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONBEGIN || entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONEND) {
continue;
}
CanalEntry.RowChange rowChage = null;
try {
rowChage = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
} catch (Exception e) {
throw new RuntimeException("ERROR ## parser of eromanga-event has an error , data:" + entry.toString(),
e);
}
CanalEntry.EventType eventType = rowChage.getEventType();
System.out.println(String.format("================> binlog[%s:%s] , name[%s,%s] , eventType : %s",
//logfileOffset可以拿到bin-log的postion
entry.getHeader().getLogfileName(), entry.getHeader().getLogfileOffset(),
entry.getHeader().getSchemaName(), entry.getHeader().getTableName(),
eventType));
for (CanalEntry.RowData rowData : rowChage.getRowDatasList()) {
if (eventType == CanalEntry.EventType.DELETE) {
redisDelete(rowData.getBeforeColumnsList());
} else if (eventType == CanalEntry.EventType.INSERT) {
redisInsert(rowData.getAfterColumnsList());
} else {
System.out.println("-------> before");
printColumn(rowData.getBeforeColumnsList());
System.out.println("-------> after");
redisUpdate(rowData.getBeforeColumnsList(), rowData.getAfterColumnsList());
}
}
}
}
private static void printColumn(List<CanalEntry.Column> columns) {
for (CanalEntry.Column column : columns) {
System.out.println(column.getName() + " : " + column.getValue() + " update=" + column.getUpdated());
}
}
private static void redisInsert(List<CanalEntry.Column> columns) {
JSONObject json = new JSONObject();
for (CanalEntry.Column column : columns) {
json.put(column.getName(), column.getValue());
}
if (columns.size() > 0) {
RedisUtil.stringSet(columns.get(0).getValue(), json.toJSONString());
}
}
private static void redisUpdate(List<CanalEntry.Column> beforColums,List<CanalEntry.Column> columns) {
JSONObject json = new JSONObject();
for (CanalEntry.Column column : columns) {
json.put(column.getName(), column.getValue());
}
if (columns.size() > 0) {
RedisUtil.stringSet(""+beforColums.get(0).getValue(), json.toJSONString());
}
}
private static void redisDelete(List<CanalEntry.Column> columns) {
JSONObject json = new JSONObject();
for (CanalEntry.Column column : columns) {
json.put(column.getName(), column.getValue());
}
if (columns.size() > 0) {
RedisUtil.delKey(columns.get(0).getValue());
}
}
}
public class RedisUtil {
private static Jedis jedis = null;
public static synchronized Jedis getJedis() {
if (jedis == null) {
jedis = new Jedis("127.0.0.1", 6379);
}
return jedis;
}
public static boolean existKey(String key) {
return getJedis().exists(key);
}
public static void delKey(String key) {
getJedis().del(key);
}
public static String stringGet(String key) {
return getJedis().get(key);
}
public static String stringSet(String key, String value) {
return getJedis().set(key, value);
}
public static void hashSet(String key, String field, String value) {
getJedis().hset(key, field, value);
}
}
canal高可用机制
-
canal宕机
如果canal宕机的话，再次启动canal，canal也会将在宕机过程中的操作的mysql数据，一样同步给客户端。因为会记录上次发送的postion -
客户端宕机
如果客户端宕机的话，再次启动客户端，canal也会将在宕机过程中的操作的mysql数据，一样同步给客户端。
基于rocketmq实现 还未写
修改连接模型 默认支持tcp, kafka, RocketMQ
canal.serverMode = kafka
#kafka连接地址，如果kafka集群的话这里配置只一个地址也是没问题的，如果需要配置可以多个用逗号:,分开
canal.mq.servers = 127.0.0.1:9092
109
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
