MySQL OCP-11-用户管理

1月 28th, 2017

— 用户管理的重要性;
在MySQL中管理用户时,可以控制允许或不允许用户执行的操作;
• 创建具有与用户的职能相应的不同特权的用户帐户;
• 避免使用root帐户:
– 限制受损害的应用程序;
– 防止在例行维护期间出错;
• 通过恰当调整单个用户特权确保数据完整性:
– 允许授权用户完成其工作;
– 阻止未经授权用户访问超出其特权的数据;

— 用户帐户验证;
在连接到MySQL服务器并执行查询时,会验证您的身份并为您的活动授权;
• 验证:验证用户的身份;这是访问控制的第一个阶段,每次连接时都必须成功验证身份,如果验证失败,则无法连接,您的客户机将断开连接;
• 授权:验证用户的特权;这是访问控制的第二个阶段,面向针对成功验证了身份的活动连接的每个请求;对于每个请求,MySQL将确定您要执行的操作,然后检查您是否有执行此操作所需的恰当特权;

— 查看用户帐户设置;
mysql数据库包含服务器上所有用户帐户的信息:
1.查询mysql数据库以查看user标识信息:mysql> SELECT user, host, password FROM mysql.user WHERE user=’root’;
2.查看包括特权在内的所有user信息:SELECT * FROM mysql.user\G
1.*_priv字段中的值Y表示已启用相应特权;
2.root帐户具有完全访问权限,该帐户的所有特权列的值均为Y;
3.除了特权以外,该用户表还包含在验证过程中有用的其他信息;
4.Password列中以加密格式显示密码;password_expired:表示口令是否过期;
5.max_*:资源限制,0表示无限制;
6.ssl_*和x509_*:SSL或x509设置;
7.plugin:列出了密码插件名称(eg:mysql_native_password插件验证身份);

— 本机验证;
• 在使用mysql_native_password插件连接过程中,MySQL将使用以下各项与mysql.user表中的行进行匹配验证帐户:
– 用户名
– 口令
– 客户机主机
• 在指定主机名时,要注意恰当的方面:
– 在使用客户机连接时指定服务器的主机名;
– 在向服务器添加用户时指定客户机的主机名;
补充:
要使用mysql客户机连接到本地服务器:shell> mysql -u -p 要使用mysql客户机连接到远程服务器:shell> mysql -u -p -h

— 创建用户帐户;
1.提供每个用户帐户的用户和主机;
2.使用CREATE USER…IDENTIFIED BY语句可以:
– 为用户 jim 建立一个帐户;
– 该帐户从 localhost 发出连接;
– 该帐户使用口令 Abc123:
CREATE USER ‘jim’@’localhost’ IDENTIFIED BY ‘Abc123’;
3.在创建帐户时避免可能的安全风险:
– 不创建没有口令的帐户;
– 不创建匿名帐户;
– 在可能的情况下,避免在指定帐户主机名时使用通配符;
4.创建一个匿名账户,不需要用户名和密码即可登录,比较危险.eg:insert into user(host, user, password) values(‘%’, ”, ”);
TIPS:
1.帐户名称的格式为’user_name’@’host_name’;
2.用户名长度最多可以有16个字符,如果用户名和主机名包含特殊字符(如短划线),则必须将它们放在单引号中;如果某个值在不带引号时也有效,则引号是可选的,但是,任何情况下都可使用引号;

— 主机名模式;
1.允许的主机名格式示例:
• 主机名:localhost;
• 合格的主机名:’hostname.example.com’;
• IP编号:192.168.9.78;
• IP地址:10.0.0.0/255.255.255.0;
• 模式或通配符:%或_;
2.使用包含%或_通配符字符的主机模式设置的帐户允许用户从整个域或子网中的任何主机连接;
3.省略帐户名称的主机部分,MySQL将假定主机名为%;
1.主机值%.example.com可匹配example.com域中的任何主机;
2.主机值 192.168.%可匹配192.168子网中的任何主机;
3.主机值%可匹配任何主机,允许用户从任何主机连接;
4.用户名和主机名示例:
• john@10.20.30.40;
• john@’10.20.30.%’;
• john@’%.ourdomain.com’;
• john@’10.20.30.0/255.255.255.0′;
5.指定匿名用户帐户(即匹配任何用户名的帐户):mysql> CREATE USER ”@’localhost’;要避免创建匿名帐户,尤其是没有口令的匿名帐户,这有助于避免由于开放对MySQL安装的访问权限带来的安全风险;
6.如果某个主机匹配两个或更多模式,则MySQL将选择最具体的模式;

— 设置帐户口令;
1.设置MySQL用户口令的方法有多种:
– CREATE USER…IDENTIFIED BY:自动为提供的口令加密;
– GRANT…IDENTIFIED BY
– SET PASSWORD
– mysqladmin password:shell> mysqladmin -u root password ‘rootpass’;
– UPDATE 授权表(不推荐)
2.SET PASSWORD语句是设置或更改帐户口令的最常用方法:
1.更改现有帐户的口令但不更改其任何特权的最常用方法;
2.语法:SET PASSWORD FOR ‘jim’@’localhost’ = PASSWORD(‘NewPass’);
3.使用SET PASSWORD语句但不带FOR子句仅能更改您自己的口令;

— 确认口令;
为所有用户帐户分配唯一的强口令:
• 避免可以轻易猜测到的口令;
• 使用以下SELECT语句可列出没有口令的所有帐户:SELECT Host, User FROM mysql.user WHERE Password = ”;
• 确定重复口令:SELECT User FROM mysql.user GROUP BY password HAVING count(user)>1;
• 让口令失效:ALTER USER jim@localhost PASSWORD EXPIRE;如果密码失效,则在下次登录时必须使用SET PASSWORD语句更改口令;所有不以SET开头的执行语句将返回错误,直到更改口令为止;

— 处理用户帐户;
• 使用RENAME USER语句可重命名用户帐户:RENAME USER ‘jim’@’localhost’ TO ‘james’@’localhost’;
– 更改现有帐户的帐户名称
– 更改帐户名称的用户名或主机名部分,或同时更改两者;
• 使用DROP USER语句可删除用户帐户:DROP USER ‘jim’@’localhost’:
– 撤消现有帐户的所有特权,然后删除该帐户;
– 从存在帐户的任何授权表中删除帐户的所有记录;
• 在用户访问要求变化时,重命名或删除用户;

— 可插入验证;
MySQL支持多种通过可插入验证提供的验证机制:
1.插件是内置的,也可作为外部库提供;
2.默认的服务器端插件是内置的,始终可用,其中包括:
– mysql_native_password:这是默认机制,插件实施标准口令格式:41字节宽的散列;
– mysql_old_password:此插件的实现验证的方式与MySQL4.1.1之前的版本相同;安全性较低,16字节宽;
– sha256_password:此插件可启用口令的SHA-256散列;
3.old_passwords系统变量的值指定PASSWORD()函数用于创建口令的算法,如下所示:
• 0:标准算法,与MySQL 4.1.1及更高版本中所用的算法相同;
• 1:旧算法,与MySQL 4.1.1之前版本中所用的算法相同;
• 2:SHA-256算法;
4.启动服务器时将default-authentication-plugin选项设置为sha256_password,可将SHA-256口令用于所有新用户,也可使用CREATE USER和IDENTIFIED WITH sha256_password子句为特定用户指定SHA-256口令;

— 客户端明文验证插件;
MySQL客户机库中有一个内置的明文验证插件mysql_clear_password,该插件:
1.用于将纯文本口令发送给服务器:口令通常经过散列处理;
2.通过以下方式启用:
– LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN:环境变量;
– 在运行MySQL客户机应用程序(如mysql和mysqladmin)时指定–enable-cleartext-plugin;
– mysql_options() C API函数的MYSQL_ENABLE_CLEARTEXT_PLUGIN选项;
3.有些验证方法(如PAM(Pluggable Authentication Module,可插入验证模块)验证)要求客户机向服务器发送纯文本口令,以便服务器处理普通形式的口令,mysql_clear_password插件支持此行为;

— 可装入验证插件;
1.测试验证插件(test_plugin_server):使用本机或旧口令验证进行身份验证,适用于测试和开发;此插件使用auth_test_plugin.so文件;
2.套接字对等凭证(auth_socket):仅允许通过UNIX套接字从具有相同名称的UNIX帐户登录的MySQL用户;此插件使用auth_socket.so文件;
3.PAM验证插件(authentication_pam):是一个企业版插件,允许使用外部验证机制登录,MySQL不存储您的口令,但使用PAM机制传输客户机提供的用户名和口令供操作系统进行验证;此插件使用authentication_pam.so文件;
4.要装入其中的某个插件,可在服务器启动时在命令行中或在my.cnf文件中使用plugin-load选项,并将该选项设置为插件的文件名;
[mysqld]
plugin-load=authentication_pam.so
5.可以开发自己的验证插件,测试验证插件适用于开发者创建自己的插件;

— PAM验证插件;
1.PAM验证插件是一个企业版插件,可向操作系统验证MySQL帐户;
2.PAM定义配置验证的服务:这些服务存储在/etc/pam.d中;
3.该插件针对以下各项进行验证:
– 操作系统用户和组;
– 外部验证(如LDAP,Active Directory),因此可以使用PAM向网络中的单个存储验证许多服务(包括MySQL);
4.要创建使用PAM验证的MySQL用户,请执行以下操作:CREATE USER user@host IDENTIFIED WITH authentication_pam AS ‘pam_service, os_group=mysql_user’;

eg:
1.加载PAM验证插件,在/etc/my.cnf中添加配置,然后重启MySQL服务;
[mysqld]
plugin-load=authentication_pam.so
2.创建PAM服务mysql-pam,可在创建文件/etc/pam.d/mysql-pam:
#%PAM-1.0
auth include password-auth
account include password-auth
3.创建操作系统账户:
shell> useradd bob
shell> passwd bob
4.要创建直接映射到某个操作系统用户的MySQL用户,可使用如下语句:CREATE USER bob@localhost IDENTIFIED WITH authentication_pam AS ‘mysql-pam’;
5.当bob登录时,MySQL会将从客户机接收的用户名和口令传递到PAM,后者向操作系统验证;客户机必须以明文形式发送口令,启用客户端明文验证插件以实现此目的:
shell> mysql –enable-cleartext-plugin -ubob -p
Enter password: bob’s_OS_password

1.要使用PAM验证插件启用基于组的登录,可创建一个启用PAM的匿名代理帐户,该帐户不与任何用户匹配,但指定一组从操作系统组到MySQL用户的映射:CREATE USER ”@” IDENTIFIED WITH authentication_pam AS ‘mysql-pam, sales=m_sales, finance=m_finance’;
2.上例假定您拥有sales和finance操作系统组以及m_sales和m_finance MySQL用户;然后,必须向该匿名代理帐户授予 PROXY特权,使其能以m_sales和m_finance MySQL用户身份登录:GRANT PROXY ON m_sales@localhost TO ”@”; GRANT PROXY ON m_finance@localhost TO ”@”;
3.现在,作为sales和finance组成员的用户可以在mysql命令行提示符处提供其操作系统凭证,以m_sales或 m_finance MySQL用户身份登录,从而拥有授予这些帐户的所有特权;例如,如果peter是sales组的成员,则可通过以下方式登录:
shell> mysql –enable-cleartext-plugin -upeter -p
Enter password: peter’s_OS_password
Welcome to the MySQL monitor. Commands end with ; or \g. …
mysql> SELECT CURRENT_USER();
+——————-+
| CURRENT_USER() |
+——————-+
| m_sales@localhost |
+——————-+
1 row in set (0.01 sec)

— 授权;
1.MySQL授权系统的主要功能是将经过验证的用户与数据库特权(如SELECT,INSERT,UPDATE和DELETE)关联起来;
2.授权系统的功能包括:可以拥有匿名用户,可以启用特定功能(如LOAD DATA INFILE和各种管理操作);
3.授权可确保用户只能执行已向其授予了相应特权的操作;
4.在用户验证之后,MySQL将提出以下问题以验证帐户特权:
– 用户是谁?
– 用户拥有哪些特权?
– 这些特权适用于哪些方面?
5.必须设置恰当的帐户和特权才能让授权生效;

— 确定相应的用户特权;
1.可以在不同级别向MySQL帐户授予多种类型的特权:全局级别,数据库级别,表级别,列级别,存储过程级别;
2.根据用户的访问要求授予用户特权:
1.只读用户:全局,数据库或表级别特权,例如SELECT;
2.修改数据库的用户:全局,数据库或表级别特权,例如INSERT/UPDATE/DELETE/CREATE/ALTER/DROP;
3.管理用户:全局级别特权,例如FILE/PROCESS/SHUTDOWN/SUPER;

— 授予管理特权;
1.FILE:允许用户指示MySQL服务器在服务器主机文件系统中读取和写入文件;
2.PROCESS:允许用户使用SHOW PROCESSLIST语句查看客户机正在执行的所有语句;
3.SUPER:管理特权允许用户执行额外任务,其中包括设置全局变量和终止客户机连接;
4.ALL/ALL PRIVILEGES:授予所有特权(但不能向其他用户授予特权)要尽可能少地授予管理特权,因为管理特权可能会被恶意用户或粗心用户滥用;
5.USAGE:允许连接到服务器,但没有任何特权;可以使用帐户访问服务器用于有限的目的,例如发出SHOW VARIABLES或SHOW STATUS语句;

补充:MySQL可以支持的权限类型:
1.管理权限:
CREATE TEMPORARY TABLES:创建临时表;
CREATE USER:创建/删除/重命名账户;
FILE:在sql语句中读写操作系统的文件;
LOCK TABLES:锁表;
PROCESS:查看进程的活动状态;
RELOAD:重新加载,FLUSH OR RESET;
REPLICATION CLIENT:作为复制功能的主机;
REPLICATION SLAVE:作为复制功能的备机;
SHOW DATABASES:查看数据库名称;
SHUTDOWN:关闭服务器;
SUPER:各种管理操作;
2.数据访问权限:
ALTER:修改表结构,alter table;
ALTER ROUTINE:修改或者删除存储过程和函数;
CREATE:创建数据库或者表;
CREATE ROUTINE:创建存储过程或者函数;
CREATE VIEW:创建视图;
DELETE:删除表中数据;
DROP:删除数据库或者表;
EXECUTE:执行函数或者过程;
GRANT OPTION:授权给其它账户;
INDEX:创建/删除索引;
INSERT:向表中插入记录;
SELECT:查询表的记录;
SHOW VIEW:查看视图定义,show create view;
UPDATE:更新表的记录:
3.特殊的权限:
1.ALL and ALL PRIVILEGES:即’all privileges except grant option’,授予账户除了把权限赋予其它帐号外的所有权限;
2.USAGE:即没有任何权限,但是它可以连接到数据库上,也在user表中生成一条记录.这类账户的存在就是为了执行向’show variables’ or ‘show status’这类的语句,也不能查看表的内容;
4.权限的级别
1.所有的权限都可以被授予全局的,一个账户拥有全局权限,它可以在任何数据库任何时间使用此权限,一般只授予管理员这样的权限;
2.一些权限可以被授予特定的数据库:ALTER, CREATE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP, GRANT OPTION, IDEX, INSERT, LOCK TABLES, SELECT, SHOW VIEW and UPDATE.一个数据库级别的权限可以应用于所有的tables和routines;
3.一些权限可以被授予特定的表:ALERT, CREATE, DELETE, DROP, GRANT OPTION, INDEX, INSERT, SELECT and UPDATE.一个标记别的权限可以应用于表中所有的列;
4.一些权限可以被授予表中的列:INSERT, SELECT and UPDATE;
5.一些权限可以被授予函数和存储过程:EXECUTE, ALTER ROUTINE and GRANT OPTION;

— GRANT语句;
1.GRANT语句可创建新帐户或者修改现有帐户;
2.GRANT语法:GRANT SELECT ON world_innodb.* TO ‘kari’@’localhost’ IDENTIFIED BY ‘Abc123’;
3.该语句的子句:
1.GRANT关键字:指定一个或多个指示要授予的特权的特权名称;特权名称不区分大小写,要列出多个特权,可用逗号分隔特权;;
2.ON子句:指定要授予的特权的级别:
1.全局:*.*;
2.数据库:.*;
3.表:
.;
4.存储例程:.;
3.TO子句:指定要向其授予特权的帐户;如果该帐户尚不存在,则该语句将创建此帐户;;
4.IDENTIFIED BY子句:(可选)将指定口令分配给帐户;如果帐户已存在,则该口令将替换旧口令,省略IDENTIFIED BY子句会有以下效果:
1.如果TO子句中的帐户已存在,则其口令保持不变;
2.如果TO子句中的帐户不存在,则将创建该帐户,并使用空白口令;
4.作为一项安全措施,启用NO_AUTO_CREATE_USER SQL模式可防止GRANT语句在未指定IDENTIFIED BY子句时创建新帐户;

eg:
1.授权语句:grant privileges on objects to ‘user’@’host’ identified by ‘password’;
1.on *.*:全局权限,所有的对象,eg:grant all privileges on *.* to ‘u1’@’%’ identified by ‘u1’ with grant option;
2.on db_name.*:对数据库的权限,eg:grant select, update on test.* to ‘u1’@’localhost’;
3.on db_name.tbl_name:数据库中表的权限,eg:grant select on mysql.user to ‘u1’@’localhost’;
4.on db_name.tbl_name.col_name:数据库中表的列的权限,eg:grant select(host, user) on mysql.user to ‘u1’@’%’;
5.on db_name.routine_name:数据库中routine的权限;
2.要添加用户并授权一般使用:grant 权限1,权限2,… on 数据库.表 to “username”@”host” identified by “password”;(创建用户的同时授予权限)
1.grant all privileges on *.* to “user1″@”%” identified by “pwd”:表示对user1授予所有数据库中所有表的所有权限,并可以从任意客户端连接进来;
2.grant select,insert,delete,update on mysql.user to “user1″@”192.168.0.1” identified by “pwd”:表示对用户user1授予mysql数据库的user表增删改查权限,并且只能从192.168.0.1客户端连接进来;
3.host列为%表示可以从任意的客户端连接到服务器端;
5.一般在开发的时候使host为%,在生产环境修改host的地址;

— 显示GRANT特权;
1.使用SHOW GRANTS语句显示常规帐户特权:
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();
2.指定帐户名称:mysql> SHOW GRANTS FOR ‘kari’@’myhost.example.com’;
3.口令以加密形式存储和显示:如果帐户有口令,则SHOW GRANTS将在GRANT语句末尾显示一条IDENTIFIED BY PASSWORD子句,该子句可列出帐户的全局特权;由于口令是 使用单向加密存储的,因此MySQL无法显示未加密的口令;

— 用户特权限制;
1.不能显式拒绝访问特定用户;
2.不能将口令与特定对象(如数据库,表或例程)关联;

— 授权表;
1.MySQL服务器在启动时将授权表从mysql数据库读取到内存中,并使所有访问控制决策都以这些表为依据;授权表还指示有关帐户的其他信息,例如:
1.应遵守的任何资源限制;
2.使用帐户的客户机连接是否必须通过使用SSL的安全连接进行;
2.表与特权级别对应:
1.user:每一个账户在user表包含一条记录,它记录了用户拥有的全局的权限,也包含了一些其它信息,比如:使用资源的限制,客户端连接进来是否需要使用SSL链接;
2.db:列出了账户对指定数据库的权限;
3.tables_priv:列出了账户对指定表的权限;
4.columns_priv:列出了账户对指定列的权限;
5.procs_priv:列出了账户对存储过程和函数的权限;
3.这些表指示了每个帐户的有效性和特权,并且每个级别的安全程度是递进的;

— 使用授权表;
1.每个授权表有host列和user列,用于标识其记录适用的帐户:
1.在连接尝试过程中,服务器会确定客户机是否能连接:将用户表中的某个记录与客户机发起连接的主机,客户机提供的用户名 以及匹配记录中列出的口令匹配;
2.在连接后,服务器会确定每条语句的访问特权;
2.MySQL安装过程会创建授权表:
1.授权表使用MyISAM存储引擎;
2.MyISAM保证可用。

— 影响特权更改;
1.MySQL会维护授权表的内存中副本以避免访问磁盘上表的开销:
1.应避免直接在授权表中修改用户帐户,原因;
1.帐户管理语句的语法设计清晰,简单明了;
2.如果在某个帐户管理语句中犯错,该语句就会失败,不会更改任何设置;
3.如果在直接更改授权表时犯错,则可能会将所有用户锁在系统外面;
2.如果直接修改授权表,应通过发出FLUSH PRIVILEGES语句(或者执行mysqladmin flush-privileges或mysqladmin reload命令显式重新装入了表)显式重新装入授权表;
2.帐户修改语句(如GRANT/REVOKE/SET PASSWORD和RENAME USER)会将更改同时应用于授权表及其内存中表副本;
3.对全局特权和口令的更改仅应用于该帐户的后续连接;
4.对数据库级别特权的更改在客户机的下一条USE db_name语句后应用;
5.对表和例程特权的更改会立即应用;

— 撤消帐户特权;
1.使用REVOKE语句可以撤消特定的SQL语句特权:
1.REVOKE关键字:指定要撤消的特权列表;
2.ON子句:指示要撤消特权的级别;
3.FROM子句:指定帐户名称;
eg:REVOKE DELETE, INSERT, UPDATE ON world_innodb.* FROM ‘Amon’@’localhost’;
2.撤消特权以便将特权授予其他用户:REVOKE GRANT OPTION ON world_innodb.* FROM ‘Jan’@’localhost’;
3.撤消所有特权(包括向他人授权):REVOKE ALL PRIVILEGES, GRANT OPTION FROM ‘Sasha’@’localhost’;
4.在发出REVOKE之前使用SHOW GRANTS语句确定要撤消的特权,随后再次确认结果;

eg:回收权限:revoke prilileges on objects from ‘user’@’host’;
1.回收全局权限:revoke all privileges, grant option from ‘u1’@’%’;
2.回收数据库的权限:revoke select, grant option on mysql.* from ‘u1’@’%’;
3.回收表的权限:revoke select on mysql.user from ‘u1’@’%’;
4.回收列的权限:revoke select(host, user) on mysql.user from ‘u1’@’%’;

— SHOW PROCESSLIST;
1.SHOW PROCESSLIST(查看INFORMATION_SCHEMA.PROCESSLIST表或mysqladmin processlist命令获取线程信息)显示了正在运行的进程线程;
2.SHOW PROCESSLIST将生成以下列:
– Id:连接标识符;
– User:发出语句的MySQL用户;
– Host:发出语句的客户机的主机名;
– db:选择默认数据库,否则为NULL;
– Command:线程正在执行的命令类型;
– Time:线程处于当前状态的时间(秒);
– State:指示线程正在执行的内容的操作,事件或状态;
– Info:线程正在执行的语句;否则为NULL;
3.PROCESS特权允许查看所有线程,否则只能查看自己的线程;
4.如果出现了“连接数太多”错误,但希望确定正在执行的语句,则使用SHOW FULL PROCESSLIST语句会非常有用;
5.MySQL额外保留了一个连接,供拥有SUPER特权的帐户使用,这样可确保即使达到连接数限制,管理员也始终可以连接并检查系统(前提是应用程序用户没有SUPER特权);
6.使用KILL语句可中止进程;

— 禁用客户机访问控制;
要指示服务器不读取授权表并禁用访问控制,可使用–skip-grant-tables选项:
1.每个连接都成功:
1.可以提供任何用户名及任何口令,并且可以从任何主机连接;
2.该选项将禁用整个特权系统:会禁用帐户管理语句,如CREATE USER,GRANT,REVOKE和SET PASSWORD;
3.连接的用户实际上拥有所有特权;
2.阻止客户机连接:
1.使用–skip-networking选项可阻止网络访问,并且仅允许在本地套接字,命名管道或共享内存上访问;
2.使用–socket选项可在非标准套接字上启动服务器以防止本地应用程序或用户随便访问;

补充:忘记用户密码:
1.Linux中的处理方法:
1.关闭服务器或者kill掉mysqld的进程;
2.使用–skip-grant-tables选项登录,启动服务器并跳过授权表
3.使用mysql登录,可以不用使用用户名和密码,并且有全部权限;
4.登录之后直接更新mysql.user表密码字段为空即可;
2.Windows中的处理方法:
1.停止mysql的服务:net stop mysql;
2.到mysql的bin目录下执行mysqld -nt –skip-grant-tables;
3.到新的命令行下执行mysqladmin -uroot flush-privileges password ‘pwd’;
4.然后关闭mysql服务:mysqladmin -uroot -p shutdown,此时输入刚刚设置的密码;
5.启动mysql服务:net start mysql;

— 设置帐户资源限制;
1.通过将全局变量MAX_USER_CONNECTIONS设置为非零值,限制使用服务器资源:这将限制任何一个帐户的同时连接数量,但不会限制客户机在连接后能执行的操作;
2.限制单个帐户的以下服务器资源:
1.MAX_QUERIES_PER_HOUR(max_queries):一个帐户每小时可发出的查询数量;
2.MAX_UPDATES_PER_HOUR(max_updates):一个帐户每小时可发出的更新数量;
3.MAX_CONNECTIONS_PER_HOUR(max_connections):一个帐户每小时可连接到服务器的次数;
4.MAX_USER_CONNECTIONS(max_user_connections):允许的同时连接数量;
3.每个限制的默认值是零,表示没有限制;
4.查看:SELECT * FROM mysql.user\G
eg:
mysql> GRANT ALL ON *.* TO ‘francis’@’localhost’
WITH MAX_QUERIES_PER_HOUR 20
MAX_UPDATES_PER_HOUR 10
MAX_CONNECTIONS_PER_HOUR 5
MAX_USER_CONNECTIONS 2;

— 课后练习;

标签:

MySQL OCP-10-分区

1月 28th, 2017

— 分区;
1.分区就是将数据库或其构成元素划分为不同的独立部分;
1.预先组织表存储的方法;
2.当大型表占用大量可用磁盘空间并且需要其他空间来存储表数据时,可以使用物理分区;
3.表文件可以放在多个位置,而不是将数据集中于一个过载的磁盘;
4.此技术与存储引擎有关;
2.MySQL支持水平分区;
1.将特定表行分配为行的子集;
2.有水平分区和垂直分区,本课着重讲述称为“水平分区”的MySQL分区技术;

— 分区类比;
1.“搬家”的类比:
1.假定您正搬出家外,您可以将客厅中的所有物品都放到一个大盒子中;
2.但是,这不是很实际;您将很难找到单个物品,并且这将是一个非常大并且非常重的盒子;
3.最好使用多个盒子来存放您的所有物品,并且根据类型对这些盒子分类;
2.“Home”数据库示例:
1.可以将标签为“书籍”的盒子用于所有书籍;
2.标签为“CD”的盒子用于CD,标签为“影片”的盒子用于影片等等;
3.您现在已将您的物品“分区”到特定盒子中;

— 特定于MySQL的分区;
1.分区的分布是跨物理存储进行的:
1.根据用户在需要时设置的指定规则:MySQL分区允许你设置的规则将各个表的部分分布在整个物理存储中;
2.每个分区存储为单独的单元,非常像一个表;
2.数据的划分:
1.根据分区功能将数据划分为子集:数据的划分通过分区功能来完成,这在MySQL中可以是针对一组范围或值列表,内部散列函数或线性散列函数的简单匹配;
2.分区类型和表达式是表定义的一部分;使用RANGE/LIST COLUMNS时,表达式可以是INT/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY等数据类型;
3.表达式可以是整数或返回整数值的函数,表达式必须返回NULL或整数值;
4.此值根据定义确定将每条记录存储在哪个分区中;

— 分区类型;
MySQL支持多种类型的分区:
1.RANGE:根据属于指定范围的列值将行分配到分区;范围应该连续但不重叠,使用VALUES LESS THAN运算符进行定义;
2.LIST:根据与离散值集之一匹配的列将行分配到分区;像在由RANGE进行分区一样,必须显式定义每个分区;
3.HASH:基于由用户定义的表达式返回的值而选择的分区,对要插入表中的行的列值进行操作;
4.KEY:与HASH类似,不同之处在于仅提供要评估的一个或多个列并且MySQL服务器提供散列函数;它适用于所有允许的列类型;
5.COLUMNS:RANGE和LIST分区上的变体;COLUMNS分区允许在分区键中使用一个或多个列;
1.RANGE COLUMNS和LIST COLUMNS分区支持使用非整数列(以及前面列出的其他数据类型)来定义值范围或列表成员;
6.LINEAR:MySQL还支持线性散列,其不同于常规散列,线性散列使用线性2的幂算法,而常规散列使用散列函数值的模;

— 分区支持;
1.确定服务器分区支持状态:SHOW PLUGINS\G
1.如果MySQL二进制文件构建有分区支持,您不需要执行任何进一步操作来启用该二进制文件(例如,my.cnf文件中不需要任何特殊条目);
2.如果您没有看到partition插件以及Status值ACTIVE列在SHOW PLUGINS的输出中,则您的MySQL版本不支持分区;
2.禁用分区支持:shell> mysqld –skip-partition
1.partition插件现在具有值DISABLED;

— 使用分区改善性能;
1.特定于大型表的地址问题:
1.将数据分为较小的组以使查询更有效:将数据分割为逻辑分组的表中时,查询搜索较少记录,这样可以大大提高速度;
2.遵守文件系统的文件大小限制;
3.加快维护和删除行运行时速度:例如维护操作运行时速度缓慢(例如,OPTIMIZE和REPAIR)和删除不需要的行时运行时速度缓慢;
2.使用较小的索引来允许“最热”分区具有内存中的整个索引;
3.删除分区来更方便地删除数据;
4.根据日期和时间部署表:例如,您可能希望按年,季度或月部署店铺订单表;

— 使用分区改善性能:删改(分区排除);
1.分区排除以将查询范围限制为确切范围:
1.MySQL优化器可以为查询过滤不需要的分区,它仅搜索符合查询标准的分区;
2.使用WHERE子句进行整表扫描可以作为非匹配删改掉分区;
3.示例所示,假定您有一个书店并且您具有一个数据库,其中包含书店的整个书籍库存;此数据库具有针对每种书籍类别的表,一个客户来到书店,要求提供1990年和2007年之间出版的医学类别的所有书籍列表;因为您使用了分区并且按年份对医学表进行了分区,所以您可以运行仅包括这些年份的查询;优化器“删改”该搜索来仅扫描1990–2007分区,节省了大量时间和工作;
SELECT title FROM book
WHERE category = ‘medicine‘
AND published > 1990 AND published < 2007; 2.执行显式分区选择; 1.例如,如果您知道p0包括2007年以前出版的所有书籍,可以显式指出该分区,从而允许优化器忽略其他分区;可以通过提供列表来选择多个分区和子分区,例如PARTITION(p0, p2sp1); SELECT title FROM book PARTITION(p0) WHERE category = 'medicine‘ AND published > 1990;
3.还将显式分区选择用于INSERT/REPLACE/UPDATE/DELETE/LOAD,即使您在分区中添加或更改数据,也必须确保新数据满足该分区的分区标准;

— 分区的性能挑战;
1.打开分区表时必须打开所有分区:这是一个小问题,因为打开的表缓存在表高速缓存中;
2.删改具有特定限制:
1.删改在索引列中效率较低,因为它每个分区仅避免一次索引查找;
2.删改对于非索引列的作用要大得多:
1.其WHERE子句中包含非索引列的查询可能需要进行完整表扫描;
2.分区扫描花费的时间是表扫描花费时间的一小部分;

— 基本分区语法;
1.通过在CREATE TABLE语句中使用PARTITION BY子句来创建分区表;PARTITION BY跟在CREATE TABLE的所有部分后面:
CREATE TABLE () ENGINE=
PARTITION BY ();
2.分区类型后面是括号括起来的分区表达式;根据使用的分区类型以及您的要求,该表达式可以是表列的名称,涉及一个或多个列的简单数学表达式或者列名称的逗号分隔列表;
3.PARTITION BY类型包括在其相应语法中:
• PARTITION BY RANGE …
• PARTITION BY RANGE COLUMNS …
• PARTITION BY LIST …
• PARTITION BY LIST COLUMNS …
• PARTITION BY HASH …
• PARTITION BY LINEAR HASH …
• PARTITION BY KEY …
• PARTITION BY LINEAR KEY …

— RANGE分区;
1.使用PARTITION BY RANGE对包含一组指定范围内的指定值的行进行分区;
2.例如,假定您有一个表来存储与其订单相关的数据,并且您希望指定id号小于10,000的订单存储在一个分区中,id号介于10,000和19,999之间的订单存储在另一个分区中,id号介于20,000和29,999之间的订单存储在另一个分区中等等;换句话说,您基于连续值范围来对表进行分区;RANGE分区非常适合于此类分区模式;
CREATE TABLE orders_range
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);

— LIST分区;
1.使用PARITION BY LIST对包含指定值的行进行分区;
2.为每个分区指定值列表,从而包含匹配列值的行将存储在相应分区中;例如,假设您的每个订单都放在组织到五个地区中的多个不同店铺中的一个店铺,并且您希望店铺订单放在相同分区中的每个地区中;使用这种类型的分区,您可以任意分隔店铺;假定在这五个地区中有19个店铺,可以创建orders_list表,其按如下所示将来自相同地区的店铺的订单分配到相同分区;
CREATE TABLE orders_list
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
INDEX idx (id)
) ENGINE = InnoDB
PARTITION BY LIST(store_id)
(
PARTITION p0 VALUES IN (1, 3, 4, 17),
PARTITION p1 VALUES IN (2, 12, 14),
PARTITION p2 VALUES IN (6, 8, 20),
PARTITION p3 VALUES IN (5, 7, 9, 11, 16),
PARTITION p4 VALUES IN (10, 13, 15, 18)
);

— HASH分区;
1.PARTITION BY HASH确保在分区中平均分布数据;
2.与RANGE或LIST不同,HASH不需要单独的分区定义;
3.该分布基于创建表时提供的表达式,使用关键字PARTITIONS,后跟所需的(整数)分区数量;
CREATE TABLE orders_hash
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY HASH(id) PARTITIONS 4;

— KEY分区;
1.PARTITION BY KEY类似于HASH,只是MySQL服务器使用其自己的散列表达式;
2.不要求分区表达式返回整数或NULL;
3.[LINEAR] KEY后面的表达式仅包含一组零或更多列名称,多个名称由逗号分隔;幻灯片中的示例创建orders_key表,其按已放置订单的日期进行分区;
CREATE TABLE orders_key
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY KEY(order_date) PARTITIONS 4;

— 子分区;
1.RANGE和LIST分区表可以进行子分区:子分区(也称为复合分区)是对分区表中的每个分区进行进一步划分;
2.子分区可以使用HASH,LINEAR HASH,KEY或LINEAR KEY分区;
3.在此“店铺订单”数据库示例中,根据orders_range表,可以进一步划分表,这样表可以将每个分区拆分为两个子分区,一共5*2=10个子分区;
CREATE TABLE orders_range_hash
(id INT AUTO_INCREMENT,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500),
PRIMARY KEY(id, store_id)
) ENGINE = InnoDB
PARTITION BY RANGE(id)
SUBPARTITION BY HASH(store_id) SUBPARTITIONS 2
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
TIPS:ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function

— 获取分区信息;
MySQL提供了多种方法来确定表的分区状态:
• SHOW CREATE TABLE:查看用于创建分区表的分区子句;
• SHOW TABLE STATUS:确定表是否进行了分区;
• INFORMATION_SCHEMA.PARTITIONS:查询INFORMATION_SCHEMA.PARTITIONS表;
• EXPLAIN PARTITIONS SELECT:显示给定SELECT语句使用的分区;

— 获取分区信息:SHOW CREATE TABLE;
SHOW CREATE TABLE orders_hash\G

— 获取分区信息:SHOW TABLE STATUS;
SHOW TABLE STATUS LIKE ‘orders_hash’\G
1.Create_options列包含字符串partitioned;
2.Engine列包含表的所有分区使用的存储引擎的名称;但是如果指定不同的引擎会报错;
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL;

— 获取分区信息:INFORMATION_SCHEMA;
1.可以在INFORMATION_SCHEMA.PARTITIONS表中查询分区详细信息;
2.获取所有数据库表及其分区的列表:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_list’ AND TABLE_SCHEMA=’orders’;
+—————-+——————+———————-+———————–+
| PARTITION_NAME | PARTITION_METHOD | PARTITION_EXPRESSION | PARTITION_DESCRIPTION |
+—————-+——————+———————-+———————–+
| p0 | LIST | store_id | 1,3,4,17 |
| p1 | LIST | store_id | 2,12,14 |
| p2 | LIST | store_id | 6,8,20 |
| p3 | LIST | store_id | 5,7,9,11,16 |
| p4 | LIST | store_id | 10,13,15,18 |
+—————-+——————+———————-+———————–+
5 rows in set (0.00 sec)

— 获取分区信息:EXPLAIN PARTITIONS;
1.显示MySQL处理分区的方式;
2.判断查询将访问哪些分区以及使用这些分区的方式;
3.只能用在包含行数据的表中:EXPLAIN PARTITIONS SELECT * FROM orders_range\G

— 更改分区;
1.数据库通常要求对现有分区设置进行动态更改;
2.MySQL允许多种类型的分区更改:
– 添加;
– 删除;
– 合并;
– 拆分;
– 重组;
– 重新定义;
3.使用ALTER TABLE语句的简单扩展进行这些更改;

— 重新定义分区类型;
1.将非分区表更改为分区表;
2.完全重新定义现有分区,包括类型;
3.将ALTER TABLE与PARTITION BY配合使用,将分区类型从RANGE更改为HASH:
1.查看分区类型:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
2.在线重定义:
ALTER TABLE orders_range PARTITION BY HASH(id) PARTITIONS 4;
3.再次查看;
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;

— 交换分区;
1.将表分区或子分区与表交换:
1.将分区或子分区中的所有现有行移至非分区表;
2.将非分区表中的所有现有行移至表分区或子分区;
3.交换分区不会在分区表或交换表上调用触发器;
4.将重置交换表中的所有AUTO_INCREMENT列;
2.要交换的表不能进行分区:
1.它必须与分区表具有相同表结构:
2.交换之前,非分区表中的行必须位于为分区或子分区定义的范围内;
例子:
1.把表修改回范围分区;
ALTER TABLE orders_range
PARTITION BY RANGE(id)
(
PARTITION p0 VALUES LESS THAN(10000),
PARTITION p1 VALUES LESS THAN(20000),
PARTITION p2 VALUES LESS THAN(30000),
PARTITION p3 VALUES LESS THAN(40000),
PARTITION p4 VALUES LESS THAN(50000)
);
2.查看表信息:
SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;
3.创建测试表并插入数据;
CREATE TABLE orders_ex
(id INT AUTO_INCREMENT PRIMARY KEY,
customer_surname VARCHAR(30),
store_id INT,
salesperson_id INT,
order_date DATE,
note VARCHAR(500)
) ENGINE = InnoDB;
INSERT INTO orders_ex VALUES(1, ‘test’, 1, 1, sysdate(), ”);
INSERT INTO orders_ex VALUES(10001, ‘test’, 1, 1, sysdate(), ”);
COMMIT;
INSERT INTO orders_range VALUES(1, ‘xxx’, 1, 1, sysdate(), ”);
INSERT INTO orders_range VALUES(10001, ‘xxx’, 1, 1, sysdate(), ”);
COMMIT;
4.分区交换;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
ERROR 1737 (HY000): Found a row that does not match the partition
5.修正数据;
mysql> UPDATE orders_ex SET id = 9999 WHERE id = 10001;
mysql> COMMIT;
mysql> ALTER TABLE orders_range EXCHANGE PARTITION p0 WITH TABLE orders_ex;
6.查看结果;
mysql> SELECT * FROM orders_ex;
mysql> SELECT * FROM orders_range PARTITION(p0);

— 删除分区;
1.可以删除一个或多个分区;
1.删除分区与删除行不一样;虽然删除分区确实可以删除分区内的所有数据,但是使用DROP,PARTITION的主要目的是删除分区指定自身,以及不再允许将数据传递到该分区;
2.可以使用ALTER TABLE … TRUNCATE PARTITION语句删除分区表的一个或多个分区中的所有行(子句中指定的分区不必是连续的);
2.RANGE或LIST表允许此操作;
3.将ALTER TABLE与DROP PARTITION配合使用:
mysql> ALTER TABLE orders_range TRUNCATE PARTITION p0, p1;
mysql> SELECT * FROM orders_range;
mysql> ALTER TABLE orders_range DROP PARTITION p0;
mysql> SELECT PARTITION_NAME, PARTITION_METHOD, PARTITION_EXPRESSION, PARTITION_DESCRIPTION
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME=’orders_range’ AND TABLE_SCHEMA=’db1′;

— 使用DROP PARTITION的问题;
1.需要DROP特权才能使用DROP PARTITION;
2.删除分区会删除该分区中的所有数据;
3.DROP PARTITION不返回所删除的行数;
4.DROP PARTITION更改表定义;
5.可以按任何顺序删除分区;
6.将根据新分区处理插入到所删除分区中的行;
1.删除LIST分区会禁止已经匹配该分区的INSERT和UPDATE操作;
2.删除RANGE分区将删除该分区及其数据,来自INSERT和UPDATE操作的新数据将存储在范围中的下一个更大的分区,如果没有范围大于已删除分区的范围的分区,无法对已删除分区的范围中的数据执行INSERT和UPDATE操作;
7.可以用单个语句删除多个分区:ALTER TABLE orders_range DROP PARTITION p1, p3;

— 删除分区;
1.要删除表中的所有分区从而将其恢复为非分区表,请将ALTER TABLE与REMOVE PARTITIONING配合使用;
1.此操作不会删除任何表数据;
2.REMOVE PARTITIONING执行完整表复制,与普通ALTER TABLE ALGORITHM=COPY一样;
例子:
1.使orders_range表恢复到非分区状态:ALTER TABLE orders_range REMOVE PARTITIONING;
2.查看orders_range表信息:
SHOW TABLE STATUS LIKE ‘orders_range’;

— 更改分区对性能的影响;
1.根据分区的数量,与创建非分区表相比,创建分区表的速度稍微慢一些;
2.分区操作处理速度比较:
1.对于大型事务表,DROP PARTITION比DELETE快得多;
2.ADD PARTITION在RANGE和LIST表上相当快;
3.对于在KEY或HASH表上运行的ADD PARTITION,速度取决于已经存储的行数;
1.如果存在更多数据,需要花费更长时间来添加新分区;
2.在KEY/HASH上运行的ADD PARTITION将所有行重新分布到新数量的分区,有效地执行完整表复制;
4.对超大型表运行COALESCE PARTITION,REORGANIZE PARTITION和PARTITION BY时,它们的执行速度可能会很慢;
3.在此类操作过程中,硬件I/O的开销比分区引擎的开销高得多;

— 测验;
d

— 分区:存储引擎功能;
1.分区存储在与InnoDB表相同位置中的文件中:
1.可以提供DATA DIRECTORY选项来重定位分区;
2.每个PARTITION子句可以包括一个[STORAGE] ENGINE选项,该选项没有作用,每个 分区使用的存储引擎与表作为一个整体而使用的存储引擎相同;
2.每个分区在以下数据目录中具有其自己的文件:#P#.ibd
1.如果禁用innodb_file_per_table,分区将存储在共享表空间中;
3.所有数据和索引都将进行分区:
1.不能仅对数据或仅对索引进行分区;
4.分区可用于其他存储引擎:
1.不可用于MERGE,FEDERATED,CSV;
2.是在存储引擎级别实现的;
eg:
CREATE TABLE entries
(id INT,
entered DATE
)
PARTITION BY RANGE(YEAR(entered))
(
PARTITION p0 VALUES LESS THAN (2000) DATA DIRECTORY = ‘/data/p0’,
PARTITION p1 VALUES LESS THAN MAXVALUE DATA DIRECTORY = ‘/data/p1’
);

— 分区与锁定;
1.MySQL表将在执行服务器代码的过程中锁定;
1.锁定操作在执行每个语句过程中由表存储引擎来处理;
2.每个存储引擎处理锁的方式均不相同;
2.每个分区有一个存储引擎实例;
1.将要求锁定每个非删改分区/存储引擎实例;
3.锁定分区表时,将锁定非删改分区;
1.仅在执行语句过程中保持该锁定;
4.ALTER…PARTITION语句通常在表上使用写入锁;
1.从此类表进行的读取相对来说不受影响,待定INSERT和UPDATE操作在分区一完成后就会执行;优先级的问题;
2.执行ALTER TABLE t PARTITION类型语句时,其在复制阶段需要在表上使用写入锁,除非该命令通过ALGORITHM=INPLACE支持联机操作;复制阶段完成后,该命令需要对表进行独占访问来更改表定义;

— 分区限制;
1.常规:
– 每个表的最大分区数为8192;
– 不支持空间类型;
– 不能对临时表进行分区;
– 不能对日志表进行分区;
2.外键和索引:
– 不支持外键;
– 不支持FULLTEXT索引;
– 无全局索引:每个分区都有各自的索引;
3.仅可能在以下情况下进行子分区:
– 通过RANGE和LIST进行分区时;
– 通过LINEAR HASH或LINEAR KEY进行时;

— 分区表达式限制;
1.用于RANGE/LIST/HASH分区的表达式的结果必须为整数:
1.RANGE COLUMNS和LIST COLUMNS分区允许更大范围的数据类型;
2.RANGE COLUMNS和LIST COLUMNS分区类型不需要整数列或表达式;
3.RANGE COLUMNS和LIST COLUMNS分区可以在分区键中使用多个列;
4.该列可以是任何整数类型/DATE/DATETIME/CHAR/VARCHAR/BINARY/VARBINARY;
2.不能在分区表达式中使用TEXT或BLOB;
3.不允许使用UDF,存储函数,变量,某些运算符和某些置函数:
1.运算符:|,&,^,< <,>>,~;
4.不应在创建表后更改SQL模式;
5.分区表达式中不支持子查询;
6.分区表达式中使用的所有列都必须是表的所有唯一索引的一部分:
1.因为索引必须与数据一起进行分区;
2.要保持“唯一性”,具有相同唯一键的两行必须转至相同分区;否则,它们将违反唯一键限制;

— 课后练习;

标签:

MySQL OCP-09-存储引擎

1月 28th, 2017

— 存储引擎和MySQL;
1.每个存储引擎具有一组特定运行特征:
1.这些特征包括用于管理查询争用的锁的类型以及该存储引擎是否支持事务;
2.这些引擎属性对查询处理性能,并发性以及死锁预防具有一定影响;
2.虽然可以使用许多其他存储引擎,但对于大多数用例,InnoDB都是最适合的;

— 可用存储引擎;
1.MySQL提供并维护多个存储引擎,MySQL服务器还可以与许多第三方存储引擎兼容;
2.MySQL存储引擎是数据库服务器内的低级别引擎,它负责存储和检索数据,并且可以通过内部MySQL API进行访问,在某些情况下,可以由应用程序直接访问;
3.当前支持的存储引擎:
• InnoDB
• MyISAM
• MEMORY
• ARCHIVE
• FEDERATED
• EXAMPLE
• BLACKHOLE
• MRG_MYISAM
• NDBCLUSTER
• CSV
TIPS:
1.请注意,InnoDB和NDBCLUSTER是具有事务性的唯一两个MySQL存储引擎;
2.请注意,一个应用程序可以在任何给定时间使用多个存储引擎;

— InnoDB存储引擎;
InnoDB是MySQL的默认存储引擎,具有高可靠性和高性能的特点,同时也被认为是效率最高的引擎之一:
• 事务安全(遵从 ACID):通过事务提交,回滚以及用于保护用户数据的故障恢复功能,可以实现ACID遵从性;
• MVCC(Multi-Versioning Concurrency Control,多版本并发控制)
– InnoDB行级别锁定;
– Oracle样式一致非锁定读取;
• 表数据进行整理来优化基于主键的查询;
• 支持外键引用完整性约束:包括级联删除和更新;
• 大型数据卷上的最大性能;
• 将对表的查询与不同存储引擎混合:在同一语句内,可以将InnoDB表与来自其他MySQL存储引擎的表混合;
• 出现故障后快速自动恢复:支持一致而联机的逻辑备份;
• 用于在内存中缓存数据和索引的缓冲区池;
• 全文索引:可以在文本列中有效地搜索单词或短语;

— InnoDB作为默认存储引擎;
1.查看默认引擎:SHOW VARIABLES LIKE ‘%engine%’;SELECT @@default_storage_engine;
2.设置默认引擎:SET default_storage_engine = MyISAM;

— InnoDB功能;
1.[a]需要InnoDB Barracuda文件格式;
2.[b]在服务器中(通过加密功能)而不是在存储引擎中实现;
3.[c]在服务器中而不是在存储产品中实现;使用MEB进行备份时例外,其不是在服务器级别进行;

— 显示存储引擎设置;
1.查看默认引擎:
• SHOW VARIABLES LIKE ‘%engine%’;
• SELECT @@default_storage_engine;
2.使用SHOW确认每个表的存储引擎:
• SHOW CREATE TABLE City\G
• SHOW TABLE STATUS LIKE ‘CountryLanguage’\G
3.使用INFORMATION_SCHEMA确认每个表的存储引擎:
SELECT TABLE_NAME, ENGINE
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’ AND TABLE_SCHEMA = ‘world_innodb’\G

— 设置存储引擎;
1.在启动配置文件中设置服务器存储引擎:
[mysqld]
default-storage-engine=
2.设置默认引擎:
• SET default_storage_engine = MyISAM;
• SET @@storage_engine=
;
3.使用CREATE TABLE语句指定:CREATE TABLE t (i INT) ENGINE =
;

— 将现有表转换为InnoDB;
1.使用ALTER TABLE更改存储引擎:
1.语法:ALTER TABLE t ENGINE = InnoDB;
2.使用ALTER TABLE更改引擎是一项成本很高的操作,因为它在内部将所有数据从一个引擎复制到另一个引擎;
3..不要将mysql数据库(例如user或host)中的MySQL系统表转换为InnoDB,此操作不受支持,系统表使用MyISAM引擎;
2.从其他存储引擎克隆表:
1.语法INSERT INTO SELECT * FROM ;
2.创建作为使用其他存储引擎的表的克隆的InnoDB表时,还可以在插入数据后创建索引;
3.以较小的块插入大型表以获得更大的控制:通过添加Key的范围,多次插入表;
INSERT INTO newtable SELECT * FROM oldtable
WHERE yourkey > something AND yourkey < = somethingelse; 补充:插入了所有记录后,可以重命名表; 1.在转换大型表的过程中,增加InnoDB缓冲区池的大小来减少磁盘I/O; 2.还可以增加InnoDB日志文件的大小; -- InnoDB系统表空间; 1.InnoDB使用两个主要的基于磁盘的资源来运行: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 2.日志文件(ib_logfileN文件):记录重做和恢复的事务活动; 2.InnoDB在表空间中存储数据,索引,元数据,日志和缓冲区; 1.默认情况下(5.6之后),数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdataN文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 3.共享表空间是逻辑概念,可以包含多个文件;可以将共享表空间中的最后一个文件配置为自动扩展,在这种情况下,如果表空间装满,InnoDB会自动扩展该表空间; 4.默认情况下,共享表空间还包含回滚段,事务修改行时,将在回滚段中存储撤消日志信息;此信息用于回滚失败的事务,通过将innodb_undo_logs选项设置为非零值并配置innodb_undo_tablespaces的值,来将回滚段移出共享表空间; 1.innodb_undo_logs:定义在InnoDB引擎使用的系统表空间中,一个事务可以使用的回滚段的数量; 2.innodb_undo_tablespaces:当innodb_undo_logs参数为非零值时,undo日志使用的表空间文件个数;默认情况下所有的重做日志都在系统表空间中;可以通过innodb_undo_directory参数来指定文件的目录; TIPS:除了系统表空间可以包含多个文件,其它的数据表空间只能包含一个文件; -- 数据表空间; 1.除了系统表空间之外,InnoDB还在数据库目录中创建另外的表空间,用于每个InnoDB表的.ibd文件; 1.InnoDB创建的每个新表在数据库目录中设置一个.ibd文件来搭配表的.frm文件; 2..ibd文件用作表自己的表空间文件,存储表内容和索引; 3.InnoDB数据字典和回滚段存放在共享表空间; 4.可以使用innodb_file_per_table选项控制此设置; 5.您需要默认设置来使用一些其他功能,例如表压缩和快速截断; 2.基于表的表空间具备以下优势: 1.表压缩 2.空间回收(通过 TRUNCATE) 3.动态行格式 3.(可选)在共享表空间中存储数据: 1.使用skip_innodb_file_per_table选项; 2.在启动时或者会话过程中进行设置,将innodb_file_per_table选项设置为OFF;禁用该选项不会影响已经创建的任何InnoDB表的可访问性;如果要修改这个值的话要关闭服务器,修改配置文件,启动服务这几步; 4.使用共享表空间可获得以下优势: 1.移除大量数据的语句使用较少文件系统开销,例如DROP TABLE或TRUNCATE TABLE; 2.避免冗余临时数据文件; TIPS:可以混合同一数据库中不同表中的表空间类型,更改该设置仅会更改已创建的新表的默认值; -- 表空间目录结构; MySQL服务器使用磁盘空间有几种方式: 1.数据库目录:每一个数据库对应一个数据目录(datadir)下的目录,不管你创建哪种类型的表.比如,一个数据库目录可以包含MYISAM引擎的表,INNODB引擎的表或者混合的表; 1..ibd文件:用作表自己的表空间文件,存储表内容和索引; 2..frm文件(Table Format Files):包含了表结构的描述信息,每一个表都有一个.frm文件在对应的数据库目录下.它与表使用的哪种引擎没有关系; 3..MYD/.MYI:由MYISAM存储引擎在适当的数据库目录下创建的数据库数据文件和索引文件; 2.InnoDB存储引擎: 1.表空间:在单个逻辑存储区域中存储表内容(数据行)和索引; 1.默认情况下,数据和索引存储在基于表的表空间中; 2.InnoDB使用共享表空间(ibdata1文件)来包含元数据(数据字典),撤销日志,更改缓冲区以及两次写缓冲区; 2.日志文件(ib_logfileN文件):记录回滚和恢复的事务活动; 3.服务器日志文件和状态文件:这些文件包含服务器上执行过的语句信息,日志被用于复制和数据恢复,获得优化查询性能的信息和误操作信息; 1.auto.cnf:保存服务器的server_id; 2.mysql.sock:套节字文件; 3.hosname.err:错误日志; 4.hostname.pid:运行进程id; 5.mysql_upgrade_info:升级信息; -- 共享表空间配置; 1.通过添加数据文件增加共享表空间大小; 1.在my.cnf文件中使用innodb_data_file_path选项,值应该是一个指定项列表: [mysqld] innodb_data_file_path=datafile_spec1[;datafile_spec2]... 2.如果命名了多个数据文件,则通过分号[;]符号分隔这些文件; 3.增加InnoDB系统表空间的一种更容易(但是不太可取)的方式是从一开始就将其配置为自动扩展;在表空间定义中指定最后一个数据文件的autoextend属性,然后,InnoDB在用完空间时会以64MB增量自动增加该文件的大小; 4.可以通过设置innodb_autoextend_increment系统变量的值来更改该增量大小,以MB为单位; 5.如果使用关键字autoextend定义了最后一个数据文件,重新配置表空间的过程必须考虑最后一个数据文件已经增长到的大小;获取数据文件的大小,将其向下舍入到1024×1024字节(=1MB)的最近倍数,并在innodb_data_file_path中显式指定舍入的大小;然后,可以添加其他数据文件(使用尚不存在的文件名); 6.仅innodb_data_file_path中的最后一个数据文件可以指定为自动扩展; 2.配置示例:创建一个表空间,其中包含一个名为ibdata1且大小为50MB(固定)的数据文件和一个名为ibdata2且大小为50MB(自动扩展)的数据文件: [mysqld] innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend 3.默认情况下将文件放置在data目录中,如果需要,使用innodb_data_home_dir参数显式指定文件位置; -- 日志文件和缓冲区:图表; 1.日志缓冲区: 1.客户机执行事务时,其进行的更改存放在InnoDB日志中,最新日志内容缓存在内存中(日志缓冲区); 2.主要包括:事务记录和重做日志记录等; 3.缓存的日志信息会在事务提交时写入并刷新到磁盘上的日志文件中,虽然也可能提前发生该操作; 4.如果修改表时发生故障,将使用日志文件进行自动恢复; 5.MySQL服务器重新启动时,它会重新应用日志中记录的更改,以确保表中反映所有已提交事务; 6.更改InnoDB日志文件的数量和大小的信息: 1.innodb_log_buffer_size 2.innodb_log_compressed_pages 3.innodb_log_file_size 4.innodb_log_files_in_group 5.innodb_log_group_home_dir 2.缓冲区池: 1.InnoDB维护其自己的缓冲区池用于在主内存中缓存常用的数据和索引;InnoDB在表空间中存储其表和索引;InnoDB表可以非常大,甚至在文件大小限制为2GB的操作系统上也是如此; 2.主要是包括插入插销日志和更新撤销日志; 3.可以启用多个缓冲区池来最大程度地降低争用; 4.此高速缓存应用于如此多种类型的信息并且将处理速度提高如此多,应该将多达80%的数据库服务器的物理内存分配给InnoDB缓冲区池; 5.由innodb_buffer_pool_%参数控制; -- 配置缓冲区池以获得良好性能; InnoDB缓冲区池很大时,可以从内存(而不是硬盘)快速检索许多数据请求: 1.MySQL使用多个缓冲区池实例来最大程度地降低线程之间的争用:用来改进大型缓冲区池的局限; 2.存储在缓冲区池中或者从缓冲区池读取的每个页将基于散列值分配给一个缓冲区池:使用散列函数将缓冲区池中的每个页随机分配给一个缓冲区池 3.每个缓冲区池管理其自己的数据:每个缓冲区池管理其自己的空闲列表,刷新列表,LRU以及连接到缓冲区池的所有其他数据结构,并由其自己的缓冲区池互斥锁进行保护; 4.配置有下列选项: 1.innodb_buffer_pool_instances:默认情况下,MySQL配置八个缓冲区池实例,范围为1-64;仅当innodb_buffer_pool_size的值大于等于1GB时才生效; 2.innodb_buffer_pool_size:缓冲池的总大小,要保证每个缓冲池实例都大于1G; 5.在启动时预装入缓冲区池来提高性能: 1.innodb_buffer_pool_load_at_startup; 2.innodb_buffer_pool_dump_at_shutdown; -- NoSQL Memcached API; 1.在相同进程空间中运行:InnoDB支持具有集成的Memcached守护进程的插件,其在相同进程空间中运行,从而以较低开销进行高效数据通信; 2.使用Memcached API:对于某些操作,通过使用较简单的Memcached API(基于文本的协议和二进制协议),而不是使用SQL层,应用程序可以在提交SQL语 句时绕过所需的解析和优化阶段,并避免检查强类型数据的开销;此类型的应用程序称为NoSQL(Not Only SQL,不仅仅是SQL); 3.使用InnoDB存储:除了传统的Memcached环境(其在重新启动Memcached时会丢失键/值存储),InnoDB集成表示可以将Memcached存储配置为持久存储到InnoDB;因为InnoDB支持该存储,所以可以受益于InnoDB的其他功能; 1.自动持久存储到磁盘; 2.缓冲区和故障恢复的优势:进行保护以免于故障,中断和损坏;可以使用内存存储的Memcached存储(由强大而持久的InnoDB表支持)来获得速度和简化; 4.仍可以通过SQL访问基础表以进行报告,分析,即席查询,批量装入,集合运算(例如并集和交集)以及非常适合于SQL的表达性和灵活性的其他运算(例如汇总和聚合); 5.因为Memcached消耗相对较少的CPU并且易于控制其内存容量,所以可以与MySQL实例在同一系统上一起顺利运行; -- 配置Memcached; 1.配置Memcached表:SOURCE /scripts/innodb_memcached_config.sql(/usr/share/mysql/innodb_memcached_config.sql);
1.innodb_memcached_config.sql脚本设置Memcached配置数据库及其表;
1.创建innodb_memcache数据库;
2.cache_policies表:存放每个Memcached操作的后备存储策略;本地RAM高速缓存,InnoDB或两者;
3.Containers表:存放用作Memcached后备存储的每个InnoDB表的配置;
4.config_options表:存放separator和table_map_delimiter配置选项的值;
2.默认InnoDB后备存储是test数据库中的demo_test表,可以通过修改innodb_memcache数据库中的容器表来更改此项;
3.该插件启用许多可配置变量:SHOW VARIABLES LIKE ‘daemon_memcached%’;
2.安装Memcached插件:
INSTALL PLUGIN daemon_memcached SONAME “libmemcached.so”;
3.配置Memcached变量:
1.daemon_memcached_option:在启动时传递给Memcached守护进程的空格分隔的选项;
2.daemon_memcached_enable_binlog:启用Memcached操作的二进制日志记录,其允许复制Memcached API操作;
3.daemon_memcached_r_batch_size:控制读取批处理大小,默认值为1,出于性能考虑;
4.daemon_memcached_w_batch_size:控制写入批处理大小,默认值为1,所以会立即提交每个Memcached写入操作;
例子:可以从test.demo_test中查看;
— Key (c1) must be VARCHAR or CHAR type, memcached supports key up to 255
— Bytes
— Value (c2) must be VARCHAR or CHAR type
— Flag (c3) is a 32 bits integer
— CAS (c4) is a 64 bits integer, per memcached define
— Exp (c5) is again a 32 bits integer

— 键/值数据;
1.使用Memcached的主要好处是读取或写入键/值数据时效率较高;
2.Memcached协议支持add/delete/set/get/incr和decr等操作;
3.每个操作处理高速缓存中的键/值对:对于键/值查找,Memcached件使用的直接低级别数据库访问路径比等效SQL查询高效得多;
1.键和值作为字符串来传递;
2.键不能包含空格,回车符,换行符,制表符等空白字符:由于Memcached协议的规定;
3.键类似于表中的主键,值类似于同一表中的另一列:在概念上,这类似于一个包含两列的表,其中键列是主键(因此是唯一的);
4.高速缓存是平面名称空间:
1.确保每个键都是唯一的;
2.例如,如果您提供其他表中的键/值,则在键前面附加该表名称;
5.值可以是简单字符串(或存储为字符串的数值)或序列化复杂数据:值存储为字符串,可以表示简单值,例如数值,字符串或已经序列化的复杂值(即,置于可以重新构造原始指针和嵌套的文本形式);必须在API接口的Memcached外部执行此序列化(例如,通过在代码中实现JavaSerializable接口)或者通过将复杂数据格式化为JSON或XML;
类似地,必须在访问memcached表的SQL语句中将简单数值从字符串转换为数值(例如,通过使用CAST()函数);

— 将NoSQL用于MySQL数据库;
1.使用InnoDB表作为Memcached操作的容器(后备存储);
1.Memcached 协议不支持简单聚合,联接数据或SQL协议支持的其他功能;
2.使用InnoDB表作为Memcached键/值对的容器,可以运行功能强大的SQL语句,来执行使用Memcached协议创建和修改的数据分组,聚合和联接;
2.键和值是字符串:
1.键最大大小为250字节;对于更长的键:
1.将键值散列到小于250字节的值;
2.为Memcached键重新配置允许的大小;
3.通过指定不同类的Memcached数据来使用多个InnoDB表作为Memcached容器;
1.在containers表中配置此项;
2.指定容器表的名称,模式和键列,值,标志以及其他设置;
3.每个容器具有一个Memcached名称,可以用来标识Memcached操作的容器;操作定位于名为“default”的容器,直到被要求更改容器;
4.例如,以get @@newcontainer形式发出Memcached请求,将后续请求重定向到名为“newcontainer”的容器;

— 引用完整性;
将相关的数据分割到单独表中时,可以使用主键和外键来加强表关系;
1.一个表的主键被另一个表中的键引用:
1.引用键称为外键;
2.每个表都需要主键:通过使用最常查询的列为每个表指定主键,如果没有明显主键则指定自动增量值;
2.关系确定SQL语句的结果:
1.INSERT到辅助表(而没有指向主表的外键)不会完成;
2.通过在联接的列上使用外键将提高JOIN性能:而且外键列需要添加索引;
3.UPDATE或DELETE数据时,可以使用CASCADE选项自动更新或删除辅助表中的任何相关数据;
3.可以查看示例数据库中的City表;
TIPS:一般不会使用外键;

— 多版本控制;
1.保存关于已更改行的旧版本的信息:
1.支持并发和回滚等事务功能;
2.InnoDB使用回滚段中的信息执行事务回滚中所需的撤消操作;
3.它还使用该信息来构建行的早期版本以实现一致读取;
2.使用称为回滚段的数据结构在表空间中存储信息:回滚段是包含撤消日志的存储区域,其默认情况下是系统表空间的一部分;
3.InnoDB向数据库中存储的每行添加三个字段:
1.DB_TRX_ID:六个字节的字段,指示插入或更新该行的最后一个事务的事务标识符;此外,删除在内部被视为更新处理,其中设置行的特殊位来将其标记为删除;
2.DB_ROLL_PTR:称为滚动指针的七个字节的字段;它指向写入回滚段的撤消日志记录,如果更新了行,撤消日志记录包含重建行被更新前的内容所需的信息;
3.DB_ROW_ID:包含行ID的六个字节的字段,随着插入新行而单调增加;如果InnoDB自动生成群集索引,该索引包含行ID值;否则,DB_ROW_ID列不显示在任何索引中;
4.仅当行放弃针对删除写入的更新撤消日志记录时,才物理地删除该行(及其索引):
1.这是快速清除操作;
2.清除线程可能需要更多资源,因为它可能会变慢并占用磁盘空间;
3.如果在表中按相同速度以小批量插入和删除行,清除线程会滞后,表会因为所有“死”行而变得越来越大,使所有操作受到磁盘限制并且非常慢;在这种情况下,通过使用innodb_max_purge_lag系统变量限制新行操作并向清除线程分配更多资源;

— 锁定;
InnoDB具有下列一般锁定属性:
1.InnoDB无需设置锁即可实现一致读取,因为它使用多版本控制,从而不需要锁;修改行的事务可以查看其自己版本的那些行,撤消日志使其他事务可以查看原始行;要强制SELECT语句锁定数据,可以向该语句添加锁定修饰符(FOR UPDATE, LOCK IN SHARE MODE);
2.需要锁时,InnoDB使用行级别锁定,与多版本控制一起,这将产生良好的查询并发性,因为给定表可以由不同客户机同时读取和修改;
3.InnoDB在发现需要锁时可以获取行锁,从不通过将锁转换为页锁或表锁来提升该锁;这样可以最大程度降低锁争用,提高并发性(虽然其对DDL操作使用表级别锁定);
4.可能会出现死锁,因为InnoDB在事务过程中直到需要锁时才获取锁,InnoDB可以删除死锁并回滚一个事务来解决死锁;
5.失败的事务最终开始超时,InnoDB将回滚这些事务;

— 下一键锁定;
1.InnoDB使用一种称为下一键锁定的算法:
1.使用行级别锁定;
2.搜索/扫描表索引并在索引记录上设置共享锁或互斥锁;
3.因此,行级别锁实际是索引记录锁;
2.InnoDB在索引记录上设置的下一键锁定会影响索引记录前面的“间隙”:
1.如果用户在索引中的记录上具有共享锁或互斥锁,其他用户无法按索引顺序正好在锁定记录前面(间隙)插入新记录;
3.锁定间隙可防止出现“幻影问题”:
1.同一查询在不同时间产生不同行集时,事务内会出现幻影问题;
2.在下面的示例中,第一个事务锁定大于10的任何值,即使该值不存在也是如此:
事务 1:
SET autocommit = ON;
START TRANSACTION;
SELECT c FROM t WHERE c > 10 FOR UPDATE;
事务 2:
INSERT INTO t(c) VALUES (50);

— 一致非锁定读取;
1.一致读取表示InnoDB使用多版本控制对查询提供某个时间点的数据库快照;查询看见在时间点之前提交的事务所进行的更改,不会看见后面或未提交事务进行的更改;
2.在幻灯片示例中,仅当会话2已提交了插入并且会话1也已提交(从而时间点超过会话2的提交)时,会话1才看见会话2插入的行;

— 减少死锁;
1.要减少死锁的可能性,使用事务,而不是LOCK TABLE语句(不升级锁):
1.保持事务较小并经常提交:从而其不会保持打开状态很长时间;
2.不同事务更新多个表时使用相同顺序的操作;
3.在WHERE子句中使用的列上创建索引;
2.如果事务由于死锁而失败则重新发出该事务:
1.死锁并不危险,只需要重试;
2.如果出现死锁,InnoDB会检测到该情况并回滚一个事务(牺牲者);
3.按固定顺序访问表和行。
4.向表添加精心选择的索引。
5.不要设置隔离级别以避免死锁。
1.隔离级别更改读取操作的行为,而死锁是由于写入操作而出现的;
6.监视死锁出现的频率;
1.使用SHOW ENGINE INNODB STATUS\G命令;

— 外键锁定;
InnoDB支持外键CONSTRAINT设置:
• 需要检查约束条件
• 用于 INSERT、UPDATE 和 DELETE
• 在要检查的记录上设置共享记录级别锁
• 在约束失败的情况下也将设置锁

1.约束就是在表中的一个或多个列值上放置的限制,从而积极强制执行完整性规则;约束使用索引来实现;
2.如果在表上定义了外键约束,将在用于引用外键约束的插入,更新或删除操作中的任何记录上放置共享记录级别锁;
3.InnoDB逐行检查外键约束;执行外键检查时,InnoDB在其必须查看的子或父记录上设置共享行级别锁;InnoDB立即检查外键约束;该检查不会延迟到事务提交时;
4.InnoDB在约束失败的情况下也设置这些锁;
5.幻灯片中的约束示例显示City表的CountryCode列与Country表的Code列相关;

— MyISAM存储引擎;
1.在硬盘上MyISAM引擎用三种文件管理表,这些文件都在数据目录中:
1..frm文件:存储表结构的定义;
2..MYD文件:存储表行的内容;
3..MYI文件:存储表的索引;
2.在操作系统上支持链接文件的特性,可以把数据文件和索引文件存放在不同的磁盘上以减小I/O:
1.查看当前服务器指定的默认存储引擎:show variables like ‘storage_engine’;
2.通过查看CREATE TABLE的帮助(? CREATE TABLE),我们可以看到,可以通过指定DATA DIRECTORY和INDEX DIRECTORY选项来把数据文件和索引文件存放在不同的目录;
1.创建存放数据文件和索引文件的目录:mkdir -p /tmp/mysql/data /tmp/mysql/index;
2.修改文件的权限:chown -R mysql:mysql /tmp/mysql;
3.创建表,并指定数据文件和索引文件的目录:CREATE TABLE t (id INTEGER) ENGINE=MyISAM DATA DIRECTORY=’/tmp/mysql/data’ INDEX DIRECTORY=’/tmp/mysql/index’;
4.此时就会在数据文件下创建真正数据文件和索引文件的链接;
5.有没有参数可以指定缺省的目录,每次创建表都去指定太费劲了吧;??????
6.分区表指定数据文件和索引文件;???????
3.MyISAM与其它引擎相比具有最灵活的AUTO_INCREMENT COLUMN功能,自动增长列:
1.创建自动增长列:create table t(id integer auto_increment primary key, name varchar(50));
2.使用自动增长列(auto_increment),此列必须是主键或者是主键中的一列;
3.插入时可以指定id列也可以不指定,如果指定的话就插入指定的值,如果不指定则插入比当前最大值大1的值,即max(id)+1;
4.获得上次插入的id值使用last_insert_id()函数:select last_insert_id();
5.设置自动增长id的初始值:alter table table_name auto_increment=n;但是如果这是的n小于max(id)则不生效;
6.为一个已存在的表增加一个自增长列:alter table table_name add column col_name integer auto_increment not null, add primary key(id);
4.不支持事务:
1.查看自动提交事务的选项:show variables like ‘autocommit’;
2.修改此选项:set session autocommit=0|1;
3.因为不支持事务,所以不管此选项为何值,都不能commit和rollback;
5.MyISAM的表可以被转换成一个fast,compressed,read-only的表从而节省空间:
1.使用myisampack工具:mysqlpack –help;
2.进入到数据文件目录:myisampack table_name;
3.观察发现压缩比例为90%,只剩下10%的数据量大小;对指定DATA DIRECTORY和INDEX DIRECTORY选项的表同样使用,亲测;
4.重启之后检测表(check table table_name)就会发生错误,表内数据丢失,对只读的性能支不好,如果使用的话,压缩完之后一定要备份;
6.MyISAM支持FULLTEXT索引和spatial数据类型,全文索引和空间数据类型:
1.对全文索引支持不够好,可以使用instr()函数去实现;
2.也可以使用第三方的插件去实现;
7.MyISAM引擎对锁的支持:
1.MySQL管理使用MyISAM引擎的表之间的查询竞争使用表级的锁,这使得查询的性能非常快,多个查询可以同时访问一张表.对于写操作,会使用一个exclusive的表级锁去阻止其它读写操作.虽然表级锁会影响点性能,但是不会发生死锁;
2.显式的对表加锁:lock table table_name lock_type; lock tables table_name1 lock_type, table_name2 lock_type;
3.解锁:unlock tables;
4.如果在session中加write锁,则本session可以进行读写操作,其它的session对表进行读和写就需要等待锁释放;
5.如果在session中加read锁,则本session和其它session都可以进行读操作,本session无法进行写操作,其它的session对表进行读和写就需要等待锁释放;
6.如果在session中加read local锁,即只锁住加锁前一时刻的表的数据,则本session可以进行读操作(查到的只是加锁前的数据),但是不能进行写操作,其它session可以进行插入操作(查到的是所有的数据),但是删除和更新操作需要等待锁释放;
8.可以通过LOW_PRIORITY和HIGHT_PRIORITY来影响MyISAM表的内部调度机制;可以通过INSERT DELAYED先把表中数据缓存到服务端,等到表不忙的时候再插入;
1.一般情况下在OLTP系统中,更新操作优先于查询操作,因为更新操作时间比较短,查询操作时间比较长(所以一般要提升查询操作的优先级,降低更新操作的优先级);要修改查询的优先级使用:SELECT HIGH_PRIORITY * FROM t;修改更新语句的优先级使用:INSERT INTO t VALUES(1);可以使用read local锁做实验,优先级低的无法插入,优先级高可以插入;
2.数据延迟插入:INSERT DELAYED INTO t VALUES(1);可以使用write锁来模拟这种情况,不实用delayed则等待,使用就马上返回成功,但是真正插入要等表不忙的时候;当有延迟操作时会产生一个延迟进程,它是一个共用的线程,只有一个;
3.查看当前链接到服务器的进程列表:show processlist;杀掉进程:kill id;
4.与延迟插入有关的全局变量:show global variables ‘%delayed%’;
1.delayed_insert_limit:延迟插入时,插入多少条数据后检查是否有查询操作,如果有查询操作,则查询操作先执行;
2.delayed_insert_timeout:限制延迟操作的等待时间;
3.delayed_queue_size:定义延迟线程队列的大小,以行为单位;
5.与延迟插入有关的全局状态:show global status ‘%delayed%’;
1.delayed_errors:记录延迟插入错误的次数;
2.delayed_insert_threads:当前有多少线程在使用延迟操作;
3.delayed_writes:使用延迟线程插入的记录行数;
9.数据表的存储格式是非常的轻便的,因此可以通过直接拷贝表所在的目录到其它的主机以实现对表的备份额迁移:
1.拷贝时需要注意文件的权限和拥有者,一般指定cp -a选项;
2.拷贝表时可能会有缓存,最好能在关闭服务器的情况系进行,以保证数据完整性;
10.可以指定一个MyISAM表最少存储多少条记录,这允许MyISAM表去调整表内部行指针的大小,也可以配置缺省的表内部行指针大小供服务器使用:
1.在创建表时使用MAX_ROWS和MIN_ROWS选项(? CREATE TABLE),这个值只是一个参考值,实际的数据行数可以大于也可以小于这个值;
2.配置系统默认大小使用myisam_data_pointer_size选项:show variables like ‘myisam_data_pointer_size’,这个选项是当创建表时没有使用MAX_ROWS选项时使用,默认为6,不能小于2也不能大于7.它代表可以用几个字节(一个字节是8位)去寻址,指定n,表示2的8n次方,即指定为2,3,4,5,6时依次代表可以存放64K,16M,4G,1T,256T的数据;
3.把max_rows设为5,myisam_data_pointer_size范围是2~7,2个字节就足够了(2^16),所以其实就是让myisam是用2个字节的指针,并不是说最多5行.如果把max_rows设置为大于2^16的值,就要用3个字节表示,所以myisam_data_pointer_size会设为3,此时最多存放2^24行.
4.如果创建表时指定了max_rows,那么表至受限于最大的行数;如果没有max_rows,则表受限于最大大小;
11.导入数据时,可以先禁用掉索引,等到导入数据后再打开索引,这样会加快导入数据的速度.当使用LOAD DATA [LOCAL] INFILE导入数据时,它会自动的禁用和启用索引,以加快导入速度
1.查看LOAD DATA [LOCAL] INFILE的帮助:? load data; load data [local] infile file_name into table table_name;
2.与LOAD DATA INFILE相对应的是SELECT … INTO OUTFILE,把表中的数据导入到文件中;
3.语法:select * from t into outfile ‘/tmp/t.txt’ fields terminated by ‘,’ enclosed by ‘”‘; 字段以[“]包围,字段之间以[,]号分割,默认每行之间以换行分割;
4.语法:load data infile ‘/tmp/t.txt’ into table t fields terminated by ‘,’ enclosed by ‘”‘;
12.向MyISAM表中添加数据时,如果磁盘空间不足时服务器会挂起操作,直到空间变为可用状态,然后继续完成操作;
13.MyISAM表的行存储格式
1.查看表使用哪种行存储格式:与查看表使用引擎的方式一样,表信息的Row_format字段.
1.表中包含有可变长度的列,则表就是Dynamic的;
2.表中没有包含可变长度的列,表就是Fixed的;
2.固定行存储格式:
1.所有的行有固定的大小;
2.行存储的位置是在行长度的整数倍的位置,方便查找;
3.占用更多的存储空间;
3.动态行存储格式:
1.行占用动态的长度;
2.查看起来不是很高效;
3.节省空间;
4.更容易产生碎片;
4.压缩行存储格式:
1.表被压缩以节省空间;
2.优化的存储以加快检索;
3.表是只读的;
5.做实验时可以使用hexdump工具,查看数据文件中实际存放的数据,可以加上-C选项;
6.手动像数据表文件中添加数据后不可用,需要经历check table table_name; optimize table table_name; repair table table_name;三个过程,应该是在information_schema库中写入统计信息;

— MEMORY存储引擎;
1.MEMORY表的.frm文件在数据库目录下,数据和索引都存储在内存中;
2.对MEMORY表的操作性能都很高;
3.在服务器重启之后,MEMORY表中的数据就不存在了,但是他的表结构还是存在的;
4.因为MEMORY表使用的是内存,所以不适用于大表;
5.MEMORY表使用表级锁来处理查询竞争,所以不会发生死锁;
6.MEMORY表不支持TEXT和BLOB类型;
7.它支持两种索引:HASH和BTREE
8.缺省使用HASH索引,这种索引算法使用唯一索引会非常高效,然而HASH索引只能用于比较运算符(=, <>);
1.BTREE索引算法更适合于范围查找,例如>,< 或者between; 2.可以使用创建表时的max_rows和服务器参数max_heap_table_size来限制MEMORY表的大小; 9.设置索引: 1.hash:alter table table_name add index idx_name using hash(col_name); 2.btree:alter table table_name add index idx_name using btree(col_name); 10.当不需要MEMORY表的内容时,要释放被MEMORY表使用的内存,使用DELETE FROM, TRUNCATE TABLE或者删除整个表DROP TABLE; 11.通过指定--max-heap-table-size选项来限制表的最大大小; -- ARCHIVE存储引擎; 1.ARCHIVE引擎被用来以非常小的空间存储大量无索引数据,从而占用更少的资源; 2.要使用此引擎需要在configure时添加--with-archive-storage-engine选项;可以通过show variables like 'have_archive'查看; 3.创建一个ARCHIVE表会有一个保存表结构的.frm文件,保存数据和元数据的.ARZ和.ARM文件,如果有优化操作的话还有一个.ARN文件; 4.ARCHIVE引擎仅仅支持SELECT和INSERT操作,以及除了几何数据类型外的所有数据类型; 5.存储:当inesrt数据时,archive引擎使用zlib无损数据压缩的方式压缩,optimize table可以分析表,并打包为更小的格式; 6.查询:在查询数据时,记录根据需要被加压缩,没有行缓存.SELECT操作执行完全表格扫描,当一个SELECT发生时,它找出当前有多少行可用,并读取行的数量; 7.不支持索引; 8.使用行级别锁定; 9.支持AUTO_INCREMENT列; 10.支持ORDER BY操作和BLOB列; -- BLACKHOLE存储引擎; 1.BLACKHOLE引擎就像黑洞一样,它接收数据但是是丢弃它而不是存储它,查询时总返回NULL; 2.创建BLACKHOLE引擎后会在数据库目录创建一个.frm文件,没有其它文件与之关联; 3.它支持所有的索引; 4.要想使用此引擎在configure时使用--with-blackhole-storage-engine选项; 5.用途: 1.BLACKHOLE表不记录任何数据,如果二进制日志被允许,SQL语句被写入日志,可以用作重复器或者过滤器机制; 2.转储文件语法的验证; 3.来自二进制记录的开销测量,通过比较允许二进制日志功能的BLACKHOLE的性能与禁止二进制功能的BLACKHOLE的性能; 4.因为BLACKHOLE本质是一个no-op存储引擎,可以用来查找与引擎自身不相关的性能瓶颈; -- 课后练习; 补充: -- BDB存储引擎; 1.需要下载包含BDB版本的MYSQL(MySql-Max分发版支持BDB); 2.安装时在configure加入--with-berkeley-db选项; 3.BDB启动选项 --bdb-home:指定BDB表的基础目录,应该和--datadir相同; --bdb-lock-detect:BDB锁定检测方式,DEFAULT,OLDEST,RANDOM,YOUNGEST; --bdb-logdir=path:BDB日志文件目录; --bdb-no-recover:不在恢复模式启动Berkeley DB; --bdb-no-sync:不同步刷新BDB日志,使用--skip-sync-bdb-logs代替; --bdb-shared-data:以多处理模式启动Berkeley DB(初始化Berkeley DB之时,不要使用DB_PRIVATE); --bdb-tmpdir=path:BDB临时文件目录; --skip-bdb:禁止BDB存储引擎; --sync-bdb-logs:同步刷新BDB日志.这个选项默认被允许,请使用--skip-sync-bdb-logs来禁止它; 4.创建一个BDB表会有两个文件,一个是.frm文件,一个是存放表数据和索引的.db文件; 5.支持事务; 6.每一个BDB表都需要一个primary key,如果创建时不指定则会隐式创建一个; 7.SELECT COUNT(*) FROM tbl_name对BDB表很慢,因为在该表中没有行计数被维持; 8.使用页面级别的锁; 9.使用mysql客户端是,应该使用--no-auto-rehash选项; 10.BDB表的限制: 1.每个BDB表在.db文件里存储文件被创建之时到该文件的路径,这个被做来允许在支持symlinks的多用户环境里检测锁定.因此,从一个数据库目录往另一个目录移动BDB表是不能的; 2.当制作BDB表的备份之时,你必须使用mysqldump要么做一个包含对每个BDB表的文件(.frm和.db文件)及BDB日志文件的备份.BDB存储引擎在它的日志文件存储未完成的事务以及要求它们在mysqld启动的时候被呈出来.BDB日志在数据目录里,具有log.XXXXXXXXXX(10位数字)形式名字的文件; 3.如果允许NULL值的列有唯一的索引,只有单个NULL值是被允许的,这不同于其它存储引擎; -- Federated存储引擎; 1.它访问的是在远程数据库表中的数据,而不是本地的表,仅在-MAX版的MySql可用; 2.如果要使用需要在在configure时添加--with-federated-storage-engine选项; 3.创建一个FEDERATED表时,服务器在数据库目录创建一个.frm文件,没有表数据文件,因为实际数据在远程数据库上; 4.操作表的内容时需要MYSQL客户端API,读取数据通过SELECT * FROM table_name来初始化,然后通过mysql_fetch_row()的c函数去一行行读取; 5.创建一个FEDERATED表 1.假设在远程服务器上有一个表为tbl; 2.在本地创建表:create table federated_tbl(id int, name varchar(10)) engine=federated 3.connection='mysql://root@remote_host:3306/federated/tbl'; 4.其他CONNECTION的格式: 1.CONNECTION='mysql://username:password@hostname:port/database/tablename'; 2.CONNECTION='mysql://username@hostname/database/tablename'; 3.CONNECTION='mysql://username:password@hostname/database/tablename'; 6.局限性: 1.远程服务器必须是一个MYSQL服务器; 2.不支持事务; 3.支持增删改查的操作和索引,但是不支持ALTER TABLE和DROP TABLE; 7.用途:可以跨服务器访问,不用创建DB LINK了; -- MRG_MYISAM存储引擎; 1.MGR_MYISAM表是一组MyISAM表的集合,每一个MERGE表在磁盘上都有2个文件,一个是.frm文件和一个包含组成MERGGE表的MyISAM表的名称的.MRG文件.这两个文件都存放在数据库目录下; 2.当对一个MERGE表操作时,相当于对组成MERGE表的所有的MyISAM表的操作; 3.一个MERGE表可以突破MyISAM表的最大大小的限制; 4.MYSQL管理MERGE表的查询竞争使用表级锁,即锁住组成它的MyISAM表,所以不会产生死锁; 5.一个MERGE表是很轻便的,因为.MRG文件是一个文本文件; 6.可以进行增删改查操作,在插入操作时可以指定是往哪个表中插入数据; 7.当MERGE引擎要锁住一个MERGE表时,就会对组成它的所有MyISAM表加锁; 8.对MERGE表执行SEELCT操作时,对底层的表加read lock; 9.对MERGE表执行更新操作(delete, update)时,对底层的表加write lock; 10.对MERGE表的操作: 1.创建MyISAM表m1:create table m1(id int, name varchar(10)) engine=myisam; 2.创建MyISAM表m2:create table m2(id int, name varchar(10)) engine=myisam; 3.创建MERGE表:create table m(id int, name varchar(10)) engine=merge union=(m1, m2); 4.插入数据:insert into m1 values(1, 'a');insert into m1 values(1, 'a'); 5.查询:select * from m; 6.创建MyISAM表m3:create table m3(id int, name varchar(10)) engine=myisam; 7.加入merge表:alter table m union=(m1, m2, m3);很灵活,可以互相组合; 8.修改表使得merge表可以插入数据,create table m(id int, name varchar(10)) engine=mgr_myisam union=(m1, m2) insert_method=last;method_method=0:不允许插入;first:插入到union中的第一个表;last:插入到union中最后一个表; 11.可以通过直接修改.MRG文件来修改MERGE表,修改后使用flush tables;来刷新表缓存; -- CSV存储引擎; 1.CSV引擎使用逗号分隔值格式的文本文件存储数据(eg:["1","aaa"]); 2.要想使用此引擎在configure时使用--with-csv-storage-engine选项; 3.CSV引擎不支持null值,所以在创建时应加上not null选项; 4.CSV引擎不支持索引; 5.创建CSV表会在数据库目录创建一个.frm文件,一个.CSV的文本文件用来存储数据和一个.CSM文件; -- EXAMPLE存储引擎; 1.EXAMPLE引擎是一个不做适合事情的存储引擎,主要用于MySql源码中一个例子用来演示如何开始编写一个新的存储引擎; 2.需要在configure时添加--with-example-storage-engine选项; 3.EXAMPLE引擎不支持编译索引;

标签:

MySQL OCP-08-事务与锁定

1月 28th, 2017
— 事务;
在MySQL中,只有那些使用事务存储引擎(如InnoDB)的表才支持事务;这些语句不会对非事务存储引擎所管理的表产生任何明显影响;
— 事务流程图;
银行转账的流程, 比如由事务来保证;
— ACID;
1.定义:
    1.Atomic(原子性):整个事务事务中的所有的操作要不全都成功,要不全都取消;
    2.Consistent(一致性):在事务开始之前和事务结束以后,数据的完整性约束没有被破坏;
    3.Isolated(隔离性):两个事务的执行是互不干扰的;
    4.Durable(持久性):事务完成以后,该事务对数据库所有的操作便持久的保存在数据库中,不会被回滚;
2.缺点:事务处理能够更有力地保证数据库操作的结果,但同时也需要更多的CPU周期,内存和磁盘空间开销;事务属性对于某些应用程序来说是必不可少的,但并非所有应用程序都如此,您可以选择对您的应用程序最有意义的事务属性;
3.使用场景:
    1.金融类操作通常需要使用事务,保证数据完整性会比增加开销所带来的成本更重要;
    2.另一方面,如果某个应用程序用于记录通过网页访问数据库表的操作,则因服务器主机出现故障而丢失几条记录可能是允许的;
— 事务SQL控制语句;
1.SAVEPOINT:事务中可以回滚到的位置的唯一标识符;
2.ROLLBACK TO SAVEPOINT:回滚之后,savepoint仍然存在,从而可以“重用”它;
3.RELEASE SAVEPOINT:不会删除任何事务语句;
— SQL控制语句流程:示例;
mysql> SET AUTOCOMMIT = ON;
mysql> CREATE TABLE t_trans(id INT, comment VARCHAR(50));
mysql> BEGIN;
mysql> SAVEPOINT sv_bgn;
mysql> INSERT INTO t_trans VALUES(1, ‘first record!’);
mysql> SELECT * FROM t_trans;
mysql> SAVEPOINT sv_ins;
mysql> INSERT INTO t_trans VALUES(2, ‘second record!’);
mysql> UPDATE t_trans SET comment = ‘after update’ WHERE id = 2;
mysql> SAVEPOINT sv_upd;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_ins;
mysql> SELECT * FROM t_trans;
mysql> ROLLBACK TO sv_bgn;
mysql> SELECT * FROM t_trans;
mysql> COMMIT;
TIPS:
1.START TRANSACTION或BEGIN:事务打开,直到通过COMMIT或ROLLBACK显式关闭;
2.事务结束之后/ROLLBACK到保存点之前的保存点都会被自动删除;
— AUTOCOMMIT模式;
1.启用AUTOCOMMIT后,每个语句仍会以原子方式执行(隐式提交);而不是没有事务;
2.可以通过在插入多个行时比较违反约束限制的效果,便可看出启用AUTOCOMMIT和根本不具有事务之间的差别;在非事务表(如MyISAM)中,一旦发生错误,语句就会终止,已经插入的行会保留在该表中;而对于InnoDB表,已经插入的所有行都会从该表中删除,从而不会产生任何实际影响;
    INSERT INTO t VALUES(1),(‘x’);
— 隐式提交;
1.导致隐式提交语句的行为就像在执行实际语句之前发出COMMIT一样;
2.这些语句本身并非事务语句,也就是说,如果成功,则无法回滚;
— 事务存储引擎;
1.使用SHOW ENGINES\G语句可以列出所有已编译到MySQL服务器中的引擎;
2.相关信息:
    1.Engine:引擎名字;
    2.Support:值为YES或NO,用于指示该引擎是否可以使用;如果该值为DISABLED,则表示该引擎存在,但已关闭;值DEFAULT用于指示服务器在默认情况下使用的存储引擎;
    3.Comment:相关的介绍;
    4.Transactions,XA和Savepoints列用于指示该存储引擎是否支持这些功能;
— 事务隔离问题;
1.前提:服务器可能同时具有多个未提交的事务(每个会话最多一个事务),当多个客户机并发访问同一个表的数据时,可能会出现一致性问题;
2.“脏”读:一个事务读取另一个未提交的事务所做的更改;eg:假定事务T1修改了某行,如果事务T2读取该行,并发现修改内容,但T1尚未提交,则会出现“脏”读问题;之所以会成为一个问题,是因为如果T1回滚,所做的更改会被撤消,但T2并不会意识到这一点;
3.不可重复读:指原始数据不可重复读;表示一个事务内的多个相同查询返回了不同的结果集;eg:事务T1内有两次对表t1的查询,但是在查询的间隙事务T2对表做了更新,结果导致第二次查询跟第一次查询的结果不同;(InnoDB使用快照来完成)
4.虚读(幻读):假定事务T1和T2开始,并且在事务T1的一次读取或者更新了某些行,此时如果事务T2插入一个新行,而事务T1读取或者更新操作时发现该行,则会发生虚读问题(新行会成为虚行);
— 隔离级别;
1.如果一个客户机的事务更改了数据,其他客户机的事务是应发现这些更改还是应与其隔离?事务隔离级别可以确定同时进行的事务在访问相同数据时彼此交互的方式;
2.使用存储引擎可实现隔离级别,隔离级别选项在不同的数据库服务器之间是不一样的,因此,InnoDB所实现的级别可能与其他数据库系统所实现的级别并不完全对应;
3.InnoDB可实现四种隔离级别,用于控制事务所做的更改在多大程度上可由其他同时进行的事务注意到:
    1.READ UNCOMMITTED:允许发生“脏”读,不可重复读和虚读;
    2.READ COMMITTED:允许发生不可重复读和虚读;未提交的更改仍不可见;(Oracle默认)
    3.REPEATABLE READ:无论其他事务所做的更改是否已提交,两次都会获得相同的结果;换句话说,也就是不同的事务会对相同的数据产生一致的结果;(InnoDB默认)
    4.SERIALIZABLE:与REPEATABLE READ类似,但其限制性更强,即一个事务所选的行不能由其他事务更改,直到第一个事务完成为止;
4.隔离级别越高,并发性能越差;
— 隔离级别问题;
— 设置隔离级别;
1.在选项文件或者命令行可以设置的参数值为:transaction-isolation = [READ-UNCOMMITTED | READ-COMMITTED | REPEATABLE-READ | SERIALIZABLE];
2.对于SET TRANSACTION ISOLATION LEVEL语句,可以设置的参数值为:SET TRANSACTION ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE];
3.此事务级别可以全局设置,也可以按会话设置,如果没有显式指定,则事务隔离级别将按会话进行设置;
4.设置的全局默认事务隔离级别适用于从设置时起所有新建立的客户机连接,现有连接不受影响;
eg:
    SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
    SET @@global.tx_isolation = ‘READ-COMMITTED’;
— 全局隔离级别;
1.客户机始终可以修改其自身会话的事务隔离级别,但要更改全局默认事务隔离级别,则需要SUPER特权;
2.查看当前隔离级别,可使用tx_isolation服务器变量;
    1.查看当前会话:SELECT @@tx_isolation;
    2.查看全局和会话:SELECT @@global.tx_isolation, @@session.tx_isolation;
如果此变量未使用前缀,则会返回会话事务隔离级别。使用 global 和 session 前缀, 可以相应地显式获取全局或会话隔离级别。
也可以使用此服务器变量来设置事务隔离级别。此隔离级别与在SET TRANSACTION ISOLATION LEVEL语法中设置的隔离级别同样有效,其差别在于,它必须以字符串形式 (而不是普通关键字)来表示,并且必须用连字符(短划线或减号)来分隔用于定义隔离 级别的词,而不是使用空格来分隔
— 事务示例:隔离;
mysql> PROMPT s1>
s1> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
s1> SELECT @@global.tx_isolation;
+———————–+
| @@global.tx_isolation |
+———————–+
| READ-COMMITTED        |
+———————–+
mysql> PROMPT s2>
s2> START TRANSACTION;
s2> USE db1;
s2> INSERT INTO t1 VALUES(1);
s1> SELECT * FROM t1;
Empty Set (0.0 sec)
s2> COMMIT;
s1> SELECT * FROM t1;
+——+
| id   |
+——+
|    1 |
+——+
— 锁定概念;
1.锁定机制可以防止因多个客户机同时访问数据而出现的问题;该机制会以某个客户机的身份锁定数据,以限制其他客户机访问该数据,直到释放锁定为止;
2.该锁定允许持有锁的客户机访问数据,而限制与之争用访问权限的其他客户机可以执行的操作,锁定机制的结果是,将对数据的访问序列化,这样,在多个客户机要执行相互冲突的操作时,每个客户机都必须轮流等待;
3.并非所有类型的并发访问都会产生冲突,因此,允许客户机访问数据所需的锁定类型取决于该客户机是希望读取还是希望写入:
    1.如果某个客户机希望读取数据,则希望读取相同数据的其他客户机不会产生冲突,它们可以同时进行读取;但是,如果另一个客户机希望写入(修改)数据,则它必须等待,直到读取完成为止;
    2.如果某个客户机希望写入数据,则所有其他客户机都必须等待,直到写入完成,而无论这些客户机是想读取还是想写入;
    3.通过共享锁和互斥锁来完成;
4.锁定可以禁止并发进行相互冲突的更改并禁止读取正在更改的数据,从而可以防止数据损坏;
— 显式行锁;
1.InnoDB支持两种锁定修饰符,这两种修饰符可以添加到SELECT语句的末尾:
    1.LOCK IN SHARE MODE子句:共享锁,也就是说,虽然任何其他事务都无法获得互斥锁,但其他事务可以同时使用共享锁;由于正常读取不会锁定任何内容,因此它们不会受锁定的影响;
    2.FOR UPDATE子句:使用互斥锁来锁定选定的每一行,以防止其他对象获得这些行上的任何锁,但允许读取这些行;
2.在REPEATABLE READ隔离级别中,可以将LOCK IN SHARE MODE添加到SELECT操作中,这样,如果其他事务想修改选定行,则它们必须等待当前事务完成;这一点与SERIALIZABLE隔离级别的工作方式类似;
3.对于该隔离级别,InnoDB会隐式将LOCK IN SHARE MODE添加到SELECT语句中,而不会包含任何显式锁定修饰符;如果选择了在未提交的事务中修改的行,则会锁定SELECT,直到该事务提交为止;
— 死锁;
1.发生死锁的原因:
    1.事务需要获得多个表上的锁定,但顺序相反;
    2.每个事务因计时问题而仅获取了部分锁定;
2.处理办法:InnoDB会检测并中止(回滚)其中一个事务,并允许另一个事务完成;
    1.如果InnoDB对某个事务执行完整回滚,则该事务所设置的所有锁定都会被释放;
    2.但是,如果因出现错误而仅回滚了一个SQL语句,则该语句所设置的某些锁定可能会保留;(如果SELECT语句在事务中调用一个存储函数,而该函数中的一个语句出现错误,则该语句将回滚;同时,如果此后执行ROLLBACK,则整个事务将回滚)
    3.发生此问题的原因是,InnoDB存储行锁的格式使它此后无法识别锁和语句之间的对应关系;
— 事务示例:死锁;
s1> CREATE TABLE t_dl1(id INT);
s1> CREATE TABLE t_dl2(id INT);
s1> INSERT INTO t_dl1 VALUES(1);
s1> INSERT INTO t_dl2 VALUES(1);
s1> COMMIT;
mysql> PROMPT s1>
s1> START TRANSACTION;
s1> UPDATE t_dl1 SET id = 1 WHERE id = 1;
mysql> PROMPT s2>
s1> START TRANSACTION;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s1> UPDATE t_dl2 SET id = 1 WHERE id = 1;
s2> UPDATE t_dl1 SET id = 1 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
— 隐式锁;
1.InnoDB表会使用行级别锁定,以满足最高可能的并发;
2.对于InnoDB存储引擎,请避免使用LOCK TABLES语句;它不会提供任何额外的保护,却会减少并发性;利用自动行级别锁定,需对表进行锁定和解锁;
— 课后练习;
标签:

MySQL OCP-07-获取元数据

1月 28th, 2017
— 元数据访问方法;
1.什么是元数据:数据库是数据的结构化集合,元数据是“有关数据的数据”;
2.MySQL通过以访问元数据的方法:
    1.INFORMATION_SCHEMA:MySQL服务器包含一个被实现为名为INFORMATION_SCHEMA的数据库(模式)的数据字典,其中包含许多显示为表的对象;
    2.SHOW语句:用于获取服务器统计信息,模式和模式对象的相关数据的专用语法;
        1.SHOW DATABASES和SHOW TABLES:返回包含数据库和表名的列表;
        2.SHOW COLUMNS:生成表中列的定义;SHOW COLUMNS FROM table等于DESC table;
        3.需要有SELECT特权才能使用SHOW语句;
    3.DESCRIBE:可用于检查表结构和列属性的SQL语句快捷方式;
    4.mysqlshow:用作指向一些SHOW语句的命令行前端的客户机程序;
— INFORMATION_SCHEMA数据库;
— INFORMATION_SCHEMA表;
1.表信息
    • COLUMNS:表和视图中的列
    • ENGINES:存储引擎
    • SCHEMATA:数据库
    • TABLES:数据库中的表
    • VIEWS:数据库中的视图
2.分区
    • PARTITIONS:表分区
    • FILES:存储 MySQL NDB 磁盘数据表的文件
3.特权
    • COLUMN_PRIVILEGES:MySQL 用户帐户所拥有的列特权
    • SCHEMA_PRIVILEGES:MySQL 用户帐户所拥有的数据库特权
    • TABLE_PRIVILEGES:MySQL 用户帐户所拥有的表特权
    • USER_PRIVILEGES:MySQL 用户帐户所拥有的全局特权
4.字符集支持
    • CHARACTER_SETS:可用的字符集
    • COLLATIONS:每个字符集的排序
    • COLLATION_CHARACTER_SET_APPLICABILITY:适用于特定字符集的排序
5.约束和索引
    • KEY_COLUMN_USAGE:关键列的约束
    • REFERENTIAL_CONSTRAINTS:外键
    • STATISTICS:表索引
    • TABLE_CONSTRAINTS:表的约束
6.服务器设置和状态
    • KEY_COLUMN_USAGE:约束
    • GLOBAL_STATUS:所有 MySQL 连接的状态值
    • GLOBAL_VARIABLES:用于新的 MySQL 连接的值
    • PLUGINS:服务器插件
    • PROCESSLIST:指示哪些线程正在运行
    • SESSION_STATUS:当前 MySQL 连接的状态值
    • SESSION_VARIABLES:当前 MySQL 连接的生效值
7.例程及相关信息
    • EVENTS:预定事件
    • ROUTINES:存储过程和功能
    • TRIGGERS:数据库中的触发器
    • PARAMETERS:存储过程和功能参数以及存储函数
8.InnoDB
    • INNODB_CMP 和 INNODB_CMP_RESET:对压缩的 InnoDB 表的相关操作的状态
    • INNODB_CMPMEM 和 INNODB_CMPMEM_RESET:InnoDB 缓冲池中压缩页面的状态
    • INNODB_LOCKS:InnoDB 事务所请求和持有的每个锁
    • INNODB_LOCK_WAITS:每个阻塞的 InnoDB 事务的一个或多个行锁
    • INNODB_TRX:当前正在 InnoDB 内部执行的所有事务
    • TABLESPACES:活动的表空间
— INFORMATION_SCHEMA表列;
— 对INFORMATION_SCHEMA使用SELECT;
— INFORMATION_SCHEMA示例;
1.显示用于给定数据库中表的存储引擎;
2.查找所有包含SET列的表;
3.显示每个字符集的默认排序规则;
4.显示每个数据库中表的编号;
5.INFORMATION_SCHEMA表是只读的,无法用INSERT/DELETE/UPDATE之类的语句进行修改;如果执行这些类型的语句以尝试更改INFORMATION_SCHEMA表中的数据,服务器将生成错误;
— 使用INFORMATION_SCHEMA表创建Shell命令;
1.本幻灯片中的示例所示,SQL语句将生成一条输出,仅导出world_innodb数据库中那些以单词“Country”开始的的表;
2.输出将生成可以在shell命令行上正确执行的shell脚本;下一步是将此输出存储在一个可
在shell命令行中执行的批处理文件中,这通过添加子句INTO OUTFILE来完成:
    SELECT CONCAT(“mysqldump -uroot -pmysql “, TABLE_SCHEMA, ” “, TABLE_NAME, ” >> “,TABLE_SCHEMA, “.sql”)
    FROM TABLES WHERE TABLE_NAME LIKE ‘Country%’
    INTO OUTFILE ‘\tmp\Country_Dump.sh’;
3.然后可以在命令行中执行此文件,命令行将运行本幻灯片中所示的两个mysqldump命令:
    shell> \tmp\Country_Dump.sh
    shell> \tmp\mysqldump -uroot -pmysql world_innodb Country >> world_innodb.sql
    shell> \tmp\mysqldump -uroot -pmysql world_innodb Country_Language >> world_innodb.sql
— 使用INFORMATION_SCHEMA表创建SQL语句;
1.本幻灯片中的示例使用mysql命令执行了一个语句,以制作world_innodb数据库中所有表的精确副本;
    1.–silent命令在输出中删除列标题;
    2.–skip-column-names命令删除输出中的格式(使输出类似于表的格式);
    3.这两个命令用来确保对命令自身的解释是正确的,没有任何干扰执行的外部格式或标题行问题;
2.添加管道符号[|]并随之执行mysql命令会将这些SQL语句发送到MySQL服务器以便执行:
shell> mysql -uroot -pmysql –silent –skip-column-names -e “SELECT CONCAT(‘CREATE TABLE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘_backup LIKE ‘, TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘world_innodb’;” | mysql -uroot -pmysql
— MySQL支持的 SHOW 语句;
1.除了INFORMATION_SCHEMA表之外,MySQL还支持SHOW和DESCRIBE语句,作为访问元数据的备选方式;
2.SHOW和DESCRIBE语法不如使用INFORMATION_SCHEMA查询灵活,但是对于大多数用途,SHOW和DESCRIBE语法就足够了;在这些情况下,使用MySQL特定语法通常会更快速,简单;
3.可以通过多种形式使用SHOW语句,如下所示:
    • SHOW DATABASES:列出可用数据库的名称
    • SHOW TABLES:列出默认数据库中的表
    • SHOW TABLES FROM <database_name>:列出指定数据库中的表
    • SHOW COLUMNS FROM <table_name>:显示表的列结构
    • SHOW INDEX FROM <table_name>:显示表中有关索引和索引列的信息
    • SHOW CHARACTER SET:显示可用的字符集及其默认排序
    • SHOW COLLATION:显示每个字符集的排序
— SHOW语句示例;
mysql> SHOW DATABASES;
mysql> SHOW TABLES;
mysql> SHOW TABLES FROM mysql;
mysql> SHOW TABLES FROM INFORMATION_SCHEMA;
mysql> SHOW COLUMNS FROM CountryLanguage;
mysql> SHOW FULL COLUMNS FROM CountryLanguage\G
— 其他SHOW语句示例;
— DESCRIBE语句;
1.DESC table_name等效于SHOW COLUMNS FROM table_name;但是,SHOW COLUMNS支持可选的LIKE和WHERE子句,而DESCRIBE不支持;
2.当指定表名称作为参数时,EXPLAIN等效于DESCRIBE:mysql> EXPLAIN table_name;
— mysqlshow客户机;
1.mysqlshow客户机为各种格式的SHOW语句提供了一个命令行界面,这些语句用于列出数据库的名称,数据库中的表或有关表列或索引的信息;
2.mysqlshow客户机的选项部分可包含任一标准连接参数选项,例如–host或–user;如果默认连接参数不适合,则必须提供选项;mysqlshow也接受特定于其自身运行的选项;
3.使用–help选项调用mysqlshow可查看其选项的完整列表;
4.mysqlshow所执行的操作取决于已提供的非选项参数的数量;
— mysqlshow示例;
1.在没有参数的情况下,mysqlshow将显示类似于SHOW DATABASES的结果;
2.在使用单个参数的情况下,mysqlshow将该参数解释为数据库名称,并针对该数据库显示类似于SHOW TABLES的结果;
3.在有两个参数的情况下,mysqlshow将参数解释为数据库和表名称,并针对该表显示类似于SHOW FULL COLUMNS的结果;
4.在有三个参数的情况下,其输出与两个参数的情况相同,不同之处在于:mysqlshow将第三个参数当做列名称,且仅针对该列显示SHOW FULL COLUMNS输出;
5.如果命令行中最后的参数包含特殊字符,mysqlshow会将该参数解释为模式,且仅显示与该模式匹配的名称;特殊字符包括:%或*(匹配任一字符序列),以及_或?(匹配任一单个字符);本示例中的命令仅显示那些名称始于w的数据库;
TIPS:这些示例要求在执行命令时使用用户和口令作为参数;
— 课后练习;
标签:

MySQL OCP-06-数据类型

1月 28th, 2017
— 数据类型:概览;
1.在MySQL中,可用的数据类型分为四个主要类别:
    1.数值;
    2.字符;
    3.二进制;
    4.时间;
2.在每个类别中,存在多种特定的数据类型,这些数据类型使用的内存大小和磁盘空间各不相同,因此会对性能产生不同的影响;
3.对于单个记录,为列选择最佳数据类型所产生的性能影响相对较小,但随着数据库的增大,这些较小的影响可能会汇聚成大的影响;应在设计过程中事先考虑这些影响,以免造成性能问题;
4.MySQL还支持“空间扩展”存储引擎功能;通过空间扩展,能够以字符和二进制格式生成,存储和分析地理特征;
5.数据类型的ABC要素:
    1.Appropriate(适当):需要以最适合数据所代表的项的类型来表示数据;
    2.Brief(简洁):选择所用存储空间最少的数据类型。这可节省资源并提高性能;
    3.Complete(完整):选择的数据类型应分配有可存储特定项的最大可能值的充足空间;
— 创建带有数据类型的表;
1.示例1:第一个示例创建了一个名为people的表,该表包含一个名为id的整数值数字列和两个名称分别为first_name和last_name 的30个字符的字符串列;
2.示例2:第二个示例说明如何通过添加UNSIGNED属性来禁止在id列中使用负值;
— 数值数据类型;
1.数值数据类型类:
    1.整数:整数没有小数部分;即,没有小数位的单个整数值;
    2.浮点数(FLOAT/DOUBLE):表示包含整数部分,小数部分或同时包括二者的近似值数值;此类数据类型使用服务器主机的CPU所用的本机二进制浮点格式(IEEE 754)来表示值;该数据类型用于存储和计算会很高效,但值会存在舍入误差;如果列可为空,则默认值为NULL;如果列不可为空,则默认值为0(数值零);
    3.定点数(DECIMAL):包含整数部分,小数部分或同时包括二者;DECIMAL列中的所有值均包含相同的小数位数,并且完全按给定方式进行存储;DECIMAL值存储起来不如浮点数值高效,但DECIMAL值没有舍入误差,因此更加精确;通常用于存储货币值(其中每个值的精度比其存储大小更加重要);
    4.BIT:BIT列规范规定了一个宽度,指明每个值的位数(1至64位);
2.“精度”和“范围”是适用于浮点值和定点值(这两种类型可以同时包含整数部分和小数部分)的术语:
    1.精度:有效位数;
    2.范围:小数点右侧的位数;
TIPS:可以使用=号跟浮点值进行比较;但由于可能会出现舍入误差,因此结果可能并不总是与预期一样;
— 字符串数据类型;
1.这些类型在以下几个方面有所不同:
    1.数据是以固定长度格式还是可变长度格式存储;
    2.可存储的最大长度;
    3.该类型是否支持非结构化字符串值;
2.为存储字符串数据,MySQL提供了以下数值数据类型存储类:
    1.文本:用于表示真实的字符串数据类型;可以使用此类型存储非结构化且格式自由的字符串(如果其长度符合已定义的空间量);
    2.整数(枚举/集合):用于表示结构化字符串类型;称为“结构化”的原因是,存储在这些类型的列中的值必须通过您所提供的值列表构建,从而定义数据类型;
— 字符集和排序支持;
1.字符串具有以下特征:
    1.序列由特定字符集中的字符组成;
    2.多字节字符集中的每个字符所需的字节数可能是固定的,也可能是可变的;
    3.比较基于对字符串所关联的字符集的排序;
    4.多字节字符比较以字符而不是字节为单位执行;
    5.排序将验证字符的大写版本和小写版本是否等效;
    6.排序将决定同一个字符的不同重音标记是否等效;
    7.排序可以为二进制,其中基于数值字符值进行比较;
2.MySQL具有一个包含大量可供选择的字符集和排序的列表;选择正确与否会对性能有很大的影响;要查看可用的字符集:SHOW CHARACTER SET;
3.使用排序选择可以为相同的字符集选择不同的排序顺序;例如,显示所有latin1字符集:SHOW COLLATION LIKE ‘latin1%’;
— 二进制字符串数据类型;
1.与字符串不同,组成此类二进制字符串值的字节不代表字符;因此,二进制字符串没有附加的字符语义,而且缺少字符串类所表示的字符集和整理信息;
2.在MySQL中,BLOB与TEXT类型非常相似,没有附加的字符集和排序;
— 时间数据类型;
1.YYYY,MM,DD,hh,mm,ss和uuuuuu分别表示“年”,“月”,“日”,“小时”,“分钟”,“秒”和可选的“秒的小数位”;
2.通过为该类型提供一个参数,可声明秒的小数位(可选);例如,TIME(3)是TIME类型,其中秒的小数位部分最多可达三位;
3.TIMESTAMP值采用UTC进行存储(将根据需要转换为当地时间或从当地时间进行转换),其范围为从1970-01-01 00:00:00.000000至2038-01-19 03:14:07.999999;
4.可以定义DATETIME和TIMESTAMP以自动记录当前日期和时间,而不管对行执行INSERT或UPDATE操作的时间为何时;
5.TIMESTAMP与DATETIME的对比:
    1.TIMESTAMP列值的范围比DATETIME列值的范围小,因此存储每个值所需的字节数更少;
    2.通过为不允许出现NULL的TIMESTAMP指定NULL值,可以将其设置为当前日期和时间;
例子:
CREATE TABLE t_time
(
    id INT NOT NULL,
    t1 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    t2 TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    t3 TIMESTAMP NOT NULL ON UPDATE CURRENT_TIMESTAMP
);
mysql> INSERT INTO t_time(id) VALUES(1);
mysql> COMMIT;
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1                  | t2                  | t3                  |
+—-+———————+———————+———————+
|  1 | 2015-08-19 14:00:42 | 2015-08-19 14:00:42 | 0000-00-00 00:00:00 |
+—-+———————+———————+———————+
mysql> UPDATE t_time SET t1 = ‘2000-01-01 00:00:00’ WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM t_time;
+—-+———————+———————+———————+
| id | t1                  | t2                  | t3                  |
+—-+———————+———————+———————+
|  1 | 2000-01-01 00:00:00 | 2015-08-19 14:00:42 | 2015-08-19 14:01:43 |
+—-+———————+———————+———————+
1 row in set (0.00 sec)
— 空间数据类型;
MySQL实现了由开放地理空间协会(Open Geospatial Consortium, OGC)提出的“包含几何类型的SQL”环境的子集:
1.用于单个几何体值:
    1.GEOMETRY:层次结构的根类,任何类型的值;
    2.POINT:坐标空间中的单个位置;
    3.CURVE:一维几何体,由点组成的序列;
    4.LINESTRING:点之间具有线性内插的曲线;
    5.SURFACE:二维几何体;
    6.POLYGON:表示多边几何体的平面;
2.用于保存几何体值的集合:
    1.MULTIPOINT:Point元素;
    2.MULTICURVE:Curve元素;
    3.MULTILINESTRING:LineString元素;
    4.MULTISURFACE:Surface元素;
    5.MULTIPOLYGON:Polygon元素;
    6.GEOMETRYCOLLECTION:任意类型的几何体;
— 将数据类型设置为NULL;
1.在SQL中,对表达式求值的结果可以为null;null值是一个特殊的值,表示值无法进行计算或未知;
2.何时使用NULL:在数据库设计的开始阶段,当列出要包含在内的数据时,发现某些数据可能不适用于所有列;
3.何时不应使用NULL:在某些情况下,不应允许列中使用null值;
    1.最常见的情况即该列为主键时;
    2.另一个例子是:当列必须包含值时,数据库设计才有意义;
    3.NULL值不保存到索引中;
— 创建带有列属性的表;
本幻灯片中所显示的示例中的表包含一个不能包含NULL值的UNSIGNED整数列;一个使用utf8字符集的字符串列;以及一个默认值为“2013-01-01”的日期列;
— 列属性;
— 选择数据类型;
— 课后练习;
标签:

MySQL OCP-05-客户机和工具

1月 28th, 2017
— 命令行客户机程序;
1.这几个客户机都必须运行mysqld服务器程序,客户机才能访问数据库;
2.mysql是通用的命令行客户机,用于向服务器发送SQL语句,其中包括用于管理的SQL语句;
3.mysqladmin是可帮助管理服务器的管理命令行客户机;
4.mysqlimport为LOAD DATA INFILE语句提供了命令行界面;使用该客户机可以将数据文件装入表中,而无需手动发出LOAD DATA INFILE语句;之后讲导入导出数据时会具体再讲;
5.mysqldump是用于转储数据库和表中内容的命令行客户机;使用它可备份数据库或将其复制到其他计算机;
— 调用命令行客户机;
两种常见的选项语法格式:
1.长选项(–<option>):双横线选项后面加等号和参数;
2.短选项(-<option>):单横线选项后加空格和参数; mysql -V;
— 连接参数选项;
1.-h:后跟给定主机的主机名或IP地址,用于连接到服务器(默认为localhost);
2.-C:压缩客户机和服务器之间发送的所有信息(如果两者都支持压缩);
3.–protocol:后跟用于连接到服务器的连接协议:{TCP|SOCKET|PIPE|MEMORY};
4.-P:后跟端口号,用于代替默认值(3306);
5.-S:用于设置UNIX套接字文件或在Windows上使用的命名管道的名称;
6.–shared-memory-base-name:(仅Windows)通过共享内存连接到本地服务器时所使用的共享内存的名称;此选项仅在服务器支持共享内存连接时适用;
eg:mysql -h localhost -P 3306 -u root -p –compress –protocol=TCP -S /var/lib/mysql/mysql.sock
— 测验;
b
— 调用mysql客户机;
1.在命令行中提供凭证:
-u<name>选项后面可带或不带空格;-p<password>选项后面不带空格,如果对该选项使用空值,则系统会提示您输入口令;
2.在登录路径中提供凭证:使用此登录路径(通过mysql_config_editor创建)的凭证;
3.执行语句:mysql –login-path=admin -e “SELECT VERSION()”;
4.使用特定选项文件执行:
5.使用包含SQL语句的文本文件执行:
    1.可以使用[ > | < ]重定向流,用来运行脚本或者批处理文件;
    2.文件必须为纯文本格式,其中每个语句都有语句终结符;
    3.文件必须位于运行 mysql 客户机的主机上;
    4.<和-e是互斥的,不能同时使用;
— mysql 客户机:安全更新;
1.可能会不小心发出一些会修改表中多个行的语句或者会返回特别大的结果集的语句,使用–safe-updates选项可帮助防止这些问题;(可以启动时设置,也可以在选项文件中设置)
2.设置安全更新模式可施加以下SQL语句限制:
    1.UPDATE和DELETE仅在包含WHERE子句(该子句通过键值明确标识了要更新或删除的记录)或LIMIT子句时才允许使用;
    2.将单表SELECT语句中的输出限制为不超过1K行,但语句包含LIMIT子句时除外;
    3.仅当MySQL为处理查询所检查的行不超过100W时,才允许使用多表SELECT语句;
例子:
# mysql
mysql> use db1;
mysql> SELECT * FROM t1;
mysql> UPDATE t1 SET id = id + 1;
mysql> COMMIT;
# mysql -uroot -p –safe-update
mysql> use db1;
mysql> UPDATE t1 SET id = id + 1;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
— mysql 客户机:输出格式;
默认情况下,无论是以交互模式还是以批处理模式使用mysql,都会生成输出:
1.交互式:当以交互模式调用mysql时,会以表格格式显示查询输出,其中使用长条和短划线显示在方框列中列出的值;
    1.–table(或 -t):生成表格输出格式,即使在以批处理模式运行时也是如此,这是交互模式的默认格式;
2.批处理:在通过使用文件作为命令行中的输入源来调用mysql时,mysql会以批处理模式运行,并且在显示的查询输出中使用制表符来分隔数据值;
    1.–batch(或 -B):生成批处理模式(用制表符分隔的)输出(即使在以交互模式运行时也是如此),且不使用历史文件,这是批处理模式的默认格式;
    2.在批处理模式下,使用–raw或-r选项可禁止字符转换(例如,将换行符和回车符转换为\n或\r等转义序列),在原始模式下,将按字面值输出字符;
3.使用以下选项可选择不同于以上任一默认格式的输出格式:
    1.–html(或 -H):生成 HTML 格式的输出;
    2.–xml(或 -X):生成 XML 格式的输出;
— mysql 客户机:MySQL客户机命令;
1.列出所有的 MySQL 客户机级别命令:mysql> HELP;
    1.?与help一样,获得帮助信息;
    2.clear:清除当前输入的语句,在错误的语句后面输入\c即可;
    3.connect:重新连接服务器,可以指定主机名和服务器,输入connect dbname host或或者\r dbname host即可;
    4.delimiter:语句分隔符,设置执行语句的符号,执行delimiter $$即可;
    5.edit:调用vi修改sql语句,在要修改的语句后面输入\e即可;
    6.go:发送命令到mysql服务器;
    7.pager:查询内容太多,分页显示,设置:>pager less,禁止:>nopager;
    8.tee:把查询结果输入到一个文件中:>tee /tmp/rst.txt,禁止notee,主要用于数据库的备份脚本输出;
    9.prompt:改变提示符,如:prompt royalwzy>;则每次输入命令是提示符变为royalwzy>字符串(可以在配置文件中定义,prompt=xxx);
    10.quit/exit:都是退出客户端,输入quit,exit和\q都行;
    11.source:执行一个sql脚本文件,文件名为参数,source /tmp/sql.txt;
    12.system:执行操作系统的命令,用法:system ls -l /tmp 或者\! ls -l /tmp;
    13.status:查看服务器信息的状态,输入status或者\s即可;
    14.use:改变使用的数据库,后面跟数据库的名称,eg:use mysql;
    15.charset:修改字符集,可以通过status查看当前使用的字符集,eg:charset latin1;
    16.warnings:开启警告信息,当输入的sql语句出错时,可以通过show warning或者show errors来打印警告或者错误信息,设置:warning或者\W,关闭:nowarning或者\w;
    17.rehash:设置客户端自动补全功能;
        1.在服务器的配置文件中[mysql]节点下,默认使用no-auto-rehash选项;
        2.注释no-auto-rehash选项,添加auto-rehash选项,reboot;
2.显示会话状态信息:mysql> \s;
    Using outfile:就是使用tee命令指定的值,把当前所有的操作和输出都重定向到一个文本;
3.日志会话查询及其输出:tee my_tee_file.txt;
— mysql 客户机:SQL语句;
1.数据定义语言(Data Definition Language, DDL)
    1.CREATE DATABASE/TABLE:用于创建具有给定名称的数据库或表;
    2.ALTER DATABASE/TABLE:可更改数据库或表的整体特性;
    3.DROP DATABASE/TABLE:用于删除数据库中的所有表并删除该数据库,或者用于删除特定的表;
2.数据操纵语言(Data Manipulation Language, DML)
    1.SELECT:用于从一个或多个表中检索所选的行;
    2.INSERT:用于在现有表中插入新行;
    3.DELETE:用于删除现有表中的行;
    4.UPDATE:用于使用新值更新指定表中现有行的列;
    5.JOIN:组合使用多个表以用于SELECT,多表DELETE和UPDATE语句;
— mysql 客户机:有关SQL语句的帮助;
获得服务器端帮助:>help contents;
1.可以通过help cmd获得更详细的信息;
2.获得管理操作的命令:>help administration;
3.获得数据类型:>help data types;
4.获得show命令的帮助:help show; ? show;
5.show variables:打印系统变量,类似oracle中的show parameter;
    1.查询包含某一关键字的变量:show variables like ‘%buffer%’;
    2.设置变量的值用set命令:set global|session key=value;
6.show variables和show status的区别
    1.show variables:当服务器运行之后如果没有人工干预所有的参数不会发生改变;
    2.show status:显示服务器运行过程中的动态信息,值会动态改变;
7.不必逐步浏览目录列表中所列出的项来获取有关特定主题的帮助,只需给出主题作为关键字即可获得一些提示:HELP STATUS;
— mysql 客户机:SQL语句终结符;
1.[;]和[\g]:常见的终结符,二者等效,可互换使用;
2.\G:用于终止查询并以垂直方式显示查询结果,其中显示的每个输出行的每个列值均位于单独的行中;此终结符在查询生成的输出行非常宽的情况下十分有用(因为竖直格式可使结果更易阅读);
3.\c:如果决定放弃正在编写的语句,则可取消该语句并返回到新的mysql>提示符下;
— mysql 客户机:特殊语句终结符;
— mysql 客户机:重新定义提示符;
1.可以更改默认提示符,将当前信息放入提示符中,例如用户(\u),主机(\h)和数据库(\d);
2.PROMPT关键字之后第一个空格后面的所有内容都将成为提示符字符串的一部分,包括其他空格;该字符串可包含特殊序列;
3.要将提示符恢复为默认值,请指定不包含参数的PROMPT或\R;
eg:prompt (\u@\h)[\d]\>
— mysql 客户机:使用脚本文件;
1.脚本文件应该为纯文本文件,其中所包含语句的格式要与以交互模式输入的语句的格式相同;具体来说,每个语句都必须以 终结符结束;
2.SOURCE命令后的文件名无需用引号括起;
— mysqladmin客户机;
查看帮助:
1.create databasename:创建数据库;
2.debug:把bug信息写入日志文件;
3.drop databasename:删除数据库;
4.extended-status:列出服务器的附加信息;
5.flush-hosts:刷新主机缓存;
6.flush-logs:刷新所有的日志;
7.flush-status:清空状态变量;
8.flush-tables:刷新表;
9.flush-threads:刷新线程缓存;
10.flush-privileges:重新加载授权表,相当于reload;
11.kill id,id,…:杀掉mysql的进程;
12.password [new-password]:以当前格式修改密码;
13.old-password [new-password]:以旧的格式修改密码;
14.ping:检查mysqld是否活动;
15.processlist:列出所有活动的进程;
16.reload:重新加载授权表;
17.refresh:刷新所有的表,并重新打开日志文件;
18.shutdown:关闭Server;
19.status:查看服务器的状态信息;
20.start-slave:启动slave;
21.stop-slave:停止slave;
22.variables:打印变量状态;
23.version:查看服务器版本信息;
— 调用mysqladmin客户机;
— 测验;
b
— MySQL工具;
1.MySQL Workbench:一个可视化的下一代数据库设计应用程序,可用于高效设计,管理和记录数据库结构;它有开源和商业两种版本;
2.MySQL Proxy:一个位于客户机与MySQL服务器之间的简单程序,可监视,分析或传输客户机与服务器之间的通信;MySQL Proxy 的灵活性使其具有多种用途,包括负载平衡/故障转移/查询分析/查询过滤和修改以及其他操作;该工具当前尚不可用于生产;
3.MySQL Enterprise Monitor:MySQL的可视化企业监视系统,可用于检测MySQL服务器,通知潜在问题并就如何修复这些问题提供建议;
4.MySQL Enterprise Backup:使用该工具可以执行联机非阻塞“热”备份,从完整备份恢复数据,还支持创建压缩的备份文件;
5.MySQL Cluster Manager:通过自动执行常见的管理任务来简化MySQL Cluster Carrier Grade Edition数据库的创建和管理;
TIPS:345工具仅在MySQL商业版本中提供;
— MySQL Enterprise Monitor;
1.Enterprise Monitor可帮助管理多节点(可水平伸缩)环境中的多台MySQL服务器,调整当前MySQL服务器,发现MySQL数据库应用程序中存在的问题,并在这些问题变成严重问题或代价高昂的故障之前将其修复;
2.此Web GUI应用程序可主动监视企业数据库环境,并就MySQL如何增强MySQL所推动系统的安全性,优化其性能和减少其停机 时间提供专家建议;
3.Enterprise Monitor可监视各种类型的配置,从单个MySQL服务器直到支持繁忙Web站点的大型MySQL服务器群;
— MySQL Enterprise Monitor:系统信息显示板;
— MySQL Enterprise Monitor:访问;
— MySQL Workbench;
MySQL Workbench针对三种主要功能为DBA和开发者提供了基于GUI的跨平台集成工具环境:
1.SQL开发有助于进行以下任务:
    1.编辑和执行SQL查询和脚本;
    2.创建或变更数据库对象;
    3.编辑表数据;
2.数据库设计和建模有助于进行以下任务:
    1.执行增强型实体关系 (enhanced entity relationship, EER)建模;
    2.编辑和执行SQL查询和脚本;
    3.设计,生成和管理数据库;
3.服务器管理(取代了MySQL Administrator)有助于进行以下任务:
    1.启动和停止服务器;
    2.编辑数据库服务器配置;
    3.管理用户;
    4.导入和导出数据;
— MySQL Workbench:GUI窗口;
— MySQL Workbench:访问;
标准版(SE)提供了:
1.OSS版本的商业扩展;
2.高级功能;
3.需要付费;
— MySQL Proxy;
1.MySQL Proxy使用MySQL网络协议连接到网络,并提供一台或多台服务器与一台或多台MySQL客户机之间的通信;
在最基本的MySQL Proxy配置中,可以执行以下操作:
    1.仅将查询从客户机传递到MySQL服务器,然后返回;
    2.对使用该协议的任何与MySQL兼容的客户机,可不经修改地使用MySQL Proxy;这包括mysql命令行客户机,任何使用MySQL客户机库的客户机以及任何支持MySQL网络协议的连接器;
    3.监视和变更客户机和服务器之间的通信;
    4.使用查询拦截添加分析,插入其他查询并删除其他结果;交换信息的拦截可脚本化;
2.使用该代理可以对查询执行额外的监视,过滤或处理,而无需对客户机做出任何修改,客户机甚至不会知道正在与其通信的根本不是真正的MySQL服务器;
3.MySQL Proxy仍在开发中,尚未作为GA软件发行;可以了解OneProxy;
— MySQL连接器;
— 第三方API;
1.大多数第三方API均基于C客户机库,并针对其他某种语言提供绑定;
2.虽然MySQL开发团队的成员经常与这些产品的开发者密切合作,但是这些API尚未得到Oracle的正式支持;
— 测验;
c
— 课后练习;
标签:

MySQL OCP-04-服务器配置

1月 28th, 2017
— MySQL配置选项;
1.预编译的选项:
    1.在生成RPM包时指定的选项;
    2.在源码安装时指定的选项;
2.命令行选项:
    1.可以在启动服务器(mysqld)时在命令行上指定启动选项;
    2.默认情况下,服务器在运行时使用其配置变量的预编译值;但是,如果默认值不适合环境,则可添加运行时选项,让服务器使用其他值来执行以下操作:
        1.指定重要的目录和文件的位置;
        2.控制服务器写入的日志文件;
        3.覆盖服务器与性能相关的变量的内置值(即,控制最大同时连接数以及缓冲区和高速缓存的大小);
        4.在服务器启动时启用或禁用预编译的存储引擎通过使用命令行选项或选项文件,或者使用两者的组合,可以指定服务器启动时的运行时选项(以更改其配置和行为);
    3.命令行选项优先于选项文件中的任何设置
3.配置文件选项:在配置文件my.cnf中指定的启动选项;最常用的方式;
4.查看相关帮助mysqld –verbose –help;
— 使用选项文件的原因;
1.将选项放在文件中后,不需要每次启动服务器时都在命令行上指定选项;对于复杂的选项(如用于配置InnoDB表空间的选项),这样做更加方便,并且更不容易出错;
2.如果所有服务器选项都在一个选项文件中,则可概览服务器的配置情况;
3.MySQL程序可以访问多个选项文件中的选项,要创建或修改某个选项文件,必须拥有该文件的写入权限;客户机程序仅需要读取访问权限;
— 选项文件组;
1.选项文件中的选项按组进行组织,每个组前面有一个为组命名的[group-name]行,通常,组名称是选项组适用的程序的类别或名称;
选项组示例包括:
2.[client]:用于指定适用于所有客户机程序的选项;[client]组的一个常见用途是指定连接参数,因为在一般情况下,不管使用什么客户机程序,都要建立到同一个服务器的连接;
3.[mysql]和[mysqldump]:分别用于指定适用于mysql和mysqldump客户机的选项;此外,也可以单独指定其他客户机选项;
4.[server]:用于指定同时适用于mysqld和mysqld_safe服务器程序的选项;
5.[mysqld],[mysqld-5.6],[mysqld56]和[mysqld_safe]:用于指定适用于不同服务器版本或启动方法的选项;
— 编写选项文件;
1.要创建或修改某个选项文件,最终用户必须拥有该文件的写入权限;服务器本身仅需要读取访问权限;服务器读取选项文件,但不创建或修改选项文件;
2.如何在选项文件中写入一个选项:
    1.使用长选项格式(像命令行上使用的那样),但省略前导短划线;
    2.如果某个选项取值,则允许在等号两则加空格( = );此规则不适用于在命令行上指定的选项,eg:指定默认选项文件;
3.在幻灯片上的示例中,请注意以下方面:
    1.[client]:此组中的选项适用于所有标准客户机;
        1.host:指定服务器主机名;
        2.compress:指示客户机/服务器协议对通过网络发送的通信使用压缩;
    2.[mysql]:此组中的选项仅适用于mysql客户机;
        1.show-warnings:指示MySQL在每条语句后显示任何当前警告;
    3.mysql客户机同时使用[client]和[mysql]组中的选项,因此将使用显示的全部三个选项;
— 选项文件位置;
1.Linux:
    1./etc/my.cnf;
    2./etc/mysql/my.cnf;
    3./usr/local/mysql/etc/my.cnf;
    4.~/.my.cnf;
    5.如果设置了MYSQL_HOME环境变量,则将搜索$MYSQL_HOME/my.cnf文件;
2.Windows:
    1.C:\目录下的my.ini和my.cnf;
    2.C:\Windows(或C:\WinNT)目录;
    3.C:\Program Files\MySQL\MySQL Server <version number>目录;
3.MySQL命令行程序会在MySQL安装目录中搜索选项文件;
— 选项文件中的启动选项;
要在选项文件中指定服务器选项,需要在[mysqld]或[server]组下指示特定选项;
1.日志记录:可以通过启用所需日志的类型为服务器启用日志记录;比如:
    1.general_log # 常规查询日志;
    2.log-bin       # 二进制日志;
    3.slow_query_log    # 慢速查询日志;
2.默认存储引擎:可以使用–default-storage-engine选项指定不同于InnoDB的默认存储引擎;
3.系统变量:可以通过设置服务器系统变量值来定制服务器;
    1.max_connections=200 # 增加允许的最大连接数;
    2.innodb_buffer_pool_instances=4  # 增加InnoDB缓冲池数的默认值;
4.共享内存:在Windows上默认不启用;可以使用shared-memory选项来启用命名管道支持;
5.命名管道:要启用命名管道支持,使用enable-named-pipe选项;
— 样例选项文件;
1.Linux:
    1.对于RPM安装,样例选项文件在/usr/share/mysql中;
    2.对于TAR文件安装,样例文件在MySQL安装目录下的share目录中;
2.Windows:选项文件位于MySQL安装目录(my.ini)中;
3.如果多次指定一个选项(不管是在同一个选项文件中,还是在多个选项文件中),则最后
出现的选项值优先;
    1.–defaults-file=<file_name>:使用指定位置的选项文件;
    2.–defaults-extra-file=<file_name>:使用指定位置的其他选项文件;
    3.–no-defaults:忽略所有选项文件;
eg:要使用/etc/my-opts.cnf文件而忽略标准选项文件,可以:shell> mysql –defaults-file=/etc/my-opts.cnf;
— 显示选项文件中的选项;
1.# my_print_defaults –defaults-file=/usr/local/mysql/my.cnf client mysql mysqld;
2.# mysql –print-defaults:貌似打印不出来;
— 遮蔽验证选项;
1.建议不要使用mysql -uroot -poracle形式在命令行上指定口令;缺点:可以通过history命令查看到密码;
2.为方便起见,可以将口令放在[client]选项组中,但口令以纯文本方式存储,对选项文件有读取访问权限的任何人都能轻易地看到;
3.利用mysql_config_editor实用程序,可以将验证凭证存储在加密的登录文件.mylogin.cnf中;在Linux和UNIX上,该文件位置是当前用户的主目录;MySQL客户机程序以后可以读取该文件以获取用于连接到MySQL服务器的验证凭证;
TIPS:加密方法是可逆的,因此不应假设凭证对任何有文件读取特权的人都是安全的;相反,该功能使得避免使用纯文本凭证变得更容易;
4.文件说明:
.mylogin.cnf登录文件的未加密格式由选项组组成,类似于其他选项文件;
.mylogin.cnf中的每个选项组称为“登录路径”,仅允许一组有限的选项:主机,用户和口令;可将登录路径视为一组值,可以指示服务器主机以及用于服务器验证的凭证;eg:
    [admin]
    user = root
    password = oracle
    host = 127.0.0.1
— 登录路径;
1.创建登录路径:mysql_config_editor set –login-path=admin –host=localhost –user=root –password;如果调用mysql_config_editor时不使用–login-path选项,则将使用[client]登录路径;默认情况下,所有标准客户机都使用此登录路径;
    1.查看生成的文件:ll ~/.mylogin.cnf;
    2.登录:mysql –login-path=admin;
2.以纯文本格式查看单个登录路径:mysql_config_editor print –login-path=admin;
3.以纯文本格式查看所有登录路径:mysql_config_editor print –all;
4.删除登录路径:mysql_config_editor remove –login-path=admin;
— 服务器系统变量;
1.查看所有参数的默认值和读取选项文件之后的值:mysqld –verbose –help;
2.查看所有参数的默认值和忽略任何选项文件中的设置:mysqld –no-defaults –verbose –help;
3.查看变量值,没有其他启动选项:SHOW GLOBAL VARIABLES;
— 动态系统变量;
1.MySQL维护了两种包含系统变量的作用域:
    1.GLOBAL变量影响服务器的整体操作;
    2.SESSION变量影响其对单个客户机连接的操作;
    3.变量存在于任一作用域中,也可同时存在于两个作用域中;
2.变量及其作用域的示例包括:
    1.仅全局:key_buffer_size,query_cache_size;
    2.全局和会话:sort_buffer_size,max_join_size;
    3.仅会话:timestamp,error_count;
3.在更改变量值时,适用以下几点:
    1.设置会话变量不需要任何特殊特权,但客户机只能更改自己的会话变量,不能更改其他任何客户机的会话变量;
    2.LOCAL和@@local是SESSION和@@session的同义词;
    3.如果不指定GLOBAL或SESSION,则当会话变量存在时,SET将更改会话变量;会话变量不存在时,将产生错误;
TIPS:修改的全局参数不会在选项文件中反应出来,需要手动修改,以便下次启动后生效;与Oracle对比;
— 显示动态系统变量;
设定特定的变量时要注意赋值的类型;
— 结构化系统变量;
1.MySQL支持一种结构化变量类型,该变量类型可以指定控制键高速缓存操作的参数;键高速缓存结构化变量具有以下组件:
    1.key_buffer_size;
    2.key_cache_block_size;
    3.key_cache_division_limit;
    4.key_cache_age_threshold;
2.要引用结构化变量实例的组件,可使用复合名称:instance_name.component_name;
示例:hot_cache.key_buffer_size/hot_cache.key_cache_block_size;cold_cache.key_cache_block_size;
3.相关文档:http://dev.mysql.com/doc/refman/5.6/en/structured-system-variables.html。
— 服务器状态变量;
1.LOCAL是SESSION的同义词;
2.如果没有修饰符,则默认值为SESSION;
3.SHOW STATUS示例:mysql> SHOW GLOBAL STATUS;
— SQL模式;
SQL模式由控制查询处理某些方面的可选值组成,设置了相应的SQL模式后,客户机就可以对以下项目进行某种程度的控制:
    1.输入数据:SQL模式可用于指示服务器对接受输入数据的宽容度;
    2.标准SQL符合性:SQL模式可用于启用或禁用与标准SQL符合性相关的行为;
    3.兼容性:SQL模式可用于改进与其他数据库系统的兼容性;
— 设置SQL模式;
1.可以使用–sql-mode选项设置服务器启动时的默认SQL模式;
2.单个客户机可按自己的要求在选项文件内配置SQL模式;
3.如果没有修饰符,则SET将更改会话SQL模式;调用SET语句时可以带一个空字符串来清除当前SQL模式,也可以带一个或多个模式名称(用逗号分隔);
4.如果值为空或者包含多个模式名称,则必须将值放在引号中;如果值包含一个模式名称,则引号可有可无;SQL 模式值不区分大小写;
5.查看当前的sql_mode模式:
    1.使用SELECT语句检查当前的sql_mode设置:SELECT @@sql_mode;
    2.查看系统变量:SHOW VARIABLES LIKE ‘sql_mode’;
例子:
    1.使用单个模式值设置SQL模式:SET sql_mode = ANSI_QUOTES; SET sql_mode = ‘TRADITIONAL’;
    2.使用多个模式名称设置SQL模式:SET sql_mode = ‘IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION’;
— 常用SQL模式;
1.STRICT_TRANS_TABLES,STRICT_ALL_TABLES:没有这些模式,MySQL将接受缺少,超出范围或格式不正确的值;启用 STRICT_TRANS_TABLES时将为事务表设置“严格模式”;也可在默认的my.cnf文件中启用;启用STRICT_ALL_TABLES 时将为所有表设置严格模式;
    mysql> CREATE DATABASE db1;
    mysql> USE db1;
    mysql> CREATE TABLE t(id INT);
    mysql> INSERT INTO t VALUES (‘1’);
    mysql> commit;
    mysql> INSERT INTO t VALUES (‘x’);
    mysql> commit;
    mysql> SELECT * FROM t;
    +——+
    | id   |
    +——+
    |    1 |
    |    0 |
    +——+
    mysql> SELECT @@sql_mode;
    +————————+
    | @@sql_mode             |
    +————————+
    | NO_ENGINE_SUBSTITUTION |
    +————————+
    mysql> SET sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES’;
    mysql> SELECT @@sql_mode;
    +—————————————+
    | @@sql_mode                            |
    +—————————————+
    | STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
    +—————————————+
    mysql> INSERT INTO t VALUES (‘x’);
    ERROR 1366 (HY000): Incorrect integer value: ‘x’ for column ‘id’ at row 1
2.TRADITIONAL:启用此SQL模式可对输入数据值施加类似于其他数据库服务器的限制;在此模式下,使用GRANT语句可创建要求指定口令的用户;
3.IGNORE_SPACE:默认情况下,必须调用函数名称与后接括号间没有空格的函数;启用此模式后,允许存在此类空格,并使函数名称成为保留字;
4.ERROR_FOR_DIVISION_BY_ZERO:默认情况下,除数为零时将产生结果NULL,在启用此模式的情况下插入数据时,除数为零将导致出现警告,在严格模式下将出现错误;(这个模式之后会被废弃掉)
    mysql> INSERT INTO t VALUES (2/1);
    mysql> INSERT INTO t VALUES (2/0);
    mysql> SET sql_mode=’STRICT_TRANS_TABLES,STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO’;
    mysql> SELECT @@sql_mode;
    mysql> INSERT INTO t VALUES (2/0);
    ERROR 1365 (22012): Division by 0
5.ANSI:使用此组合模式将使MySQL服务器变得更加“类似于ANSI”;即,此模式支持的行为更像标准SQL,如ANSI_QUOTES和PIPES_AS_CONCAT;
6.NO_ENGINE_SUBSTITUTION:如果在创建或更改表时指定了不可用的存储引擎,除非启用了此模式,否则MySQL 将替换默认存储引擎;这是默认的SQL模式;
— 日志文件;
1.错误日志(error log):
    1.记录MySQL启动,关闭和运行时产生的重大的错误的信息;
    2.如果mysqld警告一个表需要自动的检查或者修复,也会记录一个错误日志;
    3.可以使用–log-error=file_name选项来指定错误日志文件,如果没有指定,则系统默认在data目录下生产一个hostname.err的文件;也可以在配置文件中配置log_error变量;
    4.主要是由mysqld_safe脚本可创建错误日志,并在启动服务器时将其输出重定向到该错误日志;
    5.查询log_error的位置:>show variables like ‘log_error’;查看主机名:>system hostname;
2.常规查询日志(general query log):
    1.常规查询日志记录服务器运行期间收到的所有语句.当客户端连接或者断开的时候服务器会记录信息到日志中,并记录所有从客户端接收到的sql语句,它对于在客户端中排错和查看是哪个客户端发送的命令很有帮助;
    2.mysqld是按照接收到命令的方式记录语句的,这可能跟它们执行的顺序不同(这与二进制日志是有区别的,二进制日志是执行后记录);
    3.使用–general_log=[0|OFF|1|ON]来控制是否打开常规查询日志和–general_log_file=file_name来指定生成的常规查询日志文件(在mysql5.1.6中可以使用–log选项启动和使用–log-output选项指定日志输出的位置),也可以输出到”Server Log Tables”表中;如果没有指定file_name,默认是在data目录下生产一个hostname.log文件;
    4.服务器重新启动和log flush不会产生一个新的文件;
    5.默认此功能关闭,通过show variables like ‘general_log%’来查看;
    6.设置打开一般查询日志:set global general_log=1;set global general_log_file=file_name(两个变量会同时打开关闭,打开之后立即生效);
    7.可以在启动的时候指定–general-file选项或者在配置文件中指定general_log=1,general_file_log=/path;
    8.一般不打开此日志功能,数据量太大,如果打开可以放到单独的磁盘中.
    9.log_output变量值:
        1.FILE:保存到文件中;
        2.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
        3.清空日志表:TRUNCATE mysql.general_log;
    10.备份切换一般日志文件:
        1.先备份:>mv hostname.log hostname.log.bak
        2.切换日志组:mysqladmin flush-logs;
3.慢查询日志(slow query log):
    1.调优时使用,记录超出指定时间的sql语句;
    2.慢查询日志主要记录执行时间超过long_query_time变量指定时间的sql语句,这个时间不包括获得锁的时间,只包含执行时间,系统默认时间是1s(long_query_time=1.0000);
    3.使用–slow-query-log=0|1选项和–slow_query_log_file=file_name选项指定(在mysql5.1.6版本可以指定mysqld的–long-slow-queries=file_name选项启动);
    4.命令行参数:–log-slow-queries=file_name;指定慢查询日志文件
    5.系统变量:
        1.slow_query_log:开启慢查询功能,set global slow_query_log = [0|OFF|1|ON];
        2.slow_query_log_file:指定慢查询日志文件,系统默认是在data目录下的hostname-slow.log文件;
        3.long_query_time:指定查询的最大时间,set global long_query_time=n;
        4.log_queries_not_using_indexes:不用索引的慢查询,默认是功能关闭的;
            1.查看某个表是否有索引:> show index from t;
            2.打开此功能:>set global log_queries_not_using_indexes =1;
            3.查看变量:>show variables like ‘log_queries_not_using_indexes’;
    6.log_output变量值:
        1.FILE:保存到文件中;
        2.TABLE:保存到表中;SET GLOBAL log_output = ‘TABLE’;
        3.清空日志表:TRUNCATE mysql.slow_log;
    7.分析慢日志文件的工具:mysqldumpslow,查看具体的帮助是用mysqldumpslow –help;
4.二进制日志(binary log):
    1.记录所有对数据库更新和潜在的更新语句(一个delete语句,但是没有影响的行),语句以事件(event)的方式存储,同时也包含了更新语句执行的时间信息;
    2.它不记录那些不修改任何数据的语句,如果想要记录所有的语句,可以使用query log;
    3.它主要的目的是在做还原操作时尽可能全的更新数据库,因为它包含在一次备份后的所有更新操作.它同样被用在master replication server中作为一个记录发送给slave servers;
    4.打开二进制日志会损失1%的性能,但是它带来的好处远远超过这些;
    5.查看二进制日志是否打开,需要查看log_bin参数是否是ON:>show variables like ‘log_bin’;
    6.命令行参数
        1.–log-bin=filename:记录二进制日志文件的位置,尽量指定路径名,如果不指定的话则保存在数据目录;
        2.–log-bin-index=file:记录二进制日志文件索引的位置,保存了日志文件名;
        3.–max_binlog_size:单个文件最大多少;
        4.–binlog-do-db=db_name:哪个数据库使用,只有这个数据库使用;
        5.–binlog-ignore-db=db_name:哪个数据库不使用,只有这个数据库不使用;
    7.系统变量
        1.log_bin:日志的位置;
        2.binlog_cache_size:二进制日志缓存大小,是每一个连接进来的线程分配的大小,不是整个服务器的大小;
        3.max_binlog_cache_size:最大缓存大小;
        4.max_binlog_size:单个文件最大大小,超过此大小则再分配一个文件,但是一个事务必须在一个文件中,所以可能会稍大点;
        5.binlog_cache_use:当前连接使用的binlog缓存的事务的数量,使用show status like ‘binlog_cache_use’查看(show status命令显示了所有连接到mysql服务器的状态值);
        6.binlog_cache_disk_use:如果binlog_cache_use不够用,则在磁盘上缓存,应该尽量避免;
        7.binlog_do_db:设置master-slave时使用;
        8.binlog-ignore-db:设置哪个数据库不记录日志;
        9.sync_binlog:缓存与硬盘的同步频率(commit多少下同步一次,0表示服务器自动控制);
        10.binlog_format:二进制日志的格式;
    8.查看当前二进制文件的名称和大小,show binary/master logs;
    9.如果不指定二进制日志文件的位置,默认存放在data文件夹下,日志文件是:mysql-bin.xxxxxx,索引文件是mysql-bin.index;
    10.如果要切换日志的话,执行flush logs命令;
    11.初始化二进制日志系统,从新生成:reset master命令;
    12.删除某个日志文件:purge binary logs [before ‘datetime’ / to ‘log_name’] 删除指定日期之前的和删除指定文件之前的日志文件;
    13.设置日志文件的失效期:参数为–expire_logs_days,set global expire_log_days=n,N天前的日志自动删除;
    14.二进制日志的格式
        1.查看格式:show [global] variables like ‘binlog_format’;
        2.设置日志格式:set [global] binlog_format = statement|row|mixed;
        3.查看binlog中的事件:show binlog events in ‘mysql-bin.000002’ from 0;
        4.使用mysqlbinlog程序打开;
5.审计日志(audit log):
    1.用于记录企业版基于策略的审计信息;审计日志是作为企业版插件提供的;
    2.由–audit-log选项和audit_log_file选项来控制;
    3.审计过程会不断写入审计日志,直到将该插件删除,或者通过audit_log_policy=NONE 选项设置关闭审计;
    4.在服务器启动时使用audit_log=FORCE_PLUS_PERMANENT作为选项,可以防止删除该插件;
补充:
6.InnoDB重做日志(innodb redo log);
    1.与innodb数据引擎相关;
    2.用来实现灾难恢复(crash recovery),突然断电会导致innodb表空间中的数据没有写到磁盘上,通过执行redo log能够重新执行这些操作来恢复数据;
    3.提升innodb的i/o性能,innodb引擎把数据和索引都载入到内存中的缓冲池中,如果每次休息数据和索引都需要更新到磁盘,必定会增加i/o请求,而且因为每次更新的位置都是随机的,磁头需要频繁的定位导致效率很低,所以innodb每处理完一个事务后只添加一条日志log,另外有一个线程负责智能的读取日志文件并批量更新到磁盘上,实现最高效的磁盘写入;
    4.系统变量:
        1.innodb_log_buffer_size:日志缓冲区的大小;
        2.innodb_log_file_size:日志文件的大小;
        3.innodb_log_files_in_group:一组日志中有几个文件:
            1.文件名为ib_logfileX(X从0开始一次增加);
            2.先关闭数据库服务:>mysqladmin shutdown(mysql.server stop);
            3.把data目录下的ib_logfile*文件移动走:>mv ib_logfile* /tmp;
            4.在配置文件中添加innodb_log_files_in_group=n的参数;
            5.启动数据库服务:>mysqld –defaults-file=./my.cnf –user=mysql(mysql.server start);
            6.可以查看error log文件观察启动过程;
        4.innodb_log_group_home_dir:日志存放的性对路径(相对于$MYSQL_HOME/mysql/data目录,即datadir目录);
            1.关闭服务器;
            2.在配置文件中添加此参数,并指定路径;
            3.启动服务器;
        5.innodb_flush_log_at_trx_commit:根据不同的数据安全级别去设定.
            1.0:日志缓冲每秒一次的被写入到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一个事务提交不做任何修改;
            2.1:每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁盘操作的刷新;
            3.2:每个事务提交后,日志缓冲被写到日志文件,但不对日志文件做到磁盘操作刷新,对日志文件每秒刷新一次;
            4.查看此变量:>show variables like ‘innodb_flush_log_at_trx_commit’;
        6.innodb_os_log_written:写入到文件日志的数据量,使用show status查询;
        7.innodb_os_log_fsyncs:写入到磁盘的次数,使用show status查询;
— 日志文件用法列表;
— 二进制日志记录;
1.二进制日志包含描述数据库更改(如创建数据库或更改表数据)的“事件”;二进制日志 还包含可能做出更改的语句的事件(例如,没有匹配行的DELETE);该日志还包含有关 每条更新语句所用时间的信息;
2.二进制日志有两个重要用途:复制和数据恢复;
3.MySQL使用日志传送复制解决方案;使用日志传送系统时,可以将主系统上发生的所有数据更改存储在二进制日志中,然后通过从系统检索这些数据更改,并根据接收到的这些日志文件执行更改;
4.可以实时下载日志文件并执行内容;即,只要生成日志文件事件,就将其发送到连接的从系统供执行;由于网络传播存在延迟,从系统可能需要几秒到几分钟(最坏的情况)时间来接收更新;在理想的情况下,延迟会在一秒以内;
5.发生以下事件之一时,二进制日志会轮转:
    1.重新启动MySQL服务器;
    2.达到允许的最大大小(max_binlog_size);
    3.发出了FLUSH LOGS SQL命令;
6.二进制日志独立于存储引擎,不管使用的存储引擎是哪个(即InnoDB或MyISAM),MySQL复制都会工作;
— 二进制日志记录格式;
1.基于语句的二进制日志记录:
    1.包含实际SQL语句
    2.包括DDL(CREATE,DROP等)和DML(UPDATE,DELETE等)语句;
    3.相对较小的文件保存磁盘空间和网络带宽;
    4.并非所有复制的语句都会在远程计算机上正确重放;
    5.要求主系统和从系统上复制的表和列完全相同(或者符合多个限制条件);
2.基于行的二进制日志记录:
    1.指示对单个表行的影响情况;
    2.正确重放所有语句,即使对于在使用基于语句的日志记录时未正确复制的功能导致的更改也是如此;
3.按如下方式设置格式:SET [GLOBAL|SESSION] BINLOG_FORMAT=[row|statement|mixed|default];
注:使用mixed选项可让MySQL选取最适合单个事件的格式,MySQL通常会使用基于语句的二进制日志,但在需要时可恢复到基于行的复制;
— 列出二进制日志文件;
1.SHOW BINARY LOGS语句可以列出当前日志文件和文件大小;
2.SHOW MASTER STATUS语句可以显示下一个事件的主状态;需要SUPER或REPLICATION CLIENT特权;
— 查看二进制日志内容;
1.方式1:show binlog events in ‘mysql-bin.000002’ from 0;
2.方法2:mysqlbinlog mysql-bin.xxxxxx;
— 删除二进制日志;
1.默认情况下,不会删除旧的日志文件;
2.根据存在时间删除日志:
    1.要在二进制日志轮转过程中自动删除存在时间多于指定天数的任何二进制日志,可使用expire_logs_days设置;
    2..也可以在选项文件中配置expire_logs_days:
        [mysqld]
        expire_logs_days=7
    3.PURGE BINARY LOGS BEFORE now() – INTERVAL 3 day;
4.根据文件名删除日志:PURGE BINARY LOGS TO ‘mysql-bin.000010’;
— 配置企业审计;
1.要安装audit_log插件:
    1.方法1:使用INSTALL PLUGIN语法:INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;
    2.方法2:在服务器启动时设置plugin-load选项:
        [mysqld]
        plugin-load=audit_log.so
2.默认情况下,装入该插件就会启用日志记录;将选项audit-log设置为OFF可禁用日志记录;
3.要防止在运行时删除该插件,可设置以下选项:audit-log=FORCE_PLUS_PERMANENT;
4.日志文件命名为audit.log,默认情况下位于服务器数据目录中;要更改该文件的名称或位置,可在服务器启动时设置 audit_log_file系统变量;
5.要平衡遵从性和性能,可使用audit_log_strategy选项在SYNCHRONOUS,ASYNCHRONOUS,SEMISYNCHRONOUS和 PERFORMANCE之间进行选择;
6.如果将audit_log_rotate_on_size设置为某个大于0的数字,则当日志文件大小超出了该数量的4KB数据块大小时,将轮转日志文件;
— 审计日志文件;
1.每个审计记录的TIMESTAMP采用UTC格式;
2.NAME属性代表事件类型;例如,“Connect”表示登录事件,“Quit”表示客户机断开连接,“Shutdown”表示服务器关闭;
3.“Audit”和“NoAudit”表示审计开始和停止的点;
4.STATUS属性提供命令状态;这与MySQL命令SHOW ERRORS显示的Code值相同;
5.有些属性仅在特定的事件类型中出现;例如,“Connect”事件包括诸如HOST,DB,IP和USER之类的属性;“Query”事件包括SQLTEXT属性;
补充:审计过滤工具;
mysqlauditgrep –users=root –query-type=SELECT  –status=0 /var/lib/mysql/audit.log
— 练习任务;
1.修改访问端口为3309;
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    port=3309
    user=mysql
    …
    [client]
    port=3309
2.查看当前客户会话正在使用的选项状态;
    mysql> status;
标签:

MySQL OCP-03-系统管理

1月 28th, 2017
— MySQL服务器分发;
1.MySQL可用于多个操作系统,包括Linux/Windows/Mac OS X和Oracle Solaris等;本课程仅讲述Linux;
2.MySQL可以作为二进制文件分发和源代码分发的形式提供:
    1.二进制文件分发:是预编译的,可以运行的程序,可用于Enterprise和Community MySQL Server版本,这些二进制文件是正式的经过Oracle测试的版本;
    2.源代码分发:不保证与商业代码更新一致,它们也不包括Oracle支持;
TIPS:有关可用OS类型的完整列表http://dev.mysql.com/downloads/mysql/;
— MySQL二进制文件分发;
1.用于Linux的二进制文件:
    1.RPM文件可用于基于RPM的Linux分发,例如Oracle Linux;通过使用rpm程序或者通过使用yum等软件包管理器来安装这些文件,每个RPM的安装布局由RPM文件自身内包含的规范文件提供;(使用rpm -qpl <rpm_file>来查看安装时RPM文件内容的位置)
    2.TAR文件可用于多种Linux和UNIX类似系统,要安装这种类型的分发,使用.tar程序在安装目录中解压缩该分发;
2.用于Windows的二进制文件:
    1.完整分发:包含MySQL安装的所有文件以及配置向导。
    2.非安装分发:.zip归档文件,不使用安装或配置向导,您只需解压缩该归档文件并将其移至所需的安装位置;
TIPS:二进制文件分发还可以压缩文件形式用于多个其他操作系统(包括Oracle Solaris);
— MySQL源代码分发;
1.如果需要预编译分发中可能没有的功能(例如完整的调试支持),可以根据源代码编译MySQL;
2.要使服务器在运行时使用较少内存,可能需要禁用不需要的功能;例如,可能需要禁用可选存储引擎,或者仅编译实际需要的那些字符集;
3.二进制文件分发仅可用于已发行的版本,不可用于最新的开发源代码;
4.源代码分发可以安装在任何所需位置;默认Linux安装位置为/usr/local/mysql;
— 用于Linux的MySQL RPM安装文件;
1.Oracle提供两种类型的MySQL RPM:
    1.与分发无关:MySQL提供给社区的RPM,它们应该可以在支持RPM软件包并使用glibc 2.3(GNU C库是标准C库的GNU实现)的所有Linux版本上运行;(查看方式:ldd –version)
    2.特定于分发:面向目标Linux平台,Oracle为许多平台提供了RPM文件;
2.MySQL的RPM安装通常分为不同的软件包,对于标准安装,必须至少安装服务器程序 和客户机程序,标准安装不需要其他软件包;
    1.MySQL-client-advanced-<version>.rpm:客户端命令行工具;
    2.MySQL-devel-advanced-<version>.rpm:包含了编译软件所需要的头文件,需要使用客户端共享库;
    3.MySQL-embedded-advanced-<version>.rpm:嵌入式数据库,为移动智能设备使用;
    4.MySQL-server-advanced-<version>.rpm:服务端(包含mysqld的二进制文件);
    5.MySQL-shared-advanced-<version>.rpm:包含客户端的共享库(libmysqlclient.so*);
    6.MySQL-shared-compat-advanced-<version>.rpm:包含了兼容旧版本的客户端共享库;
    7.MySQL-test-advanced-<version>.rpm:包含了测试套件;
— Linux MySQL RPM安装过程;
1.解压缩包:unizp MySQL-5.6.25-oel6-x86_64.zip;
2.创建用户:useradd mysql;
3.安装RPM包:rpm -ivh MySQL-*-advanced-5.6.25-1.el6.x86_64.rpm;安装在运行时自动执行以下任务:
    1.将RPM文件提取到其默认位置;
    2.在/etc/init.d目录中注册名为mysql的启动脚本;
    3.执行mysql_install_db,即创建系统数据库和默认my.cnf文件的脚本,为root帐户设置随机口令并将该口令保存在安装用户主目录中名为.mysql_secret的文件中;
    4.为mysql设置登录帐户以及用户名和组名称(用于管理和运行服务器);
4.发生与自带版本冲突的话:
    1.删除原来的包:yum remove mysql-libs-5.1.73-3.el6_5.x86_64;缺点是会删除相关的依赖文件,可能其它程序会用;
    2.添加–replacefiles选项:rpm -ivh –replacefiles MySQL-*-advanced*;
5.产生的目录:
    1./usr/bin:客户端程序和脚本;
    2./usr/sbin:mysqld服务程序;
    3./var/lib/mysql:数据库和日志文件,之后讲到数据库结构会讲每个文件的作用;
    4./usr/share/info:info格式的MySQL手册;
    5./usr/share/man:标准的Unix man格式手册;
    6./usr/include/mysql:MySQL所需的头文件;
    7./usr/lib64/mysql:库文件;
    8./usr/share/mysql:其它杂项,包括支持文件,错误信息,字符集文件,示例配置文件和数据库安装的SQL文件等;
    9./usr/share/sql-bench:测试基线;
    10./etc/my.cnf, /usr/my.cnf:缺省配置文件;
    11./etc/init.d/:包含了mysql启动脚本;
    12./var/log:mysqld.log文件;
6.启动数据库:service mysql start;
    1.查看后台进程:ps -ef | grep mysql;
    2.本来是启动的mysqld服务,后台多了一个mysqld_safe服务,之后讲数据库启动的几种方式会提到;
7.客户端登陆:mysql;
    1.之前版本都可以使用空密码登录,现在却报错;
    2.查看安装过程,会提示密码随机了,之后需要修改密码;
        A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER !
        You will find that password in ‘/root/.mysql_secret’.
        You must change that password on your first connect,
        no other statement but ‘SET PASSWORD’ will be accepted.
        See the manual for the semantics of the ‘password expired’ flag.
        Also, the account for the anonymous user has been removed.
    3.查看到密码后再登录:mysql -uroot -p;
    4.如何使用SET PASSWORD: help SET PASSWORD,发现报错,必须先修改:SET PASSWORD = PASSWORD(“mysql”);
8.卸载数据库:
    1.查看安装了哪些软件:yum list | grep MySQL;
    2.删除软件:yum remove MySQL-*;
— Linux MySQL服务器安装目录;
— 在Linux上启动MySQL服务器;
1.可以使用多种方法在Linux上启动服务器:
    1.mysqld:手动调用服务器来调试MySQL服务器;默认情况下,错误消息传至终端,而不是错误日志;
    2.mysqld_safe:设置错误日志,然后启动mysqld并对其进行监视,如果mysqld异常终止(kill -9 pid),mysqld_safe会将其重新启动;如果服务器未正常启动,请查看错误日志;
    2.mysql.server:用作mysqld_safe的包装,针对使用System V运行级别目录的Linux和Oracle Solaris等系统;
    4.mysqld_multi:该Perl脚本用于简化单台主机上的多个服务器管理,它可以启动或停止服务器,它还可以报告服务器是否正在运行;
    5.其它:mysqladmin -uroot -p start;
2.安装正确的脚本以使服务器在启动时自动运行:
    1.在BSD样式的Linux系统上,最常见的是通过某一个系统启动脚本(例如/etc目
    录中的rc.local脚本)调用mysqld_safe;
    2.在/etc/init.d下具有运行级别目录的Linux和UNIX System V变体使用mysql.server脚本,预先构建的 Linux二进制软件包针对相应的运行级别在名称mysql安装mysql.server;使用chkconfig注册服务;
— 在Linux上停止MySQL服务器;
1.要手动停止服务器,请使用以下方法之一:
    1.mysqladmin:具有关闭命令,它作为客户机连接到服务器并且可以关闭本地或远程服务器;mysqladmin -uroot -p shutdown;
    2.mysql.server:在使用stop参数调用时停止和/或关闭本地服务器;
    3.mysqld_multi:停止和/或关闭其管理的任何服务器,它通过调用mysqladmin来执行此操作;
2.mysqld_safe没有服务器关闭功能;
— 提高安装安全性;
1.从RPM软件包安装MySQL时,将为root帐户设置随机口令并将该口令保存到安装用户主目录中的.mysql_secret文件;对于所有其他安装,初始口令为空白;
2.在不带参数的情况下调用mysql_secure_installation,这将提示您确定要执行的操作;
例子:可以运行一下# /usr/bin/mysql_secure_installation,虽然此处显示mysql_secure_installation 脚本以Linux root用户身份运行,但您可以普通用户身份运行该脚本;
— Windows MySQL服务器安装目录;
1.默认情况下,MySQL5.6安装在目录路径C:\Program Files\MySQL\MySQL 5.6 Server中;
2.\bin包含MySQL服务器和客户机程序:
    1.mysqld.exe:标准服务器;
    2.其他客户机程序,例如mysqladmin.exe;
3.\data是服务器存储数据库和日志文件的位置,此目录是预配置的,可以直接使用;例如,此目录包括mysql子目录(包含授权表)和用于test数据库的test子目录(可用于测试目的);
4.my.ini:配置选项文件指定安装目录的位置以及其他可选设置;
— 在Windows上运行MySQL;
1.通过使用net start MySQL和net stop MySQL命令从命令行手动启动和停止服务;
2.使用mysqld –install命令从命令行调用服务器;在停止服务后将其删除,使用mysqld –remove;
— 数据目录;
1.每个表都具有*.frm文件(包括视图);
2.MySQL服务器开始执行时会将其当前工作目录转到其data目录,必须确保MySQL服务器具有正确的访问权限,以便在data目录中创建文件,该服务器必须可以访问它要在其中创建数据文件或日志文件的所有目录;
3.MySQL服务器将每个数据库映射到MySQL data目录下的一个目录,并且默认情况下,它将数据库中的表映射到数据库目录中的文件名;这具有以下含义:
    1.数据库和表名称仅在具有区分大小写的文件名的操作系统(例如大多数Linux系统)上的MySQL服务器中才区分大小写;
    2.可以通过将数据目录,数据库和/或单个表(具体取决于存储引擎选项)移至不同的物理位置来分割磁盘使用,这可以提高性能;
— MySQL服务器发行版;
1.升级之前还应该查看readme文档:
    1.在关于升级的部分中,一定要阅读与正在执行的升级类型有关的注释,按照建议的过程执行操作;
    2.在关于新版本的更改注释部分中,查看在当前版本与要安装的版本之间发生的所有更改,请注意不与当前版本向后兼容的所有更改;
2.RPM和源代码升级通常不需要进行重新配置,因为它们往往使用相同安装目录位置,而不考虑MySQL版本;
3.需要进行一些重新配置的情况:
    1.如果使用通用Linux二进制文件进行升级,可以选择创建特定于新版本的目录来包含升级的发行版;
    2.此外,Windows安装程序在Program Files下创建特定于版本的文件夹;
4.设置指向旧安装目录的软链接,从而可以轻松删除并重新创建该链接来指向新安装目录,对该符号链接的后续引用将访问新安装;比如:把data目录做成一个软链接;
5.如果您的安装最初是通过安装多个RPM软件包而生成的,则最好升级所有软件包,而不仅是其中的一些;例如,如果先前安装了服务器和客户机RPM,则不要仅升级服务器RPM;
补充:
1.升级MySQL总体上可以简单使用以下步骤进行操作:
    1.备份你的数据库;
    2.关闭Server;
    3.在已存版本上安装新版本MySQL;
    4.启动Server;
2.MySQL当前提供的升级不提供跳级升级,因此,如果你当前的MySQL版本为5.1,那么升级到5.7的流程为:
5.1->5.5->5.6->5.7;
— 检查升级的表;
1.在每次进行MySQL升级时,都需要运行mysql_upgrade程序,主要是执行了以下操作:
    1.检查数据库中的所有表与MySQL服务器当前版本是否兼容性;
    2.修复表中发现的所有问题以及可能的不兼容性;
    3.升级系统表来添加新版本中可用的所有新特权或功能;
    4.使用当前MySQL版本号标记所有已检查和已修复的表;
2.mysql_upgrade会将MySQL版本号保存在数据目录下的一个mysql_upgrade_info的文件中,这个文件被用于快速查看是否所有表针对升级版本已经做了检查,是否可以跳过表检查;当然,运行mysql_upgrade时,你也可以使用–force项来跳过查看此文件;
3.在MySQL 5.7.5之前,为了检查和修正表并进行系统表升级,mysql_upgrade会调用以下2条命令:
    mysqlcheck –check-upgrade –all-databases –auto-repair
    mysql_fix_privilege_tables
  从MySQL 5.7.5以后,mysql_upgrade将直接和MySQL Server交互,发送所需的SQL语句来执行升级;
补充:升级过程;
1.停止服务:service mysql stop;
2.备份文件:
    1.创建临时目录:mkdir /tmp/mbackup;
    2.备份配置文件:cp /etc/my.cnf /tmp/mbackup/;
    3.备份数据文件:cp -rf /var/lib/mysql/ /tmp/mbackup/mysql;也可以用mysqldump/xtrabackup;
3.删除软件:yum -y remove MySQL-*-advanced-5.6.25-1.el6.x86_64;(不推荐)
4.安装软件:rpm -ivh –replacefiles MySQL-server-advanced-5.6.26-1.el6.x86_64.rpm MySQL-client-advanced-5.6.26-1.el6.x86_64.rpm;
5.拷贝回配置文件:cp /tmp/mbackup/my.cnf /etc/my.cnf;
6.尝试重启:service mysql restart;
7.执行更新:mysql_upgrade -uroot -p;
8.查看生成的更新文件并且尝试登录查看版本信息:less $MYSQL_DATADIR/mysql_upgrade_info;
— 使用多个服务器;
1.要在运行生产服务器的同一台计算机上测试MySQL的新发行版时,需要运行多个服务器;假定每个组具有其自己的指定root用户,该用户无法查看属于其他组的数据库,如果所有 客户机将共享同一服务器则可能会这样;
2.不允许任何服务器共享必须由单个服务器独占使用的资源;
3.mysqld_multi脚本设计用来管理多个mysqld进程,这些进程监听不同UNIX套接字文件和TCP/IP端口上的连接,该脚本搜索my.cnf中名为[mysqldN]的组,然后将该N的设置应用于编号的实例;
    例如,要启动两个mysqld实例,它们分别应用来自my.cnf部分[mysqld3]和[mysqld5]的设置,请运行以下命令:
    shell> mysqld_multi start 3, 5
— 多个服务器选项;
使用mysqld或mysqld_multi以及每个服务器函数的相应选项来调用每个MySQL服务器:
    1.数据目录:使用–datadir选项的唯一值启动每个服务器;
    2.网络:通过使用–port,–socket和–shared-memory-basename选项的唯一值启动每个服务器,将每个服务器设置为使用其自己的网络接口;
    3.组名称:使用mysqld_multi时,每个服务器组在Linux或UNIX上必须具有唯一的mysqldN名称;
    4.日志文件:每个服务器必须具有其自己的日志和PID文件;
    5.InnoDB表空间和日志文件:不能由多个服务器共享;
    6.Windows服务名称:每个mysqld Windows服务必须使用唯一的服务名称,通过使用–install设置服务名称;服务器启动时,它们从标准选项文件中的各个相应服务组中读取选项;
— 练习任务:
1.创建和导入world_innodb数据库;
    $ mysql -uroot -p;
    mysql> CREATE DATABASE world_innodb;
    mysql> USE world_innodb
    mysql> SET autocommit=0;
    mysql> SOURCE /labs/world_innodb.sql;
    mysql> SET autocommit=1; // 为了提高运行速度,批量提交;
2.查看本地MySQL服务器数据目录;
    mysql> SHOW VARIABLES LIKE ‘datadir’\G
3.检查MySQL服务器的状态;
    service mysql status;
4.关闭/启动MySQL服务器;
    service mysql stop/start;
— 补充:源码安装MySQL
1.准备安装工具:yum -y install gcc* make* perl*;
    1.cmake:从Mysql5.5以后使用cmake编译,可以从www.cmake.org下载最新版本;
    2.GUN make:操作系统自带;
    3.gcc:操作系统自带;
    4.perl:操作系统自带;
    5.libncurses5-dev(ncurses-devel):运行cmake必须的包,如果没有安装会报错.
2.安装cmake:
    1.解压压缩包:>tar -zxvf cmake-VERSION.tar.gz;
    2.进入到cmake的解压缩目录,执行./configure命令生成makefile;
    3.执行>make;make install;命令生成安装软件并安装cmake;
    4.软件安装到了/usr/local/share/cmake-VERSION/目录下,执行文件在/usr/local/bin目录下;
3.安装ncurses-devel插件
    1.在Debian和Ubuntu上的包名是libncurses5-dev;
    2.在RHEL和其它版本上是ncurses-devel,执行:yum install -y ncurses-devel*;
    3.如果不安装会出现以下错误;
4.创建mysql用户:>useradd mysql;
5.解压缩mysql5.5的源码包:>tar -zxvf mysql-VERSION.tar.gz;
6.进入目录mysql-VERSION目录;
7.执行cmake命令生成makefile(MyISAM,MERGE,MEMBER和CSV四种引擎默认静态编译);
    cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_FEDERATED_STORAGE_ENGINE=1 \
    -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
    -DWITH_ARCHIVE_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DENABLED_LOCAL_INFILE=1 \
    -DMYSQL_TCP_PORT=3306
8.编译文件:>make;make install;在mysql的安装目录下生成可执行文件,并自动创建了data文件(可以手动再创建一个logs目录,用来存放生成的日志文件,与数据目录不在同一块磁盘上,减小I/O并发),修改目录权限为mysql:>chown mysql:mysql data;
9.编写/etc/my.cnf;
10.在配置文件下添加目录配置,指定数据文件的位置;
    bsedir = /usr/local/mysql/
    datadir = /usr/local/mysql/data
11.数据库的初始化,主要是数据库的创建,帮助文件的填充,用户文件的填充,执行:>./scripts/mysql_install_db –defaults-file=./my.cnf –user=mysql(在my.cnf配置文件中添加user参数,并且拷贝到/etc目录下就不用再加参数,执行>./scripts/mysql_install_db即可)
12.启动服务器:>./bin/mysqld_safe –-user=mysql &;
13.修改MYSQL服务器root用户的密码:>./bin/mysqladmin –u root password ‘pwd’;
14.登录:
    1.如果没有设置root的密码,默认是空密码,使用>./bin/mysql就可以登录;
    2.如果设置了root密码,则登录时要数据密码验证>./bin/mysql –uroot -p;
    3.进入数据库后修改用户密码:update user set password=PASSWORD(‘123456′) where user=’root’;
15.把mysql添加到环境变量:
    1.打开~root/.bash_profile文件;
    2.修改环境变量:>PATH=/usr/local/mysql/bin:$PATH,尽量把mysql的bin目录放在PATH的前面,使用mysql的工具的时候提高优先级,否则可能会使用系统预装的mysql的工具,造成版本不一致的错误;
    3.使环境变量立即生效:>. ./.bash_profile;
16.把配置文件放到默认读取的路径,并在配置文件中指定启动用户为mysql,添加开机启动:
    1.拷贝文件到开机启动目录:cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld;
    2.添加开机启动项:chkconfig –add mysqld;chkconfig mysqld on;
    3.启动/关闭mysql服务:service mysqld start/stop;
— 配置文件my.cnf(RPM安装);
# ————-mysql client profile——————-
[client]
port = 3306
socket = /var/lib/mysql/mysqld.sock
# ————-mysql client profile——————-
# ————-mysql general profile——————
[mysqld]
user = mysql
port = 3306
socket = /var/lib/mysql/mysqld.sock
pid-file = /var/lib/mysql/mysqld.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_cache_size = 128
query_cache_size= 8M
binlog_cache_size = 4M
tmp_table_size = 4M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 2
# ————-mysql general profile——————
# ————-mysql base profile——————
# MYSQL server home directory
basedir = /usr/
# data files directory
datadir = /var/lib/mysql/
# ————-mysql base profile——————
# ————-log files profile——————
# binary log files directory
log_bin = /var/lib/mysql/mysql-bin.log
binlog_format = mixed
log_bin_index = /var/lib/mysql/mysql-bin.index
max_binlog_size = 500M
# mysql’s error log file dir(same as Oracle’s alter_SID.log file)
log_error = /var/lib/mysql/alert_mysql.log
# mysql’s general log file(disabled by default)
general_log_file = /var/lib/mysql/general_mysql.log
# mysql’s slow log file(disabled by default)
long_query_time = 1 # second;
slow_query_log_file = /var/lib/mysql/slow_query_mysql.log
# ————-log files profile——————
# ————-innodb engine profile——————
# InnoDB engine profile
autocommit = 0
innodb_file_per_table = 1
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_files_in_group = 4
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_data_home_dir = /var/lib/mysql/data
# same as oracle’s database buffer cache, the larger the better
innodb_buffer_pool_size = 128M
# same as oracle’s redo log files
innodb_log_file_size = 100M
# same as oracle’s redo log buffer cache, the larger the better
innodb_log_buffer_size = 4M
# turn off indexes statistic when DDL operater,default ON, it’s deprecated
#Innodb_buffer_pool_pages_free = 0
# ————-innodb engine profile——————
# ————-semi-sync profile——————
# master configure
#server-id       = 1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
# slave configure
#server-id       = 2
#rpl_semi_sync_slave_enabled=1
# ————-semi-sync profile——————
# ————-identify column——————
#Master-Master generate odd identifier
#auto_increment_offset = 1
#auto_increment_increment = 2
#Master-Master generate even identifier
#auto_increment_offset = 2
#auto_increment_increment = 2
# ————-identify column——————
# ————-others profile——————
max_connections = 1000
innodb_use_sys_malloc = 0
log_slave_updates
# skip ip to host name resolve
skip_name_resolve
# enable event scheduler process
event_scheduler = on
# add federated engine plugin
federated
# open Event Scheduler default
event_scheduler = on
# create functions
# log_bin_trust_function_creators = ON;
# ————-others profile——————
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
— 配置文件my.cnf(编译安装);
# ————-mysql client profile——————-
[client]
port = 3306
socket = /usr/local/mysql/log/mysqld.sock
# ————-mysql client profile——————-
# ————-mysql general profile——————
[mysqld]
user = mysql
port = 3306
socket = /usr/local/mysql/log/mysqld.sock
pid-file = /usr/local/mysql/log/mysqld.pid
skip-external-locking
key_buffer_size = 64M
max_allowed_packet = 16M
table_open_cache = 512
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 8M
thread_cache_size = 128
query_cache_size= 8M
binlog_cache_size = 4M
tmp_table_size = 4M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 2
# ————-mysql general profile——————
# ————-mysql base profile——————
# MYSQL server home directory
basedir = /usr/local/mysql/
# data files directory
datadir = /usr/local/mysql/data/
# ————-mysql base profile——————
# ————-log files profile——————
# binary log files directory
log_bin = /usr/local/mysql/log/mysql-bin.log
binlog_format = mixed
log_bin_index = /usr/local/mysql/log/mysql-bin.index
max_binlog_size = 500M
# mysql’s error log file dir(same as Oracle’s alter_SID.log file)
log_error = /usr/local/mysql/log/alert_mysql.log
# mysql’s general log file(disabled by default)
general_log_file = /usr/local/mysql/log/general_mysql.log
# mysql’s slow log file(disabled by default)
long_query_time = 1 # second;
slow_query_log_file = /usr/local/mysql/log/slow_query_mysql.log
# ————-log files profile——————
# ————-innodb engine profile——————
# InnoDB engine profile
autocommit = 0
innodb_file_per_table = 1
innodb_log_group_home_dir = /usr/local/mysql/log/
innodb_log_files_in_group = 4
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_data_home_dir = /usr/local/mysql/data
# same as oracle’s database buffer cache, the larger the better
innodb_buffer_pool_size = 128M
# same as oracle’s redo log files
innodb_log_file_size = 100M
# same as oracle’s redo log buffer cache, the larger the better
innodb_log_buffer_size = 4M
# turn off indexes statistic when DDL operater,default ON, it’s deprecated
#Innodb_buffer_pool_pages_free = 0
# ————-innodb engine profile——————
# ————-semi-sync profile——————
# master configure
#server-id       = 1
#rpl_semi_sync_master_enabled=1
#rpl_semi_sync_master_timeout=1000
# slave configure
#server-id       = 2
#rpl_semi_sync_slave_enabled=1
# ————-semi-sync profile——————
# ————-identify column——————
#Master-Master generate odd identifier
#auto_increment_offset = 1
#auto_increment_increment = 2
#Master-Master generate even identifier
#auto_increment_offset = 2
#auto_increment_increment = 2
# ————-identify column——————
# ————-others profile——————
max_connections = 1000
innodb_use_sys_malloc = 0
log_slave_updates
# skip ip to host name resolve
skip_name_resolve
# enable event scheduler process
event_scheduler = on
# add federated engine plugin
federated
# open Event Scheduler default
event_scheduler = on
# create functions
# log_bin_trust_function_creators = ON;
# ————-others profile——————
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M
[mysqlhotcopy]
interactive-timeout
标签:

MySQL OCP-02-体系结构

1月 28th, 2017
— MySQL体系结构;
1.MySQL安装必需的体系结构组件:MySQL服务器,客户机程序以及MySQL非客户机程序;中央程序充当服务器,客户机程序连接到该服务器以发出数据请求;
2.MySQL客户机/服务器通信并不仅限于所有计算机都运行同一操作系统的环境:
    1.客户机程序可以连接到在相同主机或不同主机上运行的服务器;
    2.客户机/服务器通信可以发生在计算机运行不同操作系统的环境中;
TIPS:在本课程中,都会使用在Linux中操作;
— 客户机程序;
1.mysql/mysqldump客户机程序是使用最多的一个客户段工具了;
2.MySQL Workbench是一款GUI工具,可以用来:
    1.为数据库建模;
    2.执行数据库查询;
    3.执行管理任务;
— 管理程序和实用程序;
1.这几款工具都是使用比较多的管理程序;
2.mysqldumpslow是Perl脚本;
TIPS:为了避免数据丢失或损坏,一些程序要求在执行之前关闭服务器和/或对当前表进行备份;
— MySQL服务器;
1.服务器和主机之间的概念差别:
    1.服务器:一个软件程序(mysqld),具有版本号和一系列功能;
    2.主机:服务器程序在其上运行的物理计算机,其中包含:硬件配置,操作系统,网络地址;
2.多个mysqld实例可同时在一台主机上运行;
— 服务器进程;
1.应用程序主要是mysqld进程来访问数据库服务器,由mysqld来操作不同的存储引擎(磁盘:InnoDB,MyISAM;内存:Memory;网络:NDB);
2.mysqld(服务器程序)进程可以划分为以下三个层:
    1.连接层:处理连接,此层存在于所有服务器软件(Web/邮件/LDAP服务器)上;
    2.SQL层:处理所连接的应用程序发送的SQL查询;
    3.存储层:处理数据存储,数据可以按不同格式和结构存储在不同物理介质上;
— 连接层;
1.连接层可通过多种通信协议接受来自应用程序的连接:
    1.TCP/IP;
    2.UNIX套接字;
    3.共享内存;
    4.命名管道;
2.其中TCP/IP适用于整个网络,也是最常用的连接方式;客户机和服务器在同一台计算机上运行时,上面列出的其他协议仅支持本地连接;
3.此层针对每个连接维护一个线程,此线程处理查询执行;在某个连接可以开始发送SQL查询之前,将会通过验证用户名+口令+客户机主机来对该连接进行验证;
— 通信协议;
1.TCP/IP(传输控制协议/Internet协议,Transmission Control Protocol/Internet Protocol):该通信协议套件用于连接Internet上的主机;在Linux操作系统中,TCP/IP是内置的,供 Internet使用,从而使其成为通过网络传输数据的标准;这也是适用于Windows的最佳连接类型;
2.UNIX套接字:一种进程间通信形式,用于在同一台计算机上的进程之间形成双向 通信链路的一端;套接字需要本地系统上的物理文件,这是适用于Linux的最佳连接类型;
3.共享内存:一种在程序之间传递数据的有效方式;一个程序创建其他进程(如果允许)可以访问的内存部分;此Windows显式“被动”模式仅适用于单台(Windows)计算机;默认情况下,共享内存处于禁用状态,要启用共享内存连接,必须使用–shared-memory选项启动服务器;
4.命名管道:命名管道的使用偏向于客户机/服务器通信,其工作方式与套接字非常相似;命名管道支持读/写操作,以及服务器应用程序的显式“被动”模式;此协议仅适用于单台(Windows)计算机;默认情况下,命名管道处于禁用状态,要启用命名管道连接,必须使用–enable-named-pipe选项启动服务器;
— SQL层;
1.解析器:解析器验证语法是否正确;
2.授权:验证是否允许所连接的用户运行特定查询;
3.优化器:创建每个查询的执行计划,这是有关如何以最优化的方式执行查询的分步指令集,确定要使用哪些索引以及采用何种顺序处理表是此步骤的最重要部分;
4.查询执行:完成每个查询的执行计划;
5.查询高速缓存:(可选)可配置的查询高速缓存,可用于存储(并立即返回)执行的查询和结果;
6.查询日志记录:可以启用以跟踪执行的查询;
— SQL语句处理;
第一次的选择主要是查看是否有配置开启[高速缓存查询]的特性;
— 存储层;
1.通过MySQL,可以使用称为“存储引擎”的不同类型的存储,数据可以存储在磁盘,内存和网络中;
2.数据库中的每个表可以使用任何可用的存储引擎,“磁盘”存储便宜且持久,而“内存”存储则要快得多;
3.InnoDB是默认存储引擎,它可提供事务,全文索引和外键约束,因此适用于各种混合查询;它具有多种用途,支持读密集型工作负荷,读/写工作负荷和事务工作负荷;
4.其他存储引擎包括:
    1.MyISAM:适用于频繁读取但很少更新的数据;
    2.MEMORY:在内存中存储所有数据;
    3.NDB:供MySQL Cluster用来为高可用性数据提供冗余的可伸缩拓扑;
注:存储引擎可扩展,超越存储层,而不只包含存储,它们还包括其他结构和实现机制;
— 存储引擎:概览;
1.客户机通过以SQL语句形式向服务器发送请求从表中检索数据或更改表中的数据;
2.服务器通过使用双层处理模型执行每条语句;
3.客户机通常不需要关心哪些引擎参与SQL语句处理,这种独立于引擎的SQL语句的一些例外情况包括:
    1.CREATE TABLE具有ENGINE选项,可基于每个表指定要使用的引擎;
    2.ALTER TABLE具有ENGINE选项,允许将表转换为使用不同的存储引擎;
    3.某些索引类型仅适用于特定存储引擎;例如,仅InnoDB和MyISAM引擎支持全文索引;
    4.COMMIT和ROLLBACK操作仅影响事务存储引擎(例如InnoDB和NDB)管理的表;
— 依赖于存储引擎的功能;
1.存储介质:表存储引擎可以在磁盘上,在内存中或通过网络存储数据;
2.事务功能:某些存储引擎支持全面的ACID事务功能,而其他存储引擎可能不具有事务支持;
3.锁定:存储引擎可能使用不同的锁定粒度(例如表级别锁定或行级别锁定)和机制来提供与并发事务的一致性;
4.备份和恢复:可能会受到存储引擎存储和操作数据的方式的影响;
5.优化:不同的索引实现可能会影响优化,存储引擎以不同的方式使用内部高速缓存,缓冲区和内存以优化性能;
6.特殊功能:某些引擎类型具有提供全文搜索和引用完整性的功能以及处理空间数据的能力;
TIPS:优化器可能需要根据存储引擎进行不同的选择,但这均是通过每种存储引擎支持的标准化接口(API)进行处理的;
— MySQL如何使用磁盘空间;
1.默认情况下,程序文件随数据目录一起存储在服务器安装目录下;执行各种客户机程序,管理程序和实用程序时将创建程序可执行文件和日志文件;
2.首要使用磁盘空间的是数据目录:
    1.服务器日志文件和状态文件包含有关服务器处理的语句的信息,日志可用于进行故障排除/监视/复制和恢复;
    2.InnoDB日志文件(适用于所有数据库)驻留在数据目录级别;
    3.InnoDB系统表空间包含数据字典,撤消日志和缓冲区;
    4.每个数据库在数据目录下均具有单一目录(无论在数据库中创建何种类型的表),数据库目录存储以下内容:
        1.数据文件:特定于存储引擎的数据文件,这些文件也可能包含元数据或索引信息,具体取决于所使用的存储引擎;
        2.格式文件(.frm):包含每个表和/或视图结构的说明,位于相应的数据库目录中;
        3.触发器:与某个表关联并在该表发生特定事件时激活的命名数据库对象;
    5.数据目录的位置取决于配置,操作系统,安装包和分发;典型位置是/var/lib/mysql;
    6.MySQL在磁盘上存储系统数据库(mysql),mysql包含诸如用户/特权/插件/帮助列表/事件/时区实现和存储例程之类的信息;
— MySQL如何使用内存;
1.内存分配可以划分为以下两种类别:
    1.全局(每实例内存):服务器启动时分配一次并在服务器关闭时释放,此内存在所有会话间共享;当所有物理内存用尽时,操作系统开始交换,这会对MySQL服务器性能具有不利影响,可能会导致服务器崩溃;
    2.会话(每会话内存):基于每个会话(有时称为“线程”)动态进行分配;此内存可在会话结束时或不再需要会话时释放,此内存多用于处理查询结果,所使用的缓冲区大小基于每个连接;例如,read_buffer为10MB且具有100个连接意味着可能总共有100*10MB同时用于所有读取缓冲区;
— 内存结构;
服务器在运行时会为许多种类的数据分配内存:
1.查询高速缓存还用于加速处理重复发出的查询;
2.线程高速缓存:在MySQL(和其他程序)中使用线程将应用程序执行划分为两个或更多个同时运行的任务,将会为连接到MySQL服务器的每个客户机创建单独的线程以处理该连接;
3.缓冲区和高速缓存:缓冲区和高速缓存提供数据管理子系统并支持快速访问项目,例如授权表缓冲区,存储引擎缓冲区(如InnoDB的日志缓冲区)和保存开放表说明符的表开放缓冲区;如果使用MEMORY存储引擎,MySQL将使用主内存作为主体数据存储,其他存储引擎也可能使用主内存进行数据存储,但MEMORY是唯一的,未设计为在磁盘上存储数据;
4.连接/会话:
    1.内部临时表:在某些查询执行情况下,MySQL会创建一个临时表来解析查询;可以在内存中或在磁盘上创建临时表,具体取决于其大小或内容或者查询语法;
    2.特定于客户机的缓冲区:专门设计为支持所连接的各个客户机;缓冲区示例包括:
        1.用于交换信息的通信缓冲区;
        2.排序操作:表读取缓冲区(包括支持联接的缓冲区);
— MySQL插件接口;
1.当前,插件API支持:
    1.可用于替换或扩充内置全文解析器的全文解析器插件;例如,某个插件可以使用不同于内置解析器所使用的规则将文本解析为字,要解析具有不同于内置解析器所预期的特征的文本,这很有用;
    2.向服务器提供低级别存储,检索和数据索引的存储引擎;
    3.信息模式插件;信息模式插件作为MySQL INFORMATION_SCHEMA数据库中的表出现,稍后将更详细地讨论INFORMATION_SCHEMA数据库;
    4.守护进程插件启动在服务器内运行的后台进程(例如,定期执行心跳处理);
2.插件接口需要mysql数据库中的PLUGINS表,此表是在MySQL安装过程中创建的;
— 小结;
标签: