默认情况下一个用户对应一个集合,用户的schema名等于用户名,并作为该用户缺省schema。所以schema集合看上去像用户名。访问一个表时,如果没有指明该表属于哪个schema,系统会自动加上缺省的schema。一个对象的完整名称为schema.object,而不属user.object。

After
I’ve modified my3306.cnf,it seems no error occurs now.

SQL Server on Linux也发布一段时间了,官方上支持Red Hat, SUSE,
Ubuntu。手上没有以上Linux版本,选用了与Red
Hat最接近的CentOS7.4来进行安装和测试。

  在MySQL中创建一个Schema和创建一个Database的效果好像是一样的,但是在SQL
Server和Oracle数据库中效果又是不同的。

After
I started mysqld several seconds later,the process down again at
once.

Repository                                                 Size

Installing:
 mssql-server                        x86_64                       
14.0.900.75-1                          
/mssql-server-14.0.900.75-1.x86_64                        870 M

  在SQL
Server中,user和schema总有一层隐含的关系,让我们很少意识到其实user和schema是两种完全不同的概念,假如我们在某一数据库中创建了用户Bosco,那么此时后台也为我们默认的创建了schema【Bosco】,schema的名字和user的名字相同。

Initialize
MySQL db.

 

  如果把database看作是一个仓库,仓库很多房间(schema),一个schema代表一个房间,table可以看作是每个房间中的储物柜,user是每个schema的主人,有操作数据库中每个房间的权利,就是说每个数据库映射的user有每个schema(房间)的钥匙。

Download
and extract MySQL software.

5. 在Windows上使用SSMS连接数据库

图片 1

Figure-1:SSMS连接Linux下的数据库

 

图片 2

Figure-2:查询版本信息

 

SELECT @@VERSION;
GO
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)
    Jul 27 2017 08:53:49
    Copyright (C) 2017 Microsoft Corporation
    Developer Edition (64-bit) on Linux (CentOS Linux 7 (Core))

图片 3
Figure-3:查询数据

发现Red Gate部分功能不能使用。rpm包安装无法指定安装目录。

 

本文转载来自标点符的《数据库中Schema(模式)概念的理解》,原文地址: http://www.biaodianfu.com/database-schema.html

1 [root@docker mysql]# mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
2 [root@docker mysql]# ln -sv /opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64 /usr/local/mysql
3 ‘/usr/local/mysql’ -> ‘/opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64’

4. 安装SQL Server Agent

update:2017-3-27

    Several
days ago,I’ve implement a docker environmnet,I’m gonna Install a MySQL
8.0 version on it.Let’s see the detail of it.

3.2 安装

yum localinstall msodbcsql-13.1.6.0-1.x86_64.rpm
yum localinstall mssql-tools-14.0.5.0-1.x86_64.rpm

PostgreSQL中模式的概念与MySQL中Database的概念是等同的。在MYSQL中可以同事访问多个Database对象,但在PostgreSQL中只能访问一个Database对象,但在PostgreSQL中可以访问多个Scheme。

 

Repository                                                    Size

Installing:
 mssql-server-agent                     x86_64                    
14.0.900.75-1                       
/mssql-server-agent-14.0.900.75-1.x86_64                     8.9 M

  在Oracle数据库中不能新建一个schema,要想创建一个schema,只能通过创建一个用户的方法解决,在创建一个用户的同时为这个用户创建一个与用户名同名的schem并作为该用户的缺省shcema。即schema的个数同user的个数相同,而且schema名字同user名字一一
对应并且相同。

 

3.4 本地连接

[root@134test /opt/mssql/bin]# sqlcmd -S localhost -U sa
Password:
1> select name from sys.databases;
2> go
name                                                                                                                          

  在SQL Server
2005中,为了向后兼容,当用sp_adduser存储过程创建一个用户的时候,sqlserver2005同时也创建了一个和用户名相同的schema,然而这个存储过程是为了向后兼容才保留的,当我们用create
user创建数据库用户时,我们可以用该用户指定一个已经存在的schema作为默认的schema,如果我们不指定,则该用户所默认的schema即为dbo
schema,dbo房间(schema)好比一个大的公共房间,在当前登录用户没有默认schema的前提下,如果你在大仓库中进行一些操作,比如create
table,如果没有制定特定的房间(schema),那么你的物品就只好放进公共的dbo房间(schema)了。但是如果当前登录用户有默认的schema,那么所做的一切操作都是在默认的schema上进行。

 

3.3 添加到环境变量

[root@134test /opt]# echo ‘export
PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bash_profile

[root@134test /opt]# source ~/.bash_profile
[root@134test /opt]# echo ‘export
PATH=”$PATH:/opt/mssql-tools/bin”‘ >> ~/.bashrc

[root@134test /opt]# source ~/.bashrc

  在学习数据库时,会遇到一个让人迷糊的Schema的概念。实际上,schema就是数据库对象的集合,这个集合包含了各种对象如:表、视图、存储过程、索引等。

1 [root@docker local]# echo "export PATH=$PATH:/usr/local/mysql/bin" >> ~/.bash_profile
2 [root@docker local]# source ~/.bash_profile
3 [root@docker local]# echo $PATH
4 /usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

Transaction Summary

Install  1 Package

Total size: 870 M
Installed size: 870 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing :
mssql-server-14.0.900.75-1.x86_64                                                                                                                      
1/1

+————————————————————–+
Please run ‘sudo /opt/mssql/bin/mssql-conf setup’
to complete the setup of Microsoft SQL Server
+————————————————————–+

  Verifying  :
mssql-server-14.0.900.75-1.x86_64                                                                                                                      
1/1

Installed:
  mssql-server.x86_64
0:14.0.900.75-1                                                                                                                                    
 

Complete!

 

 1 [root@docker mysql]# ldd /usr/local/mysql/bin/mysqld.sh
 2 ldd: /usr/local/mysql/bin/mysqld.sh: No such file or directory
 3 [root@docker mysql]# ldd /usr/local/mysql/bin/mysqld
 4     linux-vdso.so.1 =>  (0x00007fff57cca000)
 5     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f57bc85d000)
 6     libaio.so.1 => not found
 7     libnuma.so.1 => not found
 8     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f57bc625000)
 9     libssl.so.1.0.0 => not found
10     libcrypto.so.1.0.0 => not found
11     libdl.so.2 => /lib64/libdl.so.2 (0x00007f57bc420000)
12     librt.so.1 => /lib64/librt.so.1 (0x00007f57bc217000)
13     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f57bbf10000)
14     libm.so.6 => /lib64/libm.so.6 (0x00007f57bbc0e000)
15     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f57bb9f7000)
16     libc.so.6 => /lib64/libc.so.6 (0x00007f57bb62a000)
17     /lib64/ld-linux-x86-64.so.2 (0x00007f57bca7f000)
18     libfreebl3.so => /lib64/libfreebl3.so (0x00007f57bb427000)
19 [root@docker mysql]# yum install -y libaio numactl libssl libcrypto
20 -- Omitted
21 [root@docker mysql]# ldd /usr/local/mysql/bin/mysqld
22     linux-vdso.so.1 =>  (0x00007fff05f82000)
23     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fc18aca3000)
24     libaio.so.1 => /lib64/libaio.so.1 (0x00007fc18aaa0000)
25     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007fc18a894000)
26     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007fc18a65d000)
27     libssl.so.1.0.0 => not found
28     libcrypto.so.1.0.0 => not found
29     libdl.so.2 => /lib64/libdl.so.2 (0x00007fc18a458000)
30     librt.so.1 => /lib64/librt.so.1 (0x00007fc18a24f000)
31     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007fc189f48000)
32     libm.so.6 => /lib64/libm.so.6 (0x00007fc189c46000)
33     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fc189a2f000)
34     libc.so.6 => /lib64/libc.so.6 (0x00007fc189662000)
35     /lib64/ld-linux-x86-64.so.2 (0x00007fc18aec5000)
36     libfreebl3.so => /lib64/libfreebl3.so (0x00007fc18945f000)
37 [root@docker mysql]# find / -name libssl.so.1.0.0
38 /opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/lib/libssl.so.1.0.0
39 [root@docker mysql]# cp /opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/lib/libssl.so.1.0.0 /usr/lib64
40 [root@docker mysql]# find / -name libcrypto.so.1.0.0
41 /opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/lib/libcrypto.so.1.0.0
42 [root@docker mysql]# cp /opt/mysql/mysql-8.0.11-linux-glibc2.12-x86_64/lib/libcrypto.so.1.0.0 /usr/lib64
43 [root@docker mysql]# ldd /usr/local/mysql/bin/mysqld
44     linux-vdso.so.1 =>  (0x00007fff13ffe000)
45     libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f156dbc3000)
46     libaio.so.1 => /lib64/libaio.so.1 (0x00007f156d9c0000)
47     libnuma.so.1 => /lib64/libnuma.so.1 (0x00007f156d7b4000)
48     libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00007f156d57d000)
49     libssl.so.1.0.0 => /lib64/libssl.so.1.0.0 (0x00007f156d30d000)
50     libcrypto.so.1.0.0 => /lib64/libcrypto.so.1.0.0 (0x00007f156cf25000)
51     libdl.so.2 => /lib64/libdl.so.2 (0x00007f156cd21000)
52     librt.so.1 => /lib64/librt.so.1 (0x00007f156cb18000)
53     libstdc++.so.6 => /lib64/libstdc++.so.6 (0x00007f156c811000)
54     libm.so.6 => /lib64/libm.so.6 (0x00007f156c50f000)
55     libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f156c2f8000)
56     libc.so.6 => /lib64/libc.so.6 (0x00007f156bf2b000)
57     /lib64/ld-linux-x86-64.so.2 (0x00007f156dde5000)
58     libfreebl3.so => /lib64/libfreebl3.so (0x00007f156bd28000)

3.5 退出

1> quit

Modify
environment variable.

Transaction Summary

Install  1 Package

Total size: 8.9 M
Installed size: 8.9 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing :
mssql-server-agent-14.0.900.75-1.x86_64                                                                                                                
1/1

+——————————————————————————–+
Please restart mssql-server to enable Microsoft SQL Server Agent.
+——————————————————————————–+

  Verifying  :
mssql-server-agent-14.0.900.75-1.x86_64                                                                                                                
1/1

Installed:
  mssql-server-agent.x86_64
0:14.0.900.75-1                                                                                                                              
 

Complete!

 

 

Start
mysqld but failed.

2.2 yum安装

[root@134test /opt/sqlserver2017]# yum localinstall
mssql-server-14.0.900.75-1.x86_64.rpm

Loaded plugins: fastestmirror, langpacks
Repository base is listed more than once in the configuration
Repository updates is listed more than once in the configuration
Repository extras is listed more than once in the configuration
Repository centosplus is listed more than once in the configuration
Examining mssql-server-14.0.900.75-1.x86_64.rpm:
mssql-server-14.0.900.75-1.x86_64
Marking mssql-server-14.0.900.75-1.x86_64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package mssql-server.x86_64 0:14.0.900.75-1 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================================================
 Package                             Arch                         
Version                                

 

2.1 下载rpm包

[root@134test /opt]# mkdir -p /opt/sqlserver2017
[root@134test /opt]# cd /opt/sqlserver2017/
[root@134test /opt]# wget

 

4.2 安装

[root@134test /opt/sqlserver2017]# yum localinstall
mssql-server-agent-14.0.900.75-1.x86_64.rpm

Loaded plugins: fastestmirror, langpacks
Repository base is listed more than once in the configuration
Repository updates is listed more than once in the configuration
Repository extras is listed more than once in the configuration
Repository centosplus is listed more than once in the configuration
Examining mssql-server-agent-14.0.900.75-1.x86_64.rpm:
mssql-server-agent-14.0.900.75-1.x86_64
Marking mssql-server-agent-14.0.900.75-1.x86_64.rpm to be installed
Resolving Dependencies
–> Running transaction check
—> Package mssql-server-agent.x86_64 0:14.0.900.75-1 will be
installed
–> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================================================================================================
 Package                                Arch                      
Version                             

1 [root@docker data]# cat /opt/mysql/my.cnf|grep lower_case_table_names
2 lower_case_table_names              =1                              #    0
3 [root@docker data]# cat /tmp/my3306.cnf | grep lower_case_table_names
4 lower_case_table_names              =1                              #    0
5 [root@docker data]# vim /tmp/my3306.cnf 
6 [root@docker data]# cat /tmp/my3306.cnf | grep lower_case_table_names
7 lower_case_table_names              =2                              #    0

2. 安装mssql-server

Procedure

1. 环境

Linux: CentOS Linux release 7.4.1708 (Core)

Memory: 4 GB

SQL Server: SQL Server 2017 (RC2) – 14.0.900.75 (X64)

 

 

Preface

 

master                                                                                                                        
 
tempdb                                                                                                                        
 
model                                                                                                                         
 
msdb                                                                                                                                                                                                                                         
 

(5 rows affected)
1>
1>create database TestDB;
2>go
1>

1> use TestDB;
2> go
Changed database context to ‘TestDB’.
1> create table t1 (id int, cname nvarchar(50))
2> go
1>
1> insert into t1 (id, cname) values (1,’abc’);
2> go

(1 rows affected)
1> select * from t1;
2> go
id          cname                                             


          1 abc                                               

(1 rows affected)
1>

 1 [root@docker vagrant]# docker images
 2 REPOSITORY          TAG                 IMAGE ID            CREATED             SIZE
 3 centos7             latest              7d0b68af5a06        10 days ago         694MB
 4 [root@docker vagrant]# docker run -d -v /vagrant/mysql_soft:/opt/mysql -v /vagrant/data1:/data -v /etc/hosts:/etc/hosts -p2222:22 --cap-add=NET_ADMIN --name c1 -h docker centos7
 5 fbf17e3f6aa8324b8d494aa0211122f5d735bf6f648a99b6632ae2be5be996da
 6 [root@docker vagrant]# docker ps -a
 7 CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                     PORTS                  NAMES
 8 fbf17e3f6aa8        centos7             "/usr/bin/supervisor…"   5 seconds ago       Up 3 seconds (healthy)     0.0.0.0:2222->22/tcp   c1
 9 103f318448c9        centos7             "/usr/bin/supervisor…"   25 minutes ago      Exited (0) 3 minutes ago                          node1
10 [root@docker vagrant]# docker rm container 103f318448c9
11 103f318448c9
12 Error: No such container: container
13 [root@docker vagrant]# docker ps
14 CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS                    PORTS                  NAMES
15 fbf17e3f6aa8        centos7             "/usr/bin/supervisor…"   32 seconds ago      Up 30 seconds (healthy)   0.0.0.0:2222->22/tcp   c1
16 [root@docker vagrant]# ssh root@172.17.0.2
17 root@172.17.0.2's password: 
18 Last login: Wed Jun 13 00:18:11 2018 from 172.17.0.1
19 [root@docker ~]# df -h
20 Filesystem                                                                                          Size  Used Avail Use% Mounted on
21 /dev/mapper/docker-253:0-16777839-60de86a769f75e2e96c849e76f1b3de10ac8c8d9985597ff3f758070edada54d   10G  682M  9.4G   7% /
22 tmpfs                                                                                                64M     0   64M   0% /dev
23 tmpfs                                                                                               497M     0  497M   0% /sys/fs/cgroup
24 none                                                                                                612G  242G  370G  40% /data
25 /dev/mapper/centos-root                                                                             8.4G  2.1G  6.3G  26% /etc/hosts
26 shm                                                                                                  64M     0   64M   0% /dev/shm
27 tmpfs                                                                                               497M     0  497M   0% /proc/scsi
28 tmpfs                                                                                               497M     0  497M   0% /sys/firmware

6. 参考

 

 

 

 

 

2.4 验证服务

[root@134test /opt/mssql/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 Thu 2017-12-14 18:25:03 CST; 1min 23s ago
     Docs:
 Main PID: 56504 (sqlservr)
   CGroup: /system.slice/mssql-server.service
           ├─56504 /opt/mssql/bin/sqlservr
           └─56525 /opt/mssql/bin/sqlservr

Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.36
Server      Server is listening on [ ::1 <ipv6> 1434].
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.37
Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.37
Server      Dedicated admin connection support was established for
listening locally on port 1434.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.38
spid19s     SQL Server is now ready for client connections. This is an
informational message; … required.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.42
spid9s      Starting up database ‘tempdb’.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.61
spid9s      The tempdb database has 1 data file(s).
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.61
spid20s     The Service Broker endpoint is in disabled or stopped
state.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.62
spid20s     The Database Mirroring endpoint is in disabled or stopped
state.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.62
spid20s     Service Broker manager has started.
Dec 14 18:25:10 134test sqlservr[56504]: 2017-12-14 18:25:10.63
spid6s      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.

2.5 停止、启动或重启 SQL Server 服务
systemctl stop mssql-server
systemctl start mssql-server
systemctl restart mssql-server

2.6 日志文件
/var/opt/mssql/log/errorlog

发表评论

电子邮件地址不会被公开。 必填项已用*标注