• 1
  • 2
  • 3
  • 4
  • 5
mssql数据库问题 首 页  »  帮助中心  »  数据库  »  mssql数据库问题
mariadb使用 connect引擎连接sqlserver
发布日期:2016-4-27 22:4:35

  网上很多使用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

  http://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 http://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 http://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)