- Notifications
You must be signed in to change notification settings - Fork1
dzw1113/binlog
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
###解决通过java订阅binlog，解析数据库的dml/dcl/ddl转换成sql文件进行存储。
maven引用或者直接下代码进行编译
<dependency> <groupId>io.github.dzw1113</groupId> <artifactId>binlog</artifactId> <version>1.0.1</version></dependency>主要依赖：<dependency> <groupId>com.github.shyiko</groupId> <artifactId>mysql-binlog-connector-java</artifactId> <version>${version}</version></dependency>server-id =1user=mysqllog-bin=master-mysql-bincharacter-set-server = utf8mb4default-storage-engine = InnoDBlog-bin=mysql-binbinlog_format=mixedlog_bin_trust_function_creators=1log_slave_updates=1gtid_mode=ONenforce_gtid_consistency=ONauto_increment_increment = 2query-cache-size = 0external-locking = FALSEmax_allowed_packet = 32Msort_buffer_size = 2Mjoin_buffer_size = 2Mthread_cache_size = 51query_cache_size = 32Mtmp_table_size = 96Mquery_cache_type=1log-error=D:\mysql\mysql-5.7.20-winx64-master2\mysqld.logslow_query_log = 1slow_query_log_file = D:\mysql\mysql-5.7.20-winx64-master2\slow.loglong_query_time = 0.1expire-logs-days = 14sync_binlog = 1binlog_cache_size = 4Mmax_binlog_cache_size = 8Mmax_binlog_size = 1024Mlog_slave_updatesbinlog_format = MIXED#这里使用的混合模式复制relay_log_recovery = 1#不需要同步的表replicate-wild-ignore-table=mydb.sp_counter#不需要同步的库replicate-ignore-db = mysql,information_schema,performance_schemakey_buffer_size = 32Mread_buffer_size = 1Mread_rnd_buffer_size = 16Mbulk_insert_buffer_size = 64Mmyisam_sort_buffer_size = 128Mmyisam_max_sort_file_size = 10Gmyisam_repair_threads = 1transaction_isolation = REPEATABLE-READ#innodb_additional_mem_pool_size = 16Minnodb_buffer_pool_size = 1024Minnodb_buffer_pool_load_at_startup = 1innodb_buffer_pool_dump_at_shutdown = 1#innodb_data_file_path = ibdata1:1024M:autoextendinnodb_flush_log_at_trx_commit = 2innodb_log_buffer_size = 32Minnodb_log_file_size = 2Ginnodb_log_files_in_group = 2innodb_io_capacity = 4000innodb_io_capacity_max = 8000innodb_max_dirty_pages_pct = 50# innodb_flush_method = O_DIRECTinnodb_file_format = Barracudainnodb_file_format_max = Barracudainnodb_lock_wait_timeout = 10innodb_rollback_on_timeout = 1innodb_print_all_deadlocks = 1innodb_file_per_table = 1innodb_locks_unsafe_for_binlog = 0# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=02、启动项目访问Swagger（http://127.0.0.1:19911/doc.html）
执行【启动MySQL监听】 入参为（依据自身调整）：
{"driverClassName": "com.mysql.jdbc.Driver","host": "127.0.0.1","jdbcName": "test1","password": "xtt@123456","port": 13308,"serverId": 2,"url": "jdbc:mysql://127.0.0.1:13308/","username": "root"}3、打开日志输出（http://127.0.0.1:19911/）
CREATE DATABASE `test111` CHARACTER SET 'utf8' COLLATE 'utf8_bin';use test111;create table test(id int);insert test select 2;alter table test add column first_name varchar(20);insert test VALUES(1,'2'),(1,3);update test set first_name = '再说2' where id =2;DELETE FROM TEST WHERE ID = 1;再切换网页查看日志。
RESET MASTER;
FLUSH LOGS;
show master status;
show binary logs;
show binlog events in 'mysql-bin.000001';
show binlog events in 'mysql-bin.000001' FROM 28375689 LIMIT 10;
set binlog_rows_query_log_events=1;
show variables like '%binlog_format%';
show engine innodb status;
CREATE USER canal IDENTIFIED BY 'dzw';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON. TO 'dzw'@'%';
-- GRANT ALL PRIVILEGES ON. TO 'dzw'@'%' ;
FLUSH PRIVILEGES;
参考
https://blog.csdn.net/qq_32352565/article/details/77506618
https://www.cnblogs.com/codingLiu/p/12725789.html
http://blog.itpub.net/20892230/viewspace-2129567/
https://www.cnblogs.com/mysql-dba/tag/mysql/
https://www.cnblogs.com/kevingrace/p/5569753.html