linux mysql 数据库同步工具_在Linux系统中创建数据库和表, 并使用canal数据同步工具...

在Linux系统中创建数据库和表

进入MySQL数据库

[root@localhost ~]# mysql -uroot -p

Enter password:

查看数据库

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| performance_schema |

| sys |

+--------------------+

创建数据库guli

mysql> create database guli;

再次查询数据库确认

mysql>show databases;+--------------------+

| Database |

+--------------------+

| information_schema |

| guli |

| mysql |

| performance_schema |

| sys |

+--------------------+

在创建表之前先选择要使用的数据库

mysql> use guli;

创建表members

mysql>create table members (-> id intprimary key,-> username varchar(100),-> age int

-> );

查看所有表

mysql>show tables;+----------------+

| Tables_in_guli |

+----------------+

| members |

+----------------+

在Windows本地创建表members

查询members为空

mysql> select *from members;

Empty set (0.49 sec)

检查binlog功能是否有开启

mysql> show variables like 'log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | OFF |

+---------------+-------+

如果显示状态为OFF表示该功能未开启，开启binlog功能

退出MySQL

mysql>exit

Bye

修改 mysql 的配置文件 my.cnf

[root@localhost etc]# vim my.cnf

追加内容：

log-bin=mysql-bin #binlog文件名

binlog_format=ROW #选择row模式

server_id=1 #mysql实例id,不能和canal的slaveId重复

MySQL5.7的配置如图：

重启MySQL

[root@localhost ~]# service mysqld restart

Redirecting to/bin/systemctl restart mysqld.service

登录 mysql 客户端，查看 log_bin 变量

mysql> show variables like 'log_bin';+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| log_bin | ON |

+---------------+-------+

在Windows系统MySQL数据库中创建canal用户

CREATE USER 'canal'@'%' IDENTIFIED BY 'RedFace.ren2020';

GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON*.* TO 'canal'@'%';

FLUSH PRIVILEGES;

SELECT DISTINCT CONCAT('User: ''',USER,'''@''',HOST,''';') AS QUERY FROM mysql.user;

在Linux系统的MySQL数据库中创建canal用户

mysql>show tables;+---------------------------+

| Tables_in_mysql |

+---------------------------+

| columns_priv |

| db |

| engine_cost |

| event |

| func |

| general_log |

| gtid_executed |

| help_category |

| help_keyword |

| help_relation |

| help_topic |

| innodb_index_stats |

| innodb_table_stats |

| ndb_binlog_index |

| plugin |

| proc |

| procs_priv |

| proxies_priv |

| server_cost |

| servers |

| slave_master_info |

| slave_relay_log_info |

| slave_worker_info |

| slow_log |

| tables_priv |

| time_zone |

| time_zone_leap_second |

| time_zone_name |

| time_zone_transition |

| time_zone_transition_type |

| user |

+---------------------------+

31 rows in set (0.00 sec)

mysql> CREATE USER 'canal'@'%' IDENTIFIED BY 'RedFace.ren2020';

Query OK,0 rows affected (0.02sec)

mysql> GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%';

Query OK,0 rows affected (0.02sec)

mysql>FLUSH PRIVILEGES;

Query OK,0 rows affected (0.01sec)

mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;+------------------------------------+

| query |

+------------------------------------+

| User: 'canal'@'%'; |

| User: 'root'@'%'; |

| User: 'mysql.session'@'localhost'; |

| User: 'mysql.sys'@'localhost'; |

| User: 'root'@'localhost'; |

+------------------------------------+

5 rows in set (0.02 sec)

测试连接Linux中的数据库

创建canal文件夹

[root@localhost ~]# cd /usr/local

[root@localhost local]# ls

bin etc games include lib lib64 libexec nginx redis sbin share src

[root@localhost local]# mkdir canal

[root@localhost local]# ls

bin canal etc games include lib lib64 libexec nginx redis sbin share src

[root@localhost local]# cd canal/

使用Xshell工具，可以直接将canal.deployer-1.0.24.tar.gz文件拖拽到canal文件夹，并将其解压

[root@localhost local]# cd canal/[root@localhost canal]# rz-E

rz waiting to receive.

[root@localhost canal]# ls

canal.deployer-1.0.24.tar.gz

[root@localhost canal]# tar zxvf canal.deployer-1.0.24.tar.gz

更改配置信息

[root@localhost canal]# cd conf/example/[root@localhost example]# ls

instance.properties

[root@localhost example]# vim instance.properties

我更改后的配置信息如下【红色为要更改的地方】

#################################################

## mysql serverId

canal.instance.mysql.slaveId= 1234# position info

canal.instance.master.address= 192.168.211.135:3306canal.instance.master.journal.name=canal.instance.master.position=canal.instance.master.timestamp=#canal.instance.standby.address=#canal.instance.standby.journal.name=#canal.instance.standby.position=#canal.instance.standby.timestamp=# username/password

canal.instance.dbUsername=canal

canal.instance.dbPassword=RedF***.ren20**

canal.instance.defaultDatabaseName=canal.instance.connectionCharset= UTF-8# table regex

canal.instance.filter.regex= .*\\..*# table black regex

canal.instance.filter.black.regex=#################################################

启动canal

[root@localhost canal]# cd bin/[root@localhost bin]# ls

startup.bat startup.sh stop.sh

[root@localhost bin]# ./startup.sh

查看canal状态

[root@localhost bin]# ps -ef | grep canal

其他MySQL命令

停止MySQL

[root@localhost ~]# service mysqld stop

Redirecting to/bin/systemctl stop mysqld.service