网上很多使用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)
mariadbconnect引擎连接sqlserver
•
生活家
赞 (0)
八字看婚配都看什么，算八字,看婚配
上一篇
2022年3月21日
两个人婚姻八字合不合，我想知道两个人生辰八字合不合？如果结婚以后婚姻如
下一篇
2022年3月21日