oracle建立mysql的dblink,连接mysql进行操作(window系统)

╰半夏微凉° 2022-01-28 15:17 1599阅读 0赞

** 微信扫一扫关注个公众号。谢谢各位**

![aHR0cHM6Ly9tcC53ZWl4aW4ucXEuY29tL21wL3FyY29kZT9zY2VuZT0xMDAwMDAwNCZzaXplPTEwMiZfX2Jpej1NelUxTVRNM056TXhPUT09Jm1pZD0xMDAwMDAxMDQmaWR4PTEmc249ZDljMmUzMjNmYzA2Y2VlNzc3OTRlM2U2MmFlNTM0NjAmc2VuZF90aW1lPTE1NjQ1Mzc4ODk][]

**1.控制面板-所有控制面板项-管理工具-ODBC 数据源**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70][]

**2.切记如果是外网服务，ip server 要填写外网地址**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70 1][]

**3.找到oracle安装目录，找到D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\NETWORK\\ADMIN目录下的listener.ora**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70 2][]

**4.然后再同目录下找到tnsnames.ora加入**

MYSQLODBC =  
  (DESCRIPTION =  
    (ADDRESS\_LIST =  
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) \#如果是外网调用，把localhost改为外网ip  
    )  
    (CONNECT\_DATA =  
      (SERVICE\_NAME = mysqlodbc)  
    )  
    (HS = OK)\#此项必须加上  
  )

**5.配置完成后找到D:\\app\\Administrator\\product\\11.2.0\\dbhome\_1\\hs\\admin目录创建initmysqlodbc.ora文件。命名规则是init+数据源名称.ora**

HS\_FDS\_CONNECT\_INFO = mysqlodbc \#注意这里的名字  
HS\_FDS\_TRACE\_LEVEL = off \#调试状态，如果连接出错，在路径

接下来执行一下命令

**6.打开cmd命令 执行lsnrctl stop此命令成功后。lsnrctl start进行开启监听**

**7.通过oracle连接工具查询当前用户是否存在创建dblink的权限**

select \* from user\_sys\_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='BMN';  
![20190524101549689.png][]

如果为空说明，需要授权

grant create public database link to BMN;

**8.然后找到oracle连接工具\#localhost===>更改为mysql所在外网的ip。用户名和密码均为mysql的**

create public database link mysqlodbc connect to "root" identified by  "123456" using  '(DESCRIPTION=(ADDRESS\_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT\_DATA=(SERVICE\_NAME=MYSQLODBC))(HS=OK))';

接下来测试是否连接成功

![20190524101721677.png][]

![20190524101750455.png][]

然后就可以爽歪歪的用了

select \* from "user"@mysqlodbc

[aHR0cHM6Ly9tcC53ZWl4aW4ucXEuY29tL21wL3FyY29kZT9zY2VuZT0xMDAwMDAwNCZzaXplPTEwMiZfX2Jpej1NelUxTVRNM056TXhPUT09Jm1pZD0xMDAwMDAxMDQmaWR4PTEmc249ZDljMmUzMjNmYzA2Y2VlNzc3OTRlM2U2MmFlNTM0NjAmc2VuZF90aW1lPTE1NjQ1Mzc4ODk]: /images/20220128/7d1742d5ac1e4aefae327a5ccfbeb0ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70]: /images/20220128/bdb5724b65b345d596209b8a13983ebd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70 1]: /images/20220128/8cf17983428c4cce9e5742768b6ae0ad.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NheWdvb2Q5OTk_size_16_color_FFFFFF_t_70 2]: /images/20220128/ca05723e72b1472aa56b0301f182bb4f.png
[20190524101549689.png]: /images/20220128/965523aa3f224de480f8e72a4342a444.png
[20190524101721677.png]: /images/20220128/4a465398a29d4000a27c16d1a8796a69.png
[20190524101750455.png]: /images/20220128/b7ceb336bdca4d35b9ee33e2e6fa13d5.png