安装Mysql客户端
Mysql迁移Postgresql——Mysql客户端安装
编译安装
mysql_fdw - PostgreSQL foreign data wrapper for MySQL
yum -y install git
cd /opt
git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw/
export PATH=/usr/local/pgsql/bin/:$PATH
make USE_PGXS=1
make USE_PGXS=1 install
su - postgres
psql etl
create extension mysql_fdw; --安装插件mysql_fdw
\q
使用
create server mysql_server_test foreign data wrapper mysql_fdw options (host '10.10.10.10', port '3306');
create user mapping for public server mysql_server_test options (username 'root', password '000000');
create foreign table tmp(id int,name varchar(50)) server mysql_server_test options(dbname 'etl',table_name 'tmp');
select * from tmp;
- 此时如果报如下错,一些高版本的mysql需要修改密码策略将mysql的密码策略改为mysql_native_password
Error occurred during SQL query execution
原因:
SQL 错误 [HV00N]: ERROR: failed to connect to MySQL: Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory
alter user root@'%' identified with mysql_native_password by '000000';
select * from tmp;
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/4874124958cfa1e93c55bbb630858522.png)