在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 int primary 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.02 sec) mysql> GRANT SHOW VIEW, SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'canal'@'%'; Query OK, 0 rows affected (0.02 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.01 sec) 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:3306 canal.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
