Ansible入门学习

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:ANSIBLE入门学习
当我们的基础设备和环境变得越来越多的时候,我们需要一个工具来帮助我们管理服务器,无论是物理机还是虚拟机。Ansible这个工具可以帮助我们进行配置、管理和大规模进行部署。而且Ansible是基于ssh来执行的,不需要在其他服务器上安装agent类的插件。现在就开始来入门。
当前我的系统是Ubuntu 16.04.4 LTS,将作为ansible的管理机器。使用virtualbox的网卡,IP地址是192.168.56.1。而我的虚拟机使用的是centos系统,IP地址是192.168.56.91。这个虚拟机将要是被我Ansible管理的机器。
第一步,我们需要安装ansible,我这里直接使用

postgres@postgres-N65S01:~$ sudo apt-get install ansible
[sudo] password for postgres: 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following additional packages will be installed:
  ieee-data python-crypto python-ecdsa python-httplib2 python-jinja2 python-markupsafe python-netaddr python-paramiko python-pkg-resources python-selinux python-six python-yaml
Suggested packages:
  sshpass python-crypto-dbg python-crypto-doc python-jinja2-doc ipython python-netaddr-docs python-setuptools
The following NEW packages will be installed:
  ansible ieee-data python-crypto python-ecdsa python-httplib2 python-jinja2 python-markupsafe python-netaddr python-paramiko python-pkg-resources python-selinux python-six python-yaml
0 upgraded, 13 newly installed, 0 to remove and 95 not upgraded.
Need to get 2,967 kB of archives.
After this operation, 17.9 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

安装完成之后,通过查看一下版本命令看看是否正常运行。

postgres@postgres-N65S01:~$ ansible --version
ansible 2.0.0.2
  config file = /etc/ansible/ansible.cfg
  configured module search path = Default w/o overrides

在开始使用Ansible之前(因为我们不想使用root),所以两个主机都会创建一个组并创建一个用户。同时还要生成密钥。生成密钥非常重要。因为我们将使用无密码ssh身份验证从控制主机到托管主机进行通信。

postgres@postgres-N65S01:~$ sudo groupadd ansible
postgres@postgres-N65S01:~$ sudo useradd -g ansible ansible
postgres@postgres-N65S01:~$ sudo passwd ansible
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
postgres@postgres-N65S01:/home$ su - ansible
Password: 
$ ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/postgres/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/postgres/.ssh/id_rsa.
Your public key has been saved in /home/postgres/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:z0mFh7iuQig7vGlg55GA8i9LyysG9X32fiR41+cdvEQ postgres@postgres-N65S01
The key's randomart image is:
+---[RSA 2048]----+
|                 |
|         . o     |
|.       . o o    |
|o..      . o   E |
|.o.o..  S..  .o  |
|+.o+o ..++o.o .+.|
|=o+o.  o.o++  .o+|
|+*ooo  .  . .  .o|
|o=*+ ..  ...     |
+----[SHA256]-----+

为了不需要密码来控制,我们需要将ssh公钥文件从控制主机复制到托管主机:

$ ssh-copy-id -i .ssh/id_rsa.pub ansible@192.168.56.91
/usr/bin/ssh-copy-id: INFO: Source of key(s) to be installed: ".ssh/id_rsa.pub"
The authenticity of host '192.168.56.91 (192.168.56.91)' can't be established.
ECDSA key fingerprint is SHA256:/+Re8LQTEBXAvC2rNaTpKiuO5vAL+4yBZvRa3soV0zs.
Are you sure you want to continue connecting (yes/no)? yes
/usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed
/usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys
ansible@192.168.56.91's password: 

Number of key(s) added: 1

Now try logging into the machine, with:   "ssh 'ansible@192.168.56.91'"
and check to make sure that only the key(s) you wanted were added.

当然我们还需要添加主机设置,这样控制机才知道它应管理哪些主机。因为我们使用ansible用户单独管理,而不是root,这里把权限修改一下。

postgres@postgres-N65S01:/etc$ ls -l ansible/*
-rw-r--r-- 1 root root 10301 1月  15  2016 ansible/ansible.cfg
-rw-r--r-- 1 root root   982 8月  21 23:13 ansible/hosts
postgres@postgres-N65S01:/etc$ sudo chown -R ansible:ansible /etc/ansible/*
postgres@postgres-N65S01:/etc$ su - ansible
Password: 
$ cat /etc/ansible/hosts
# This is the default ansible 'hosts' file.
#
# It should live in /etc/ansible/hosts
#
#   - Comments begin with the '#' character
#   - Blank lines are ignored
#   - Groups of hosts are delimited by [header] elements
#   - You can enter hostnames or ip addresses
#   - A hostname/ip can be a member of multiple groups

# Ex 1: Ungrouped hosts, specify before any group headers.

#green.example.com
#blue.example.com
#192.168.100.1
#192.168.100.10

# Ex 2: A collection of hosts belonging to the 'webservers' group

#[webservers]
#alpha.example.org
#beta.example.org
#192.168.1.100
#192.168.1.110

# If you have multiple hosts following a pattern you can specify
# them like this:

#www[001:006].example.com

# Ex 3: A collection of database servers in the 'dbservers' group

#[dbservers]
#
#db01.intranet.mydomain.net
#db02.intranet.mydomain.net
#10.25.1.56
#10.25.1.57

# Here's another example of host ranges, this time there are no
# leading 0s:

#db-[99:101]-node.example.com

这里给出了一系列的示例,我们这里设置如下,括号中的名称是所谓的组名。这意味着在Ansible命令中引用“pg-servers”,会将组名解析为为服务器地址。让我们做一个基本测试。

[pg-servers]
192.168.56.91

$ ansible pg-servers -a "/bin/echo I love ansible"
192.168.56.91 | SUCCESS | rc=0 >>
I love ansible

$ ansible pg-servers -a "/bin/mkdir -p aaa"
192.168.56.91 | SUCCESS | rc=0 >>

postgres@postgres-N65S01:/etc$ ssh ansible@192.168.56.91
ansible@192.168.56.91's password: 
Last failed login: Wed Oct 24 23:41:56 CST 2018 from 192.168.56.1 on ssh:notty
There was 1 failed login attempt since the last successful login.
Last login: Wed Oct 24 23:41:45 2018 from 192.168.56.1
[ansible@db ~]$ ls -lrt
total 0
drwxrwxr-x. 2 ansible ansible 6 10月 24 23:41 aaa

可以看到ansible执行了我们相要的操作。非常酷。

PostgreSQL创建表和索引,磁盘会发生什么情况?

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:PostgreSQL创建表和索引,磁盘会发生什么情况?
当我们在PostgreSQL中创建一个表或一个索引时,磁盘上的文件是怎么创建的,我们来研究一下?

postgres=# select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit
(1 row)

首先我们来创建一个表。接下来我们使用pg_relation_filepath函数,获取到该表对应的文件名。

postgres=# create table t1(name varchar(30));
CREATE TABLE
postgres=# select pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/13806/16384
(1 row)

或者我们也可以使用oid2name程序,指定数据库名字和表名即可。

-bash-4.2$ ./oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13806       postgres  pg_default
  13805      template0  pg_default
      1      template1  pg_default

-bash-4.2$ ./oid2name -d postgres -t t1
From database "postgres":
  Filenode  Table Name
----------------------
     16384          t1

此时我们可以查看该文件.

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384
-rw-------. 1 postgres postgres 0 Oct 24 11:18 /var/lib/pgsql/10/data/base/13806/16384

可以看到文件字节大小是0,此时并没有任何数据,没有以“_fsm”后缀的Free Space Mapping文件和以”vm”为后缀的visibility map文件。

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres 0 Oct 24 11:18 /var/lib/pgsql/10/data/base/13806/16384

接下来我们试下在这个表上创建索引会发生什么情况。

postgres=# create index idx1 on t1 (name);
CREATE INDEX
postgres=# select pg_relation_filepath('idx1');
 pg_relation_filepath 
----------------------
 base/13806/16387
(1 row)

此时可以发现创建的文件是有大小的。可以看到这个paag是一个元数据的page。

-bash-4.2$ ls -lrt $PGDATA/base/13806/16387*
-rw-------. 1 postgres postgres 8192 Oct 24 15:38 /var/lib/pgsql/10/data/base/13806/16387

我们可以通过创建扩展包pageinspect工具来研究下page(物理文件的单位,默认大小为8K)。

postgres=# create extension pageinspect;
CREATE EXTENSION
postgres=# SELECT * FROM bt_metap('idx1');
 magic  | version | root | level | fastroot | fastlevel 
--------+---------+------+-------+----------+-----------
 340322 |       2 |    0 |     0 |        0 |         0
(1 row)

postgres=# SELECT * FROM bt_page_stats('idx1',0);
ERROR:  block 0 is a meta page

如果我们在插入一条数据看看,可以看到文件有了大小。用strings可以看到里面的内容。

postgres=# insert into t1 values('buddy');
INSERT 0 1

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres 8192 Oct 24 15:53 /var/lib/pgsql/10/data/base/13806/16384

-bash-4.2$ strings /var/lib/pgsql/10/data/base/13806/16384
buddy

此时还是没有Free Space Mapping文件和visibility map文件。需要vacuum一下。此时文件的大小变成了8k。

postgres=# vacuum t1;
VACUUM

-bash-4.2$ ls -lrt $PGDATA/base/13806/16384*
-rw-------. 1 postgres postgres  8192 Oct 24 15:54 /var/lib/pgsql/10/data/base/13806/16384
-rw-------. 1 postgres postgres  8192 Oct 24 15:57 /var/lib/pgsql/10/data/base/13806/16384_vm
-rw-------. 1 postgres postgres 24576 Oct 24 15:57 /var/lib/pgsql/10/data/base/13806/16384_fsm

oracle18c新特性-用户NO AUTHENTICATION认证方式和Proxy user连接

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:oracle18c新特性-用户NO AUTHENTICATION认证方式和Proxy user连接

使用Oracle18.3,我们可以在没有密码的情况下创建用户。实际上出于安全原因,没有人可以直接连接到应用程序的架构是一个非常好的功能。一个很好方法是使用代理连接,实际上连接为t1用户但使用t2用户的密码,。下面我们就来研究测试一下。
创建T1用户,赋予create session,resource权限;

create user t1 identified by t1;
grant create session,resource to t1;

创建T2用户,只赋予create session权限;

create user t2 identified by t2;
grant create session to t2;

赋予允许proxy连接的权限.

alter user t1 grant connect through t2;

使用代理连接的方式如下,这样就不用输入t1用户的密码,就连上了t1用户

Usage:  ::= [][/][@]

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.
SQL> select sys_context('USERENV','SESSION_USER') as session_user,sys_context('USERENV','SESSION_SCHEMA') as session_schema,sys_context('USERENV','PROXY_USER') as proxy from dual;

SESSION_USER      SESSION_SCHEMA      PROXY
----------------- ------------------- ------------
T1                T1                  T2

当我们把T1账号锁定一下,会发现使用代理的连接会被锁定。

SQL> alter user t1 account lock;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
ERROR:
ORA-28000: The account is locked.

搞明白代理用户的连接方式,接下来我们来研究NO AUTHENTICATION认证。这里先把t1和t2用户删除。

以下是18c的新特性。使用NO AUTHENTICATION,可以创建一个没有密码的用户。也没有办法登陆这个用户。

NO AUTHENTICATION Clause
Use the NO AUTHENTICATION clause to create a schema that does not have a password and cannot be logged into. This is intended for schema only accounts and reduces maintenance by removing default passwords and any requirement to rotate the password.

SQL> create user t1 no authentication;
User created.

SQL> grant create session to t1; 
Grant succeeded

SQL> connect t1@ORCLPDB1
Enter password: 
ERROR:
ORA-01017: invalid username/password; logon denied

可以看到这里是没办法按登录的。需要输入密码。我们可以使用proxy连接试试看能不能登陆。

SQL> create user t2 identified by t2;
User created.

SQL> alter user t1 grant connect through t2;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

可以看到这里可以通过proxy用户进行连接了。即使t1用户没有密码也可以连接。当然这里有个问题,就是这么做有什么作用?我们的一个想法是,让t1用户里面有数据,然后t1用户是没有密码且不能登录的。然后我们通过代理t2用户对t1用户下的数据进行查询和其他操作。这样我们就影藏了数据库的用户。全程通过代理用户做所有的操作。我们来测试一下。
1.先删除t1和t2用户

drop user t1 cascade;
drop user t2 cascade;

2.创建t1,t2用户,t1使用no authentication认证方式。,同时赋权让t2成为proxy用户。

SQL> create user t1 no authentication;
User created.
 
SQL> grant create session , create table to t1;
Grant succeeded.

SQL> create user t2 identified by t2;
User created.

SQL> alter user t1 grant connect through t2;
User altered.

3.测试使用proxy方式连接。

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

4.直接登陆,需要输入密码,此时无法登陆

SQL> connect t1/t1@ORCLPDB1
ERROR:
ORA-01017: invalid username/password; logon denied

5.授权sysdba给用户t1,发现管理权限不能被授权给该用户,因为该用户使用的是no authentication认证方式。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> grant sysdba to t1;
grant sysdba to t1
*
ERROR at line 1:
ORA-40366: Administrative privilege cannot be granted to this user.

6.把no authentication认证方式修改成密码认证。此时就可以授予sysdba权限了

SQL> alter user t1 identified by t1; 
User altered.
 
SQL> grant sysdba to t1;
Grant succeeded.

7.再次修改成no authentication认证方式,此时是不行的,不能把sysdba权限用户设置成no authentication认证方式,收回sysdba权限,就又能成功了。

SQL> alter user t1 no authentication;
alter user t1 no authentication
*
ERROR at line 1:
ORA-40367: An Administrative user cannot be altered to have no authentication
type.

SQL> revoke sysdba from t1;
Revoke succeeded.
 
SQL> alter user t1 no authentication;
User altered.

8.再次取消no authentication认证方式,在t1用户下创建表和插入数据.

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> alter user t1 identified by t1;

SQL> connect t1/t1@ORCLPDB1
Connected.

SQL> create table t1 (name varchar2(32));
Table created.
 
SQL> insert into t1 values('buddy');
1 row created.
 
SQL> commit;
Commit complete.

9.再次重新设置no authentication认证方式,通过proxy方式进行连接,可以查看表的数据和插入数据。此时虽然表属于t1用户,但是t1用户是没有密码和不能登录的,只能通过t2用户使用proxy连接方式登录操作。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> alter user t1 no authentication;
User altered.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.

SQL> select * from t1;
NAME
--------------------------------
buddy

SQL> insert into t1 values('buddy99');
1 row created.

SQL> commit;
Commit complete.

SQL> select object_name, object_type, owner from all_objects where object_name ='T1';

OBJECT_NAME          OBJECT_TYPE             OWNER
-------------------- ----------------------- ------------------------------
T1                   TABLE                   T1

那么我们能不能使用审计来审计是什么用户干的呢,能不能看到代理用户干的?

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> create audit policy t1_user_audit_policy privileges create table when 'SYS_CONTEXT(''USERENV'',''SESSION_USER'') = ''T1''' evaluate per session container=current;
Audit policy created

SQL> audit policy t1_user_audit_policy whenever successful;
Audit succeeded.

SQL> connect t2[t1]/t2@ORCLPDB1
Connected.
SQL> create table a1 (name varchar2(30));
Table created.

查看审计unified_audit_trail表,可以看到DBPROXY_USERNAME字段不为空就是代理用户T2创建的。

SQL> connect sys/oracle@ORCLPDB1 as sysdba;
Connected.

SQL> select object_name,event_timestamp, dbusername, dbproxy_username from unified_audit_trail where action_name = 'CREATE TABLE';
OBJECT_NAME                    EVENT_TIMESTAMP                DBUSERNAME                     DBPROXY_USERNAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
A1                             23-OCT-18 10.21.33.073649 PM   T1                             T2

使用PostgreSQL分析操作系统命令及文件

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用PostgreSQL分析操作系统命令及文件
通过在Linux安装了sysstat,可以实现自动采集性能数据存放在/var/log/sa中。要开启自动采集,需要开启syssata服务,在centos 7当中使用下列命令:

systemctl enable sysstat
systemctl start sysstat

当然自动采集是10分钟一次,为了实验方便,我修改了配置文件调整成1分钟1次。

[root@db sa]# more /etc/cron.d/sysstat 
# Run system activity accounting tool every 10 minutes
*/1 * * * * root /usr/lib64/sa/sa1 1 1
# 0 * * * * root /usr/lib64/sa/sa1 600 6 &
# Generate a daily summary of process accounting at 23:53
53 23 * * * root /usr/lib64/sa/sa2 -A

接下来我们运行sar -f命令查看自动生成的文件.

[root@db sa]# sar -f sa22 | head -20
Linux 3.10.0-693.21.1.el7.x86_64 (db.gowithme.tw) 	2018年10月22日 	_x86_64_	(1 CPU)

21时48分59秒       LINUX RESTART
21时59分54秒       LINUX RESTART

22时00分01秒     CPU     %user     %nice   %system   %iowait    %steal     %idle
22时01分01秒     all      0.02      0.00      0.20      0.03      0.00     99.75
22时02分01秒     all      0.02      0.00      0.12      0.02      0.00     99.85
22时03分01秒     all      0.00      0.00      0.07      0.02      0.00     99.92
22时04分01秒     all      0.00      0.00      0.07      0.00      0.00     99.93
22时05分01秒     all      0.02      0.00      0.08      0.00      0.00     99.90
22时06分01秒     all      0.00      0.00      0.10      0.02      0.00     99.88
22时07分02秒     all      0.02      0.00      0.07      0.00      0.00     99.92
22时08分01秒     all      0.00      0.00      0.07      0.02      0.00     99.91
22时09分01秒     all      0.02      0.00      0.07      0.02      0.00     99.90
22时10分01秒     all      0.00      0.00      0.07      0.00      0.00     99.93
22时11分01秒     all      0.02      0.00      0.07      0.00      0.00     99.92
22时12分01秒     all      0.00      0.00      0.07      0.02      0.00     99.92
22时13分01秒     all      0.00      0.00      0.08      0.00      0.00     99.92

接下来我们就可以使用postgresql将OS命令的结果存储到变量中.

-bash-4.2$ psql 
psql (10.4)
Type "help" for help.

然后查询变量,就可以查看到os的数据.
postgres=# \set varls `ls -la`
postgres=# select :'varls';
                               ?column?                               
----------------------------------------------------------------------
 total 20                                                            +
 dr-xr-xr-x.  18 root    root     239 Oct 22 20:33 .                 +
 dr-xr-xr-x.  18 root    root     239 Oct 22 20:33 ..                +
 lrwxrwxrwx.   1 root    root       7 Mar 31  2018 bin -> usr/bin    +
 dr-xr-xr-x.   5 root    root    4096 Mar 31  2018 boot              +
 drwxr-xr-x.  18 root    root    3040 Oct 22 21:33 dev               +
 drwxr-xr-x.  76 root    root    8192 Oct 22 21:45 etc               +
 drwxr-xr-x.   3 root    root      21 Mar 31  2018 home              +
 lrwxrwxrwx.   1 root    root       7 Mar 31  2018 lib -> usr/lib    +
 lrwxrwxrwx.   1 root    root       9 Mar 31  2018 lib64 -> usr/lib64+
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 media             +
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 mnt               +
 drwxr-xr-x.   3 root    root      39 Mar 31  2018 opt               +
 dr-xr-xr-x. 113 root    root       0 Oct 22 21:33 proc              +
 dr-xr-x---.   3 root    root     163 Mar 31  2018 root              +
 drwxr-xr-x.  23 root    root     700 Oct 22 21:45 run               +
 lrwxrwxrwx.   1 root    root       8 Mar 31  2018 sbin -> usr/sbin  +
 drwxr-xr-x.   2 root    root       6 Nov  5  2016 srv               +
 dr-xr-xr-x.  13 root    root       0 Oct 22 21:33 sys               +
 drwxrwxrwt.   7 root    root     140 Oct 22 21:45 tmp               +
 drwxr-xr-x.  16 root    root     199 Jun 28 10:50 usr               +
 drwxr-xr-x.   1 vagrant vagrant 4096 Oct 22 21:23 vagrant           +
 drwxr-xr-x.  19 root    root     267 Mar 31  2018 var
(1 row)

接下来我们在把sar的结果放在var_iostat变量里面.

postgres=# \set var_iostat `sar -f /var/log/sa/sa22`
postgres=# select :'var_iostat';
                                            ?column?                                             
-------------------------------------------------------------------------------------------------
 Linux 3.10.0-693.21.1.el7.x86_64 (db.gowithme.tw)       10/22/2018      _x86_64_        (1 CPU)+
                                                                                                +
 09:48:59 PM       LINUX RESTART                                                                +
                                                                                                +
 09:59:54 PM       LINUX RESTART                                                                +
                                                                                                +
 10:00:01 PM     CPU     %user     %nice   %system   %iowait    %steal     %idle                +
 10:01:01 PM     all      0.02      0.00      0.20      0.03      0.00     99.75                +
 10:02:01 PM     all      0.02      0.00      0.12      0.02      0.00     99.85                +
 10:03:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.92                +
 10:04:01 PM     all      0.00      0.00      0.07      0.00      0.00     99.93                +
 10:05:01 PM     all      0.02      0.00      0.08      0.00      0.00     99.90                +
 10:06:01 PM     all      0.00      0.00      0.10      0.02      0.00     99.88                +
 10:07:02 PM     all      0.02      0.00      0.07      0.00      0.00     99.92                +
 10:08:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.91                +
 10:09:01 PM     all      0.02      0.00      0.07      0.02      0.00     99.90                +
 10:10:01 PM     all      0.00      0.00      0.07      0.00      0.00     99.93                +
 10:11:01 PM     all      0.02      0.00      0.07      0.00      0.00     99.92                +
 10:12:01 PM     all      0.00      0.00      0.07      0.02      0.00     99.92                +

由于我们只对iowait这一列感兴趣,我们可以使用awk进行过滤。

postgres=# \set var_iostat `sar -f /var/log/sa/sa22 | egrep -v "^$|Average|Linux|LINUX|CPU" | awk -F " " '{print $7}'`
postgres=# select :'var_iostat';
 ?column? 
----------
 0.03    +
 0.02    +
 0.02    +
 0.00    +
 0.00    +
 0.02    +
 0.00    +
 0.02    +
 0.02    +
 0.00    +
 0.00    +
 0.02    +
 0.00    +
 0.02    +

如果我们把这个插入到表中,结果会是一个varchar列,而不是多列。这对于我们分析数据不是很好,接下来我们需要做一下行列转换,先把这么多行转换成一行数据,并用,分割开。

postgres=# \set var_iostat `sar -f /var/log/sa/sa22 | egrep -v "^$|Average|Linux|LINUX|CPU" | awk -F " " '{print $7}' | sed ':a;N;$!ba;s/\n/,/g'`
postgres=# select :'var_iostat';
                                                                                                                                                            ?column?                                                                                          
                                                                   
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------
 0.03,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.0
0,0.00,0.02,0.00,0.02,0.00,0.07,0.00,0.02,0.00,0.00,0.02,0.00,0.02
(1 row)

现在我们把上述这个变量用逗号分割,然后转换成数组。

postgres=# select string_to_array(:'var_iostat',',')::numeric[];
                                                                                                                                                          string_to_array                                                                                     
                                                                     
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------
 {0.03,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.02,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.02,0.00,0.02,0.02,0.00,0.00,0.02,0.00,0.00,0.00,0.00,0.00,0.02,0.00,0.00,0.00,0.02,0.00,0.00,0.02,0.00,0.02,0.02,0.00,0.
00,0.00,0.02,0.00,0.02,0.00,0.07,0.00,0.02,0.00,0.00,0.02,0.00,0.02}
(1 row)

接下来存储到表里面,我们就可以查询出当前使用率最大值和最小值做一些统计.

postgres=# create table tab_iowaits (varday date, iowaits numeric[]);
postgres=# insert into tab_iowaits values (current_date, string_to_array(:'var_iostat',',')::numeric[]);
postgres=# with tab as ( select unnest(iowaits) vals, varday from tab_iowaits where varday = current_date ) select max(vals) from tab;
 max  
------
 0.07
(1 row)

postgres=# with tab as ( select unnest(iowaits) vals, varday from tab_iowaits where varday = current_date ) select min(vals) from tab;
 min  
------
 0.00
(1 row)

使用Vagrant Boxes来搜索你想要的环境

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用VAGRANT BOXES来搜索你想要的环境
 
自从有了Vagrant之后,安装各类环境我一直都采用它,昨天突然想安装一个PostgreSQL。现在开源的产品特别多,要是一个一个的去安装操作系统,安装软件,是非常耗时的,还好Vagrant能很好的帮助我们解决这类问题。但是我们如何寻找已经配置好的环境呢?我们可以通过官方网站的Vagrant Boxes来找。

进入到Boxes之后,我们可以搜索,比如我想安装pg11,我就搜到了一个在debain环境下安装的pg11.

当然这里要和你的虚拟机搭配,比如我搜索的这个就是VirtualBox环境。点到虚拟机里面,然后就看到下面两条命令,执行一下,就可以把环境拖下来搭建好。

执行命令就可以搭建了。

C:\Users\Administrator>cd /d G:\VM DATA\vagrant-debain
G:\VM DATA\vagrant-debain>vagrant init jurafejfar/debian-9.5-pg11
G:\VM DATA\vagrant-debain>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'jurafejfar/debian-9.5-pg11' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'jurafejfar/debian-9.5-pg11'
    default: URL: https://vagrantcloud.com/jurafejfar/debian-9.5-pg11
==> default: Adding box 'jurafejfar/debian-9.5-pg11' (v2018-10-17) for provider: virtualbox
    default: Downloading: https://vagrantcloud.com/jurafejfar/boxes/debian-9.5-pg11/versions/2018-10-17/providers/virtualbox.box
==> default: Box download is resuming from prior download progress
    default: Download redirected to host: vagrantcloud-files-production.s3.amazonaws.com
    default: Progress: 100% (Rate: 1000k/s, Estimated time remaining: --:--:--)
==> default: Successfully added box 'jurafejfar/debian-9.5-pg11' (v2018-10-17) for 'virtualbox'!
==> default: Importing base box 'jurafejfar/debian-9.5-pg11'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'jurafejfar/debian-9.5-pg11' is up to date...
==> default: Setting the name of the VM: vagrant-debain_default_1539794274257_97733
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2222 (host) (adapter 1)
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2222
    default: SSH username: vagrant
    default: SSH auth method: private key
    default:
    default: Vagrant insecure key detected. Vagrant will automatically replace
    default: this with a newly generated keypair for better security.
    default:
    default: Inserting generated public key within guest...
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
    default: The guest additions on this VM do not match the installed version of
    default: VirtualBox! In most cases this is fine, but in rare cases it can
    default: prevent things such as shared folders from working properly. If you see
    default: shared folder errors, please make sure the guest additions within the
    default: virtual machine match the version of VirtualBox you have installed on
    default: your host and reload your VM.
    default:
    default: Guest Additions Version: 5.2.20
    default: VirtualBox Version: 5.1
==> default: Mounting shared folders...
    default: /vagrant => G:/VM DATA/vagrant-debain

然后配置下网络,在Vagrantfile文件中添加“config.vm.network “private_network”, ip: “192.168.56.90””就可以通过CRT进行连接了。

以后再也不同折腾各种环境了!

使用dbms_compression.get_compression_ratio评估压缩比例

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:使用dbms_compression.get_compression_ratio评估压缩比例

DBMS_COMPRESSION是用于管理Oracle数据库表压缩功能的包。它通过消除磁盘页中的重复值来压缩数据。随着数据库大小的增加,表压缩可以节省资源,它不仅可以节省磁盘,还可以减少缓冲区缓存中的内存使用量。DBMS_COMPRESSION是在11gR2中引入,这个软件包一直在不断发展,现在在12c版本中引入了一些非常有用的增强功能。我们来试用一下。

首先创建一个用户和表用于测试。

SQL> alter session set container=ORCLPDB1;
Session altered.

SQL> create user test1 identified by test1;
User created.

SQL> grant dba to test1;
Grant succeeded.

SQL> create tablespace testtbs datafile '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/testtbs.dbf' size 100m autoextend on next 100m maxsize unlimited;
Tablespace created.

SQL> alter user test1 quota unlimited on testtbs;
User altered

SQL> connect test1/test1@ORCLPDB1
Connected.

SQL> create table testtab tablespace testtbs as select rownum id, a.* from dba_objects a where 1 = 0;
Table created.

接下来我们往表里面插入一百万数据。

declare
  l_cnt  number;
  l_rows number := 1000000;
begin
  insert /*+ append */ into testtab select rownum, a.* from dba_objects a;
  l_cnt := sql%rowcount;
  commit;
  while (l_cnt < l_rows) loop
     insert /*+ append */ into testtab
     select rownum+l_cnt, owner,object_name,subobject_name,object_id,data_object_id,object_type,created,last_ddl_time,timestamp,status,temporary,generated,
     secondary,namespace,edition_name,sharing,editionable,oracle_maintained,application,default_collation,duplicated,sharded,created_appid,created_vsnid,modified_appid,modified_vsnid
       from testtab a
      where rownum <= l_rows-l_cnt;
     l_cnt := l_cnt + sql%rowcount;
     commit;
  end loop;
  commit;
end;
/

让我们查询dba_segments目录视图,可以看到分配了160MB。

SQL> SELECT segment_name, SUM(bytes)/1024/1024 MB FROM dba_segments WHERE segment_name='TESTTAB' GROUP BY segment_name;

SEGMENT         MB
------- ----------
TESTTAB        160

现在在12c可以在使用dbms_compression包得到每个块的估计行数。DBMS_COMPRESSION包使用不同的数字常量来描述每种类型的可能压缩,现在可以从1到1000000,具体可以参考官方文档,如下列表所示:

当选择(comptype = 2)的时候执行的是COMP_ADVANCED。具体如下所示:

set serveroutput on
declare
  v_blocks_comp   pls_integer;
  v_blocks_uncomp   pls_integer;
  v_rows_comp   pls_integer;
  v_rows_uncomp   pls_integer;
  v_compress_ratio  number;
  v_compress_type   varchar2(32767);
begin
  dbms_compression.get_compression_ratio (
   scratchtbsname  => 'testtbs',
   ownname=>'test1',
   objname=>'testtab',
   subobjname=> null,
   comptype=> 2,
   blkcnt_cmp=> v_blocks_comp,
   blkcnt_uncmp=> v_blocks_uncomp,
   row_cmp=>v_rows_comp,
   row_uncmp=>v_rows_uncomp,
   cmp_ratio=>v_compress_ratio,
   comptype_str=>v_compress_type,
   subset_numrows=>dbms_compression.comp_ratio_minrows
  ) ;
  dbms_output.put_line('output: ');
  dbms_output.put_line('estimated compression ratio: ' || v_compress_ratio);
  dbms_output.put_line('blocks used - compressed sample: ' || v_blocks_comp);
  dbms_output.put_line('blocks used - uncompressed sample: ' || v_blocks_uncomp);
  dbms_output.put_line('rows in a block - compressed sample: ' || v_rows_comp);
  dbms_output.put_line('rows in a block - uncompressed sample: ' || v_rows_uncomp);
end;
/
Estimated Compression Ratio: 2.6
Blocks used - compressed sample: 915
Blocks used - uncompressed sample: 2432
Rows in a block - compressed sample: 129
Rows in a block - uncompressed sample: 48

这里可以计算出压缩比例未2.6%,使用这个级别的压缩可以使blocks从2432(uncompressed)到915(compressed),压缩前是每个block存储48行,到压缩之后可以存储129行。
我们可以再次执行上述查询,把comptype=> 2修改成4,4是HCC Query High压缩方式。

结果如下:

Compression Advisor self-check validation successful. select count(*) on both
Uncompressed and EHCC Compressed format = 1000000 rows
OUTPUT:
Estimated Compression Ratio: 23.1
Blocks used - compressed sample: 872
Blocks used - uncompressed sample: 20192
Rows in a block - compressed sample: 1147
Rows in a block - uncompressed sample: 49

可以看到,压缩比例提升到23.1%,使用这个级别的压缩可以使blocks从20192(uncompressed)到872(compressed),压缩前是每个block存储49行,到压缩之后可以存储1147行。当然上面的提示也很明显,是做的EHCC压缩。当然这需要在Exadata才能压缩出这种比例。

RedHat7.4上安装SQL Server 2017

版权声明:本文为Buddy Yuan原创文章,未经允许不得转载。原文地址:REDHAT7.4上安装SQL SERVER 2017

今天测试了一下在RedHat7.4上安装SQL Server 2017,整个安装过程并不复杂。以下是安装过程的详细步骤。
1.检查CPU和内存,CPU至少2个核心,内存2GB以上,磁盘空间大于6GB,如图所示:

[root@10 ~]# grep -i --color "model name" /proc/cpuinfo
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz
model name : Intel(R) Core(TM) i7-6700HQ CPU @ 2.60GHz

[root@10 ~]# grep -i --color "MemTotal" /proc/meminfo
MemTotal: 4046612 kB

[root@10 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 42G 4.0G 38G 10% /
devtmpfs 2.0G 0 2.0G 0% /dev
tmpfs 2.0G 0 2.0G 0% /dev/shm
tmpfs 2.0G 9.5M 2.0G 1% /run
tmpfs 2.0G 0 2.0G 0% /sys/fs/cgroup
/dev/sda1 497M 153M 344M 31% /boot
tmpfs 396M 4.0K 396M 1% /run/user/992
tmpfs 396M 48K 396M 1% /run/user/1000
tmpfs 396M 0 396M 0% /run/user/0

2.下载Microsoft SQL Server 2017 Red Hat存储库配置文件:

[root@10 ~]# sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 232 100 232 0 0 778 0 --:--:-- --:--:-- --:--:-- 781

3.运行yum安装sql server

[root@10 ~]# yum install -y mssql-server
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
packages-microsoft-com-mssql-server-2017 | 2.9 kB 00:00:00
packages-microsoft-com-mssql-server-2017/primary_db | 16 kB 00:00:00
Resolving Dependencies
--> Running transaction check
---> Package mssql-server.x86_64 0:14.0.3038.14-2 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================================================================================================
Installing:
mssql-server x86_64 14.0.3038.14-2 packages-microsoft-com-mssql-server-2017 169 M

Transaction Summary
================================================================================================================================================================================================================================================================
Install 1 Package

Total download size: 169 M
Installed size: 169 M
Downloading packages:
warning: /var/cache/yum/x86_64/7Server/packages-microsoft-com-mssql-server-2017/packages/mssql-server-14.0.3038.14-2.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID be1229cf: NOKEY====================================== ] 5.7 MB/s | 168 MB 00:00:00 ETA
Public key for mssql-server-14.0.3038.14-2.x86_64.rpm is not installed
mssql-server-14.0.3038.14-2.x86_64.rpm | 169 MB 00:00:30
Retrieving key from https://packages.microsoft.com/keys/microsoft.asc
Importing GPG key 0xBE1229CF:
Userid : "Microsoft (Release signing) <gpgsecurity@microsoft.com>"
Fingerprint: bc52 8686 b50d 79e3 39d3 721c eb3e 94ad be12 29cf
From : https://packages.microsoft.com/keys/microsoft.asc
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : mssql-server-14.0.3038.14-2.x86_64 1/1

+--------------------------------------------------------------+
Please run 'sudo /opt/mssql/bin/mssql-conf setup'
to complete the setup of Microsoft SQL Server
+--------------------------------------------------------------+

SQL Server needs to be restarted in order to apply this setting. Please run
'systemctl restart mssql-server.service'.
Verifying : mssql-server-14.0.3038.14-2.x86_64 1/1

Installed:
mssql-server.x86_64 0:14.0.3038.14-2

4.运行SQL Server配置脚本(/opt/mssql/bin/mssql-conf),这里选择安装的版本,因为我们是测试,就选择了Developer版。同时还需要设置SA的密码,密码规则是强密码,需要最少8个字符,包括大写和小写字母、数字和/或非字母数字符号。

[root@10 bin]# pwd
/opt/mssql/bin
[root@10 bin]# ./mssql-conf setup
Choose an edition of SQL Server:
1) Evaluation (free, no production use rights, 180-day limit)
2) Developer (free, no production use rights)
3) Express (free)
4) Web (PAID)
5) Standard (PAID)
6) Enterprise (PAID)
7) Enterprise Core (PAID)
8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 2
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Do you accept the license terms? [Yes/No]:Yes

Enter the SQL Server system administrator password:
The specified password contains an invalid character. Valid characters include uppercase letters, lowercase letters, numbers, symbols, punctuation marks, and unicode characters that are categorized as alphabetic but are not uppercase or lowercase.
Enter the SQL Server system administrator password:
Confirm the SQL Server system administrator password:
Configuring SQL Server...

ForceFlush is enabled for this instance.
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

5.查看mssql服务的状态,目前是running的。

systemctl status mssql-server
[root@10 bin]# 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 Mon 2018-10-15 23:02:15 CST; 3min 27s ago
Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 4375 (sqlservr)
CGroup: /system.slice/mssql-server.service
├─4375 /opt/mssql/bin/sqlservr
└─4414 /opt/mssql/bin/sqlservr

Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.81 spid11s Polybase feature disabled.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.82 spid11s Clearing tempdb database.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.84 spid6s 8 transactions rolled forward in database 'msdb' (4:0). This is an informational message only. No user action is required.
Oct 15 23:02:18 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:18.90 spid6s 0 transactions rolled back in database 'msdb' (4:0). This is an informational message only. No user action is required.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.13 spid11s Starting up database 'tempdb'.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.34 spid11s The tempdb database has 1 data file(s).
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.36 spid22s The Service Broker endpoint is in disabled or stopped state.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.36 spid22s The Database Mirroring endpoint is in disabled or stopped state.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.40 spid22s Service Broker manager has started.
Oct 15 23:02:19 10.0.2.15 sqlservr[4375]: 2018-10-15 23:02:19.41 spid6s Recovery is complete. This is an informational message only. No user action is required.

6.默认SQL Server通过1433端口连接,此时要检查防火墙状态,如果是打开的,需要把1433端口在防火墙上做放开设置,允许连接。或者直接关闭Linux防火墙。我在这里直接关闭了防火墙。

[root@10 bin]# firewall-cmd --state
running
[root@10 bin]# systemctl stop firewalld.service
[root@10 bin]# systemctl disable firewalld.service
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

7.接下来需要安装sqlcmd和bcp等客户端工具。和安装服务器一样,首先也要下载配置文件。

[root@10 bin]# sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 193 100 193 0 0 682 0 --:--:-- --:--:-- --:--:-- 681

接下来如果有旧版的mssql工具,需要删除。

[root@10 bin]# yum remove unixODBC-utf16 unixODBC-utf16-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
No Match for argument: unixODBC-utf16
No Match for argument: unixODBC-utf16-devel
No Packages marked for removal

删除完成之后安装新的工具,在安装过程中出现下列的问题:

[root@10 bin]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: langpacks, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
packages-microsoft-com-prod | 2.9 kB 00:00:00
packages-microsoft-com-prod/primary_db | 144 kB 00:00:00
No package unixODBC-devel available.
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.2.0.2-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.3.0.0 for package: mssql-tools-17.2.0.2-1.x86_64 --> Processing Dependency: msodbcsql17 >= 17.2.0.0 for package: mssql-tools-17.2.0.2-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.2.0.1-1.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package: msodbcsql17-17.2.0.1-1.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
--> Processing Dependency: unixODBC >= 2.3.1 for package: msodbcsql17-17.2.0.1-1.x86_64
---> Package unixODBC-utf16.x86_64 0:2.3.1-1 will be installed
--> Processing Conflict: msodbcsql17-17.2.0.1-1.x86_64 conflicts unixODBC-utf16
--> Finished Dependency Resolution
Error: msodbcsql17 conflicts with unixODBC-utf16-2.3.1-1.x86_64
Error: Package: msodbcsql17-17.2.0.1-1.x86_64 (packages-microsoft-com-prod)
Requires: unixODBC >= 2.3.1
You could try using --skip-broken to work around the problem
You could try running: rpm -Va --nofiles --nodigest

下列问题主要是因为软件的yum仓库不可用,百度了办法修改成了163的centos的yum仓库就可以了。

[root@10 run]# yum install -y mssql-tools unixODBC-devel
Loaded plugins: fastestmirror, product-id, search-disabled-repos, subscription-manager
This system is not registered with an entitlement server. You can use subscription-manager to register.
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.2.0.2-1 will be installed
--> Processing Dependency: msodbcsql17 < 17.3.0.0 for package: mssql-tools-17.2.0.2-1.x86_64 --> Processing Dependency: msodbcsql17 >= 17.2.0.0 for package: mssql-tools-17.2.0.2-1.x86_64
---> Package unixODBC-devel.x86_64 0:2.3.1-11.el7 will be installed
--> Processing Dependency: unixODBC(x86-64) = 2.3.1-11.el7 for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtemplate.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libtdsS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libsapdbS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboraodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: liboplodbcS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbctxtS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcnnS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcminiS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg2S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbcdrvcfg1S.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libodbccr.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libnn.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libmimerS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Processing Dependency: libesoobS.so.2()(64bit) for package: unixODBC-devel-2.3.1-11.el7.x86_64
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.2.0.1-1 will be installed
---> Package unixODBC.x86_64 0:2.3.1-11.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

================================================================================================================================================================================================================================================================
Package Arch Version Repository Size
================================================================================================================================================================================================================================================================
Installing:
mssql-tools x86_64 17.2.0.2-1 packages-microsoft-com-prod 254 k
unixODBC-devel x86_64 2.3.1-11.el7 base 55 k
Installing for dependencies:
msodbcsql17 x86_64 17.2.0.1-1 packages-microsoft-com-prod 4.3 M
unixODBC x86_64 2.3.1-11.el7 base 413 k

Transaction Summary
================================================================================================================================================================================================================================================================
Install 2 Packages (+2 Dependent packages)

Total download size: 5.1 M
Installed size: 6.0 M
Downloading packages:
warning: /var/cache/yum/x86_64/$releasever/base/packages/unixODBC-devel-2.3.1-11.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Public key for unixODBC-devel-2.3.1-11.el7.x86_64.rpm is not installed
(1/4): unixODBC-devel-2.3.1-11.el7.x86_64.rpm | 55 kB 00:00:00
(2/4): unixODBC-2.3.1-11.el7.x86_64.rpm | 413 kB 00:00:00
(3/4): mssql-tools-17.2.0.2-1.x86_64.rpm | 254 kB 00:00:00
(4/4): msodbcsql17-17.2.0.1-1.x86_64.rpm | 4.3 MB 00:00:01
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.4 MB/s | 5.1 MB 00:00:01
Retrieving key from http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
Importing GPG key 0xF4A80EB5:
Userid : "CentOS-7 Key (CentOS 7 Official Signing Key) <security@centos.org>"
Fingerprint: 6341 ab27 53d7 8a78 a7c2 7bb1 24c6 a8a7 f4a8 0eb5
From : http://mirrors.163.com/centos/RPM-GPG-KEY-CentOS-7
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
rhn-check-2.0.2-17.el7.noarch has missing requires of yum-rhn-plugin >= ('0', '1.6.4', '1')
Installing : unixODBC-2.3.1-11.el7.x86_64 1/4
The license terms for this product can be downloaded from
https://aka.ms/odbc172eula and found in
/usr/share/doc/msodbcsql17/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : msodbcsql17-17.2.0.1-1.x86_64 2/4
The license terms for this product can be downloaded from
http://go.microsoft.com/fwlink/?LinkId=746949 and found in
/usr/share/doc/mssql-tools/LICENSE.txt . By entering 'YES',
you indicate that you accept the license terms.

Do you accept the license terms? (Enter YES or NO)
YES
Installing : mssql-tools-17.2.0.2-1.x86_64 3/4
Installing : unixODBC-devel-2.3.1-11.el7.x86_64 4/4
Verifying : unixODBC-devel-2.3.1-11.el7.x86_64 1/4
Verifying : unixODBC-2.3.1-11.el7.x86_64 2/4
Verifying : msodbcsql17-17.2.0.1-1.x86_64 3/4
Verifying : mssql-tools-17.2.0.2-1.x86_64 4/4

Installed:
mssql-tools.x86_64 0:17.2.0.2-1 unixODBC-devel.x86_64 0:2.3.1-11.el7

Dependency Installed:
msodbcsql17.x86_64 0:17.2.0.1-1 unixODBC.x86_64 0:2.3.1-11.el7

8.接下来做一些测试连接,先查看用的是否是1434端口

[root@10 run]# netstat -tulpn | grep sqlservr
tcp 0 0 0.0.0.0:1433 0.0.0.0:* LISTEN 4414/sqlservr
tcp 0 0 127.0.0.1:1434 0.0.0.0:* LISTEN 4414/sqlservr
tcp6 0 0 :::1433 :::* LISTEN 4414/sqlservr
tcp6 0 0 ::1:1434 :::* LISTEN 4414/sqlservr

[root@10 run]# sqlcmd -S 127.0.0.1 -U SA -P 'Passwd!123*'
bash: sqlcmd: command not found...

这里没配置好环境变量。先设置一下。

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

再一次连接,如果成功,就会显示sqlcmd命令提示符:1>

[root@10 run]# sqlcmd -S 127.0.0.1 -U SA
Password:
1> SELECT Name from sys.Databases
2> GO
Name
--------------------------------------------------------------------------------------------------------------------------------
master
tempdb
model
msdb
TestDB

(5 rows affected)

1> USE TestDB
2> CREATE TABLE test (id INT, name VARCHAR(50))
3> INSERT INTO test VALUES (1, 'test install');
4> GO
Changed database context to 'TestDB'.

(1 rows affected)

1> select * from test
2> GO
id name
----------- --------------------------------------------------
1 test install

(1 rows affected)

至此整个安装过程结束,参考微软官方文档:https://docs.microsoft.com/zh-cn/sql/linux/quickstart-install-connect-red-hat?view=sql-server-2017