标签归档:MySQL

centos 6.x yum mysql 5.7

a.下载包

打开 https://dev.mysql.com/downloads/repo/yum/ 选择对应版本

点击下一download 后,复制 No thanks, just start my download. 上的连接

下载:

wget https://dev.mysql.com/get/mysql57-community-release-el6-9.noarch.rpm

 

b.安装用来配置mysql的yum源的rpm包

rpm -Uvh mysql57-community-release-el6-9.noarch.rpm

yum localinstall -y mysql57-community-release-el6-9.noarch.rpm

安装成功后在/etc/yum.repos.d/

2.安装mysql

yum install mysql-community-server

3.开启mysql服务

service mysqld start

4.默认密码
启动后创建的超级用户'root'@'localhost' 密码被存储在/var/log/mysqld.log
命令查看密码
grep 'password' /var/log/mysqld.log

使用mysql生成的'root'@'localhost'用户和密码登录数据库,并修改默认密码
shell> mysql -uroot -p
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';

MySQL 全文索引(fulltext index)

1.创建全文索引(FullText index)

旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。

不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引,所以具体信息要随时关注官网,
1.1. 创建表的同时创建全文索引

CREATE TABLE article (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title, body)
) TYPE=MYISAM;

1.2.通过 alter table 的方式来添加

ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name (`name`) #ft_stu_name是索引名,可以随便起

或者:ALTER TABLE `student` ADD FULLTEXT ft_stu_name (`name`)

1.3. 直接通过create index的方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

也可以在创建索引的时候指定索引的长度:

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20))

2. 删除全文索引
2.1. 直接使用 drop index(注意:没有 drop fulltext index 这种用法)

DROP INDEX full_idx_name ON tommy.girl ;
2.2. 使用 alter table的方式

ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

3.使用全文索引

跟普通索引稍有不同

使用全文索引的格式: MATCH (columnName) AGAINST ('string')

eg:

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

当查询多列数据时:

建议在此多列数据上创建一个联合的全文索引,否则使用不了索引的。

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')
3.1. 使用全文索引需要注意的是:(基本单位是词)

分词,全文索引以词为基础的,MySQL默认的分词是所有非字母和数字的特殊符号都是分词符(外国人嘛)

这里推荐一篇文章:利用mysql的全文索引实现模糊查询

3.2. MySQL中与全文索引相关的几个变量:

使用命令:mysql> SHOW VARIABLES LIKE 'ft%'; #ft就是FullText的简写

ft_boolean_syntax + -><()~*:""&| #改变IN BOOLEAN MODE的查询字符,不用重新启动MySQL也不用重建索引 ft_min_word_len 4 #最短的索引字符串,默认值为4,(通常改为1)修改后必须重建索引文件 重新建立索引命令:repair table tablename quick ft_max_word_len 84 #最长的索引字符串,默认值为84,修改后必须重建索引文件 ft_query_expansion_limit 20 #查询括展时取最相关的几个值用作二次查询 ft_stopword_file (built-in) #全文索引的过滤词文件,具体可以参考:MySQL全文检索中不进行全文索引默认过滤词 特别注意:50%的门坎限制(当查询结果很多,几乎所有记录都有,或者极少的数据,都有可能会返回非所期望的结果) -->可用IN BOOLEAN MODE即可以避开50%的限制。

此时使用全文索引的格式就变成了: SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)

更多内容请参考:MySQL中的全文检索(1)

4. ft_boolean_syntax (+ -><()~*:""&|)使用的例子: 4.1 + : 用在词的前面,表示一定要包含该词,并且必须在开始位置。 eg: +Apple 匹配:Apple123, "tommy, Apple" 4.2 - : 不包含该词,所以不能只用「-yoursql」这样是查不到任何row的,必须搭配其他语法使用。 eg: MATCH (girl_name) AGAINST ('-林志玲 +张筱雨') 匹配到: 所有不包含林志玲,但包含张筱雨的记录 4.3. 空(也就是默认情况),表示可选的,包含该词的顺序较高。 例子: apple banana 找至少包含上面词中的一个的记录行 +apple +juice 两个词均在被包含 +apple macintosh 包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些 +apple -macintosh 包含 “apple” 但不包含 “macintosh” 4.4. > :提高该字的相关性,查询的结果会排在比较靠前的位置。
4.5.< :降低相关性,查询的结果会排在比较靠后的位置。 例子:4.5.1.先不使用 >< select * from tommy.girl where match(girl_name) against('张欣婷' in boolean mode); 可以看到完全匹配的排的比较靠前 4.5.2. 单独使用 >

select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴' in boolean mode);

使用了>的李秀琴马上就排到最前面了

4.5.3. 单独使用 <

select * from tommy.girl where match(girl_name) against('张欣婷 <不是人' in boolean mode);

看到没,不是人也排到最前面了,这里使用的可是 < 哦,说好的降低相关性呢,往下看吧。 4.5.4.同时使用>< select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴 <练习册 <不是人>是个鬼' in boolean mode);

到这里终于有答案了,只要使用了 ><的都会往前排,而且>的总是排在<的前面 小结一下:1. 只要使用 ><的总比没用的 靠前; 2. 使用 >的一定比 <的排的靠前 (这就符合相关性提高和降低); 3. 使用同一类的,使用的越早,排的越前。 4.6. ( ):可以通过括号来使用字条件。 eg: +aaa +(>bbb <ccc) // 找到有aaa和bbb和ccc,aaa和bbb,或者aaa和ccc(因为bbb,ccc前面没有+,所以表示可有可无), 然后 aaa&bbb > aaa&bbb&ccc > aaa&ccc
4.7. ~ :将其相关性由正转负,表示拥有该字会降低相关性,但不像「-」将之排除,只是排在较后面。

eg: +apple ~macintosh 先匹配apple,但如果同时包含macintosh,就排名会靠后。
4.8. * :通配符,这个只能接在字符串后面。

MATCH (girl_name) AGAINST ('+*ABC*') #错误,不能放前面

MATCH (girl_name) AGAINST ('+张筱雨*') #正确
4.9. " " :整体匹配,用双引号将一段句子包起来表示要完全相符,不可拆字。

eg: "tommy huang" 可以匹配 tommy huang xxxxx 但是不能匹配 tommy is huang。
5.补充:Windows下无法修改 ft_min_word_len的情况,
5. 1. 使用cmd打开 services.msc,

找到你的 MySQL服务,右键Properties,找到你的my.ini所在的路径

5.2. 停止MySQL,在my.ini中增加 ft_min_word_len = 1,重启MySQL,

然后使用命令 show variables like 'ft_min_word_len'; 查看是否生效了

MySQL FullText

1.创建全文索引(FullText index)

旧版的MySQL的全文索引只能用在MyISAM表格的char、varchar和text的字段上。

不过新版的MySQL5.6.24上InnoDB引擎也加入了全文索引,所以具体信息要随时关注官网,
1.1. 创建表的同时创建全文索引

CREATE TABLE article (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT(title, body)
) TYPE=MYISAM;

1.2.通过 alter table 的方式来添加

ALTER TABLE `student` ADD FULLTEXT INDEX ft_stu_name (`name`) #ft_stu_name是索引名,可以随便起

或者:ALTER TABLE `student` ADD FULLTEXT ft_stu_name (`name`)

1.3. 直接通过create index的方式

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`)

也可以在创建索引的时候指定索引的长度:

CREATE FULLTEXT INDEX ft_email_name ON `student` (`name`(20))

2. 删除全文索引
2.1. 直接使用 drop index(注意:没有 drop fulltext index 这种用法)

DROP INDEX full_idx_name ON tommy.girl ;
2.2. 使用 alter table的方式

ALTER TABLE tommy.girl DROP INDEX ft_email_abcd;

3.使用全文索引

跟普通索引稍有不同

使用全文索引的格式: MATCH (columnName) AGAINST ('string')

eg:

SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

当查询多列数据时:

建议在此多列数据上创建一个联合的全文索引,否则使用不了索引的。

SELECT * FROM `student` WHERE MATCH(`name`,`address`) AGAINST('聪 广东')

3.1. 使用全文索引需要注意的是:(基本单位是词)

分词,全文索引以词为基础的,MySQL默认的分词是所有非字母和数字的特殊符号都是分词符

3.2. MySQL中与全文索引相关的几个变量:

使用命令:mysql> SHOW VARIABLES LIKE 'ft%'; #ft就是FullText的简写

ft_boolean_syntax + -><()~*:""&| #改变IN BOOLEAN MODE的查询字符,不用重新启动MySQL也不用重建索引

ft_min_word_len 4 #最短的索引字符串,默认值为4,(通常改为1)修改后必须重建索引文件 重新建立索引命令:repair table tablename quick

ft_max_word_len 84 #最长的索引字符串,默认值为84,修改后必须重建索引文件

ft_query_expansion_limit 20 #查询括展时取最相关的几个值用作二次查询

ft_stopword_file (built-in) #全文索引的过滤词文件,具体可以参考:MySQL全文检索中不进行全文索引默认过滤词 特别注意:50%的门坎限制(当查询结果很多,几乎所有记录都有,或者极少的数据,都有可能会返回非所期望的结果) -->可用IN BOOLEAN MODE即可以避开50%的限制。

此时使用全文索引的格式就变成了: SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪' IN BOOLEAN MODE)

4. ft_boolean_syntax (+ -><()~*:""&|) 逻辑全文搜索:

逻辑全文搜索支持下面的操作符:

+
一个领头的加号表示,该词必须出现在每个返回的记录行中。

-
一个领头的减号表示,该词必须不出现在每个返回的记录行中。

缺省的 (当既没有加号也没有负号被指定时)词是随意的,但是包含它的记录行将被排列地更高一点。这个模仿没有 IN BOOLEAN MODE 修饰词的 MATCH() ... AGAINST() 的行为。

< >
这两个操作符用于改变一个词的相似性值的基值。< 操作符减少基值,> 操作符则增加它。参看下面的示例。

( )
圆括号用于对子表达式中的词分组。

~
一个领头的否定号的作用象一个否定操作符,引起行相似性的词的基值为负的。它对标记一个噪声词很有用。一个包含这样的词的记录将被排列得低一点,但是不会被完全的排除,因为这样可以使用 - 操作符。

*
一个星号是截断操作符。不想其它的操作符,它应该被追加到一个词后,不加在前面。

""
短语,被包围在双引号""中,只匹配包含这个短语(字面上的,就好像被键入的)的记录行。

一些示例:

apple banana
找至少包含上面词中的一个的记录行
+apple +juice
... 两个词均在被包含
+apple macintosh
... 包含词 “apple”,但是如果同时包含 “macintosh”,它的排列将更高一些
+apple -macintosh
... 包含 “apple” 但不包含 “macintosh”
+apple +(>pie <strudel)
... 包含 “apple” 和 “pie”,或者包含的是 “apple” 和 “strudel” (以任何次序),但是 “apple pie” 排列得比 “apple strudel” 要高一点
apple*
... 包含 “apple”,“apples”,“applesauce” 和 “applet”
"some words"
... 可以包含 “some words of wisdom”,但不是 “some noise words”

4.1. 降低相关性例子,查询的结果会排在比较靠后的位置。
4.1.1.先不使用 ><
select * from tommy.girl where match(girl_name) against('张欣婷' in boolean mode);

完全匹配的排的比较靠前

4.1.2. 单独使用 >

select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴' in boolean mode);

使用了>的李秀琴排到最前面

4.1.3. 单独使用 <

select * from tommy.girl where match(girl_name) against('张欣婷 <不是人' in boolean mode);

看到没,不是人也排到最前面了,这里使用的可是 < 哦,说好的降低相关性呢,往下看吧。

4.1.4.同时使用><

select * from tommy.girl where match(girl_name) against('张欣婷 >李秀琴 <练习册 <不是人>是个鬼' in boolean mode);

只要使用了 ><的都会往前排,而且>的总是排在<的前面
小结:
1. 只要使用 ><的总比没用的 靠前;
2. 使用 >的一定比 <的排的靠前 (这就符合相关性提高和降低);
3. 使用同一类的,使用的越早,排的越前。

 

5.补充
Windows下无法修改 ft_min_word_len的情况,

5. 1. 使用cmd打开 services.msc,

找到你的 MySQL服务,右键Properties,找到你的my.ini所在的路径

5.2. 停止MySQL,在my.ini中增加 ft_min_word_len = 1,重启MySQL,

然后使用命令 show variables like 'ft_min_word_len'; 查看是否生效了

 

来源: http://blog.csdn.net/u011734144/article/details/52817766

 

 

mysqldump

mysqldump 用来转储数据库或搜集数据库进行备份或将数据转存为文件
如在服务器上进行备份并且表均为MyISAM表,应考虑使用mysqlhotcopy,因为可以更快地进行备份和恢复。

1.mysqldump的几种常用方法:

(1)导出整个数据库(包括数据库中的数据)

mysqldump -u username -p dbname > dbname.sql

(2)导出数据库结构(不含数据)

mysqldump -u username -p -d dbname > dbname.sql

(3)导出数据库中的某张数据表(包含数据)

mysqldump -u username -p dbname tablename > tablename.sql

(4)导出数据库中的某张数据表的表结构(不含数据)

mysqldump -u username -p -d dbname tablename > tablename.sql

(5)转储几个数据库
mysqldump ---database db_name1 [db_name2 ...] > my_databases.sql

(6)转储全库
mysqldump --all-databases > all_databases.sql

(7)Master/Slave 数据转移
mysqldump -uroot -ppassword --opt --default-character-set=utf8 --extended-insert=true --single-transaction -R --flush-logs --master-data=1 --all-databases > 20170809.sql

注:查询file和position的值 grep -i "change master to" 20170809.sql
windows find "CHANGE MASTER TO " d:\\20170809.sql

2. 还原数据到库

mysql> source /home/dba/dbname.sql
shell> mysql db_name < backup-file.sql
shell> mysqldump --opt db_name > backup-file.sql

库内具有函数时考虑:
SET GLOBAL log_bin_trust_function_creators = 1;
source /home/dba/dbname.sql
SET GLOBAL log_bin_trust_function_creators = 0;

3. mysqldump支持的选项
如果运行mysqldump没有--quick或--opt选项,mysqldump在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用,但可以用--skip-opt禁用。

如果使用最新版本的mysqldump程序生成一个转储重装到很旧版本的MySQL服务器中,不应使用--opt或-e选项。

mysqldump支持下面的选项:

· ---help,-?

显示帮助消息并退出。

· --add-drop--database

在每个CREATE DATABASE语句前添加DROP DATABASE语句。

· --add-drop-tables

在每个CREATE TABLE语句前添加DROP TABLE语句。

· --add-locking

用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

· --all--database,-A

转储所有数据库中的所有表。与使用---database选项相同,在命令行中命名所有数据库。

· --allow-keywords

允许创建关键字列名。应在每个列名前面加上表名前缀。

· ---comments[={0|1}]

如果设置为 0,禁止转储文件中的其它信息,例如程序版本、服务器版本和主机。--skip—comments与---comments=0的结果相同。 默认值为1,即包括额外信息。

· --compact

产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。

· --compatible=name

产生与其它数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用几个值,用逗号将它们隔开。这些值与设置服务器SQL模式的相应选项有相同的含义。

该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储输出更兼容的SQL模式值。例如,--compatible=oracle 不映射Oracle类型或使用Oracle注释语法的数据类型。

· --complete-insert,-c

使用包括列名的完整的INSERT语句。

· --compress,-C

压缩在客户端和服务器之间发送的所有信息(如果二者均支持压缩)。

· --create-option

在CREATE TABLE语句中包括所有MySQL表选项。

· ---database,-B

转储几个数据库。通常情况,mysqldump将命令行中的第1个名字参量看作数据库名,后面的名看作表名。使用该选项,它将所有名字参量看作数据库名。CREATE DATABASE IF NOT EXISTS db_name和USE db_name语句包含在每个新数据库前的输出中。

· ---debug[=debug_options],-# [debug_options]

写调试日志。debug_options字符串通常为'd:t:o,file_name'。

· --default-character-set=charset

使用charsetas默认字符集。如果没有指定,mysqldump使用utf8。

· --delayed-insert

使用INSERT DELAYED语句插入行。

· --delete-master-logs

在主复制服务器上,完成转储操作后删除二进制日志。该选项自动启用--master-data。

· --disable-keys,-K

对于每个表,用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地装载转储文件,因为在插入所有行后创建索引。该选项只适合MyISAM表。

· --extended-insert,-e

使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小,重载文件时可以加速插入。

· --fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=...,--行-terminated-by=...

这些选项结合-T选项使用,与LOAD DATA INFILE的相应子句有相同的含义。

· --first-slave,-x

不赞成使用,现在重新命名为--lock-all-tables。

· --flush-logs,-F

开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。请注意如果结合--all--database(或-A)选项使用该选项,根据每个转储的数据库刷新日志。例外情况是当使用--lock-all-tables或--master-data的时候:在这种情况下,日志只刷新一次,在所有 表被锁定后刷新。如果你想要同时转储和刷新日志,应使用--flush-logs连同--lock-all-tables或--master-data。

· --force,-f

在表转储过程中,即使出现SQL错误也继续。

· --host=host_name,-h host_name

从给定主机的MySQL服务器转储数据。默认主机是localhost。

· --hex-blob

使用十六进制符号转储二进制字符串列(例如,'abc' 变为0x616263)。影响到的列有BINARY、VARBINARY、BLOB。

· --lock-all-tables,-x

所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭--single-transaction和--lock-tables。

· --lock-tables,-l

开始转储前锁定所有表。用READ LOCAL锁定表以允许并行插入MyISAM表。对于事务表例如InnoDB和BDB,--single-transaction是一个更好的选项,因为它不根本需要锁定表。

请注意当转储多个数据库时,--lock-tables分别为每个数据库锁定表。因此,该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转储状态可以完全不同。

· --master-data[=value]

该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限,并且必须启用二进制日志。如果该选项值等于1,位置和文件名被写入CHANGE MASTER语句形式的转储输出,如果你使用该SQL转储主服务器以设置从服务器,从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2,CHANGE MASTER语句被写成SQL注释。如果value被省略,这是默认动作。

--master-data选项启用--lock-all-tables,除非还指定--single-transaction(在这种情况下,只在刚开始转储时短时间获得全局读锁定。又见--single-transaction。在任何一种情况下,日志相关动作发生在转储时。该选项自动关闭--lock-tables。

· --no-create-db,-n

该选项禁用CREATE DATABASE /*!32312 IF NOT EXISTS*/ db_name语句,如果给出---database或--all--database选项,则包含到输出中。

· --no-create-info,-t

不写重新创建每个转储表的CREATE TABLE语句。

· --no-data,-d

不写表的任何行信息。如果你只想转储表的结构这很有用。

· --opt

该选项是速记;等同于指定 --add-drop-tables--add-locking --create-option --disable-keys--extended-insert --lock-tables --quick --set-charset。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启,但可以用--skip-opt禁用。要想只禁用确信用-opt启用的选项,使用--skip形式;例如,--skip-add-drop-tables或--skip-quick。

· --password[=password],-p[password]

连接服务器时使用的密码。如果你使用短选项形式(-p),不能在选项和密码之间有一个空格。如果在命令行中,忽略了--password或-p选项后面的 密码值,将提示你输入一个。

· --port=port_num,-P port_num

用于连接的TCP/IP端口号。

· --protocol={TCP | SOCKET | PIPE | MEMORY}

使用的连接协议。

· --quick,-q

该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

· --quote-names,-Q

用‘`’字符引用数据库、表和列名。如果服务器SQL模式包括ANSI_QUOTES选项,用‘"’字符引用名。默认启用该选项。可以用--skip-quote-names禁用,但该选项应跟在其它选项后面,例如可以启用--quote-names的--compatible。

· --result-file=file,-r file

将输出转向给定的文件。该选项应用在Windows中,因为它禁止将新行‘\n’字符转换为‘\r\n’回车、返回/新行序列。

· --routines,-R

在转储的数据库中转储存储程序(函数和程序)。使用---routines产生的输出包含CREATE PROCEDURE和CREATE FUNCTION语句以重新创建子程序。但是,这些语句不包括属性,例如子程序定义者或创建和修改时间戳。这说明当重载子程序时,对它们进行创建时定义者应设置为重载用户,时间戳等于重载时间。

如果你需要创建的子程序使用原来的定义者和时间戳属性,不使用--routines。相反,使用一个具有mysql数据库相应权限的MySQL账户直接转储和重载mysql.proc表的内容。

该选项在MySQL 5.1.2中添加进来。在此之前,存储程序不转储。

· --set-charset

将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SET NAMES语句,使用--skip-set-charset。

· --single-transaction

该选项从服务器转储数据之前发出一个BEGIN SQL语句。它只适用于事务表,例如InnoDB和BDB,因为然后它将在发出BEGIN而没有阻塞任何应用程序时转储一致的数据库状态。

当使用该选项时,应记住只有InnoDB表能以一致的状态被转储。例如,使用该选项时任何转储的MyISAM或HEAP表仍然可以更改状态。

--single-transaction选项和--lock-tables选项是互斥的,因为LOCK TABLES会使任何挂起的事务隐含提交。

要想转储大的表,应结合--quick使用该选项。

· --socket=path,-S path

当连接localhost(为默认主机)时使用的套接字文件。

· --skip--comments

参见---comments选项的描述。

· --tab=path,-T path

产生tab分割的数据文件。对于每个转储的表,mysqldump创建一个包含创建表的CREATE TABLE语句的tbl_name.sql文件,和一个包含其数据的tbl_name.txt文件。选项值为写入文件的目录。

默认情况,.txt数据文件的格式是在列值和每行后面的新行之间使用tab字符。可以使用--fields-xxx和--行--xxx选项明显指定格式。

注释:该选项只适用于mysqldump与mysqld服务器在同一台机器上运行时。你必须具有FILE权限,并且服务器必须有在你指定的目录中有写文件的许可。

· --tables

覆盖---database或-B选项。选项后面的所有参量被看作表名。

· --triggers

为每个转储的表转储触发器。该选项默认启用;用--skip-triggers禁用它。

· --tz-utc

在转储文件中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同时区的服务器之间转储和重载。(不使用该选项,TIMESTAMP列在具有本地时区的源服务器和目的服务器之间转储和重载)。--tz-utc也可以保护由于夏令时带来的更改。--tz-utc默认启用。要想禁用它,使用--skip-tz-utc。该选项在MySQL 5.1.2中加入。

· --user=user_name,-u user_name

连接服务器时使用的MySQL用户名。

· --verbose,-v

冗长模式。打印出程序操作的详细信息。

· --version,-V

显示版本信息并退出。

· --where='where-condition', -w 'where-condition'

只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符,一定要将条件引用起来。

例如:

"--where=user='jimf'" "-wuserid>1" "-wuserid<1" · --xml,-X

将转储输出写成XML。

还可以使用--var_name=value选项设置下面的变量:

· max_allowed_packet

客户端/服务器之间通信的缓存区的最大大小。最大为1GB。

· net_buffer_length

客户端/服务器之间通信的缓存区的初始大小。当创建多行插入语句时(如同使用选项--extended-insert或--opt),mysqldump创建长度达net_buffer_length的行。如果增加该变量,还应确保在MySQL服务器中的net_buffer_length变量至少这么大

5. 基于时间点的恢复

例:
(1)上午10点发生误操作,可以用如下语句备份和BINLOG将数据恢复到故障前
#mysqlbinlog --stop-date="2010-10-31 9:59:59" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

(2)跳过故障时间点,继续执行后面的BINLOG,完成恢复
#mysqlbinlog --start-date="2010-10-31 10:01:00" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

基于位置恢复(不完全恢复)
和基于时间点恢复类是,但是更加精确.因为同一时间点可能有多条SQL语句执行;
例:
#mysqlbinlog --start-date="2010-10-31 9:55:00" --stop-date="2010-10-31 10:05:00" /usr/local/mysql/var/mysql-bin.000013 > /tmp/mysql_restore.sql

该命令将在/tmp/目录下创建小的文件,编辑它找到错误语句前后的位置号,例如前后位置号分别是368312 和 368315
(2)恢复了以前的备份文件后,输入
#mysqlbinlog --stop-position="368312" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p

#mysqlbinlog --start-position="368315" /usr/local/mysql/var/mysql-bin.000013 |mysql -uroot -p


7. 参考

mysqldump error

mysqldump error

mysqldump备份有时报错了,内容如下:

mysql@localhost mysql]$ mysqldump --single-transaction -A -uroot -proot123 --master-data=2 > /u01/backup/testbk.sql
Warning: Using a password on the command line interface can be insecure.
Error: Couldn't read status information for table slave_master_info ()
mysqldump: Couldn't execute 'show create table `slave_master_info`': Table 'mysql.slave_master_info' doesn't exist (1146)

错误提示有innodb_index_stats,innodb_table_stats,slave_master_info,slave_relay_log_info,slave_worker_info等几个
使用select语句进行查询时,也会提示这些表不存在

最终解决方法是:删除错误的表后重建

1.查询不可使用的表
show create table mysql.innodb_index_stats;
show create table mysql.innodb_table_stats;
show create table mysql.slave_master_info;
show create table mysql.slave_relay_log_info;
show create table mysql.slave_worker_info;

2.删除上述5张表或有错误的表
drop table mysql.innodb_index_stats;
drop table mysql.innodb_table_stats;
drop table mysql.slave_master_info;
drop table mysql.slave_relay_log_info;
drop table mysql.slave_worker_info;

3.进入data/mysql相应文件夹找到关于5张表的数据文件(.frm,.ibd)并删除
rm -rf innodb_index_stats.*
rm -rf innodb_table_stats.*
rm -rf slave_master_info.*
rm -rf slave_relay_log_info.*
rm -rf slave_worker_info.*

4. 查找表结构
去找$MYSQL_HOME/share/mysql_system_tables.sql,分别search到每一表的建表语句,然后重建设;

mysql innodb_table_stats does not exist

InnoDB: Error: table `mysql`.`innodb_table_stats` does not exist in the InnoDB internal

这个原因很明显 ,是mysql库的innodb_table_stats表损坏了。

再点击 mysql的innodb_index_stats 表,同样损坏。

 

解决方法:

损坏了,当然就是删除重补回来。

1,删除表,进入mysql库,把innodb前缀的表文件删除。

[root@AY140311174146476cc0Z mysql]# pwd
/usr/local/mysql/data/mysql
[root@AY140311174146476cc0Z mysql]# rm -f innodb_*

2,重新执行sql文件语句。当然语句来自安装包。

具体路径,如5.6为例:mysql-5.6.19/scripts/mysql_system_tables.sql

语句贴出来吧

CREATE TABLE IF NOT EXISTS innodb_index_stats (
    database_name           VARCHAR(64) NOT NULL,
    table_name          VARCHAR(64) NOT NULL,
    index_name          VARCHAR(64) NOT NULL,
    last_update         TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    /* there are at least:
    stat_name='size'
    stat_name='n_leaf_pages'
    stat_name='n_diff_pfx%' */
    stat_name           VARCHAR(64) NOT NULL,
    stat_value          BIGINT UNSIGNED NOT NULL,
    sample_size         BIGINT UNSIGNED,
    stat_description        VARCHAR(1024) NOT NULL,
    PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0
 
 
 
CREATE TABLE IF NOT EXISTS innodb_table_stats (
    database_name           VARCHAR(64) NOT NULL,
    table_name          VARCHAR(64) NOT NULL,
    last_update         TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    n_rows              BIGINT UNSIGNED NOT NULL,
    clustered_index_size        BIGINT UNSIGNED NOT NULL,
    sum_of_other_index_sizes    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0

 

然后,再查询相关的innodb表。看下是否还有错误 。

这个错误到此解决了。

Mysql Replicate Relay log read failure

一、描述

Mysql主从复制模式中,slave上报错 “relay log read failure”,导致主从同步停止。

mysql> show slave status\G
...
Master_Log_File: dd-bin.002542
Relay_Master_Log_File: dd-bin.002540
Exec_Master_Log_Pos: 950583017
Last_Error: Relay log read failure:...
...

二、分析

      报错信息为从库“无法读取relay log 里的条目”,可能原因为master库的binglog错误,或slave库的中继日志错误。或者为网络问题及bug原因。

      一般是由于网络故障或slave库压力过大,导致relay-log格式错误造成的。找到当前已经同步的时间点,重新设置主从同步,就会产生新的中继日志,恢复正常。

三、问题处理

从"show  slave  status\G"的输出中,找到如下信息:

Relay_Master_Log_File: dd-bin.002540     //slave库已读取的master的binlog

Exec_Master_Log_Pos: 950583017           //在slave上已经执行的position位置点



    停掉slave,以slave已经读取的binlog文件,和已经执行的position为起点,重新设置同步。会产生新的中继日志,问题解决。

(不需要指定host,user,password等,默认使用当前已经设置好的)

mysql>stop slave;

mysql>change master to master_log_file='dd-bin.002540' , master_log_pos=950583017;

mysql>start slave;



四、验证结果

再次查看,错误已经解决,slave 开始追 master 的日志

mysql>show  slave status\G

Exec_Master_Log_Pos: 225927489        //slave上已经执行的position已经变化

Seconds_Behind_Master: 58527            //slave  落后主库的时间,单位秒



过几秒钟,再次查看。离与master同步更近了

mysql>show  slave status\G

Exec_Master_Log_Pos: 307469867

Seconds_Behind_Master: 29570

MySQL大数据场景的优化和运维之道

前言

MySQL数据库大家应该都很熟悉,而且随着前几年的阿里的去IOE,MySQL逐渐引起更多人的重视。

MySQL历史

  • 1979年,Monty Widenius写了最初的版本,96年发布1.0

  • 1995-2000年,MySQL AB成立,引入BDB

  • 2000年4月,集成MyISAM和replication

  • 2001年,Heikki Tuuri向MySQL建议集成InnoDB

  • 2003发布5.0,提供了视图、存储过程等功能

  • 2008年,MySQL AB被Sun收购,09年推出5.1

  • 2009年4月,Oracle收购Sun,2010年12月推出5.5

  • 2013年2月推出5.6 GA,5.7开发中

MySQL的优点

  • 使用简单

  • 开源免费

  • 扩展性“好”,在一定阶段扩展性好

  • 社区活跃

  • 性能可以满足互联网存储和性能需求,离不开硬件支持

上面这几个因素也是大多数公司选择考虑MySQL的原因。不过MySQL本身存在的问题和限制也很多,有些问题点也经常被其他数据库吐槽或鄙视

MySQL存在的问题

  • 优化器对复杂SQL支持不好

  • 对SQL标准支持不好

  • 大规模集群方案不成熟,主要指中间件

  • ID生成器,全局自增ID

  • 异步逻辑复制,数据安全性问题

  • Online DDL

  • HA方案不完善

  • 备份和恢复方案还是比较复杂,需要依赖外部组件

  • 展现给用户信息过少,排查问题困难

  • 众多分支,让人难以选择


到了刚才讲的MySQL的优势和劣势,可以看到MySQL面临的问题还是远大于它的优势的,很多问题也是我们实际需要在运维中优化解决的,这也是
MySQL
DBA的一方面价值所在。并且MySQL的不断发展也离不开社区支持,比如Google最早提交的半同步patch,后来也合并到官方主线。
Facebook Twitter等也都开源了内部使用MySQL分支版本,包含了他们内部使用的patch和特性。

数据库开发规范

数据库开发规范定义:开发规范是针对内部开发的一系列建议或规则, 由DBA制定(如果有DBA的话)。

开发规范本身也包含几部分:基本命名和约束规范,字段设计规范,索引规范,使用规范。

规范存在意义

  • 保证线上数据库schema规范

  • 减少出问题概率

  • 方便自动化管理

  • 规范需要长期坚持,对开发和DBA是一个双赢的事情

想想没有开发规范,有的开发写出各种全表扫描的SQL语句或者各种奇葩SQL语句,我们之前就看过开发写的SQL 可以打印出好几页纸。这种造成业务本身不稳定,也会让DBA天天忙于各种救火。

基本命名和约束规范

  • 表字符集选择UTF8 ,如果需要存储emoj表情,需要使用UTF8mb4(MySQL 5.5.3以后支持)

  • 存储引擎使用InnoDB

  • 变长字符串尽量使用varchar varbinary

  • 不在数据库中存储图片、文件等

  • 单表数据量控制在1亿以下

  • 库名、表名、字段名不使用保留字

  • 库名、表名、字段名、索引名使用小写字母,以下划线分割 ,需要见名知意

  • 库表名不要设计过长,尽可能用最少的字符表达出表的用途

字段规范

  • 所有字段均定义为NOT NULL ,除非你真的想存Null

  • 字段类型在满足需求条件下越小越好,使用UNSIGNED存储非负整数 ,实际使用时候存储负数场景不多

  • 使用TIMESTAMP存储时间

  • 使用varchar存储变长字符串 ,当然要注意varchar(M)里的M指的是字符数不是字节数;使用UNSIGNED INT存储IPv4 地址而不是CHAR(15) ,这种方式只能存储IPv4,存储不了IPv6

  • 使用DECIMAL存储精确浮点数,用float有的时候会有问题

  • 少用blob text

关于为什么定义不使用Null的原因

* 1.浪费存储空间,因为InnoDB需要有额外一个字节存储

* 2.表内默认值Null过多会影响优化器选择执行计划

关于使用datatime和timestamp,现在在5.6.4之后又有了变化,使用二者存储在存储空间上大差距越来越小 ,并且本身datatime存储范围就比timestamp大很多,timestamp只能存储到2038年

点击查看原图

索引规范

  • 单个索引字段数不超过5,单表索引数量不超过5,索引设计遵循B+ Tree索引最左前缀匹配原则

  • 选择区分度高的列作为索引

  • 建立的索引能覆盖80%主要的查询,不求全,解决问题的主要矛盾

  • DML和order by和group by字段要建立合适的索引

  • 避免索引的隐式转换

  • 避免冗余索引

关于索引规范,一定要记住索引这个东西是一把双刃剑,在加速读的同时也引入了很多额外的写入和锁,降低写入能力,这也是为什么要控制索引数原因。之前看到过不少人给表里每个字段都建了索引,其实对查询可能起不到什么作用。

冗余索引例子

  • idx_abc(a,b,c)

  • idx_a(a) 冗余

  • idx_ab(a,b) 冗余

隐式转换例子

字段:remark varchar(50) NOT Null

MySQL>SELECT id, gift_code FROM gift WHERE deal_id = 640 AND remark=115127; 1 row in set (0.14 sec)

MySQL>SELECT id, gift_code FROM pool_gift WHEREdeal_id = 640 AND remark=‘115127’; 1 row in set (0.005 sec)

字段定义为varchar,但传入的值是个int,就会导致全表扫描,要求程序端要做好类型检查

SQL类规范

  • 尽量不使用存储过程、触发器、函数等

  • 避免使用大表的JOIN,MySQL优化器对join优化策略过于简单

  • 避免在数据库中进行数学运算和其他大量计算任务

  • SQL合并,主要是指的DML时候多个value合并,减少和数据库交互

  • 合理的分页,尤其大分页

  • UPDATE、DELETE语句不使用LIMIT ,容易造成主从不一致

数据库运维规范

运维规范主要内容

  • SQL审核,DDL审核和操作时间,尤其是OnlineDDL

  • 高危操作检查,Drop前做好数据备份

  • 权限控制和审计

  • 日志分析,主要是指的MySQL慢日志和错误日志

  • 高可用方案

  • 数据备份方案

版本选择

  • MySQL社区版,用户群体最大

  • MySQL企业版,收费

  • Percona Server版,新特性多

  • MariaDB版,国内用户不多

建议选择优先级为:MySQL社区版 > Percona Server > MariaDB > MySQL 企业版

不过现在如果大家使用RDS服务,基本还以社区版为主

Online DDL问题

原生MySQL执行DDL时需要锁表,且锁表期间业务是无法写入数据的,对服务影响很大,MySQL对这方面的支持是比较差的。大表做DDL对DBA来说是很痛苦的,相信很多人经历过。如何做到Online DDL呢,是不是就无解了呢?当然不是!

点击查看原图

上面表格里提到的 Facebook OSC和5.6 OSC也是目前两种比较靠谱的方案

MySQL 5.6的OSC方案还是解决不了DDL的时候到从库延时的问题,所以现在建议使用Facebook OSC这种思路更优雅

下图是Facebook OSC的思路

点击查看原图

后来Percona公司根据Facebook OSC思路,用perl重写了一版,就是我们现在用得很多的pt-online-schema-change,软件本身非常成熟,支持目前主流版本。

使用pt-online-schema-change的优点有:

  • 1.无阻塞写入

  • 2.完善的条件检测和延时负载策略控制

值得一提的是,腾讯互娱的DBA在内部分支上也实现了Online DDL,之前测试过确实不错,速度快,原理是通过修改InnoDB存储格式来实现。

使用pt-online-schema-change的限制有:

  • 改表时间会比较长(相比直接alter table改表)

  • 修改的表需要有唯一键或主键

  • 在同一端口上的并发修改不能太多

可用性

关于可用性,我们今天分享一种无缝切主库方案,可以用于日常切换,使用思路也比较简单

在正常条件下如何无缝去做主库切换,核心思路是让新主库和从库停在相同位置,主要依赖slave start until 语句,结合双主结构,考虑自增问题。

点击查看原图

MySQL集群方案:

  • 集群方案主要是如何组织MySQL实例的方案

  • 主流方案核心依然采用的是MySQL原生的复制方案

  • 原生主从同步肯定存在着性能和安全性问题

MySQL半同步复制:

现在也有一些理论上可用性更高的其它方案

  • Percona XtraDB Cluster(没有足够的把控力度,不建议上)

  • MySQL Cluster(有官方支持,不过实际用的不多)

点击查看原图

红框内是目前大家使用比较多的部署结构和方案。当然异常层面的HA也有很多第三方工具支持,比如MHA、MMM等,推荐使用MHA

sharding拆分问题

  • Sharding is very complex, so itʼs best not to shard until itʼs obvious that you will actually need to!

  • sharding是按照一定规则数据重新分布的方式

  • 主要解决单机写入压力过大和容量问题

  • 主要有垂直拆分和水平拆分两种方式

  • 拆分要适度,切勿过度拆分

  • 有中间层控制拆分逻辑最好,否则拆分过细管理成本会很高

曾经管理的单表最大60亿+,单表数据文件大小1TB+,人有时候就要懒一些

点击查看原图

上图是水平拆分和垂直拆分的示意图

数据库备份

首先要保证的,最核心的是数据库数据安全性。数据安全都保障不了的情况下谈其他的指标(如性能等),其实意义就不大了。

备份的意义是什么呢?

  • 数据恢复!

  • 数据恢复!

  • 数据恢复!

目前备份方式的几个纬度:

  • 全量备份 VS 增量备份

  • 热备 VS 冷备

  • 物理备份 VS 逻辑备份

  • 延时备份

  • 全量binlog备份

建议方式:

  • 热备+物理备份

  • 核心业务:延时备份+逻辑备份

  • 全量binlog备份

借用一下某大型互联网公司做的备份系统数据:一年7000+次扩容,一年12+次数据恢复,日志量每天3TB,数据总量2PB,每天备份数据量百TB级,全年备份36万次,备份成功了99.9%。

主要做的几点:

  • 备份策略集中式调度管理

  • xtrabackup热备

  • 备份结果统计分析

  • 备份数据一致性校验

  • 采用分布式文件系统存储备份

备份系统采用分布式文件系统原因:

  • 解决存储分配的问题

  • 解决存储NFS备份效率低下问题

  • 存储集中式管理

  • 数据可靠性更好

使用分布式文件系统优化点:

* Pbzip压缩,降低多副本存储带来的存储成本,降低网络带宽消耗

* 元数据节点HA,提高备份集群的可用性

* erasure code方案调研

数据恢复方案


前的MySQL数据恢复方案主要还是基于备份来恢复,可见备份的重要性。比如我今天下午15点删除了线上一张表,该如何恢复呢?首先确认删除语句,然后用
备份扩容实例启动,假设备份时间点是凌晨3点,就还需要把凌晨3点到现在关于这个表的binlog导出来,然后应用到新扩容的实例上,确认好恢复的时间
点,然后把删除表的数据导出来应用到线上。

性能优化

复制优化

MySQL复制:

  • 是MySQL应用得最普遍的应用技术,扩展成本低

  • 逻辑复制

  • 单线程问题,从库延时问题

  • 可以做备份或读复制

问题很多,但是能解决基本问题

点击查看原图

上图是MySQL复制原理图,红框内就是MySQL一直被人诟病的单线程问题

单线程问题也是MySQL主从延时的一个重要原因,单线程解决方案:

  • 官方5.6+多线程方案

  • Tungsten为代表的第三方并行复制工具

  • sharding

点击查看原图

上图是MySQL5.6 目前实现的并行复制原理图,是基于库级别的复制,所以如果你只有一个库,使用这个意义不大

当然MySQL也认识到5.6这种并行的瓶颈所在,所以在5.7引入了另外一种并行复制方式,基于logical timestamp的并行复制,并行复制不再受限于库的个数,效率会大大提升

点击查看原图

上图是5.7的logical timestamp的复制原理图

刚才我也提到MySQL原来只支持异步复制,这种数据安全性是非常差的,所以后来引入了半同步复制,从5.5开始支持

点击查看原图

上图是原生异步复制和半同步复制的区别。可以看到半同步通过从库返回ACK这种方式确认从库收到数据,数据安全性大大提高

在5.7之后,半同步也可以配置你指定多个从库参与半同步复制,之前版本都是默认一个从库

对于半同步复制效率问题有一个小的优化,就是使用5.6+的mysqlbinlog以daemon方式作为从库,同步效率会好很多

关于更安全的复制,MySQL 5.7也是有方案的,方案名叫Group replication 官方多主方案,基于Corosync实现

点击查看原图

主从延时问题

原因:一般都会做读写分离,其实从库压力反而比主库大/从库读写压力大非常容易导致延时。

解决方案:

  • 首先定位延时瓶颈

  • 如果是IO压力,可以通过升级硬件解决,比如替换SSD等

  • 如果IO和CPU都不是瓶颈,非常有可能是SQL单线程问题,解决方案可以考虑刚才提到的并行复制方案

  • 如果还有问题,可以考虑sharding拆分方案

提到延时不得不提到很坑人的Seconds behind master,使用过MySQL的应该很熟悉

这个值的源码里算法

long time_diff= ((long)(time(0) – mi->rli.last_master_timestamp) – mi->clock_diff_with_master);

Secondsbehindmaster来判断延时不可靠,在网络抖动或者一些特殊参数配置情况下,会造成这个值是0但其实延时很大了。通过heartbeat表插入时间戳这种机制判断延时是更靠谱的

复制注意点:

  • Binlog格式,建议都采用row格式,数据一致性更好

  • Replication filter应用

主从数据一致性问题:

  • row格式下的数据恢复问题

InnoDB优化

成熟开源事务存储引擎,支持ACID,支持事务四个隔离级别,更好的数据安全性,高性能高并发,MVCC,细粒度锁,支持O_DIRECT。

主要优化参数:

  • innodbfileper_table =1

  • innodbbufferpool_size,根据数据量和内存合理设置

  • innodbflushlog_attrxcommit= 0 1 2

  • innodblogfile_size,可以设置大一些

  • innodbpagesize

  • Innodbflushmethod = o_direct

  • innodbundodirectory 放到高速设备(5.6+)

  • innodbbufferpool_dump

  • atshutdown ,bufferpool dump (5.6+)

    点击查看原图

 

上图是5.5 4G的redo log和5.6 设置大于4G redo log文件性能对比,可以看到稳定性更好了。innodblogfile_size设置还是很有意义的

InnoDB比较好的特性:

  • Bufferpool预热和动态调整大小,动态调整大小需要5.7支持

  • Page size自定义调整,适应目前硬件

  • InnoDB压缩,大大降低数据容量,一般可以压缩50%,节省存储空间和IO,用CPU换空间

  • Transportable tablespaces,迁移ibd文件,用于快速单表恢复

  • Memcached API,full text,GIS等

InnoDB在SSD上的优化:

  • 在5.5以上,提高innodbwriteiothreads和innodbreadiothreads

  • innodbiocapacity需要调大

  • 日志文件和redo放到机械硬盘,undo放到SSD,建议这样,但必要性不大

  • atomic write,不需要Double Write Buffer

  • InnoDB压缩

  • 单机多实例

也要搞清楚InnoDB哪些文件是顺序读写,哪些是随机读写

随机读写:

  • datadir

  • innodbdata file_path

  • innodbundo directory

顺序读写:

  • innodbloggrouphomedir

  • log-bin

InnoDB VS MyISAM:

  • 数据安全性至关重要,InnoDB完胜,曾经遇到过一次90G的MyISAM表repair,花了两天时间,如果在线上几乎不可忍受

  • 并发度高

  • MySQL 5.5默认引擎改为InnoDB,标志着MyISAM时代的落幕

TokuDB:

  • 支持事务 ACID 特性,支持多版本控制(MVCC)

  • 基于Fractal Tree Index,非常适合写入密集场景

  • 高压缩比,原生支持Online DDL

  • 主流分支都支持,收费转开源 。目前可以和InnoDB媲美的存储引擎

目前主流使用TokuDB主要是看中了它的高压缩比,Tokudb有三种压缩方式:quicklz、zlib、lzma,压缩比依次更高。现在很多使用zabbix的后端数据表都采用的TokuDB,写入性能好,压缩比高。

下图是我之前做的测试对比和InnoDB

点击查看原图

点击查看原图

上图是sysbench测试的和InnoDB性能对比图,可以看到TokuDB在测试过程中写入稳定性是非常好的。

tokudb存在的问题:

  • 官方分支还没很好的支持

  • 热备方案问题,目前只有企业版才有

  • 还是有bug的,版本更新比较快,不建议在核心业务上用

比如我们之前遇到过一个问题:TokuDB的内部状态显示上一次完成的checkpoint时间是“Jul 17 12:04:11 2014”,距离当时发现现在都快5个月了,结果堆积了大量redo log不能删除,后来只能重启实例,结果重启还花了七八个小时

MySQL优化相关的case

Query cache,MySQL内置的查询加速缓存,理念是好的,但设计不够合理,有点out。

锁的粒度非常大MySQL 5.6默认已经关闭

When the query cache helps, it can help a lot. When it hurts, it can hurt a lot.明显前半句已经没有太大用处,在高并发下非常容易遇到瓶颈。

关于事务隔离级别 ,InnoDB默认隔离级别是可重复读级别,当然InnoDB虽然是设置的可重复读,但是也是解决了幻读的,建议改成读已提交级别,可以满足大多数场景需求,有利于更高的并发,修改transaction-isolation。

点击查看原图

点击查看原图

上图是一个比较经典的死锁case,有兴趣可以测试下

关于SSD

关于SSD,还是提一下吧。某知名大V说过“最近10年对数据库性能影响最大的是闪存”,稳定性和性能可靠性已经得到大规模验证,多块SATA SSD做Raid5,推荐使用。采用PCIe SSD,主流云平台都提供SSD云硬盘支持。

最后说一下大家关注的单表60亿记录问题,表里数据也是线上比较核心的。

先说下当时情况,表结构比较简单,都是bigint这种整型,索引比较多,应该有2-3个,单表行数60亿+,单表容量1.2TB左右,当然内部肯定是有碎片的。

形成原因:历史遗留问题,按照我们前面讲的开发规范,这个应该早拆分了,当然不拆有几个原因:

  1. 性能未遇到瓶颈 ,主要原因

  2. DBA比较“懒“

  3. 想看看InnoDB的极限,挑战一下。不过风险也是很大的,想想如果在一个1.2TB表上加个字段加个索引,那感觉绝对酸爽。还有就是单表恢复的问题,恢复时间不可控。

我们后续做的优化 ,采用了刚才提到的TokuDB,单表容量在InnoDB下1TB+,使用Tokudb的lzma压缩到80GB,压缩效果非常好。这样也解决了单表过大恢复时间问题,也支持online DDL,基本达到我们预期。

今天讲的主要针对MySQL本身优化和规范性质的东西,还有一些比较好的运维经验,希望大家能有所收获。今天这些内容是为后续数据库做平台化的基础。我今天分享就到这里,谢谢大家。

QA

Q1:use schema;select * from table; 和select * from schema.table;两种写法有什么不一样吗?会对主从同步有影响吗?

对于主从复制来说执行效率上差别不大,不过在使用replication filter时候这种情况需要小心,应该要使用ReplicateWildIgnoreTable这种参数,如果不使用带wildignore,第一种方式会有问题,过滤不起作用。

Q2:对于用于MySQL的ssd,测试方式和ssd的参数配置方面,有没有好的建议?主要针对ssd的配置哈

关于SATA SSD配置参数,建议使用Raid5,想更保险使用Raid50,更土豪使用Raid 10

点击查看原图

上图是主要的参数优化,性能提升最大的是第一个修改调度算法的

Q3:数据库规范已制定好,如何保证开发人员必须按照规范来开发?

关于数据库规范实施问题,也是有两个方面吧,第一、定期给开发培训开发规范,让开发能更了解。第二、还是在流程上规范,比如把我们日常通用的建表和字段策略固化到程序,做成自动化审核系统。这两方面结合 效果会比较好。

Q4:如何最大限度提高innodb的命中率?

这个问题前提是你的数据要有热点,读写热点要有交集,否则命中率很难提高。在有热点的前提下,也要求你的你的内存要足够大,能够存更多的热点数据。尽量不要做一些可能污染bufferpool的操作,比如全表扫描这种。

Q5:主从复制的情况下,如果有CAS这样的需求,是不是只能强制连主库?因为有延迟的存在,如果读写不在一起的话,会有脏数据。

如果有CAS需求,确实还是直接读主库好一些,因为异步复制还是会有延迟的。只要SQL优化的比较好,读写都在主库也是没什么问题的。

Q6:关于开发规范,是否有必要买国标?

这个国标是什么东西,不太了解。不过从字面看,国标应该也是偏学术方面的,在具体工程实施时候未必能用好。

Q7:主从集群能不能再细化一点那?不知道这样问合适不?

看具体哪方面吧。主从集群每个小集群一般都是采用一主多从方式,每个小集群对应特定的一组业务。然后监控备份和HA都是在每个小集群实现。

Q8:如何跟踪数据库table某个字段值发生变化?

追踪字段值变化可以通过分析row格式binlog好一些。比如以前同事就是通过自己开发的工具来解析row格式binlog,跟踪数据行变化。

Q9:对超大表水平拆分,在使用MySQL中间件方面有什么建议和经验分享?

对于超大表水平拆分,在中间件上经验不是很多,早期人肉搞过几次。也使用过自己研发的数据库中间件,不过线上应用的规模不大。关于目前众多的开源中间件里,360的atlas是目前还不错的,他们公司内部应用的比较多。

Q10:我们用的MySQL proxy做读负载,但是少量数据压力下并没有负载,请问有这回事吗?

少量数据压力下,并没有负载 ,这个没测试过,不好评价

Q11:对于binlog格式,为什么只推荐row,而不用网上大部分文章推荐的Mix ?

这个主要是考虑数据复制的可靠性,row更好。mixed含义是指如果有一些容易导致主从不一致的SQL ,比如包含UUID函数的这种,转换为row。既然要革命,就搞的彻底一些。这种mix的中间状态最坑人了。

Q12: 读写分离,一般是在程序里做,还是用proxy ,用proxy的话一般用哪个?

这个还是独立写程序好一些,与程序解耦方便后期维护。proxy国内目前开源的比较多,选择也要慎重。

Q13: 我想问一下关于mysql线程池相关的问题,什么情况下适合使用线程池,相关的参数应该如何配置,老师有这方面的最佳实践没有?

线程池这个我也没测试过。从原理上来说,短链接更适合用线程池方式,减少建立连接的消耗。这个方面的最佳配置,我还没测试过,后面测试有进展可以再聊聊。

Q14: 误删数据这种,数据恢复流程是怎么样的(从库也被同步删除的情况)?


你删除数据的情况,如果只是一张表,单表在几GB或几十GB。如果能有延时备份,对于数据恢复速度是很有好处的。恢复流程可以参考我刚才分享的部分。目前
的MySQL数据恢复方案主要还是基于备份来恢复
,可见备份的重要性。比如我今天下午15点删除了线上一张表,该如何恢复呢。首先确认删除语句,然后用备份扩容实例启动,假设备份时间点是凌晨3点。就还
需要把凌晨3点到现在关于这个表的binlog导出来,然后应用到新扩容的实例上。确认好恢复的时间点,然后把删除表的数据导出来应用到线上。

Q15: 关于备份,binlog备份自然不用说了,物理备份有很多方式,有没有推荐的一种,逻辑备份在量大的时候恢复速度比较慢,一般用在什么场景?

物理备份采用xtrabackup热备方案比较好。逻辑备份一般用在单表恢复效果会非常好。比如你删了一个2G表,但你总数据量2T,用物理备份就会要慢了,逻辑备份就非常有用了

 

 

 

 

摘自:http://sanwen8.cn/p/10913Vm.html

MySQL数据库命名规范及约定

一、【操作规范】
1. 如无备注,则表中的第一个id字段一定是主键且为自动增长;
2. 如无备注,则数值类型的字段请使用UNSIGNED属性;
3. 如无备注,排序字段order_id在程序中默认使用降序排列;
4. 如无备注,所有字段都设置NOT NULL,并设置默认值;
5. 如无备注,所有的布尔值字段,如is_hot、is_deleted,都必须设置一个默认值,并设为0;
6. 所有的数字类型字段,都必须设置一个默认值,并设为0;
7. 针对varchar类型字段的程序处理,请验证用户输入,不要超出其预设的长度;
8. 建表时将数据字典中的字段中文名和属性备注写入数据表的备注中(“PK、自动增长”不用写);
9. 如无说明,建表时一律采用innodb引擎;

二、【常用表名约定】
0. 说明:表前缀用项目名称首字母缩写;所以表名都小写,单词之间用下划线分开,单词都用单数形式
1. user – 用户
2. category – 分类
3. goods – 商品、产品等一切可交易网站的物品都用此命名
4. good_gallery – 物品的相册
5. good_cate – 物品的分类,除了单独作为表名,其他地方分类单词一律用缩写cate
4. attr – 属性
5. article – 文章、新闻、帮助中心等以文章形式出现的,一般都用此命名
6. cart – 购物车
7. feedback – 用户反馈
8. order – 订单
9. site_nav – 包括页头和页尾导航
10. site_config – 系统配置表
11. admin – 后台用户 【RBAC标准表】
12. role – 后台用户角色【RBAC标准表】
13. access – 后台操作权限,相当于action【RBAC标准表】
14. role_admin – 后台用户对应的角色【RBAC标准表】
15. access_role – 后台角色对应的权限【RBAC标准表】
16. 待续

三、【常用列名约定】
1. 表名_id – 通常用作外键命名
2. cid –
特殊的编号,带有元数据,方便关联查询,你可以把它理解成类别(层次)编号。举个例子,产品在分类时,往往需要将其归类到子分类下,相应的字段中也一般只
记录子分类的id,这时若需要知道该产品属于哪个主分类,就需要通过子分类信息再查询到主分类信息,这是比较麻烦的,cid字段就是要解决这个问题。一般
的站点几十个分类肯定是够用了,所以这里假设某一主分类的cid为11,则子分类的cid从1101开始编号,处理时只需截取前两位数值便可知道该产品属
于哪一个主分类了。
3. add_time – 添加时间、上架时间等
4. last_time – 最后操作时间,如登录、修改记录
5. expire_time – 过期时间
6. name – 商品名称、商家名称等,不要跟title混用,title只用于文章标题、职称等
7. price – 价格
8. thumb – 只要是列表页面中的窗口图,一律用此命名
9. image_src – 相册中的图片地址一律用此命名,不要出现各种img,image,img_url,thumb_url等
10. head_thumb – 用户头像, 虽然有点长,一定要遵守。不要出现上述情况
11. image_alt – 相册中图片的alt属性
12. desc – 描述、简介,比如goods_desc,不要出现goods_txt这种
13. details – 详情、文章内容等
14. order_id – 排序
15. telephone – 座机号码
16. mobile – 手机号码
17. phone – 当不区分手机和座机时,请用phone命名
18. address – 地址,单独出现不要用addr缩写,组合出现时需用缩写,比如mac地址,mac_addr
19. zipcode – 邮编
20. region – 地区,大的区域,比如记录杭州市、温州市等
21. area – 区域,小的,比如上城区,江干区等
22. avg_cost – 人均消费
23. 待续

四、【数据表字段设计范例】

分类表(t_category

字段名

列名

类型

属性备注

说明

流水号 id int(10) PK、自动增长
特殊编号 cid varchar(4) 第一个主分类为11、第一个子分类为1101,类推,仅支持二级分类
名称 name varchar(10) 页面中需注明输入不超过10个字
父分类 pid int(10)
统计量 count int(10)
是否热门 is_hot tinyint(1)
首页显示 is_index tinyint(1)
排序 order_id int(10)