MySQL Enterprise Audit in Oracle MySQL Cloud Service

Oracle Cloud Service似乎是唯一个即使你只提交了创建RDS服务的请求,也会自动帮你创建一个虚拟主机,并且提供主机登录权限的服务提供商,也许这就是Oracle一直在宣称的IaaS+PaaS的整合。

在创建MySQL Cloud Service时,会要求提供一个SSH登录密钥,所有的Oracle云服务主机登录用户都是opc,因此在服务创建完毕,收到通知邮件之后,就可以通过以下命令登录到云主机上。

ssh -i id_rsa_oracle_cloud_enmotech opc@your_host_ip

登录到主机以后,屏幕欢迎词是个小惊喜,Oracle贴心得显示了MySQL运行状态和存储使用量的提示。
MySQL Cloud Service Host

在比较了多方的RDS之后,Oracle MySQL Cloud Service确实如一直以来宣传的那样,提供了最多的安全选件功能,如果我们用MySQL Workbench登录到数据库中,在Server Status页面可以看到:SSL连接,PAM验证,密码校验,数据库审计等多种安全功能全部是开启的。

Oracle RDS security

而与之相比,阿里云MySQL RDS提供的安全功能就少的可怜了。说少不合适,是一项安全功能都没开启。
Aliyun RDS security

Oracle对于数据库安全性的看重确实超越了大多数数据库提供商,而这也带来了一些小麻烦。

在创建MySQL云服务的时候,需要指定数据库root用户的密码,这个密码有比较强的安全要求,需要有大写、小写英文字母,有数字,同时还需要有特殊字符(比如#)。因此在后续创建Login Path时,在mysql_config_editor命令提示输入密码的时候,需要在密码前后加上双引号(比如”Passw#rd”),否则会一直出现拒绝访问的提示。

--opc用户没有办法直接登录MySQL,需要切换到oracle用户
[opc@mysql-cloud-mysql-1 ~]$ mysql -uroot -p
Please switch to "oracle" user to use mysql client
[opc@mysql-cloud-mysql-1 ~]$ sudo su - oracle
--使用密码直接登录是没有问题的
[oracle@mysql-cloud-mysql-1 ~]$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1509246
Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
--创建Login Path,仍然输入上述一样的密码
[oracle@mysql-cloud-mysql-1 ~]$ mysql_config_editor set --host=localhost --user=root --password
Enter password:
--检查确认Login Path已经创建成功
[oracle@mysql-cloud-mysql-1 ~]$ mysql_config_editor print --login-path=client
[client]
user = root
password = *****
host = localhost
--直接登录报错
[oracle@mysql-cloud-mysql-1 ~]$ mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
--在密码前后加上双引号重新创建Login Path
[oracle@mysql-cloud-mysql-1 ~]$ mysql_config_editor set --host=localhost --user=root --password
Enter password:
WARNING : 'client' path already exists and will be overwritten.
 Continue? (Press y|Y for Yes, any other key for No) : Y
--再次登录,成功
[oracle@mysql-cloud-mysql-1 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1510011
Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.17, for linux-glibc2.5 (x86_64) using  EditLine wrapper

Connection id:		1510011
Current database:
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
UNIX socket:		/tmp/mysql.sock
Uptime:			10 days 13 hours 24 min 56 sec

Threads: 13  Questions: 50733146  Slow queries: 0  Opens: 6203  Flush tables: 1  Open tables: 4808  Queries per second avg: 55.610
--------------

在进行Audit功能的检查之前,对于默认的mysql命令行提示只有mysql> 这样简陋的显示不能忍,要加上当前登录的用户和数据库名称。在oracle用户的.my.cnf下增加以下行。

[mysql]
prompt=\\u@\\h [\\d]>\\_

再次登录,mysql命令行的提示就比较顺眼了。

[oracle@mysql-cloud-mysql-1 ~]$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1510385
Server version: 5.7.17-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@localhost [(none)]> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mem                     |
| mem__advisor_text       |
| mem__advisors           |
| mem__bean_config        |
| mem__config             |
| mem__enterprise         |
| mem__events             |
| mem__instruments        |
| mem__instruments_config |
| mem__inventory          |
| mem__quan               |
| mysql                   |
| performance_schema      |
| sys                     |
| testdb                  |
+-------------------------+
16 rows in set (0.00 sec)

root@localhost [(none)]> use testdb
Database changed
root@localhost [testdb]> show tables;
Empty set (0.00 sec)

root@localhost [testdb]>

接下来进入本文的正题,在Oracle MySQL Cloud Service中默认是如何设置Audit的?
首先先检查一下audit_log的相关参数设置。

root@localhost [testdb]> show variables like 'audit_log_%';
+-----------------------------+--------------+
| Variable_name               | Value        |
+-----------------------------+--------------+
| audit_log_buffer_size       | 1048576      |
| audit_log_connection_policy | ALL          |
| audit_log_current_session   | ON           |
| audit_log_exclude_accounts  |              |
| audit_log_file              | audit.log    |
| audit_log_filter_id         | 0            |
| audit_log_flush             | OFF          |
| audit_log_format            | NEW          |
| audit_log_include_accounts  |              |
| audit_log_policy            | ALL          |
| audit_log_rotate_on_size    | 1073741824   |
| audit_log_statement_policy  | ERRORS       |
| audit_log_strategy          | ASYNCHRONOUS |
+-----------------------------+--------------+
13 rows in set (0.01 sec)

那么audit_log_file=audit.log表示仍然使用了默认的名字,到MySQL的数据文件目录中检查一下audit.log文件的存在。

[oracle@mysql-cloud-mysql-1 mysql]$ ls -l audit*
-rw-r----- 1 oracle oracle  838348830 Apr 21 13:42 audit.log
-rw-r----- 1 oracle oracle 1073742130 Apr 14 23:20 audit.log.14922120508218349.xml

这里可以看到总共的audit日志已经有1.9GB之大,在第一个audit.log达到audit_log_rotate_on_size参数设置的大小之后,自动切换成了新的audit.log。

题外话,之所以有这么巨大的audit.log,是由于启用了MySQL Enterprise Monitor,不断地记录了Monitor进程的登录和退出,为了方便后面观察audit.log,先将Monitor停止。

[oracle@mysql-cloud-mysql-1 monitor]$ pwd
/u01/bin/enterprise/monitor
[oracle@mysql-cloud-mysql-1 monitor]$ ./mysqlmonitorctl.sh stop
Stopping tomcat service . [ OK ]

接下来进行一些常规操作,并观察audit.log文件的输出内容。

1. 用root用户登录,在audit文件中显示一条Connect类型的记录。TIMESTAMP记录了时间,USER标签记录了登录的用户,HOST标签记录了登录的机器,COMMAND_CLASS为connect。

NewImage

2. 尝试直接CTAS一张测试表,在enforce_gtid_consistency=ON时会报1786错误,这是GTID特性决定的。

root@localhost [testdb]> create table mytables as select * from information_schema.tables;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.

在audit.log中也记录下了这次报错的操作,STATUS标签为错误代码1786,STATUS_CODE标签值为1表示这条SQL没有成功执行,SQLTEXT标签记录了整个SQL语句。

NewImage

3. 下面的三条语句,select,create table,insert均没有在audit.log中记录下来。

root@localhost [testdb]> select count(*) from information_schema.tables;
+----------+
| count(*) |
+----------+
|      591 |
+----------+
1 row in set (0.01 sec)

root@localhost [testdb]> create table mytables like information_schema.tables;
Query OK, 0 rows affected (0.10 sec)

root@localhost [testdb]> insert into mytables select * from information_schema.tables;
Query OK, 592 rows affected (1.94 sec)
Records: 592  Duplicates: 0  Warnings: 0

这样的行为是由audit_log_statement_policy=ERRORS参数决定的,默认只记录报错的SQL,而不会记录所有的执行语句。

4. 将audit_log_statement_policy参数修改为ALL,再执行同样的select语句。

root@localhost [testdb]> SET GLOBAL audit_log_statement_policy = ALL;
Query OK, 0 rows affected (0.00 sec)

root@localhost [testdb]> select count(*) from mytables;
+----------+
| count(*) |
+----------+
|      592 |
+----------+
1 row in set (0.00 sec)

可以发现audit.log中已经有记录了。

NewImage

结论:在Oracle MySQL Cloud Service中默认会启动MySQL Enterprise Audit组件,并且设置即记录用户登录又记录执行语句(audit_log_policy=ALL),用户登录情况则不管登录成功还是失败每次都记录(audit_log_connection_policy=ALL),而执行语句则只记录执行失败的语句(audit_log_statement_policy=ERRORS)。

How to Set MySQL Group Replication into Multi-Primary Mode

在MySQL 5.7.17版本中发布的MySQL Group Replication(后文简称为MGR)被很多人称为MySQL复制方案的正规军,可以一举取代现在的MySQL Replication,Semisynchronous replication,甚至是可以取代之前最成功的MySQL集群方案Galera。

MGR有两种模式,一种是Single-Primary,一种是Multi-Primary,单主或者多主。

在前一种模式Single-Primary中,无论集群中有多少个节点,只有一个节点允许写入,其它节点都是只读的,这个允许写入的节点被称为主节点,只有当这个主节点出现问题从集群中被踢出,才会在剩余的节点中选举出另外一个节点成为新的主节点,并且将该节点置为可写模式。

这个过程可以通过log清晰地看到。

2017-03-16T02:04:32.689278Z 0 [Note] Plugin group_replication reported: 'getstart group_id 4317e324'
2017-03-16T02:04:33.081743Z 0 [Note] Plugin group_replication reported: 'Unsetting super_read_only.'
2017-03-16T02:04:33.081756Z 28 [Note] Plugin group_replication reported: 'A new primary was elected, enabled conflict detection until the new primary applies all relay logs'

而在后一种模式Multi-Primary中,所有的节点都是主节点,都可以同时被读写,看上去这似乎更好,但是因为多主的复杂性,在功能上如果设置了多主模式,则会有一些使用的限制,比如不支持Foreign Keys with Cascading Constraints。

在多主模式下,集群中的节点退出集群,也不再会出现重新选举的动作,因为本来所有的节点都是Primary节点。

前面这些并不是本文的重点,实际上在5.7.17的官方文档中有详细地描述如何设置Single-Primary MGR的方法。
Deploying Group Replication in Single-Primary Mode

但是不确认是什么原因,却没有单独的章节来描述如何设置集群为Multi-Primary模式。只是在最后语焉不详地提及了一句:Multi-primary mode groups (members all configured with group_replication_single_primary_mode=OFF) 让读者可以知道跟group_replication_single_primary_mode参数有关。虽然确实也就是跟这个参数有关,但是文档写的这样半半拉拉也确实值得吐槽。

以下为设置Multi-Primary MGR的方法。假设集群之前已经处于Single-Primary模式。

--group_replication_single_primary_mode=ON,表示启动了Single-Primary模式,那么修改为OFF就意味着要启动Multi-Primary模式。
(root@localhost) [(none)]> show variables like 'group_replication_single_primary_mode';
+----------------------------------------------------+-------------------------------------------------+
| Variable_name                                      | Value                                           |
+----------------------------------------------------+-------------------------------------------------+
| group_replication_single_primary_mode              | ON                                              |
+----------------------------------------------------+-------------------------------------------------+

--如果MGR已经启动,则无法动态修改该参数
(root@localhost) [(none)]> set global group_replication_single_primary_mode=off;
ERROR 3093 (HY000): Cannot change into or from single primary mode while Group Replication is running.

--停止复制
(root@localhost) [(none)]> stop GROUP_REPLICATION;
Query OK, 0 rows affected (8.67 sec)

--设置单主模式参数为off
(root@localhost) [(none)]> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)

--该参数设置为ON,则禁用了在多主模式下一些可能产生未知数据冲突的操作
(root@localhost) [(none)]> set global group_replication_enforce_update_everywhere_checks=ON;
Query OK, 0 rows affected (0.00 sec)

--设置为第一个准备启动MGR(bootstrap)的节点
(root@localhost) [(none)]> SET GLOBAL group_replication_bootstrap_group=ON;
Query OK, 0 rows affected (0.00 sec)

--启动复制
(root@localhost) [(none)]> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.29 sec)

--为了防止后续由于意外再启动另外一个复制组,关闭bootstrap参数
(root@localhost) [(none)]> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

--此时可以从视图中看到整个集群只有一个节点是ONLINE
(root@localhost) [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 72ad2062-08a3-11e7-a513-5bfce171938d | bogon       |       24801 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

可以加入第二个节点了。

--同样设置单主模式参数为off
(root@localhost) [(none)]> set global group_replication_single_primary_mode=off;
Query OK, 0 rows affected (0.00 sec)

--设置update检查参数为on
(root@localhost) [(none)]> set global group_replication_enforce_update_everywhere_checks=ON;
Query OK, 0 rows affected (0.00 sec)

--启动复制
(root@localhost) [(none)]> start group_replication;
Query OK, 0 rows affected (5.42 sec)

--此时检查视图,可以发现集群中已经存在两个节点
(root@localhost) [(none)]> SELECT * FROM performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 9003e830-08a3-11e7-8ae3-e62d2f6366d2 | bogon       |       24802 | ONLINE       |
| group_replication_applier | 96fe2b5a-08a3-11e7-b383-d7f02f17e847 | localhost   |       24803 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

--group_replication_primary_member值为空,表示启动的是Multi-Primary Mode,否则该参数显示的是单主模式中的Primary节点
(root@localhost) [(none)]> SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME='group_replication_primary_member';
+----------------------------------+----------------+
| VARIABLE_NAME                    | VARIABLE_VALUE |
+----------------------------------+----------------+
| group_replication_primary_member |                |
+----------------------------------+----------------+
1 row in set (0.00 sec)

同样的方法可以加入第三个节点,在当前版本中MGR最多支持一个集群中拥有9个节点。

如果需要在MySQL重启之后这些参数仍然生效,那么需要将这些参数加入到my.cnf文件中,一个典型的配置了MGR的my.cnf如下所示。

[mysqld]

# server configuration
datadir=/Users/Kamus/mysql_data/s2
basedir=/usr/local/Cellar/mysql/5.7.17

port=24802
socket=/tmp/s2.sock

#
# Replication configuration parameters
#
server_id=2
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW

#
# Group Replication configuration
#
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address= "127.0.0.1:24902"
loose-group_replication_group_seeds= "127.0.0.1:24901,127.0.0.1:24902,127.0.0.1:24903"
loose-group_replication_bootstrap_group= off

#
## Group Replication configuration multi-primary mode
##
# loose-group_replication_single_primary_mode=off
# loose-group_replication_enforce_update_everywhere_checks=ON

Public preview of the next release of SQL Server on Linux

SQL Server for Linux

当微软宣布即将发布SQL Server for Linux版本的时候,有些人觉得很兴奋,有些人觉得然并卵,但是既然Gartner在2016年的数据库管理系统魔力象限图中将微软列在了第一位,超过了一直以来的霸主Oracle,那么无论如何这个SQL Server for Linux版本的发布都是值得关注的,微软将这个版本称为SQL Server vNext on Linux。vNext,好直白的期望。

Gartner-OP-DBMS-MQ-2016

Gartner-OP-DBMS-MQ-2016

微软在2016年11月中旬正式发布了SQL Server for Linux的第一个公众预览版,这条产品线将支持所有的企业级Linux平台,在第一个预览版中支持Red Hat Enterprise Linux 7.2和Ubuntu Linux 16.04,并且支持在macOS和Linux中的Docker容器,后续还会支持Suse Linux Enterprise Server,另外,微软承诺Linux上的SQL Server绝对不会是“SQL Server Lite”这样的阉割版数据库,而会是一个具备SQL Server 2016完整功能集的真正的企业级数据库,比如 in-memory OLTP,还有always-on encryption和row-level security这样的企业级安全功能。在现在的预览版中以下这些功能还不支持,但是后续会逐渐支持。

SQL Server Unsupported features and services

本文中会对SQL Server for Linux的安装、配置、使用做简单的测试,说实话,如果Oracle数据库的安装使用也能这样简单就太好了。

本文使用的测试环境是AWS的一个EC2实例,Red Hat Enterprise Linux 7.2,整个安装过程,从开始下载一直到数据库启动结束,不超过20分钟,这其中还包括了下载RPM包的15分钟。

第一步:用root用户下载安装镜像库的repo文件

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

第二步:通过yum安装

sudo yum install -y mssql-server

第三步:设置数据库并启动
Update@2017-05-11
与之前不同,设置数据库的命令整合到mssql-conf命令中,而不再有sqlservr-setup命令。

[root@opreative-server-3 ~]# /opt/mssql/bin/mssql-conf setup
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746388
and found in /usr/share/doc/mssql-server/LICENSE.TXT.

Do you accept the license terms? [Yes/No]:Y
Setting up Microsoft SQL Server
Enter the new SQL Server system administrator password:
Confirm the new SQL Server system administrator password:
Starting Microsoft SQL Server...
Enabling Microsoft SQL Server to run at boot...
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup completed successfully.

[11:24:34:794][ec2-user@ip-172-31-11-228 ~]$ sudo /opt/mssql/bin/sqlservr-setup
[11:24:34:876]Microsoft(R) SQL Server(R) Setup
[11:24:34:876]
[11:24:34:876]You can abort setup at anytime by pressing Ctrl-C. Start this program
[11:24:34:876]with the –help option for information about running it in unattended
[11:24:34:876]mode.
[11:24:34:876]
[11:24:34:876]The license terms for this product can be downloaded from
[11:24:34:877]http://go.microsoft.com/fwlink/?LinkId=746388 and found
[11:24:34:877]in /usr/share/doc/mssql-server/LICENSE.TXT.
[11:24:34:880]
[11:24:45:937]Do you accept the license terms? If so, please type “YES”: YES
[11:24:45:938]
[11:24:55:008]Please enter a password for the system administrator (SA) account:
[11:24:59:810]Please confirm the password for the system administrator (SA) account:
[11:24:59:810]
[11:24:59:811]Setting system administrator (SA) account password…
[11:25:03:006]
[11:25:07:728]Do you wish to start the SQL Server service now? [y/n]: y
[11:25:11:927]Do you wish to enable SQL Server to start on boot? [y/n]: y
[11:25:12:022]Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
[11:25:12:058]Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server-telemetry.service to /usr/lib/systemd/system/mssql-server-telemetry.service.
[11:25:12:087]
[11:25:12:088]Setup completed successfully.

That’s it! SQL Server for Linux数据库就安装完毕并正常启动了。

可以通过systemctl来检查mssql-server的服务的启动状态。

[root@opreative-server-3 ~]# systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
   Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
   Active: active (running) since Thu 2017-05-11 17:00:17 CST; 34s ago
     Docs: https://docs.microsoft.com/en-us/sql/linux
 Main PID: 16147 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─16147 /opt/mssql/bin/sqlservr
           └─16166 /opt/mssql/bin/sqlservr

May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.52 spid18s     Server is listening on [ 0.0.0.0  1433].
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 Server      Server is listening on [ 127.0.0.1  1434].
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 Server      Dedicated admin connection support was established for listening locally on port 1434.
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.53 spid18s     SQL Server is now ready for client connections. This is an informational message... required.
May 11 17:00:20 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:20.86 spid11s     Starting up database 'tempdb'.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid11s     The tempdb database has 1 data file(s).
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid22s     The Service Broker endpoint is in disabled or stopped state.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.03 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.05 spid22s     Service Broker manager has started.
May 11 17:00:21 opreative-server-3 sqlservr[16147]: 2017-05-11 17:00:21.08 spid7s      Recovery is complete. This is an informational message only. No user action is required.
Hint: Some lines were ellipsized, use -l to show in full.

[ec2-user@ip-172-31-11-228 ~]$ systemctl status mssql-server
● mssql-server.service – Microsoft(R) SQL Server(R) Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2016-12-04 22:25:08 EST; 1min 59s ago
Main PID: 16558 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─16558 /opt/mssql/bin/sqlservr
└─16571 /opt/mssql/bin/sqlservr

安装完毕以后可以通过自带的mssql-conf命令进行一些简单的配置,可配置项不多,主要是监听端口,默认的数据文件所在目录,日志所在目录,备份所在目录等。

[ec2-user@ip-172-31-11-228 ~]$ sudo /opt/mssql/bin/mssql-conf list
Supported settings using the 'set' option: 
tcpport
defaultbackupdir
defaultdumpdir
defaultlogdir
defaultdatadir
For all command line options use -h or --help

如果希望在服务器上直接通过sqlcmd命令行登入数据库,还需要额外安装一个mssql-tools的RPM包,这个安装包里包括sqlcmd和bcp(Bulk import-export utility)。

第一步:用root用户下载安装mssql-tools的repo文件

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo

第二步:通过yum安装

sudo yum install -y mssql-tools

然后就可以通过sqlcmd来登入数据库,并使用Transact-SQL (T-SQL)语言来进行各种操作和管理了。我并非专业的SQL Server DBA,只是浅尝辄止而已。登录以后,创建了一个新数据库testdb,然后进入testdb,创建了一张新表inventory,然后在inventory表中插入了2条记录。

[ec2-user@ip-172-31-11-228 ~]$ sqlcmd -S localhost -U SA -P 'PASSWORD OF SA'
1> SELECT Name from sys.Databases;
2> GO
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            

(4 rows affected)
1> create database testdb;
2> go
1> SELECT Name from sys.Databases;
2> go
Name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------
master                                                                                                                          
tempdb                                                                                                                          
model                                                                                                                           
msdb                                                                                                                            
testdb                                                                                                                          

(5 rows affected)
1> use testdb
2> go
Changed database context to 'testdb'.
1> CREATE TABLE inventory (id INT, name NVARCHAR(50), quantity INT);
2> GO
1> INSERT INTO inventory VALUES (1, 'banana', 150);
2> INSERT INTO inventory VALUES (2, 'orange', 154);
3> GO

(1 rows affected)

(1 rows affected)
1> SELECT * FROM inventory WHERE quantity > 152;
2> GO
id          name                                               quantity   
----------- -------------------------------------------------- -----------
          2 orange                                                     154

(1 rows affected)

除了sqlcmd命令行之外,在Windows操作系统下有全套的SQL Server Management Studio (SSMS) ,可以直接使用这个具有丰富功能的图形化管理工具来直接管理SQL Server vNext on Linux。多说一句,由于是使用1433端口连接服务器上的数据库,因此需要在AWS EC2中将该服务器实例所属的Security Group中的Inbound策略中开放TCP 1433端口。

SQL Server Management Studio

【结论】SQL Server vNext on Linux预览版在安装、操作过程中非常顺畅,在后续有更多功能加入以后,应该是企业级数据库非常优秀的选择。先不说是否会有Oracle用户迁移到SQL Server上,这至少给了现在正在使用SQL Server的客户们更广阔的选择空间,现在他们运行在Windows Server上的SQL Server数据库也可以移植到Linux中了,无论如何,这是一个很大的进步,微软这步要是能早一些迈出可能就更好了。