Canal

Posted on September 13, 2015

binlog

  • mysql 重启:

    To start:
    launchctl load ~/Library/LaunchAgents/com.mysql.mysqld.plist
    
    To stop:
    launchctl unload ~/Library/LaunchAgents/com.mysql.mysqld.plist
    

    或者安装了brew services: brew services restart mysql

  • mysql 配置文件:

    mysql --help 中可以看到:

    Default options are read from the following files in the given order:
    /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
    
  • 查看binlog状态: show variables like '%log_bin%';

    +---------------------------------+-------+
    | Variable_name                   | Value |
    +---------------------------------+-------+
    | log_bin                         | OFF   | #表示是否开启binlog
    | log_bin_trust_function_creators | OFF   |
    | sql_log_bin                     | ON    |
    +---------------------------------+-------+
    
  • 配置开启:

    [mysqld]
    log-bin=mysql-bin #添加这一行就ok
    binlog-format=ROW #选择row模式
    server_id=1 #配置mysql replaction需要定义,不能和canal的slaveId重复
    

    其他常见配置参数:

    • max_binlog_size
    • expire_logs_days
  • log 文件

    二进制日志由配置文件的log-bin选项负责启用,Mysql服务器将在数据根目录创建两个新文件XXX-bin.001(Mysql会在重启时生成一个新的日志文件,文件序号递增)和XXX-bin.index

    mac 默认在/usr/local/var/mysql 可以通过ps aux | grep mysqld--datadir 知悉

  • 查看生成的日志文件: show binary logs

  • 查看binlog mysqlbinlog mysql-bin.000003, 条目示例

    BEGIN
    /*!*/;
    # at 369
    #150913 16:09:00 server id 1  end_log_pos 468   Query thread_id=1 exec_time=0 error_code=0
    SET TIMESTAMP=1442131740/*!*/;
    insert into t (title) values ('xyz')
    /*!*/;
    # at 468
    #150913 16:09:00 server id 1  end_log_pos 495   Xid = 18
    COMMIT/*!*/;
    

相关的mysql操作

  • 查看用户列表:

    select host,user from mysql.user;

  • 创建canal server使用的mysql用户, 并进行赋权:

    CREATE USER canal IDENTIFIED BY 'canal';
    GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';
    -- GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%' ;
    FLUSH PRIVILEGES;
    
  • 查看用户权限:

    show grants for canal


参考资料