mariadbconnect引擎连接sqlserver
- 作者: 较劲_麻黄素
- 来源: 51数据库
- 2021-10-11
网上很多使用mariadb的connect引擎连接oracle，唯独没有mariadb使用connect引擎连接sqlserver,特小记一篇.
---1.使用最新版connect
# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 10.1.10-MariaDB-log Source distribution
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#rpm2cpio MariaDB-10.1.10-centos6-x86_64-connect-engine.rpm | cpio --extract --make-directories
#cp /soft/usr/lib64/mysql/plugin/ha_connect.so /data/mysql/lib/plugin/
root@node01 13:30:33>INSTALL SONAME 'ha_connect';
Query OK, 0 rows affected (0.00 sec)
# ls /data/mysql/lib/plugin/
adt_null.so dialog.so ha_federatedx.so metadata_lock_info.so semisync_master.so
auth_0x0100.so example_key_management.so ha_innodb.so mypluglib.so semisync_slave.so
auth_pam.so file_key_management.so ha_mroonga.so mysql_clear_password.so server_audit.so
auth_socket.so ha_archive.so handlersocket.so qa_auth_client.so simple_password_check.so
auth_test_plugin.so ha_blackhole.so ha_spider.so qa_auth_interface.so sql_errlog.so
daemon_example.ini ha_connect.so ha_test_sql_discovery.so qa_auth_server.so wsrep_info.so
debug_key_management.so ha_example.so libdaemon_example.so query_cache_info.so
dialog_examples.so ha_federated.so locales.so query_response_time.so
--权限重要
#chown mysql:mysql -R /data/mysql
root@node01 15:18:54>show engines \G;
*************************** 1. row ***************************
Engine: MRG_MyISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: CONNECT
Support: YES
Comment: Management of External Data (SQL/MED), including many file formats
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: SEQUENCE
Support: YES
Comment: Generated tables filled with sequential values
Transactions: YES
XA: NO
Savepoints: YES
*************************** 7. row ***************************
Engine: SPHINX
Support: YES
Comment: Sphinx storage engine 2.2.6-release
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Percona-XtraDB, Supports transactions, row-level locking, foreign keys and encryption for tables
Transactions: YES
XA: YES
Savepoints: YES
*************************** 9. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 10. row ***************************
Engine: Aria
Support: YES
Comment: Crash-safe tables with MyISAM heritage
Transactions: NO
XA: NO
Savepoints: NO
10 rows in set (0.00 sec)
--2.自动安装unixODBC 2.3.0:(网络通畅的情况下)
[root@node01 msodbcsql-11.0.2270.0]# ./build_dm.sh
Build unixODBC 2.3.0 DriverManager script
Copyright Microsoft Corp.
In order to use the Microsoft ODBC Driver 11 for SQL Server on Linux,
the unixODBC DriverManager must be installed on your computer. unixODBC
DriverManager is a third-party tool made available by the unixODBC Project.
To assist you in the installation process, this script will attempt to
download, properly configure, and build the unixODBC DriverManager from
https://www.unixodbc.org/ for use with the Microsoft ODBC Driver 11
for SQL Server ODBC Driver on Linux.
Alternatively, you can choose to download and configure unixODBC
DriverManager from https://www.unixodbc.org/ yourself.
Note: unixODBC DriverManager is licensed to you under the terms of an
agreement between you and the unixODBC Project, not Microsoft. Microsoft
does not guarantee the unixODBC DriverManager or grant any rights to
you. Prior to downloading, you should review the license for unixODBC
DriverManager at https://www.unixodbc.org/.
The script is provided as a convenience to you as-is, without any express
or implied warranties of any kind. Microsoft is not liable for any issues
arising out of your use of the script.
Enter 'YES' to have this script continue: YES
Verifying processor and operating system ................................... OK
Verifying wget is installed ................................................ OK
Verifying tar is installed ................................................. OK
Verifying make is installed ................................................ OK
Downloading unixODBC 2.3.0 DriverManager
--手动安装unixODBC 2.3.0:(网络不好的情况下 )--推荐
tar xvzf unixODBC-2.3.1.tar.gz.
CPPFLAGS="-DSIZEOF_LONG_INT=8"
export CPPFLAGS
./configure --prefix=/usr --libdir=/usr/lib64 --sysconfdir=/etc --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE
make
make install
touch /etc/odbcinst.ini
touch /etc/odbc.ini
---3.安装微软odbc驱动
[root@node01 soft]# ls
msodbcsql-11.0.2270.0.tar.gz
[root@node01 soft]# tar msodbcsql-11.0.2270.0.tar.gz
tar: Old option `b' requires an argument.
Try `tar --help' or `tar --usage' for more information.
[root@node01 soft]# tar xvzf msodbcsql-11.0.2270.0.tar.gz
msodbcsql-11.0.2270.0/
msodbcsql-11.0.2270.0/include/
msodbcsql-11.0.2270.0/include/msodbcsql.h
msodbcsql-11.0.2270.0/bin/
msodbcsql-11.0.2270.0/bin/SQLCMD.rll
msodbcsql-11.0.2270.0/bin/BatchParserGrammar.dfa
msodbcsql-11.0.2270.0/bin/BatchParserGrammar.llr
msodbcsql-11.0.2270.0/bin/bcp.rll
msodbcsql-11.0.2270.0/bin/bcp-11.0.2270.0
msodbcsql-11.0.2270.0/bin/sqlcmd-11.0.2270.0
msodbcsql-11.0.2270.0/WARNING
msodbcsql-11.0.2270.0/build_dm.sh
msodbcsql-11.0.2270.0/lib64/
msodbcsql-11.0.2270.0/lib64/msodbcsqlr11.rll
msodbcsql-11.0.2270.0/lib64/libmsodbcsql-11.0.so.2270.0
msodbcsql-11.0.2270.0/install.sh
msodbcsql-11.0.2270.0/LICENSE
msodbcsql-11.0.2270.0/README
msodbcsql-11.0.2270.0/docs/
msodbcsql-11.0.2270.0/docs/en_US.tar.gz
[root@node01 soft]# ls
msodbcsql-11.0.2270.0 msodbcsql-11.0.2270.0.tar.gz
[root@node01 soft]# cd msodbcsql-11.0.2270.0
[root@node01 msodbcsql-11.0.2270.0]# ls
bin build_dm.sh docs include install.sh lib64 LICENSE README WARNING
# cd msodbcsql-11.0.2270.0
# ls
bin build_dm.sh docs include install.sh lib64 LICENSE README unixODBC-2.3.1.tar.gz WARNING
./install.sh verify
./install.sh install
--4.安装完成测试测试
# odbcinst -q -d -n "ODBC Driver 11 for SQL Server"
[ODBC Driver 11 for SQL Server]
Description=Microsoft ODBC Driver 11 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-11.0.so.2270.0
Threading=1
UsageCount=1
Trace=Yes
TraceFile=/var/log/odbc.log
# odbcinst -j
unixODBC 2.3.0
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
# sqlcmd -S 192.168.1.143 -U sa -P sa01 -d master -Q 'select "Hello World"'
-----------
Hello World
# sqlcmd -S 192.168.1.143 -U sa -P sa01 -d master -Q 'select name from sys.databases'
name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
test
DebugMonitor
InternalDebugMonitor
JinriMonitor
#vi /etc/template.ini
[dsn01]
Description = linux to sqlserver
Driver = ODBC Driver 11 for SQL Server
Server = 192.168.1.143
Port = 1433
UID = sa
PWD = sa01
Database = master
Trace = Yes
TraceFile = /var/log/odbc.log
#odbcinst -i -s -l -f /etc/template.ini
# isql -v dsn01 sa sa01
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select name from sys.databases;
+---------------------------------------------------------------------------------------------------------------------------------+
| name |
+---------------------------------------------------------------------------------------------------------------------------------+
| master |
| tempdb |
| model |
| msdb |
| test |
| DebugMonitor |
| InternalDebugMonitor |
| JinriMonitor |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
8 rows fetched
# sudo -u mysql isql -v dsn01 sa sa01
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
---5.新建connect引擎的表
create table t01
engine=CONNECT
table_type=ODBC
block_size=10
tabname='t01'
connection='DSN=dsn01; UID=sa; PWD=sa01';
root@node01 18:30:56>create table t01
-> engine=CONNECT
-> table_type=ODBC
-> block_size=10
-> tabname='t01'
-> connection='DSN=dsn01; UID=sa; PWD=sa01';
Query OK, 0 rows affected (0.26 sec)
root@node01 18:30:58>select * from t01;
+------+---------+
| sid | sname |
+------+---------+
| 101 | ocpyang |
+------+---------+
1 row in set (0.04 sec)
---6.验证
--mysql端插入数据
root@node01 18:37:00>insert into t01 values(102,'jyl');
Query OK, 1 row affected (0.48 sec)
root@node01 18:37:12>commit;
Query OK, 0 rows affected (0.00 sec)
root@node01 18:37:14>select * from t01;
+------+---------+
| sid | sname |
+------+---------+
| 101 | ocpyang |
| 102 | jyl |
+------+---------+
2 rows in set (0.02 sec)
推荐阅读
- SQLite数据库操作：原生操作，GreenDao操作讲解
- 数据库SQL实战题：获取员工其当前的薪水比其manager当前薪水还高的相关信息（教程）
- SQLSERVER查询区分大小写的写法分析
- SQL学习总结之SQL的分类介绍
- 阶梯到高级T-SQL 1级：高级T-SQL介绍交叉连接
- 高级T-SQL级别1的Stairway:使用CROSS JOIN引入高级T-SQL分析
- 什么是SQL隔离级别？四个SQL隔离级别定义介绍
- Sql递归介绍之用with实现递归查询
- SQLSERVER查询时日期格式化的实例讲解
- 数据库SQL实战：从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略（题解）
热点文章
SQLite数据库操作：原生操作，GreenDao操作讲解
18
数据库SQL实战题：获取员工其当前的薪水比其manager当前薪水还高的相关信息（教程）
4
SQLSERVER查询区分大小写的写法分析
36
SQL学习总结之SQL的分类介绍
6
阶梯到高级T-SQL 1级：高级T-SQL介绍交叉连接
4
高级T-SQL级别1的Stairway:使用CROSS JOIN引入高级T-SQL分析
4
什么是SQL隔离级别？四个SQL隔离级别定义介绍
2
Sql递归介绍之用with实现递归查询
6
SQLSERVER查询时日期格式化的实例讲解
4
数据库SQL实战：从titles表获取按照title进行分组，注意对于重复的emp_no进行忽略（题解）
7
