第1章 链接服务器简介

1.1 简介

现代的应用程序驱动着我们的世界。

每天他们都习惯于访问银行帐户，管理医疗保健计划，并从互联网远程启动汽车。大多数人都知道存储类似于社会保障号码，信用证卡号和保健信息这些敏感数据的背后是一个数据库。

然而，大多数人没有意识到的是泄露一个数据库，通常可以用最小的代价接管各种企业环境。 其中一种方法是通过“数据库链接”。

数据库链接本质上是从一个数据库服务器到另一个数据库服务器的可信连接。

数据库链接可以“爬网”，类似于抓取网站的超链接。 通过爬行从数据库服务器到数据库服务器的链接，可以访问通常不能被用户访问的数据和系统。

在现代企业环境中，使用数据库链接抓取技术获取对数百个数据库服务器的未经授权的访问并不罕见。 这种访问通常导致敏感数据和网络的完全泄露。

可以通过直接数据库连接和SQL注入攻击来启动数据库链接抓取。 更好地了解与这种数据库配置相关的风险变得更加重要。

在本文中，我提供了创建可用于通过直接连接和各种类型的SQL注入对SQL

Server数据库链接执行攻击的实验室环境的说明。 我还提供了数据库链接的一般介绍和如何利用它们的示例。

这些内容应该有助于渗透测试人员，Web开发人员和数据库管理员尝试更好地了解与数据库服务器链接配置错误或过度使用相关的风险。

尽管本文重点介绍SQL Server，但将涵盖的许多概念可以应用于其他数据库平台。

1.2什么是数据库服务器链接？

在我们动手之前，让我们简短的了解一下数据库链接。

微软声称数据库链接可用于“使SQL Server数据库引擎能够针对SQL Server实例之外的OLE DB数据源执行命令”。

这基本上意味着我们可以在数据库级别创建预配置的“链接”，以连接和查询各种数据存储，包括但不限于：

SQL Server

Oracle服务器

访问文件

Excel文件

文本文件

如果你不是数据库管理员，可能不明白为什么有人想要配置数据库链接。 下面我提供了一些我们遇到的常见用例。

查询多个应用程序数据库以了解数据趋势

结合两个数据源的信息，避免数据库平台迁移

在SQL Server实例之间复制数据子集

集中管理数据库服务器配置

通常，数据库管理员使用它们来查询和更新远程异构数据来源

对于有兴趣的人，我列出了在SQL Server上列出数据库链接的两种方法。 默认情况下，您应该看到自己的服务器。 但是，数据访问将被禁用，所以我不相信你可以查询它。

sp_linkeservers

SELECTsrvnameFROMmaster..sysservers

有关更多信息，您可以访问Microsoft的Linked Server页面，网址为http://msdn.microsoft/en-us/ library/ms188279.aspx。

1.3数据库服务器链接如何成为威胁？

尽管数据库链接非常有用，但它们通常配置有过高的权限，可能导致多个数据库和系统的折中。 以下是数据库链接可能出现的风险的简短列表。

过高的权限是很常见的。 大多数DBA使用静态用户名和密码来配置数据库链接，而不是配置数据库链接来继承执行查询的数据库用户的权限。 这意味着当数据库用户查询链接时，查询将使用预配置的链接权限运行，而不是数据库用户的。

默认情况下可以使用Openquery()。

Openquery()是可用于查询链接服务器的本地SQL Server函数。

默认情况下，属于PUBLIC固定服务器角色的任何SQL登录名都可以使用它查询数据库链接。

所以与类似的openrowset（）函数不同，它通常允许所有数据库用户默认查询所有预配置的数据库链接。 下面是一个基本的Openquery示例：

SELECT*FROMopenquery(Server1,'select SYSTEM_USER')

可以嵌套数据库链接查询。

所有数据库链接查询都可以通过openquery（）函数嵌套。 因此，数据库链接可以从数据库服务器到数据库服务器，类似于网页抓取。

在我们的测试期间，我们发现数据库链接抓取可能导致对我们称为“数据库链接网络”的数百个数据库服务器的未经授权的访问。

抓取数据库链接网络时我们遇到的唯一限制是openquery（）函数允许的最大字符数。

基于Microsoft的文档，openquery（）函数具有8000个字符的限制。 以下是通过嵌套数据库链接执行的查询的基本示例：

SELECTsrvnameFROMopenquery(Server1,'SELECT srvname FROM openquery(Server2,''SELECT srvname FROM master..sysservers'')')

可以通过数据库链接执行存储过程。

尽管Microsoft文档声明说，可以盲目执行openquery函数来执行存储过程。

这意味着危险的程序（如xp_cmdshell）可以用于通过多个嵌套数据库链接在操作系统级别上执行任意命令。

这在数据库社区已经有一段时间的社会化，但在信息安全界没有多少讨论。

第2章 设置实验室

本章将介绍如何构建可用于测试常见数据库链接攻击的实验室环境。 总而言之，你将设置三个SQL Server实例，在它们之间创建数据库链接，并安装连接到其中一个实例的Web应用程序。 以下是实验室完成后我们应该能够执行的攻击的概述。

2.1安装三个SQL Server数据库实例

安装名为“server1”，“server2”和“server3”的三个SQL Server命名实例。 这些实例可以安装在单个或多个服务器上。 确保记住每个SQL Server实例的sa帐户密码。 你将需要它们来设置数据库帐户和链接。

安装SQL实例时，请确保设置了以下配置：

1.配置“混合模式”认证。

2.配置所有服务作为LocalSystem运行。

3.为每个实例启用TCP / IP和命名管道。

注意：如果所有实例都配置在同一台服务器上，则可能不需要。

有关更多信息，请访问以下链接：http://msdn.microsoft/en-us/library/bb909712%28v=vs.90%29.aspx

SQL Server 2008下载

http://microsoft/en-us/download/details.aspx?id=1695

SQL Server 2008安装

http://blog.sqlauthority/2008/06/12/sql-server-2008-step-by-step-installation-guide-with-images/

2.2安装SQL Server登录

我已经提供了设置下面所需的SQL Server登录的说明。 但是，如果您需要其他帮助，请点击下面的几个链接来指导您创建数据库登录。

有关更多信息，请访问http://msdn.microsoft/en-us/library/ms189751.aspx

设置SQL Server登录

1.打开SQL Server Management Studio并使用sa帐户登录到所有SQL Server实例。 通过打开三个独立的“对象探测器”，可以同时打开所有三个实例。 使用三个对象浏览器中的查询窗口在其余步骤中执行查询。

2.在server1上使用sysadmin角色创建名为“s1admin”，密码为“s1password”的数据库登录名。 注意：您可能需要禁用密码策略。

CREATELOGIN s1adminWITHPASSWORD='s1password';

GO

EXECmaster..sp_addsrvrolemember @loginame = N's1admin', @rolename = N'sysadmin'

3.在server1上使用默认权限创建名为“s1user”的数据库登录名，密码为“s1password”。

CREATELOGIN s1userWITHPASSWORD='s1password';

4.在server2上使用默认权限创建一个名为“s2user”的数据库登录名，密码为“s2password”。

CREATELOGIN s2userWITHPASSWORD='s2password';

5.在server3上使用默认权限创建名为“s3user”的数据库登录名，密码为“s3password”。

CREATELOGIN s3userWITHPASSWORD='s3password';

6.使用OSQL验证实例和登录是否正确设置。 检查实例是否响应广播请求：

sqlcmd -L

在Windows cmd.exe控制台中，使用对象资源管理器或sqlcmd检查每个实例的数据库登录名是否可以登录：

sqlcmd -U s1admin -P s1password -S .\server1 -Q"select @@version"

sqlcmd -U s1user -P s1password -S .\server1 -Q"select @@version"

sqlcmd -U s2user -P s2password -S .\server2 -Q"select @@version"

sqlcmd -U s3user -P s3password -S .\server3 -Q"select @@version"

2.3安装XP_CMDSHELL

以下是启用可以通过数据库执行本地操作系统命令的xp_cmdshell存储过程的说明。 只能在server1上启用它。

EXECsp_configure'show advanced options',1

RECONFIGURE

go

EXECsp_configure'xp_cmdshell',1

2.4安装SQL Server数据库链接

以下是创建数据库的说明。 有关更多信息，可以在http://msdn.microsoft/en-us/library/ms190479.aspx中找到官方Microsoft页面。 我还建议使用Microsoft SQL Server Management Studio。 它能让人生不那么艰难。

1.使用s2user数据库登录创建一个从server1到server2的数据库链接。 添加数据库链接：

USE [master]

EXECmaster.dbo.sp_addlinkedserver

@server = N'RELATIVESERVERNAMEHERE\SERVER2', @srvproduct=N'SQL Server';

添加将应用于数据库链接的数据库用户：

EXECsp_addlinkedsrvlogin'RELATIVESERVERNAMEHERE\server2','false',NULL,'s2user','s2password';

验证数据库链接已添加以下查询：

selectsrvnamefrommaster..sysservers;

通过向其发出查询来验证数据库链接的工作原理：

select*fromopenquery("RELATIVESERVERNAMEHERE\server2",'select @@servername')

2.使用s3user数据库登录创建一个从server2到server3的数据库链接。 添加数据库链接：

@server = N'RELATIVESERVERNAMEHERE\SERVER3', @srvproduct=N'SQL Server';

添加数据库用户的数据库链接：

EXECsp_addlinkedsrvlogin'RELATIVESERVERNAMEHERE\server3','false',NULL,'s3user','s3password';

select srvname from master..sysservers;

select*fromopenquery("RELATIVESERVERNAMEHERE\server3",'select @@servername')

3.使用s1admin数据库登录创建一个从server3到server1的数据库链接。 添加数据库链接：

@server = N'RELATIVESERVERNAMEHERE\SERVER1',

@srvproduct=N'SQL Server';

EXECsp_addlinkedsrvlogin'RELATIVESERVERNAMEHERE\server1','false',NULL,'s1admin','s1password';

select*fromopenquery("RELATIVESERVERNAMEHERE\server1",'select @@servername')

验证用户是否具有sysadmin权限。 下面的查询应该返回1：

select*fromopenquery("RELATIVESERVERNAMEHERE\server1",'select is_srvrolemember(''sysadmin'')')

4.使用任何凭据在server1，server2和server3上创建一个坏的链接到“BADSERVER”。 此链接旨在模拟不再存在的服务器的链接。

添加数据库链接：

@server = N'BADSERVER',

添加数据库用户的数据库链接。 凭证在这里并不重要，因为这是为了模拟到不再存在的服务器的链接。 所以'baduser'和'badpassword'可以是任何值。

EXECsp_addlinkedsrvlogin'badserver','false',NULL,'baduser','badpassword';

验证访问坏链接的尝试超出预期。

select*fromopenquery("BADSERVER",'select @@version')

2.5安装易受攻击的Web应用程序

1.在Server1上从Microsoft下载并安装AdventureWorks2008_Database.zip数据库。 它可以从http://msftdbprodsamples.codeplex/releases/view/93587下载。

以下是安装步骤的概述。

a. 将AdventureWorks2008_Database.zip解压到c：驱动器上的文件夹

b. 打开Microsoft SQL Server Management Studio并连接到server1实例

c. 右键单击数据库节点

d. 选择附加...

e. 从解压到的路径中选择AdventureWorks2008数据库文件

f. 使数据库登录s1user为AdventureWorks的数据库所有者。

USE AdventureWorks2008;

EXEC sp_changedbowner's1user';

有关更多信息，请访问http://msdn.microsoft/en-us/library/ms178630(v=sql.90).aspx。

2.安装IIS服务器并启用对asp页面的支持。

a. 在Windows上安装IIS：http://howtogeek/howto/windows-vista/how-to-install-iis-on-windows-vista/

b. 启用ASP页面的使用：http://iis/learn/application-frameworks/running-classic-asp-applications-on-iis-

7-and-iis-8 / classic-asp-not-installed-by-default-on-IIS

3.从GitHub下载并安装易受攻击的ASP页面到C：\ Inetpub \ wwwroot。 链接到易受攻击的页面下载列表如下。

employee.asp

https://raw2.github/nullbind/Metasploit-Modules/master/employee.asp

search.asp

https://raw2.github/nullbind/Metasploit-Modules/master/search.asp

4.编辑empolyee.asp和search.asp页面。 将db_server变量更改为您为server1安装的服务器名称和实例。 它应该看起来像下面的例子。

db_server = "mytestserver\server1”

5.启用详细错误消息。 根据您的版本IIS详细错误可能会启用，但是如果他们不是下面的链接应该能够给你一些指导。

http://iis/learn/application-frameworks/running-classic-asp-applications-on-iis-7-and-

iis-8/classic-asp-script-error-messages-no-longer-shown-in-web-browser-by-default

http://blogs.msdn/b/rakkimk/archive/2007/05/25/iis7-how-to-enable-the-detailed-error-

messages-for-the-website-while-browsed-from-for-the-client-browsers.aspx

第3章通过直接数据库连接手动攻击

在本章中，您将使用SQL

Server Management

Studio连接到SERVER1，通过链接爬网将权限升级到sysadmin，并通过xp_cmdshell存储过程在OS级别接管服务器。

在下面的示例中，10.2.9.183中已经安装了所有SQL Server实例。

这是在发现没有sysadmin访问权限的登录后，在内部渗透测试中可能会发现的一个很好的例子。

3.1以用户身份登录Server1

SQL Server Management Studio Express可以作为SQL Server安装的一部分或单独安装。 安装完成后，使用它连接到SERVER1实例，如下所示。 但是，请注意，您的IP地址可能不同。

单击“新建查询”按钮，并键入以下查询以确定s1user是否为系统管理员。

SELECTis_srvrolemember（'sysadmin'）

结果应该类似于下面的截图。

“0”告诉我们，s1user没有sysadmin权限，所以让我们使用下面的查询找到一些爬网链接。

SELECTsrvnamefrommaster..sysservers

结果应该类似于下面的屏幕截图。

它应该显示三个链接的服务器。 “BADSERVER”是一个死链接，“WIN-ODG2LLLUIPN”没有数据访问...

本文英文原文发表于知名技术博客《Highly Scalable Blog》，对NoSQL数据库中的分布式算法和思想进行了详细的讲解。文章很长，由@juliashine 进行翻译投稿。感谢译者的共享精神！

译者介绍：Juliashine是多年抓娃工程师，现工作方向是海量数据处理与分析，关注Hadoop与NoSQL生态体系。

英文原文：《Distributed Algorithms in NoSQL Databases》

译文地址：《NoSQL数据库的分布式算法》

系统的可扩展性是推动NoSQL运动发展的的主要理由，包含了分布式系统协调，故障转移，资源管理和许多其他特性。这么讲使得NoSQL听起来像是一个大筐，什么都能塞进去。尽管NoSQL运动并没有给分布式数据处理带来根本性的技术变革，但是依然引发了铺天盖地的关于各种协议和算法的研究以及实践。正是通过这些尝试逐渐总结出了一些行之有效的数据库构建方法。在这篇文章里，我将针对NoSQL数据库的分布式特点进行一些系统化的描述。

接下来我们将研究一些分布式策略，比如故障检测中的复制，这些策略用黑体字标出，被分为三段：

数据一致性。NoSQL需要在分布式系统的一致性，容错性和性能，低延迟及高可用之间作出权衡，一般来说，数据一致性是一个必选项，所以这一节主要是关于数据复制和数据恢复。

数据放置。一个数据库产品应该能够应对不同的数据分布，集群拓扑和硬件配置。在这一节我们将讨论如何分布以及调整数据分布才能够能够及时解决故障，提供持久化保证，高效查询和保证集群中的资源（如内存和硬盘空间）得到均衡使用。

对等系统。像 leader election 这样的的技术已经被用于多个数据库产品以实现容错和数据强一致性。然而，即使是分散的的数据库（无中心）也要跟踪它们的全局状态，检测故障和拓扑变化。这一节将介绍几种使系统保持一致状态的技术。

数据一致性

众所周知，分布式系统经常会遇到网络隔离或是延迟的情况，在这种情况下隔离的部分是不可用的，因此要保持高可用性而不牺牲一致性是不可能的。这一事实通常被称作“CAP理论”。然而，一致性在分布式系统中是一个非常昂贵的东西，所以经常需要在这上面做一些让步，不只是针对可用性，还有多种权衡。为了研究这些权衡，我们注意到分布式系统的一致性问题是由数据隔离和复制引起的，所以我们将从研究复制的特点开始：

可用性。在网络隔离的情况下剩余部分仍然可以应对读写请求。

读写延迟。读写请求能够在短时间内处理。

读写延展性。读写的压力可由多个节点均衡分担。

容错性。对于读写请求的处理不依赖于任何一个特定节点。

数据持久性。特定条件下的节点故障不会造成数据丢失。

一致性。一致性比前面几个特性都要复杂得多，我们需要详细讨论一下几种不同的观点。 但是我们不会涉及过多的一致性理论和并发模型，因为这已经超出了本文的范畴，我只会使用一些简单特点构成的精简体系。

原子写。假如数据库提供了API，一次写操作只能是一个单独的原子性的赋值，避免写冲突的办法是找出每个数据的“最新版本”。这使得所有的节点都能够在更新结束时获得同一版本，而与更新的顺序无关，网络故障和延迟经常造成各节点更新顺序不一致。 数据版本可以用时间戳或是用户指定的值来表示。Cassandra用的就是这种方法。

原子化的读-改-写。应用有时候需要进行 读-改-写 序列操作而非单独的原子写操作。假如有两个客户端读取了同一版本的数据，修改并且把修改后的数据写回，按照原子写模型，时间上比较靠后的那一次更新将会覆盖前一次。这种行为在某些情况下是不正确的（例如，两个客户端往同一个列表值中添加新值）。数据库提供了至少两种解决方法：

冲突预防。 读-改-写 可以被认为是一种特殊情况下的事务，所以分布式锁或是 PAXOS [20, 21] 这样的一致协议都可以解决这种问题。这种技术支持原子读改写语义和任意隔离级别的事务。另一种方法是避免分布式的并发写操作，将对特定数据项的所有写操作路由到单个节点上（可以是全局主节点或者分区主节点）。为了避免冲突，数据库必须牺牲网络隔离情况下的可用性。这种方法常用于许多提供强一致性保证的系统（例如大多数关系数据库，HBase，MongoDB）。

冲突检测。数据库跟踪并发更新的冲突，并选择回滚其中之一或是维持两个版本交由客户端解决。并发更新通常用向量时钟 [19] （这是一种乐观锁）来跟踪，或者维护一个完整的版本历史。这个方法用于 Riak, Voldemort, CouchDB.

写一致性。分区的数据库经常会发生写冲突。数据库应当能处理这种冲突并保证多个写请求不会被不同的分区所处理。这方面数据库提供了几种不同的一致性模型：

写后读一致性。在数据项X上写操作的效果总是能够被后续的X上的读操作看见。

读后读一致性。在一次对数据项X的读操作之后，后续对X的读操作应该返回与第一次的返回值相同或是更加新的值。

读写一致性。从读写的观点来看，数据库的基本目标是使副本趋同的时间尽可能短（即更新传递到所有副本的时间），保证最终一致性。除了这个较弱的保证，还有一些更强的一致性特点：

现在让我们仔细看看常用的复制技术，并按照描述的特点给他们分一下类。第一幅图描绘了不同技术之间的逻辑关系和不同技术在系统的一致性、扩展性、可用性、延迟性之间的权衡坐标。 第二张图详细描绘了每个技术。

复本因子是4。读写协调者可以是一个外部客户端或是一个内部代理节点。

我们会依据一致性从弱到强把所有的技术过一遍：

（A, 反熵） 一致性最弱，基于策略如下。写操作的时候选择任意一个节点更新，在读的时候如果新数据还没有通过后台的反熵协议传递到读的那个节点，那么读到的仍然是旧数据。（下一节会详细介绍反熵协议）。这种方法的主要特点是：

过高的传播延迟使它在数据同步方面不太好用，所以比较典型的用法是只作为辅助性的功能来检测和修复计划外的不一致。Cassandra就使用了反熵算法来在各节点之间传递数据库拓扑和其他一些元数据信息。

一致性保证较弱：即使在没有发生故障的情况下，也会出现写冲突与读写不一致。

在网络隔离下的高可用和健壮性。用异步的批处理替代了逐个更新，这使得性能表现优异。

持久性保障较弱因为新的数据最初只有单个副本。

（B） 对上面模式的一个改进是在任意一个节点收到更新数据请求的同时异步的发送更新给所有可用节点。这也被认为是定向的反熵。

与纯粹的反熵相比，这种做法只用一点小小的性能牺牲就极大地提高了一致性。然而，正式一致性和持久性保持不变。

假如某些节点因为网络故障或是节点失效在当时是不可用的，更新最终也会通过反熵传播过程来传递到该节点。

（C） 在前一个模式中，使用提示移交技术 [8] 可以更好地处理某个节点的操作失败。对于失效节点的预期更新被记录在额外的代理节点上，并且标明一旦特点节点可用就要将更新传递给该节点。这样做提高了一致性，降低了复制收敛时间。

（D, 一次性读写）因为提示移交的责任节点也有可能在将更新传递出去之前就已经失效，在这种情况下就有必要通过所谓的读修复来保证一致性。每个读操作都会启动一个异步过程，向存储这条数据的所有节点请求一份数据摘要（像签名或者hash），如果发现各节点返回的摘要不一致则统一各节点上的数据版本。我们用一次性读写来命名组合了A、B、C、D的技术- 他们都没有提供严格的一致性保证，但是作为一个自备的方法已经可以用于实践了。

（E, 读若干写若干） 上面的策略是降低了复制收敛时间的启发式增强。为了保证更强的一致性，必须牺牲可用性来保证一定的读写重叠。 通常的做法是同时写入W个副本而不是一个，读的时候也要读R个副本。

首先，可以配置写副本数W>1。

其次，因为R+W>N，写入的节点和读取的节点之间必然会有重叠，所以读取的多个数据副本里至少会有一个是比较新的数据（上面的图中 W=2, R=3, N=4 ）。这样在读写请求依序进行的时候（写执行完再读）能够保证一致性（对于单个用户的读写一致性），但是不能保障全局的读一致性。用下面图示里的例子来看，R=2，W=2，N=3，因为写操作对于两个副本的更新是非事务的，在更新没有完成的时候读就可能读到两个都是旧值或者一新一旧：

对于某种读延迟的要求，设置R和W的不同值可以调整写延迟与持久性，反之亦然。

如果W<=N/2，并发的多个写入会写到不同的若干节点（如，写操作A写前N/2个，B写后N/2个）。 设置 W>N/2 可以保证在符合回滚模型的原子读改写时及时检测到冲突。

严格来讲，这种模式虽然可以容忍个别节点的失效， 但是对于网络隔离的容错性并不好。在实践中，常使用”近似数量通过“这样的方法，通过牺牲一致性来提高某些情景下的可用性。

（F, 读全部写若干）读一致性问题可以通过在读数据的时候访问所有副本（读数据或者检查摘要）来减轻。这确保了只要有至少一个节点上的数据更新新的数据就能被读取者看到。但是在网络隔离的情况下这种保证就不能起到作用了。

（G, 主从） 这种技术常被用来提供原子写或者 冲突检测持久级别的读改写。为了实现冲突预防级别，必须要用一种集中管理方式或者是锁。最简单的策略是用主从异步复制。对于特定数据项的写操作全部被路由到一个中心节点，并在上面顺序执行。这种情况下主节点会成为瓶颈，所以必须要将数据划分成一个个独立的片区（不同片有不同的master），这样才能提供扩展性。

（H, Transactional Read Quorum Write Quorum and Read One Write All） 更新多个副本的方法可以通过使用事务控制技术来避免写冲突。 众所周知的方法是使用两阶段提交协议。但两阶段提交并不是完全可靠的，因为协调者失效可能会造成资源阻塞。 PAXOS提交协议 [20, 21] 是更可靠的选择，但会损失一点性能。 在这个基础上再向前一小步就是读一个副本写所有副本，这种方法把所有副本的更新放在一个事务中，它提供了强容错一致性但会损失掉一些性能和可用性。

上面分析中的一些权衡有必要再强调一下：

一致性与可用性。 严密的权衡已经由CAP理论给出了。在网络隔离的情况下，数据库要么将数据集中，要么既要接受数据丢失的风险。

一致性与扩展性。 看得出即使读写一致性保证降低了副本集的扩展性，只有在原子写模型中才可以以一种相对可扩展的方式处理写冲突。原子读改写模型通过给数据加上临时性的全局锁来避免冲突。这表明， 数据或操作之间的依赖，即使是很小范围内或很短时间的，也会损害扩展性。所以精心设计数据模型，将数据分片分开存放对于扩展性非常重要。

一致性与延迟。 如上所述，当数据库需要提供强一致性或者持久性的时候应该偏向于读写所有副本技术。但是很明显一致性与请求延迟成反比，所以使用若干副本技术会是比较中允的办法。

故障转移与一致性/扩展性/延迟。有趣的是容错性与一致性、扩展性、延迟的取舍冲突并不剧烈。通过合理的放弃一些性能与一致性，集群可以容忍多达 up to 的节点失效。这种折中在两阶段提交与 PAXOS 协议的区别里体现得很明显。这种折中的另一个例子是增加特定的一致性保障，比如使用严格会话进程的“读己所写”，但这又增加了故障转移的复杂性 [22]。

反熵协议， 谣言传播算法

让我们从以下场景开始：

有许多节点，每条数据会在其中的若干的节点上面存有副本。每个节点都可以单独处理更新请求，每个节点定期和其他节点同步状态，如此一段时间之后所有的副本都会趋向一致。同步过程是怎样进行的？同步何时开始？怎样选择同步的对象？怎么交换数据？我们假定两个节点总是用较新版本的数据覆盖旧的数据或者两个版本都保留以待应用层处理。

这个问题常见于数据一致性维护和集群状态同步（如集群成员信息传播）等场景。虽然引入一个监控数据库并制定同步计划的协调者可以解决这个问题，但是去中心化的数据库能够提供更好的容错性。去中心化的主要做法是利用精心设计的传染协议[7]，这种协议相对简单，但是提供了很好的收敛时间，而且能够容忍任何节点的失效和网络隔离。尽管有许多类型的传染算法，我们只关注反熵协议，因为NoSQL数据库都在使用它。

反熵协议假定同步会按照一个固定进度表执行，每个节点定期随机或是按照某种规则选择另外一个节点交换数据，消除差异。有三种反风格的反熵协议：推，拉和混合。推协议的原理是简单选取一个随机节点然后把数据状态发送过去。在真实应用中将全部数据都推送出去显然是愚蠢的，所以节点一般按照下图所示的方式工作。

节点A作为同步发起者准备好一份数据摘要，里面包含了A上数据的指纹。节点B接收到摘要之后将摘要中的数据与本地数据进行比较，并将数据差异做成一份摘要返回给A。最后，A发送一个更新给B，B再�...

由 Tomi Mester 写于2017年5月9日

本文转载自https://data36/sql-for-data-analysis-tutorial-beginners/

如果你想成数据分析师或是数据科学家，那么 SQL 是必须的。在过去的几年中，我已经与许多公司有线业务合作 - 从 5 人创业公司到 5000 多名员工跨国公司，并没有看到一家不会以某种方式使用SQL进行数据分析（以及更多的事情）的公司。

SQL简单并且容易理解。因而，不只是工程师、开发者、数据分析师或数据科学家可以使用它，而是任何愿意花费几天来学习和练习它的人。

我已经创建了这个 SQL 系列，成为有志的数据分析师和数据科学家的最实用和多动手的 SQL 教程。如果你从没有接触过编码、编程、查询，它将从头开始，这并不会是一个问题！

让我们开始吧！

注意：因为这是一个实用教程，我鼓励你和我一起编码。如果你这样做，你将需要一个数据服务器来练习。所以我建议你先通过阅读这篇文章。

利用SQL进行数据分析？这是什么？为什么好呢？

SQL代表『结构化查询语言』。 但是我喜欢描述它为「加强版的 Excel」。下面是同一份数据在 Excel 和 SQL:

如你所见，SQL 和 Excel 十分相似。它们用具有行和列的二维列表展示数据。两者都非常结构化，非常透明。

不过也有差异。作为初学者，你应该知道两件关于 SQL 的重要事情：

1.第一是性能。虽然 Excel 在小数据集中表现很棒，但是当你使用不同的公式处理 100 k+ 行的文件，它变得十分缓慢和低效。在 SQL 中，甚至超过 10 M+ 行的文件可以很快的处理。（例如，在接下来的利用 SQL进行数据分析文章中，我们将使用一个 7 M+ 行的数据集。）

2.第二是怎样访问你的数据。Excel 主要是图形用户界面（也成为 GUI）。你可以滚动、敲入公式、用光标选择你的范围等等，非常方便。在 SQL 中，你没有这样的界面，你必须使用称为『SQL 查询』来代替。看下面的gif：

在 Excel 中求和

在 SQL 中求和

因为第一次看到这个，可能感觉到麻烦。但是相信我，一旦你了解 SQL 基础知识，你会发现它比 Excel 更加清晰、更有效率。只提两个有点：在 SQL 中，连接表比 Excel 更简单。同样，使用 SQL 自动化和重复使用脚本也将会变得更加方便。

当你使用 SQL 进行数据分析，你将会使用它（最有可能）进行简单任务：聚合事情、连接数据集、使用简单的统计和数学方法。但是，你可以比以前更有效率执行这些任务和更大的数据集。

Python，R 和 bash怎么样？

如果你已经完成了我之前的 bash 教程 或者你已经参加一些我的在线直播,你可能会问：SQL 比 Python，R 或者 bash 好还是坏？答案是...好吧，这个问题没有明确答案！

一旦你开始在实际生活的数据项目中应用这些语言，你将看到 Python 和 R 在某些方面是表现好的，而 SQL 在其他方面表现好。主要是语法、功能、性能不同...但是，我现在不想进入该主题，因为：

a）我承诺一个使用教程，其主题有点理论（甚至有点哲学）。

b）它还有点先进的东西。

c）在这个水平上，你完全不需要担心它的性能，并且你有可能得到资深数据科学家或者数据工程师的帮助。

注意：如果你真的很好奇，我给你~1小时在这里比较 Python,R,SQL 和 bash。

但是现在，让我们试试吧！

STEP 0 - 安装你自己的 SQL 环境来练习！

注意：如果你已经有自己的 SQL 环境，你可以跳到STEP-获得数据！

首先，请仔细阅读这篇文章。它是关于如何一步一步设置你自己的数据服务和安装 bash,Python,R 和 SQL 的教程。

注意：在我的『利用 SQL 进行数据分析』文章中，我将使用 postgreSQL！还有其他类型的 SQL 语言（例如，另一个众所周知的开源语言 mySQL）。好消息是，所有的 SQL 语言都非常相似 -- 如果你学习 postgreSQL，则需要几个小时（甚至几分钟）才能适应另一个。我选择了 postgreSQL，因为它很受欢迎（许多在线企业都在使用它），同时它被称为最先进的开源 SQL 语言。

如果你通过上述链接的文章，请仔细检查，如果你有以下3件事情：

1.具有终端（或 iTerm）访问权限的数据服务器。

2.PostgreSQL 安装在你的数据服务器上。

3.你的计算机上的Pgadmin4（或SQL Workbench）。

如果缺少某些内容，请再次阅读：安装 bash，Python，R 和 SQL!

注意：作为一个 SQL 查询工具，相比于 pgadmin4，我更喜欢 SQL Workbench，但这是一种品味的问题。在我的利用 SQL 进行数据分析教程中，我将使用 SQL Workbench（以下是有关如何在计算机上安装它的教程），但你可以随意使用 pgadmin4。这不会有什么区别！

STEP 1 – 通过命令行登录你的 SQL 数据库!

太好了！你将在一分钟内编写你的第一个 SQL 查询！首先从命令行访问你的 SQL 数据库！你已经做了一次，我们将重复这个过程：

1.打开终端（或 iTerm）。

2.ssh到你的数据服务器

在我的环境下，我输入：

ssh tomi@[myipadress]

3.一旦我登录服务器，我想访问我的 postgreSQL 数据库。因为我已经访问了我的用户，我只需要键入这个命令：

psql -U tomi -d postgres- psql

是命令本身以及

-U

指定你的用户名（我的为「tomi」），以及

-d

指定你的数据库的名称（我的是 postgres 同样适用于你）。你的提示应改为：

postgres=>

完成！你可以完全访问你的 SQL 数据库！ 4.作为测试，键入：

\dt

这会列出你所有的数据表。到目前为止，你只有一个数据表，但是这将会马上改变！

再次提醒！如果缺少某些东西或者不能运行，请再次阅读：安装 bash，Python，R 和 SQL!

STEP 2 - 获得数据！

在本教程中，我们将使用一个非常小的数据集，称为 zoo。

您可以从这里以原始 .tsv 格式下载，你可以在 Excel 中操作它。如果您已经完成了bash 教程，或者在bash /command line。

但是，继续使用SQL：

1）

创建一个表

，我们可以向表中载入数据。在我的后续教程中，我将详细解释这里发生了什么，但现在，你只需将这几行代码复制粘贴到终端中：

CREATE TABLE zoo (

2）仔细检查表格是否已创建：

我们有新的数据表：zoo！

3）载入数据！

再次说明：我稍后会解释，但是现在，只是复制粘贴这个 SQL 查询：

INSERT INTO zoo (animal,uniq_id,water_need) VALUES

如果一切正常，你会得到这条提醒：

INSERT 0 22

最重要的 SQL 语句：SELECT

是时候学习最基本的 SQL 语句。这是：

SELECT * FROM table_name;

每当你在数据上使用读取，过滤，转换，聚合或执行任何操作，你将会使用 SELECT。在这系列教程中,90％的内容将会显示出来，以某种方式修改单一查询。作为开始，我们从 zoo 数据表中 SELECT 任意内容。

SELECT * FROM zoo;

我已经得到完整的可读格式的表。（如果要从这个视角中退出，请在键盘上按Q键。）

我想它甚至不需要一个解释 -- 因为语法本身非常接近英语 - 但是为了以防万一：

SELECT

是主语句，它告诉 SQL 我们想从我们的数据表中读取一些东西。

*

通常是指「一切」 -- 在这种情况下，这意味着我们想选择每一列。

FROM

告诉 SQL，我们要从指定的表中读取数据。

zoo

是表的名称。如果你有更多的表，您可以将其替换为任何其他表名。

;

这是一个 SQL 特定的语法。每个查询都应该用分号关闭。如果您不小心错过了，SQL 将期望您继续查询，不会在屏幕上返回任何内容。

从现在起，你将会使用这种语法。

SELECT columns

你可以用实际的列名替换 * 字符。尝试这个查询：

SELECT animal, water_need FROM zoo;

（如果要从此视图中退出，请在键盘上按Q键。）

确切地说，你所期望的是：你的屏幕上列出了「animal」和「waterneed」列，而不是「uniqid」。

这样可以 SELECT 任何列。你只需要用逗号分隔指定的列名。

你也可以这样做：

SELECT animal, animal, animal FROM zoo;

你会看到同一列多次...但是，因为这是没有意义的，我建议只是根本不这样做。

显示数据的前几行 -- LIMIT 从句

现在我们正在使用 22 行的数据表。在「利用 SQL 进行数据分析」系列的下一集中，我们将使用一个 7 M+ 行数据文件。这是一个很大的变化，在这种情况下，每次都在屏幕上打印所有数据是不合理的，而是获取前几行的小样本。为此，使用 LIMIT 从句 - 这是之前介绍的「基础查询」顶部的一个小「扩展」：

SELECT * FROM zoo LIMIT 10;

这将仅打印前10行。（记住它是如何在 bash 中完成的？这是

head -10

命令。）

当然，你可以决定要打印多少行，并在 LIMIT 之后指定！

过滤指定行 -- WHERE从句

基于数值，你可以用 WHERE 从句选择特定的行。例如：

SELECT * FROM zoo WHERE animal = 'elephant';

SELECT * FROM zoo

-这是「基础查询」

WHERE

- 这告诉 SQL 你想过滤一些东西

animal = 'elephant'

- animal 是你正在寻找给定值的列名。而 elephant 是值本身。在 SQL 中，你必须添加列名，寻找值。

- 永远不要忘记分号！

在利用 SQL 进行数据分析系列的下一集中，我将详细介绍如何充分利用 WHERE！现在，如果你知道这是为了过滤行，便足够了。

自我测试＃1

这是一个介绍性的文章，所以这第一个任务也将是相当容易的：

从 zoo 表中选择前 3 个 zebra！

该解或多或少是本文的总结！

. . . 准备？

这是我的解决方案：

SELECT * FROM zoo WHERE animal = 'zebra' LIMIT 3;

SQL 很容易，对吧？

还有一件事：语法...

稍后，我将向你展示一些最佳实践，以及如何保持 SQL 查询的清洁和高效，但在第一篇文章中，我仅强调两件事情：

所有的查询都应以分号（;）结尾。如果你不小心错过了，SQL 将期望您继续查询，不会在屏幕上返回任何内容。例如：

当谈到SQL关键字（SELECT，WHERE，LIMIT等），SQL 并不区分大小写。例如：

postgres=> SELECT * FROM zoo;

postgres=> SELECT * FROM zoo

postgres->

这不是很好...这样更好：

SELECT * FROM ZOO WHERE ANIMAL = 'ELEPHANT';

– DOES NOT WORK

SELECT * FROM ZOO WHERE ANIMAL = 'elephant';

– WORKS

select * from zoo;

表名，列名和值的区分大小写是你设置的问题。在我们当前的设置（使用postgreSQL）中，表名和列名不区分大小写，但是字段值是。例如。

工作原理如下：

请注意，按照惯例上大多数人使用大写 SQL 关键字（SELECT，WHERE，LIMIT等）。它有助于阅读你的代码。

结论

相当不错的学习和使用 SQL 进行数据分析！恭喜！现在你可以写你的第一个非常基本的 SQL 查询...但这只是开始！让我们继续下一集，并学习如何使用 WHERE 从句从数据集中过滤东西。

如果你不想错过下一集,并且也想获得有关即将到来的网络研讨会的信息，请订阅我的每周通讯

MySQL简介

MySQL是最流行的开放源码 SQL数据库管理系统，它是由 MySQL AB 公司开发、发布并支持的。它的插入式存储引擎可以让使用者根据实际应用使用不同的存储。

生产环境中应：

MySQL源码安装+LVM逻辑卷

虚拟机测试环境，添加一块硬盘

重启电脑 创建一个分区

[root@localhost6 ~]# fdisk /dev/sdb

Device Boot Start End Blocks Id System

/dev/sdb1 1 3917 31463271 8e Linux LVM [root@localhost6 ~]# partprobe /dev/sdb #安装程序是parted

创建一个卷组 VG

查看卷组vgdisplay mysqlvg

[root@localhost6 ~]# vgcreate mysqlvg /dev/sdb1

No physical volume label read from /dev/sdb1

Physical volume /dev/sdb1 not found

Physical volume "/dev/sdb1" successfully created

Volume group "mysqlvg" successfully created

创建一个逻辑卷 LV

[root@localhost6 ~]# lvcreate -L 10G -n mysqllv mysqlvg

Logical volume "mysqllv" created

lvdisplay /dev/mysqlvg/mysqllv --查看

[root@localhost6 ~]# lvextend -L +3G /dev/mysqlvg/mysqllv 动态增加3G空间

Extending logical volume mysqllv to 13.00 GiB

Logical volume mysqllv successfully resized

格式化逻辑卷并挂载

[root@localhost6 ~]# mkfs -t ext4 /dev/mysqlvg/mysqllv

[root@localhost6 ~]# mkdir -p /usr/local/mysql/data

[root@localhost6 ~]# mount /dev/mysqlvg/mysqllv /usr/local/mysql/data

实现开机自动挂载

[root@localhost6 ~]# vim /etc/fstab

/dev/mysqlvg/mysqllv /usr/local/mysql/data ext4 defaults 0 0

[root@localhost6 ~]# mount -a

安装MySQL数据库准备

[root@localhost6 ~]# ls

cmake-2.8.12.tar.gz mysql-5.7.2-m12.tar.gz

安装前确认主从服务器时间一致，全部配置时间同步服务器

ntpdate 202.112.31.197 中国教育网的时间

安装cmake

[root@localhost6 ~]# tar xf cmake-2.8.12.tar.gz -C /usr/src/

[root@localhost6 ~]# cd /usr/src/cmake-2.8.12/

[root@localhost6 cmake-2.8.12]# ./configure && gmake && gmake install

安装MySQL

[root@localhost6 ~]# tar xf mysql-5.7.2-m12.tar.gz -C /usr/src/

[root@localhost6 ~]# cd /usr/src/mysql-5.7.2-m12/

[root@localhost6 mysql-5.7.2-m12]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DSYSCONFDIR=/etc && make && make install

[root@localhost6 mysql-5.7.2-m12]# cp support-files/my-defaultf /etc/myf

cp：是否覆盖"/etc/myf"？ y

[root@localhost6 mysql-5.7.2-m12]# cp support-files/mysql.server /etc/init.d/mysqld

[root@localhost6 mysql-5.7.2-m12]# chmod +x /etc/init.d/mysqld

[root@localhost6 mysql-5.7.2-m12]# chkconfig --add mysqld

[root@localhost6 mysql-5.7.2-m12]# chkconfig --list mysqld

mysqld 0:关闭1:关闭2:启用3:启用4:启用5:启用6:关闭

[root@localhost6 mysql-5.7.2-m12]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile

[root@localhost6 mysql-5.7.2-m12]# . /etc/profile

[root@localhost6 mysql-5.7.2-m12]# groupadd mysql

[root@localhost6 mysql-5.7.2-m12]# useradd -M -s /sbin/nologin -g mysql mysql

[root@localhost6 mysql-5.7.2-m12]# chown -R mysql:mysql /usr/local/mysql/

[root@localhost6 mysql-5.7.2-m12]# /usr/local/mysql/scripts/mysql_install_db --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data --user=mysql

[root@localhost6 mysql-5.7.2-m12]# service mysqld start

Starting MySQL. SUCCESS!

[root@localhost6 mysql-5.7.2-m12]# mysqladmin -u root password '123123'

[root@localhost6 mysql-5.7.2-m12]# mysql -u root -p123123

在实际生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性，高可用还是高并发等各个方面都不能完全满足实际需求的，因此一般来说都是通过主从复制（Master-Slave）的方式来同步数据，在通过读写分离来提升数据的高并发负载能力这样的方案来进行部署。

MYISAM 读写操作相互 互斥 （磁盘的IO限制，读与写不能同时进行）

MySQL Replication 俗称MySQL AB复制（主从，双机热备），从库以一定的频率去读取主库的二进制日志文件，按照日志中记录对从库进行同样的操作，以达到同步效果。

MySQL 支持的复制类型

基于语句的复制在主服务器上执行的SQL语句，在从服务器上执行同样的语句，MySQL默认采用基于语句的复制，效率比较高

基于行的复制把改变的内容复制过去，而不是把命令在从服务器上执行一遍

混合类型的复制默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用行

mysql 5.6 以后多了一个GTIDS复制

复制的工作过程

1.主库在每个事务更新完数据完成之前，Master在二进制日志（Binary log，binlog）记录这些改变，写入二进制日志完成后，Master通知存储引擎提交事务

2.Slave将Master的Binary log复制到其中中继日志，首先Slave开始一个工作线程-I/O线程在Master上打开一个普通的连接，然后开始Binlog dump process,Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它会催眠并等待Master产生新的事件，I/O线程将这些事件写入中继日志。

3.SQL Slave Thread（SQL从线程）处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新Slave的数据，使其与Master中的数据一致，只要该线程与I/O线程保持一致，中继日志通常会在OS的缓存中，所以中继日志的开销很小。

复制过程的限制

1.即复制在Slave上是串行化的，也就是说Master上的并行更新不能在Slave上并行操作

2.MYSQL的版本都要高于3.2，还有一个基本的原则就是作为从数据库的数据库版本可以高于主服务器数据库的版本，但是不可以低于主服务器的数据库版本

从库以一定的频率去读取主库的二进制日志文件，按照日志中记录对从库进行同样的操作，以达到同步效果。

MySQL Master IP: 192.168.8.60

MySQL Slave IP: 192.168.8.70

Amoeba IP: 192.168.8.80

每台机器操作：

[root@localhost6 ~]# service iptables stop

iptables：将链设置为政策 ACCEPT：filter [确定]

iptables：清除防火墙规则： [确定]

iptables：正在卸载模块： [确定]

[root@localhost6 ~]# setenforce 0

setenforce: SELinux is disabled

[root@localhost6 ~]# service sshd restart

停止 sshd： [确定]

正在启动 sshd： [确定]

配置mysql master服务器

[root@localhost6 ~]# vim /etc/myf

[mysqld]

server-id=1 依次设置server-id号

log-bin=mysql-binlog

log-slave-updates=true

[root@localhost6 ~]# /etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@localhost6 ~]# /etc/init.d/mysqld start

创建Replication用户

mysql> grant replication slave on *.* to 'myslave'@'192.168.8.%' identified by '123123';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

replication slave：用于复制型从属服务器（从主服务器中读取二进制日志事件）

获得Master DB的相关信息

mysql> show master status;

+---------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

| mysql-binlog.000001 | 428 | | | |

1 row in set (0.00 sec)

供Slave DB连接时使用，记录下File和Position的值

如果一个数据库里面有数据，现在要加一个从服务器需将数据一致后再配置服务器AB复制

mysqldump -u root -p123123 --all-databases > /root/alldbbackup.sql 导出数据

scp /root/alldbbackup.sql 传到从数据库

mysql -u root -p < /root/alldbbackup.sql 导入备份

从服务器slave配置MySQL配置文件

[root@localhost7 ~]# vim /etc/myf

server-id=2

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

[root@localhost7 ~]# /etc/init.d/mysqld stop

[root@localhost7 ~]# /etc/init.d/mysqld start

mysql> stop slave;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='192.168.8.60', MASTER_USER='myslave', MASTER_PASSWORD='123123', MASTER_LOG_FILE='mysql-binlog.000001', MASTER_LOG_POS=428;

Query OK, 0 rows affected, 2 warnings (0.17 sec)

参数说明：

mysql> CHANGE MASTER TO

-> MASTER_HOST='master_host_name', //主服务器的IP地址

-> MASTER_USER='replication_user_name', //同步数据库的用户

-> MASTER_PASSWORD='replication_password', //同步数据库的密码

-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记录的参数)

-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记录的参数)

mysql> start slave;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.8.60

Master_User: myslave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-binlog.000001

Read_Master_Log_Pos: 428

Relay_Log_File: relay-log-bin.000002

Relay_Log_Pos: 286

Relay_Master_Log_File: mysql-binlog.000001

Slave_IO_Running: Yes 显示为两个Yes即配置与连接没问题

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 428

Relay_Log_Space: 457

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID: 39dc0a9f-96b5-11e7-aeee-000c292a38b6

Master_Info_File: /usr/local/mysql/data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp: 如果上面显示的不是两个Yes，这里会显示哪里出了

Last_SQL_Error_Timestamp: 问题，网络、IO···

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

在主master数据库上创建一个数据库testdb，在从slave数据库上进行查看

mysql> create database testdb;

Query OK, 1 row affected (0.01 sec)

从数据库有，即主从同步（AB复制）数据库已完成

报错解决方法：

ERROR 1201(HY000):Could now initialize master info structure; more error messages can be found in the MySQL error log

stop slave;

reset slave;

CHANGE MASTER TO MASTER_HOST='192.168.8.60', MASTER_USER='slave', MASTER_PASSWORD='123123',MASTER_LOG_FILE='mysql-binlog.000003', MASTER_LOG_POS=246;

start slave;

停止slave角色：

重置slave角色：

mysql> reset slave;

启动slave角色：

数据不同步解决办法：

mysql> set global sql_slave_skip_counter=1;

主从数据库相关命令：

slave stop; slave start; 开始停止从数据库。

show slave status\G显示从库状态信息

show master status\G显示主库状态信息

purge master logs to ’binlog.000004’; 此命令非常小心，删除主数据库没用的二进制日志文件。如果误删除，那么从库就没有办法自动更新了。

change master；从服务器上修改参数使用

另外，如果你当前操作的从库以前曾经与其他服务器建立过主从关系，你可能会发现即使你在myf文件中即便更改了主服务器的位置，但是MSQL仍然

在试图连接就旧的主服务器的现象。发生这种问题的时候，我们可以通过清除master.info这个缓存文件或者在mysql中通过命令来进行设置。方式如下：

删除master.info方法

这个文件位于数据文件存放目录里,可以直接将其删除，然后重新启动服务器。

b、mysql命令方法

如果你不方便重新启动服务器的话，那么就只能使用mysql命令来帮助你做到。

首先登录到主服务器上，查看当前服务器状态：

mysql> show master status\G;

+---------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

| mysql-bin.003 | 73 | test | manual,mysql |

1.FLUSH TABLES WITH READ LOCK

这个命令是全局读锁定，执行了命令之后所有库所有表都被锁定只读。一般都是用在数据库联机备份，这个时候数据库的写操作将被阻塞，读操作顺利进行

解锁的语句是unlock tables

2.LOCK TABLES tbl_name [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}

这个命令是表级别的锁定，可以定制锁定某一个表。例如： lock tables test read; 不影响其他表的写操作。

解锁语句也是unlock tables。

这两个语句在执行的时候都需要注意个特点,在退出mysql终端的时候都会隐式的执行unlock tables。也就是如果要让表锁定生效就必须一直保持对话

P.S. MYSQL的read lock和wirte lock

read-lock: 允许其他并发的读请求，但阻塞写请求，即可以同时读，但不允许任何写。也叫共享锁

write-lock: 不允许其他并发的读和写请求，是排他的(exclusive)。也叫独占锁

3. flush table tablename

目前较为常见的MySQL读写分离有两种：

1.基于程序代码内部实现

在代码中根据select,insert进行路由分类，这类方法也是目前生产环境应用最广泛的，优点是性能好，因为在程序代码中实现，不需要曾加额外的设备作为硬件开支，缺点是需要开发人员来实现，运维人员无从下手。

2.基于中间代理层实现

    SQL Server 提供一个称为 bcp 的流行的命令提示符实用工具，用于将数据从一个表移动到另一个表（表既可以在同一个服务器上，也可以在不同服务器上）。SqlBulkCopy 类允许编写提供类似功能的托管代码解决方案。还有其他将数据加载到 SQL Server 表的方法（例如 INSERT 语句），但相比之下 SqlBulkCopy 提供明显的性能优势。    使用 SqlBulkCopy 类只能向 SQL Server 表写入数据。但是，数据源不限于 SQL Server；可以使用任何数据源，只要数据可加载到 DataTable 实例或可使用 IDataReader 实例读取数据。    下面分享如何使用实现批量导入信息资料功能。

1. 信息资料数据表设计

2. WinForm 程序设计Winform 程序 采用VS2008 C#语言开发。界面设计为工具栏、查询条件、数据列表及状态栏。

3. 数据导入界面设计数据导入选择导入数据文件，经解释正确后，进行确定，导入数据。

4. 选择CSV数据文件进行解释    本示例使用CSV文本文件导入。4.1. CSV 数据文件内容

4.2. CSV数据导入解释方法

5. 使用导入数据

    至此，导入数据分享完毕，几万行数据，可在几秒内瞬间导入。从中我们也发现，想要达到这种速度，是有很多条件的。比如程序不会针对每一行数据进行数据有效性验证；导入的数据列必须与数据库中的数据表设计完全一致。所以一般只在数据转移、信息系统升级中常用。

摘要：复制原理：Mysql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（INSERT,UPDATE,DELETE,ALTER TABLE,GRANT等等）。主从复制...

复制原理：

Mysql中有一种日志叫做bin日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL语句（INSERT,UPDATE,DELETE,ALTER TABLE,GRANT等等）。

主从复制的原理其实就是

主服务器将改变记录到二进制日志(binlog)中，从服务器将主服务器的binlog拷贝到它的中继日志(relaylog)，然后重做中继日志中的事件，在本服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

环境说明：

两台服务器，192.168.1.1（当做master），192.168.1.2（当做slave）

一、配置二进制日志文件：

在master服务器上：

1：编辑主节点配置文件

vim /etc/myf

2.启用二进制日志文件，添加以下配置

server-id = 1log-bin = mysql-binbinlog-format = mixedserver-id = 1 //给服务器起一个独特的ID

log-bin = mysql-bin //声明 二进制日志文件为 mysql-bin.XXXX

binlog-format = mixed //设置二进制复制模式，可选值有(mixed/statement/row)

在slave服务器上：

1：编辑主节点配置文件

vim /etc/myf

2.启用二进制日志文件，添加以下配置

server-id = 2log-bin = mysql-binbinlog-format = mixedrelay-log = relay-log

server-id = 2 //给服务器起一个独特的ID

log-bin = mysql-bin //声明 二进制日志文件为 mysql-bin.XXXX

binlog-format = mixed //设置二进制复制模式，可选值有(mixed/statement/row)

relay-log = relay-log //声明 relay-log日志文件为 relay-log.XXXX

3重启mysql：

service mysqld start二、建立主从连接

1、在master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master。进行复制操作的用户会授予REPLICATION SLAVE权限。用户名的密码都会存储在文本文件master.info中，命令如下：

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO backup@'192.168.1.2' IDENTIFIED BY '1234';说明：建立一个帐户backup，并且只能允许从192.168.1.2这个地址上来登陆，密码是1234。

主服务器配置完成.

注意：确保主服务器的iptables没有阻断3306的访问端口。

2.在slave服务器上执行以下命令，连接master服务器：

CHANGE MASTER TO MASTER_HOST='192.168.1.1',MASTER_USER='backup',MASTER_PASSWORD='1234',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=4;说明：MASTER_LOG_FILE是指定读取主服务器的哪个binlog日志文件，MASTER_LOG_POS是指定从该日志的哪个位置开始读，这两个选项要从master服务器上运行命令：SHOW MASTER STATUS; 查看

查看slave的设置是否正确:

SHOW SLAVE STATUSSlave_IO_State:Master_Host: server1Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 4Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos: 4Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: NoSlave_SQL_Running: No ...omitted...Seconds_Behind_Master: NULLSlave_IO_State, Slave_IO_Running, 和Slave_SQL_Running是No，表明slave还没有开始复制过程。

在slave上启动复制线程 ：

START SLAVE;可以运行 SHOW SLAVE STATUS 查看输出结果：

Slave_IO_State: Waiting for master to send eventMaster_Host: server1Master_User: replMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 164Relay_Log_File: mysql-relay-bin.000001Relay_Log_Pos: 164Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes...omitted...Seconds_Behind_Master: 0当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常。

至此，mysql的主从复制配置完成。

3.实际测试

登录192.168.1.1主MySQL，添加一张数据表，并添加数据，然后登陆192.168.1.2从MySQL，发现刚才新建的表和数据都出现了，主从复制的配置就是如此的简单。

“复制”作为MySQL原生的数据同步功能，在MySQL高可用架构中起着至关重要的作用。本文梳理了UCloud在运维本公司MySQL高可用产品UDB过程中遇到的复制问题，并总结了当复制发生异常时，排查复制异常的方法。

本文承接上一篇文章，通过收集到的相关复制信息和错误信息，针对几种常见的复制错误进行分析。

常见复制错误原因及分析过程

在收集到上述复制相关信息和错误信息后，需要根据实际的误信息进行分析，这里罗列了几种常见的复制错误，可以通过部分或者全部在前述章节收集的相关信息，分析出复制错误发生原因。

1. 从库执行语句与主库冲突

1.1 错误原因

从库执行DML语句或者DDL语句后，主库和从库会出现数据不一致的情况，从而导致主库执行的语句在从库没有办法正常执行。

1.2 错误信息

由于从库执行与主库冲突的语句而导致复制错误，常见错误信息如下：

创建库或者表失败017-04-18T07:52:40.440723Z 6 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:2' at master log binlog001.000001, end_log_pos 493; Error 'Can't create database 'mydb4'; database exists' on query. Default database: 'mydb4'. Query: 'create database mydb4', Error_code: 1007插入语句主键冲突2017-04-18T07:57:35.975310Z 10 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1' at master log binlog001.000001, end_log_pos 378; Could not execute Write_rows event on table mydb.test; Duplicate entry '10' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 378, Error_code: 1062删除语句找不到对应的语句[ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1' at master log binlog001.000001, end_log_pos 506; Could not execute Delete_rows event on table mydb.I1; Can't find record in 'I1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log FIRST, end_log_pos 506, Error_code: 1032

由于这是比较常见的原因，所有导致主从冲突的操作均会导致复制出错，这里不再一一列举。

1.3 原因分析过程

这里以“由于数据库存在而导致创建数据库出错”为例来分析原因。

1.3.1 查看 error log

Error log中显示的详细错误信息如下：2017-04-18T08:22:26.885713Z 16 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:6' at master log binlog001.000001, end_log_pos 1710; Error 'Can't create database 'mydb10'; database exists' on query. Default database: 'mydb10'. Query: 'create database mydb10', Error_code: 1007

显示在执行GTID 0c1b77a7-c113-11e6-9bd6-d4ae52a34783:6时失败。错误原因在于数据库已经存在，无法创建。

1.3.2 查看 show slave status

当错误发生后，查看show slave status显示的信息时，会发现如下信息：Master_UUID: 0c1b77a7-c113-11e6-9bd6-d4ae52a34783 Retrieved_Gtid_Set: 0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1-5 Executed_Gtid_Set: 0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1-5, 3a169e6c-f1d0-11e6-bb30-d4ae52a34783:1

在Executed_Gtid_Set显示的信息中，除了Master的UUID对应GTID外，还存在另外一个GTID，我们可以查看从库的GTID，执行如下语句：

发现另外的GTID是由从库执行而产生。

1.3.3 查看从库binlog日志

从库binlog日志记录的是SQL线程复现的主机binlog信息或者是从库本身执行事务的binlog日志。这些事务可以通过server_id或者GTID来区分。

这里以创建数据库失败为例，在从库binlog中，查找3a169e6c-f1d0-11e6-bb30-d4ae52a34783:1对应的事务，发现如下信息：#170418 16:22:15 server id 30002 end_log_pos 1791 CRC32 0xfa89e69a GTID last_committed=6 sequence_number=7 SET @@SESSION.GTID_NEXT= '3a169e6c-f1d0-11e6-bb30-d4ae52a34783:1'/*!*/; # at 1791 …… create database mydb10

1.3.4 查看从库relay log日志

从库relay log日志记录的是IO线程从主库接收到的binlog日志信息，我们查看执行失败的GTID对应的事务信息：#170418 16:22:26 server id 30001 end_log_pos 1610 CRC32 0x27c8228f GTID last_committed=5 sequence_number=6 SET @@SESSION.GTID_NEXT= '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:6'/*!*/; # at 519 …… create database mydb10 /*!*/;

1.3.5 总结

最终可以确认是由于从库执行了创建数据库语句后，SQL线程再次执行创建数据库语句时发生复制失败的情况。

2. 主库的binlog丢失

2.1 错误原因

复制过程中，由于从库需要读取的主库binlog丢失，从而导致复制发生异常。导致主库binlog丢失的主要原因如下：

主库执行reset master命令主库执行purge binary/master logs命令主库设置了expire_logs_days，自动删除了binlog主库的binlog被误删除

2.2 错误信息

如果发生找不到主机binlog的情况，从库error log会报出如下错误：2017-04-18T09:01:42.894612Z 17 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'could not find ne

2.3 原因分析过程

2.3.1 查看 error log

Error log中显示的详细错误信息如下：2017-04-18T09:01:42.894612Z 17 [ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from './binlog001.000002' at 154, the last byte read from './binlog001.000002' at 154.', Error_code: 1236

错误信息显示无法找到对应的binlog文件。

2.3.2 查看 binlog 日志

查看主库的binlog日志文件列表，可能会发现主库的binlog变成重新开始记录：-rw-r----- 1 songlei.wang songlei.wang 449 Apr 18 17:01 binlog001.000001 -rw-r----- 1 songlei.wang songlei.wang 19 Apr 18 17:01 binlog_index001.index

或者需要复制的binlog已经被删除：-rw-r----- 1 songlei.wang songlei.wang 241 Apr 18 17:12 binlog001.000006 -rw-r----- 1 songlei.wang songlei.wang 1716 Apr 18 17:12 binlog001.000007 -rw-r----- 1 songlei.wang songlei.wang 194 Apr 18 17:12 binlog001.000008 -rw-r----- 1 songlei.wang songlei.wang 57 Apr 18 17:12 binlog_index001.index

2.3.3 总结

如果binlog重新开始记录，通常是由于主库执行了reset master命令，导致所有的binlog被删除。

如果binlog仍然在继续记录，只是从库需要的binlog被删除，通常是由于主库手动执行了purge binary logs命令，或者日志的保留时间超过了expire_logs_days设置的时间。

3. 从库没有执行主库复制的语句

3.1 错误原因

复制过程中，用于主库执行事务对应的GTID已经存在于从库GTID_EXECUTED中，那么从库便不会执行这些事务，从而导致主库和从库的数据不一致。通常有如下情况：

主机执行了reset master（从库当前读取主机的第一个binlog，并不会因为reset master而导致找不到文件）重做主从，从库没有清除从库的binlog。

3.2 错误信息

在从库忽略主机执行事务的过程中，从库复制不会报出任何错误，所以这种复制的异常容易被忽略，没有办法及时发现。

由于主库和从库的数据库不一致，后续的DML和DDL操作可能会发生执行失败的错误。

3.3 原因分析过程

这里我们以插入语句找不到对应的表为例。

3.3.1 查看 error log

Error log中记录错误信息：2017-04-18 20:39:01 682 [ERROR] Slave SQL: Error 'Table 'mydb.mytbl2' doesn't exist' on query. Default database: 'mydb'. Query: 'insert into mydb.mytbl4 values (1)', Error_code: 1146

3.3.2 查看show slave status

show slave status显示的信息全部正常，无从库执行事务的binlog产生。这里不排除从库关闭binlog执行drop table操作的可能。

3.3.3 查看表

分别在主机和从库执行命令show create table mydb.mytbl4，发现从库上并不存在mydb.mytbl4。

3.3.4 解析 binlog 日志

解析主机binlog日志，查看建表的事务日志：#170418 20:50:47 server id 30061 end_log_pos 199 CRC32 0xaaa00818 GTID [commit=yes] SET @@SESSION.GTID_NEXT= '1ac5f388-0dea-11e7-b2ec-d4ae52a34783:1'/*!*/; # at 199 #170418 20:50:47 server id 30061 end_log_pos 319 CRC32 0x06f5789f Query thread_id=3 exec_time=0 error_code=0 …… create table if not exists mydb.mytbl4 (A int)

解析从库的binlog日志，查找是否存在建表的事务日志:#170418 20:49:49 server id 30061 end_log_pos 199 CRC32 0x3051fade GTID [commit=yes] SET @@SESSION.GTID_NEXT= '1ac5f388-0dea-11e7-b2ec-d4ae52a34783:1'/*!*/; # at 199 #170418 20:49:49 server id 30061 end_log_pos 278 CRC32 0x7156a1c9 Query thread_id=3 exec_time=40 error_code=0 …… BEGIN /*!*/; # at 278 #170418 20:49:49 server id 30061 end_log_pos 385 CRC32 0x25560e04 Query thread_id=3 exec_time=40 error_code=0 use `mydb`/*!*/; SET TIMESTAMP=1492519789/*!*/; insert into mydb.mytbl values (1) /*!*/; ……

这时我们发现对于相同的GTID，从库和主机执行的语句不相同。

3.3.5 总结

通过上述分析，我们推断是从库并没有执行建表语句，进而导致主库数据不一致。

3.3.6 说明

这种情况MySQL-5.7版本会在复制时有更严格校验，如果主机发送GTID要少于从库的GTID，那么会报告出如下的错误：Got fatal error 1236 from master when reading data from binary log: 'Slave has more GTIDs than the master has, using the master's SERVER_UUID. This may indicate that the end of the binary log was truncated or that the last binary log file was lost, e.g., after a power or disk failure when sync_binlog != 1. The master may or may not have rolled back transactions that were already replica', Error_code: 1236

但是，即使在5.7版本，如果启动复制时（错误后重新启动），主库执行的GTID超过了从库，仍然会报出同样的错误。

4. 主库执行了不进行复制的语句

4.1 错误原因

主库上执行的操作并不会写入binlog，因此这里不考虑主库主动关闭binlog的情况。

4.2 错误信息

由于主库和从库的数据不一致，导致主库执行的操作复制到从库后，发生从库执行失败的情况。如：

创建FEDERATED引擎的表失败：2017-01-24T08:04:31.682038Z 3 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '887ca716-e202-11e6-8a82-52540086ae59:7' at master log mysql-bin.000004, end_log_pos 906; Error 'server name: 's' doesn't exist!' on query. Default database: 'mydb'. Query: 'CREATE TABLE `mytbl` ( `idaction` int(10) unsigned NOT AUTO_INCREMENT, ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='s'', Error_code: 143

4.3 原因分析过程

这里以使用CONNECTION创建FEDERATED引擎的表为例。

4.3.1 查看error log

Error log中记录错误信息：2017-01-24T08:04:31.682038Z 3 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '887ca716-e202-11e6-8a82-52540086ae59:7' at master log mysql-bin.000004, end_log_pos 906; Error 'server name: 's' doesn't exist!' on query.

4.3.2 查看主库和从库的server表

主库中server表中存在名字为s的记录。

从库中不存在名字为s的记录。

4.3.3 查看CREATE SERVER文档说明

文档中记录的create server语句并不会记录到binlog中，所以导致主库和从库的数据不一致，复制无法正常进行。

4.3.4 总结

对于不记入binlog的操作，需要主库和从库同时执行，以防发生主库和从库不一致的情况。

5. 从库重复执行relay log的语句（非GTID，非多线程复制）

5.1 错误原因

如果物理机发生宕机或者从库发生意外中断，那么可能发生SQL线程已经执行过了某一个relay log中的事务，但是这个事务对应文件和位置信息并没有及时更新到relay_log.info中的情况。在从库发生重启之后，会将执行过的事务再次重新执行。

5.2 错误信息

重复执行的事务包括任何记录到relay log中的事务，可能出现的错误信息包括：

创建库或者表失败2017-04-18T07:52:40.440723Z 6 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:2' at master log binlog001.000001, end_log_pos 493; Error 'Can't create database 'mydb4'; database exists' on query. Default database: 'mydb4'. Query: 'create database mydb4', Error_code: 1007插入语句主键冲突2017-04-18T07:57:35.975310Z 10 [ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1' at master log binlog001.000001, end_log_pos 378; Could not execute Write_rows event on table mydb.test; Duplicate entry '10' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 378, Error_code: 1062删除语句找不到对应的语句[ERROR] Slave SQL for channel '': Worker 0 failed executing transaction '0c1b77a7-c113-11e6-9bd6-d4ae52a34783:1' at master log binlog001.000001, end_log_pos 506; Could not execute Delete_rows event on table mydb.I1; Can't find record in 'I1', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log FIRST, end_log_pos 506, Error_code: 1032

由于各种类型的事务均可能执行，这里不再一一列举。

5.3 原因分析过程

这里以插入语句主键冲突为例

5.3.1 查看error log

Error log中记录以下报错信息：2017-04-19T03:02:15.448429Z 4 [Note] Slave SQL thread for channel '' initialized, starting replication in log 'binlog001.000001' at position 403, relay log './relaylog002.000002' position: 616 2017-04-19T03:02:15.459468Z 4 [ERROR] Slave SQL for channel '': Could not execute Write_rows event on table mydb.k2; Duplicate entry '101' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log binlog001.000001, end_log_pos 621, Error_code: 1062 2017-04-19T03:02:15.459526Z 4 [Warning] Slave: Duplicate entry '101' for key 'PRIMARY' Error_code: 1062

可以看到是SQL线程在启动后执行第一个事务就发生主键冲突的错误。

5.3.2 查看show slave status

show slave status显示的信息全部正常，无从库执行事务的binlog产生。

5.3.3 mydb.k2

表中已经存在了这条记录。

5.3.4 mydb.k2

查看从库的relay log，从复制的起始位置./relaylog002.000002：616查看：#17...

随着大数据的热潮，非关系型数据库NoSQL在近两年也受到了更多人的关注，有人还提出NoSQL将主导未来的大数据分析领域。而事实上，SQL与NoSQL之间其实就是互补的关系，未来双方在大数据和云计算时代都将会有很大的发展空间。

概念

SQL (Structured Query Language) 数据库，指关系型数据库。主要代表：SQL Server，Oracle，MySQL(开源)，PostgreSQL(开源)。

NoSQL（Not Only SQL）泛指非关系型数据库。主要代表：MongoDB，Redis，CouchDB。

SQL 数据库:

在表中存储相关联的数据

在使用之前需要定义表的一个模式

鼓励标准化减少数据冗余

支持从多个表中检索相关数据表连接在一个单一的命令

实现数据完整性规则

提供事务使两个或两个以上的成功或失败的数据更改作为一个原子单元

可以扩展(有一些努力)

使用一个强声明性语言查询

提供足够的支持,专业技能和工具。

NoSQL 数据库

将相关联的数据存储在类似 JSON 格式，名称-值

可以保存没有指定格式的数据

通常必须规范化，所以一个项目的信息包含在一个文档里

应该不需要连接（假设使用规范化的文档）

允许任何数据被保存在任何时候任何地方，不需要验证

保证更新一个文档 – 但不是多个文档

提供出色的性能和可伸缩性

使用 JSON 数据对象查询

是一个新的、令人兴奋的技术。

SQL和NoSQL之间的区别

SQL的独特优势包括：

1. SQL能够加强与数据的交互，并允许对单个数据库设计提出问题。这是很关键的特征，因为无法交互的数据基本上是没用的，并且，增强的交互性能够带来新的见解、新的问题和更有意义的未来交互。

2. SQL是标准化的，使用户能够跨系统运用他们的知识，并对第三方附件和工具提供支持。

3. SQL能够扩展，并且是多功能和经过时间验证的，这能够解决从快写为主导的传输到扫描密集型深入分析等问题。

4. SQL对数据呈现和存储采用正交形式，一些SQL系统支持JSON和其他结构化对象格式，比NoSQL具有更好的性能和更多功能。

NoSQL特点：

易扩展

NoSQL数据库种类繁多，但是一个共同的特点都是去掉关系数据库的关系型特性。数据之间无关系，这样就非常容易扩展。也无形之间，在架构的层面上带来了可扩展的能力。

大数据量，高性能

NoSQL数据库都具有非常高的读写性能，尤其在大数据量下，同样表现优秀。这得益于它的无关系性，数据库的结构简单。NoSQL的Cache是记录级的，是一种细粒度的Cache，所以NoSQL在这个层面上来说就要性能高很多了。

灵活的数据模型

NoSQL无需事先为要存储的数据建立字段，随时可以存储自定义的数据格式。而在关系数据库里，增删字段是一件非常麻烦的事情。如果是非常大数据量的表，增加字段简直就是一个噩梦。这点在大数据量的web2.0时代尤其明显。

高可用

NoSQL在不太影响性能的情况，就可以方便的实现高可用的架构。比如Cassandra，HBase模型，通过复制模型也能实现高可用。

总结

SQL和NoSQL水火不容的想法是错误的，理由并不仅仅是因为很多公司都选择同时使用它们。就像我以前讨论过的所有技术一样，世上真的没有那种“万金油”的方法，要根据用例来选择恰当的技术。如果你的数据需求在迅速变化，就需要高通量来应对快速的增长，如果你的数据在大幅增长，就需要快速且有效的扩展性，NoSQL可能会适合你。但如果你的数据结构没有变化，增长速度适中可控，那么SQL技术可能最适合你。毫无疑问，SQL还没有消亡。

无论是运维、开发、测试，还是架构师，数据库技术是一个必备加薪神器，那么，一直说学习数据库、学MySQL，到底是要学习它的哪些东西呢？

13、MySQL中如何执行区分大小写的字符串比较？

在Windows平台下，MySQL是不区分大小的，因此字符串比较函数也不区分大小写。如果 想执行区分大小写的比较，可以在字符串前面添加BINARY关键字。例如默认情况下，’a’=‘A’ 返回结果为1，如果使用BINARY关键字，BINARY’a’=‘A’结果为0,在区分大小写的情况下，’a’ 与’A’并不相同。

14、如何从日期时间值中获取年、月、日等部分日期或时间值？

MySQL中，日期时间值以字符串形式存储在数据表中，因此可以使用字符串函数分别截取日期时间值的不同部分，例如某个名称为dt的字段有值“2010-10-01 12:00:30”，如果只需要获的年值，可以输入LEFT(dt, 4)，这样就获得了字符串左边开始长度为4的子字符串，即YEAR 部分的值；如果要获取月份值，可以输入MID(dt,6,2)，字符串第6个字符开始，长度为2的子 字符串正好为dt中的月份值。同理，读者可以根据其他日期和时间的位置，计算并获取相应的值。

15、如何改变默认的字符集？

CONVERT()函数改变指定字符串的默认字符集，在开始的章节中，向读者介绍使用GUI图形化安装配置工具进行MySQL的安装和配置，其中的一个步骤是可以选择MySQL的默认字符集。但是，如果只改变字符集，没有必要把配置过程重新执行一遍，在这里，一个简单的方式是 修改配置文件。在Windows中，MySQL配置文件名称为my.ini，该文件在MySQL的安装目录下面。修改配置文件中的default-character-set和character-set-server参数值，将其改为想要的字 符集名称，如gbk、gb2312、latinl等，修改完之后重新启动MySQL服务，即可生效。读者可以在修改字符集时使用SHOW VARIABLES LIKE ’character_set_°%’;命令查看当前字符集，以进行对比。

16、DISTINCT可以应用于所有的列吗？

查询结果中，如果需要对列进行降序排序，可以使用DESC，这个关键字只能对其前面的列 进行降序排列。例如，要对多列都进行降序排序，必须要在每一列的列名后面加DESC关键字。 而DISTINCT不同，DISTINCT不能部分使用。换句话说，DISTINCT关键字应用于所有列而不 仅是它后面的第一个指定列。例如，查询3个字段s_id，f_name，f_price，如果不同记录的这3个字段的组合值都不同，则所有记录都会被查询出来。

17、ORDER BY可以和LIMIT混合使用吗？

在使用ORDER BY子句时，应保证其位于FROM子句之后，如果使用LIMIT，则必须位 于ORDER BY之后，如果子句顺序不正确，MySQL将产生错误消息。

18、什么时候使用引号？

在查询的时候，会看到在WHERE子句中使用条件，有的值加上了单引号，而有的值未加。 单引号用来限定字符串，如果将值与字符串类型列进行比较，则需要限定引号；而用来与数值进 行比较则不需要用引号。

19、在WHERE子句中必须使用圆括号吗？

任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确操作顺序。 如果条件较多，即使能确定计算次序，默认的计算次序也可能会使SQL语句不易理解，因此使 用括号明确操作符的次序，是一个好的习惯。

20、更新或者删除表时必须指定WHERE子句吗？

在前面章节中可以看到，所有的UPDATE和DELETE语句全都在WHERE子句中指定了条 件。如果省略WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。因此，除非 确实打算更新或者删除所有记录，否则要注意使用不带WHERE子句的UPDATE或DELETE 语句。建议在对表进行更新和删除操作之前，使用SELECT语句确认需要删除的记录，以免造 成无法挽回的结果。

21、索引对数据库性能如此重要，应该如何使用它？

为数据库选择正确的索引是一项复杂的任务。如果索引列较少，则需要的磁盘空间和维护开销 都较少。如果在一个大表上创建了多种组合索引，索引文件也会膨胀很快。而另一方面，索引较多 可覆盖更多的查询。可能需要试验若干不同的设计，才能找到最有效的索引。可以添加、修改和删 除索引而不影响数据库架构或应用程序设计。因此，应尝试多个不同的索引从而建立最优的索引。

22、尽量使用短索引。

对字符串类型的字段进行索引，如果可能应该指定一个前缀长度。例如，如果有一个 CHAR(255)的列，如果在前10个或30个字符内，多数值是惟一的，则不需要对整个列进行索引。 短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

23、MySQL存储过程和函数有什么区别？

在本质上它们都是存储程序。函数只能通过return语句返回单个值或者表对象；而存储过程 不允许执行return，但是可以通过out参数返回多个值。函数限制比较多，不能用临时表，只能用表变量，还有一些函数都不可用等等；而存储过程的限制相对就比较少。函数可以嵌入在SQL 语句中使用，可以在SELECT语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

24、存储过程中的代码可以改变吗？

目前，MySQL还不提供对已存在的存储过程代码的修改，如果必须要修改存储过程，必须使用DROP语句删除之后，再重新编写代码，或者创建一个新的存储过程。

25、存储过程中可以调用其他存储过程吗？

存储过程包含用户定义的SQL语句集合，可以使用CALL语句调用存储过程，当然在存储 过程中也可以使用CALL语句调用其他存储过程，但是不能使用DROP语句删除其他存储过程。

26、存储过程的参数不要与数据表中的字段名相同。

在定义存储过程参数列表时，应注意把参数名与数据库表中的字段名区别开来，否则将出 现无法预期的结果。

27、存储过程的参数可以使用中文吗？

一般情况下，可能会出现存储过程中传入中文参数的情况，例如某个存储过程根据用户的 名字查找该用户的信息，传入的参数值可能是中文。这时需要在定义存储过程的时候，在后面加 上character set gbk,不然调用存储过程使用中文参数会出错，比如定义userInfo存储过程，代码 如下：

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

28、MySQL中视图和表的区别以及联系是什么？

两者的区别：

(1)视图是已经编译好的SQL语句，是基于SQL语句的结果集的可视化的表，而表不是。

(2)视图没有实际的物理记录，而基本表有。

(3)表是内容，视图是窗口。

(4)表占用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它 进行修改，但视图只能用创建的语句来修改。

(5)视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL 语句的集合。从安全的角度来说，视图可以防止用户接触数据表，因而用户不知道表结构。

(6)表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。

(7)视图的建立和删除只影响视图本身，不影响对应的基本表。

两者的联系：

视图（view)是在基本表之上建立的表，它的结构（即所定义的列）和内容（即所有记录） 都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也

可以对应多个基本 表。视图是基本表的抽象和在逻辑意义上建立的新关系。

29、使用触发器时须特别注意。

在使用触发器的时候需要注意，对于相同的表，相同的事件只能创建一个触发器，比如对 表account创建了一个BEFORE INSERT触发器，那么如果对表account再次创建一个BEFORE INSERT触发器，MySQL将会报错，此时，只可以在表account上创建AFTER INSERT或者 BEFORE UPDATE类型的触发器。灵活的运用触发器将为操作省去很多麻烦。

30、及时删除不再需要的触发器。

触发器定义之后，每次执行触发事件，都会激活触发器并执行触发器中的语句。如果需求 发生变化，而触发器没有进行相应的改变或者删除，则触发器仍然会执行旧的语句，从而会影响 新的数据的完整性。因此，要将不再使用的触发器及时删除。

31、应该使用哪种方法创建用户？

创建用户有几种方法：GRANT语句、CREATE USER语句和直接操作user表。一般情况， 最好使用GRANT或者CREATE USER句，而不要直接将用户信息插入user表，因为user表中存储了全局级别的权限以及其他的账户信息，如果意外破坏了 user表中的记录，则可能会对 MySQL服务器造成很大影响。

32、mysqldump备份的文件只能在MySQL中使用吗？

mysqldump备份的文本文件实际是数据库的一个副本，使用该文件不仅可以在MySQL中恢 复数据库，而且通过对该文件的简单修改，可以使用该文件在SQL Server或者Sybase等其他数 据库中恢复数据库。这在某种程度上实现了数据库之间的迁移。

33、如何选择备份工具？

直接复制数据文件是最为直接、快速的备份方法，但缺点是基本上不能实现增量备份。备 份时必须确保没有使用这些表。如果在复制一个表的同时服务器正在修改它，则复制无效。备份 文件时，最好关闭服务器，然后重新启动服务器。为了保证数据的一致性，需要在备份文件前， 执行以下SQL语句：

CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) character set gbk, OUT u_age INT)

目录下即可。mysqlhotcopy 是一个 PERL 程序，它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件所在的 机器上，并且mysqlhotcopy只能用于备份MyISAM表。mysqlhotcopy适合于小型数据库的备份， 数据量不大，可以使用mysqlhotcopy程序每天进行一次完全备份。mysqldump将数据表导成SQL脚本文件，在不同的MySQL版本之间升级时相对比较合适， 这也是最常用的备份方法。mysqldump比直接复制要慢些。也就是把内存中的数据都刷新到磁盘中，同时锁定数据表，以保证复制过程中不会有新的 数据写入。这种方法备份出来的数据恢复也很简单，直接复制回原来的数据库

34、平时应该打开哪些日志？

日志既会影响MySQL的性能，又会占用大量磁盘空间。因此，如果不必要，应尽可能少地 开启日志。根据不同的使用环境，可以考虑开启不同的日志。例如，在开发环境中优化查询效率 低的语句，可以开启慢查询日志；如果需要记录用户的所有查询操作，可以开启通用查询日志； 如果需要记录数据的变更，可以开启二进制日志；错误日志是默认开启的。

35、如何使用二进制日志？

二进制日志主要用来记录数据变更。如果需要记录数据库的变化，可以开启二进制日志。 基于二进制日志的特性，不仅可以用来进行数据恢复，还可用于数据复制。在数据库定期备份的 情况下，如果出现数据丢失，可以先用备份恢复大部分数据，然后使用二进制日志恢复最近备份 后变更的数据。在双机热备情况下，可以使用MySQL的二进制日志记录数据的变更，然后将变 更部分复制到备份服务器上。

36、如何使用慢查询日志？

慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记 录查询时间较长的查询语句，然后对这些语句进行优化。通过配long_query_time的值，可以灵活地掌握不同程度的慢查询语句。

37、是不是索引建立得越多越好？

合理的索引可以提高查询的速度，但不是索引越多越好。在执行插入语句的时候，MySQL 要为新插入的记录建立索引。所以过多的索引会导致插入操作变慢。原则上是只有查询用的字段 才建立索引。

MySQL复制有多种类型：

1、异步复制：一个主库，一个或多个从库，数据异步同步到从库

2、同步复制：在MySQL Cluster中特有的复制方式

3、半同步复制：在异步复制的基础上，确保任何一个主库上的事务在提交之前至少有一个从库已经收到该事务并日志记录下来

4、延迟复制：在异步复制的基础上，人为设定主库和从库的数据同步延迟时间，即保证数据延迟至少是这个参数

MySQL复制的工作原理是数据库修改事件记录到bin log中并传递到slave，然后slave在本地还原的过程。而事件记录到bin log的格式会有所不同。

MySQL复制有三种核心格式：

1、基于语句的复制(statement based replication)：基于主库将SQL语句写入到bin log中完成复制

2、基于行数据的复制(row based replication)：基于主库将每一个行数据变化的信息作为事件写入到bin log中完成日志

3、混合复制(mixed based replication)：上述两者的结合。默认情况下优先使用基于语句的复制，只有当部分语句如果基于语句复制不安全的情况下才会自动切换为基于行数据的复制

互联网企业大多用的数据库是MySQL的，想要众多的IT工作者中脱颖而出，就需要拥有高深的技术，学习增值是必不可少的。学习之路，是贵在坚持的。