做简单的网站多少钱,微信公众号运营策划方案,凡客优品官方网站,互联网网站界面设计 要素参考1:MySQL(通过该配置实现了实时同步) 参考2:experimental MaterializedMySQL 参考3:[experimental] MaterializedMySQL(包含设置 allow_experimental_database_materialized_mysql) MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中#xff0c;并允许您对表进行I…参考1:MySQL(通过该配置实现了实时同步) 参考2:experimental MaterializedMySQL 参考3:[experimental] MaterializedMySQL(包含设置 allow_experimental_database_materialized_mysql) MySQL引擎用于将远程的MySQL服务器中的表映射到ClickHouse中并允许您对表进行INSERT和SELECT查询以方便您在ClickHouse与MySQL之间进行数据交换 MySQL数据库引擎会将对其的查询转换为MySQL语法并发送到MySQL服务器中因此您可以执行诸如SHOW TABLES或SHOW CREATE TABLE之类的操作。 一、在MySQL数据库中创建一个测试数据库
执行如下语句进行创建数据库名test、数据表名mysql_table
mysql USE test;
Database changedmysql CREATE TABLE mysql_table (- int_id INT NOT NULL AUTO_INCREMENT,- float FLOAT NOT NULL,- PRIMARY KEY (int_id));
Query OK, 0 rows affected (0,09 sec)mysql insert into mysql_table (int_id, float) VALUES (1,2);
Query OK, 1 row affected (0,00 sec)mysql select * from mysql_table;
-----------
| int_id | value |
-----------
| 1 | 2 |
-----------
1 row in set (0,00 sec)二、在clickhouse数据库中创建MySQL引擎
语法
CREATE DATABASE [IF NOT EXISTS] db_name [ON CLUSTER cluster]
ENGINE MySQL(host:port, [database | database], user, password)创建过程
说明由于刚开始使用的是MaterializeMySQL方式所以在示例代码中能看到MaterializeMySQL直接替换为MySQL即可 1、执行如下语句
CREATE DATABASE mysqllzh ENGINE MaterializeMySQL(192.168.0.176:3306, integration_shandong, root, 123456)SETTINGSallows_query_when_mysql_losttrue,max_wait_time_when_mysql_unavailable10000;2、报错如下 报错信息MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it.
详细信息如下
[root172 clickhouse-server]# clickhouse-client
ClickHouse client version 23.11.1.2711 (official build).
Connecting to localhost:9000 as user default.
Password for user (default):
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.11.1.Warnings:* Linux is not using a fast clock source. Performance can be degraded. Check /sys/devices/system/clocksource/clocksource0/current_clocksource* Linux transparent hugepages are set to always. Check /sys/kernel/mm/transparent_hugepage/enabled* Maximum number of threads is lower than 30000. There could be problems with handling a lot of simultaneous queries.172.20.219.19 :) CREATE DATABASE mysqllzh ENGINE MaterializeMySQL(192.168.0.176:3306, integration_shandong, root, 123456)SETTINGSallows_query_when_mysql_losttrue,max_wait_time_when_mysql_unavailable10000;CREATE DATABASE mysqllzh
ENGINE MaterializeMySQL(192.168.0.176:3306, integration_shandong, root, 123456)
SETTINGS allows_query_when_mysql_lost 1, max_wait_time_when_mysql_unavailable 10000Query id: f7b41d0d-e9b6-4b4f-a2be-86dae43a1e7cElapsed: 0.001 sec. Received exception from server (version 23.11.1):
Code: 336. DB::Exception: Received from localhost:9000. DB::Exception: MaterializedMySQL is an experimental database engine. Enable allow_experimental_database_materialized_mysql to use it. (UNKNOWN_DATABASE_ENGINE)
3、解决方案 执行如下命令
SET allow_experimental_database_materialized_mysql14、再次执行命令报错如下 ConnectionFailed: Host 192.168.0.197 is not allowed to connect to this MySQL server ((nullptr):0),. (ASYNC_LOAD_FAILED) 详细信息如下
172.20.219.19 :)
CREATE DATABASE mysqllzh ENGINE MaterializeMySQL(192.168.0.196:3306, integration_shandong, root, 123456)SETTINGSallows_query_when_mysql_losttrue,max_wait_time_when_mysql_unavailable10000;CREATE DATABASE mysqllzh
ENGINE MaterializeMySQL(192.168.0.196:3306, integration_shandong, root, 123456)
SETTINGS allows_query_when_mysql_lost 1, max_wait_time_when_mysql_unavailable 10000Query id: 4713dd46-d3c1-44b1-9aa5-9c96e7b33f31Elapsed: 4.588 sec. Received exception from server (version 23.11.1):
Code: 695. DB::Exception: Received from localhost:9000. DB::Exception: Load job startup MaterializedMySQL database mysqllzh failed: Poco::Exception. Code: 1000, e.code() 1130, mysqlxx::ConnectionFailed: Host 192.168.0.197 is not allowed to connect to this MySQL server ((nullptr):0),. (ASYNC_LOAD_FAILED) 5、解决方案 将mysql的root权限设置为允许所有ip地址可以访问执行如下sql语句
use mysql;
SELECT Host, User FROM mysql.user;
update mysql.user set host% where userroot;
-- GRANT ALL PRIVILEGES ON *.* TO root192.168.0.197 IDENTIFIED BY 123456 WITH GRANT OPTION;
-- FLUSH PRIVILEGES;6、再次执行命令报错如下
Received exception from server (version 23.11.1):
Code: 695. DB::Exception: Received from localhost:9000. DB::Exception: Load job startup MaterializedMySQL database mysqllzh failed: Code: 537. DB::Exception: Illegal MySQL variables, the MaterializedMySQL engine requires default_authentication_pluginmysql_native_password. (ILLEGAL_MYSQL_VARIABLE),. (ASYNC_LOAD_FAILED)7、解决方案
先查看一下目前default_authentication_plugin的值如下
show variables like %default_authentication_plugin%;通过如下语句设置为mysql_native_password但是执行后会看到如下错误因为是只读的所以 通过修改配置文件来修改
set default_authentication_pluginmysql_native_password在配置文件my.ini中增加如下配置内容
[mysqld]
default_authentication_plugin mysql_native_password注意mysql配置文件目录 1.安装路径,例如:C:\Program Files\MySQL\MySQL Server 8.2\bin\mysqld.exe 2.配置目录,例如:“C:\ProgramData\MySQL\MySQL Server 8.2\my.ini”
修改后重启MySQL服务
再次执行命令
show variables like ‘%default_authentication_plugin%’;
8、在clickhouse服务器中再次执行命令成功了
CREATE DATABASE mysqllzh ENGINE MaterializeMySQL(‘192.168.0.137:3306’, ‘db’, ‘root’, ‘123456’) SETTINGS allows_query_when_mysql_losttrue, max_wait_time_when_mysql_unavailable10000; 其实是要用如下的语句 CREATE DATABASE mysql_db ENGINE MySQL(192.168.0.137:3306, test, root, 123456) 9、继续执行如下sql语句检查是否存在表test
show tables from mysqllzh 10、以下语句检查是否存在数据 select * from mysqllzh.test 在MySQL中新增几条数据后检查是否能自动同步过来? 最后发现并没有
三、新方法只是创建MySQL引擎的语句有改变其余的都没有变补充
1、先在mysql中创建数据库和表以及数据
create table mysql_table(
int_id int not null AUTO_INCREMENT,
float FLOAT not null,
primary key(int_id)
);insert into mysql_table(int_id,float)VALUES(5,6);
select * from mysql_table;参考重要
2、在clickhouse中执行如下语句创建MySQL引擎与上一部重复请用此步骤
CREATE DATABASE mysql_db ENGINE MySQL(192.168.0.137:3306, test, root, 123456)3、更改mysql数据后自动同步
