你需要经常察看以下3个配置项。不然,可能很快就会出问题。

innodb_buffer_pool_size:这是你安装完InnoDB后第一个应该设置的选项。缓冲池是数据和索引缓存的地方:这个值越大越好,这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)。

innodb_log_file_size这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。一直到MySQL 5.1,它都难于调整,因为一方面你想让它更大来提高性能,另一方面你想让它更小来使得崩溃后更快恢复。幸运的是从MySQL 5.5之后,崩溃恢复的性能的到了很大提升,这样你就可以同时拥有较高的写入性能和崩溃恢复性能了。一直到MySQL 5.5,redo日志的总尺寸被限定在4GB(默认可以有2个log文件)。这在MySQL 5.6里被提高。

一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6,你可以一开始就把它这是成4G。

max_connections:如果你经常看到‘Too many connections’错误,是因为max_connections的值太低了。这非常常见因为应用程序没有正确的关闭数据库连接,你需要比默认的151连接数更大的值。max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

InnoDB配置

从MySQL 5.5版本开始,InnoDB就是默认的存储引擎并且它比任何其他存储引擎的使用都要多得多。那也是为什么它需要小心配置的原因。

innodb_file_per_table:这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里(innodb_file_per_table = OFF) 或者为每张表的数据单独放在一个.ibd文件(innodb_file_per_table = ON)。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的,比如数据压缩。但是它不会带来任何性能收益。你不想让每张表一个文件的主要场景是:有非常多的表(比如10k+)。

MySQL 5.6中,这个属性默认值是ON,因此大部分情况下你什么都不需要做。对于之前的版本你必须在加载数据之前将这个属性设置为ON,因为它只对新创建的表有影响。

innodb_flush_log_at_trx_commit:默认值为1,表示InnoDB完全支持ACID特性。当你的主要关注点是数据安全的时候这个值是最合适的,比如在一个主节点上。但是对于磁盘(读写)速度较慢的系统,它会带来很巨大的开销,因为每次将改变flush到redo日志都需要额外的fsyncs。将它的值设置为2会导致不太可靠(unreliable)因为提交的事务仅仅每秒才flush一次到redo日志,但对于一些场景是可以接受的,比如对于主节点的备份节点这个值是可以接受的。如果值为0速度就更快了,但在系统崩溃时可能丢失一些数据:只适用于备份节点

innodb_flush_method: 这项配置决定了数据和日志写入硬盘的方式。一般来说,如果你有硬件RAID控制器,并且其独立缓存采用write-back机制,并有着电池断电保护,那么应该设置配置为O_DIRECT;否则,大多数情况下应将其设为fdatasync(默认值)。sysbench是一个可以帮助你决定这个选项的好工具。

innodb_log_buffer_size: 这项配置决定了为尚未执行的事务分配的缓存。其默认值(1MB)一般来说已经够用了,但是如果你的事务中包含有二进制大对象或者大文本字段的话,这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量,如果它不是0,增加innodb_log_buffer_size。

其他设置

query_cache_size: query cache(查询缓存)是一个众所周知的瓶颈,甚至在并发并不多的时候也是如此。 最佳选项是将其从一开始就停用,设置query_cache_size = 0(现在MySQL 5.6的默认值)并利用其他方法加速查询:优化索引、增加拷贝分散负载或者启用额外的缓存(比如memcache或redis)。如果你已经为你的应用启用了query cache并且还没有发现任何问题,query cache可能对你有用。这是如果你想停用它,那就得小心了。

log_bin:如果你想让数据库服务器充当主节点的备份节点,那么开启二进制日志是必须的。如果这么做了之后,还别忘了设置server_id为一个唯一的值。就算只有一个服务器,如果你想做基于时间点的数据恢复,这(开启二进制日志)也是很有用的:从你最近的备份中恢复(全量备份),并应用二进制日志中的修改(增量备份)。二进制日志一旦创建就将永久保存。所以如果你不想让磁盘空间耗尽,你可以用 PURGE BINARY LOGS 来清除旧文件,或者设置 expire_logs_days 来指定过多少天日志将被自动清除。
记录二进制日志不是没有开销的,所以如果你在一个非主节点的复制节点上不需要它的话,那么建议关闭这个选项。


skip_name_resolve:
当客户端连接数据库服务器时,服务器会进行主机名解析,并且当DNS很慢时,建立连接也会很慢。因此建议在启动服务器时关闭skip_name_resolve选项而不进行DNS查找。唯一的局限是之后GRANT语句中只能使用IP地址了,因此在添加这项设置到一个已有系统中必须格外小心。

总结

当然还有其他的设置可以起作用,取决于你的负载或硬件:在慢内存和快磁盘、高并发和写密集型负载情况下,你将需要特殊的调整。然而这里的目标是使得你可以快速地获得一个稳健的MySQL配置,而不用花费太多时间在调整一些无关紧要的MySQL设置或读文档找出哪些设置对你来说很重要上。

原文连接:http://www.oschina.net/translate/10-mysql-settings-to-tune-after-installation

mysqldump导入导出数据库总结

基本的使用方法总结:

1 导出所有库
系统命令行
mysqldump -uusername -ppassword –all-databases > all.sql

2 导入所有库
mysql命令行
mysql>source all.sql;

3 导出某些库
系统命令行
mysqldump -uusername -ppassword –databases db1 db2 > db1db2.sql

4 导入某些库
mysql命令行
mysql>source db1db2.sql;

5 导入某个库
系统命令行
mysql -uusername -ppassword db1 < db1.sql;
或mysql命令行
mysql>source db1.sql;

6 导出某些数据表
系统命令行
mysqldump -uusername -ppassword db1 table1 table2 > tb1tb2.sql

7 导入某些数据表
系统命令行
mysql -uusername -ppassword db1 < tb1tb2.sql
或mysql命令行
mysql>
user db1;
source tb1tb2.sql;

8 mysqldump字符集设置
mysqldump -uusername -ppassword –default-character-set=gb2312 db1 table1 > tb1.sql

mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个SQL服务器(不一定是一个MySQL服务器)。转储包含创建表和/或装载表的SQL语句。
如果你在服务器上进行备份,并且表均为MyISAM表,应考虑使用mysqlhotcopy,因为可以更快地进行备份和恢复。
有3种方式来调用mysqldump:
shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] —database DB1 [DB2 DB3…]
shell> mysqldump [options] –all–database
如果没有指定任何表或使用了—database或–all–database选项,则转储整个数据库。
要想获得你的版本的mysqldump支持的选项,执行mysqldump —help。
如果运行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变量至少这麼大。
还可以使用–set-variable=var_name=value或-O var_name=value语法设置变量。然而,现在不赞成使用该语法。
mysqldump最常用於备份一个整个的数据库:
shell> mysqldump –opt db_name > backup-file.sql
你可以这样将转储文件读回到服务器:
shell> mysql db_name < backup-file.sql
或者为:
shell> mysql -e “source /path-to–backup/backup-file.sql” db_name
mysqldump也可用於从一个MySQL服务器向另一个服务器复制数据时装载数据库:
shell> mysqldump –opt db_name | mysql –host=remote_host -C db_name
可以用一个命令转储几个数据库:
shell> mysqldump —database db_name1 [db_name2 …] > my_databases.sql
如果你想要转储所有数据库,使用–all–database选项:
shell> mysqldump –all-databases > all_databases.sql
如果表保存在InnoDB存储引擎中,mysqldump提供了一种联机备份的途径(参见下面的命令)。该备份只需要在开始转储时对所有表进行全局读锁定(使用FLUSH TABLES WITH READ LOCK)。获得锁定后,读取二进制日志的相应内容并将锁释放。因此如果并且只有当发出FLUSH…时正执行一个长的更新语句,MySQL服务器才停止直到长语句结束,然后转储则释放锁。因此如果MySQL服务器只接收到短(“短执行时间”)的更新语句,即使有大量的语句,也不会注意到锁期间。
shell> mysqldump –all-databases –single-transaction > all_databases.sql
对於点对点恢复(也称为“前滚”,当你需要恢复旧的备份并重放该备份以后的更改时),循环二进制日志或至少知道转储对应的二进制日志内容很有用:
shell> mysqldump –all-databases –master-data=2 > all_databases.sql

shell> mysqldump –all-databases –flush-logs –master-data=2 > all_databases.sql
如果表保存在InnoDB存储引擎中,同时使用–master-data和–single-transaction提供了一个很方便的方式来进行适合点对点恢复的联机备份。

6. 提昇資料複製和高可用度。

複製(Replication)是MySQL的功能中,最廣泛被實現在資料庫的擴展性和高可用性(HA)的架構中。MySQL 5.6中包含了一些設計給開發人員構建下一代網路,雲計算,社群和手機應用程式和自我修補的複製拓撲和高效能主從架構的新功能。關鍵功能包括:

• 新的全域交易的標識符(GTIDs) – GTIDs讓複製的交易進行的完整性藉由主從的架構下,可以被紀錄下來,進而提供了一個基礎的自我修復,同時使DBA和開發人員可以輕鬆地在”主”(master)伺服器出現故障時,由事件中找到最新的”從”(slave)伺服器。而GTIDs是直接放置在BINLOG裡,如果要消除GTIDs,需要複雜的第三方附加組件(add-ons)來追蹤GTIDs。

• 新的MySQL的複製工具 – 一組新的Python工具的設計,以充分利用新的複製GTIDs,以提供在”主”伺服器故障或維護時,複製(Replication)的自動故障轉移(Auto Fail-over)的管理和監控。

• 新的從伺服器多線程 – 基於資料庫綱要(schema),分割工作線程來處理資料,讓資料的更新可以同時並行處理,而不是順序處理。這對工作負載的好處是可以隔離應用程式來使用資料庫 – 例如,多用戶系統。

• 新的二進制日誌組提交(BGC) – 在MySQL5.6複製主伺服器寫入BINLOG,而不是一次性地將它們提交,的拓撲結構中,這樣的改變在主伺服器上有顯著的性能提昇。 BGC也可以有更精細的鎖定,而減少解鎖的等待,而再次地增加效能。

• 行複製的新優化 – MySQL5.6提供了一個新的選項變數binlog-row-image=minimal,這可以使應用程序只複製在DML操作下而改變的資料成分。這種方式提高了主/從伺服器兩端的複製吞吐量,並最大限度地減少二進制日誌的磁碟空間,網路資源和伺服器所需要的記憶體空間。
• 新的從伺服器故障安全保護(Crash-Save) – MySQL 5.6將二進制日誌的內部資料儲存在資料表中,以便從伺服器可以自動回滾(roll back)複製到故障之前的最後一次交易事件,並不需管理員干預而能繼續複製。這不僅降低了運營的負擔,同時也消除了從伺服器試圖恢復損壞的資料所造成的資料損失的風險。此外,如果主伺服器的故障導致二進制日誌檔損毀,服務器會自動恢復到一個可以正確讀取資料的位置。

• 新的複製檢查 – MySQL 5.6藉由偵測資料是否損毀以及在損毀事件套用在從伺服器前回傳錯誤,來確保資料複製的完整性,以防止從伺服器故障。

• 新的時間延遲(Time-delayed)複製 – MySQL 5.6允許開發人員在複製串流上設定時間延遲,以防止在主伺服器上的操作失誤傳送到從伺服器。隨著配置的主從時間延遲,在發生故障或為了將資料庫恢復到之前的狀態,從伺服器可以提升成為新的主伺服器。它也讓資料庫可以在發生錯誤或電力中斷前就被檢測到,進而保護資料庫的安全以避免需要資料補救。

提昇效能綱要(Performance Schema)。

MySQL校能綱要在MySQL5.5中引入的目的是在關鍵時間點下提供績效指標的衡量標準。 在MySQL 5.6中,預設my.cnf設定檔開啟優化過及自動調整的效能綱要,最大限度地減少系統負荷。因此,在正式環境中使用的監控常用的應用程式用不會造成問題。此外,在計費和或以計費為目的的環境中,新的MySQL計量工具能夠針對消費用戶,主機帳戶,應用程序等捕捉到細部的資源使用狀況。

8. 提昇安全性。

MySQL 5.6引入了一個密碼在內部處理和加密大修改。新的選項和功能,包括:

• master.info的新替代密碼 – MySQL 5.6的複製,延伸了START SLAVE命令,讓DBA可以在複製的從伺服器的選項中指定主用戶名和密碼,來進行帳戶驗證。以往要連結到主伺服器,需借用外部的驗證插件。有了這些選項,用戶名和密碼將不再需要被暴露在純文字的master.info文件中。

• 一般查詢日誌,慢速查詢日誌,二進制日誌中的新加密 – 指令在寫入這些日誌檔時,指令中的密碼,不會再以純文本的格式。

• 使用適當強度的新密碼雜湊 – MySQL伺服器內部驗證的預設密碼雜湊從PASSWORD函數改成使用SHA-256密碼雜湊算法並使用隨機salt值。

• 命令列上新的密碼選項 – MySQL的5.6引入了新的選項/設定檔(.mylogin.cnf),可用於命令列上操作來安全地存儲用戶密碼。

• 下次登錄時改變密碼 – 現在DBA和開發人員可以透過控制mysql.user資料表中的一個新password_expired標誌,讓使用者下次登入時必須更改密碼。

• 新的密碼驗證策略 – 現在密碼可以根據強度,長度,大小寫混合,特殊字符以及用戶自定的低,中,高的策略來驗證密碼是否合適。(細節說明如下)

啟用密碼插件:http://dev.mysql.com/doc/refman/5.6/en/validate-password-plugin.html
validate_password插件可於MySQL 5.6.6之後的版本使用。可用來測試密碼及提昇安全性。
它提供了三種密碼強度的檢查方式:

i. WEAK: 只檢查密碼長度。
ii. MEDIUM:檢查大小寫和特殊符號。
iii. STRONG:檢查密碼是否存在密碼字典檔中。

步驟如下:

a. 在MySQL configuration file中(e.g. /etc/my.cnf)的mysqld設定中,加入plugin-load=validate_password.so。

1
2
3
4
# cat /etc/mycnf
[mysqld]
plugin-load=validate_password.so

b. 重新啟動mysqld。

c. 檢查環境變數。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_dictionary_file    |        |
| validate_password_length             | 8      |
| validate_password_mixed_case_count   | 1      |
| validate_password_number_count       | 1      |
| validate_password_policy             | MEDIUM |
| validate_password_special_char_count | 1      |
+--------------------------------------+--------+
6 rows in set (0.00 sec)

d. 測試password validation。

1
2
3
4
mysql> set password for 'stanley' @'%s' = password('test');
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql> set password for 'stanley'@'%' = password('Stanley!1234');
Query OK, 0 rows affected (0.00 sec)

官方文件建議,password函數只適用在MySQL資料庫內部使用,而不要在應用程式中使用MySQL password的加密方法來儲存密碼,因為這樣會有安全性的疑慮。
因為密碼可以將明碼藉由sha1及unhex兩個函數來組成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select user, host, password from mysql.user where user='stanley' and host='%';
+---------+------+-------------------------------------------+
| user    | host | password                                  |
+---------+------+-------------------------------------------+
| stanley | %    | *76274CDEF97EB4B8B3B5E156F65C09379CF28D63 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select concat("*", sha1(unhex(sha1("Stanley!1234"))));
+------------------------------------------------+
| concat("*", sha1(unhex(sha1("Stanley!1234")))) |
+------------------------------------------------+
| *76274cdef97eb4b8b3b5e156f65c09379cf28d63      |
+------------------------------------------------+
1 row in set (0.00 sec)

 9. 其它的重要的提昇。

• 預設配置的新優化 – MySQL的5.6引入了伺服器的預設配置更改以提供現今的系統架構更好的即裝即用校能。這些新的預設值,是針對常被變更的設定和配置選項而設計,以盡量減少前期所需花費的時間。

• 改進TIME / TIMESTAMP/ DATETIME資料類型:

• TIME / TIMESTAMP/ DATETIME – 現在允許更精確微秒級的精準度的時間/日期比較和資料選擇。

• TIMESTAMP/ DATETIME – 在MySQL 5.5中已經新增此兩種資料類型。此欄位的預設值允許開發人員指定當前的時間戳,自動更新,或兩者。

• TIMESTAMP – 在MySQL 5.6的TIMESTAMP資料欄,其預設值為可以為空。在沒有將欄位設定為確切值或此欄位不允許為空值的情況下,TIMESTAMP列不會在自動設定為DEFAULT NOW()或ON UPDATE NOW()這兩個屬性值。

• 較好的狀況處理 – GET DIAGNOSTICS

藉由新的MySQL診斷區和相應的GET DIAGNOSTICS指令,MySQL 5.6使開發人員能夠輕鬆地檢查錯誤條件和異常代碼。診斷區可以通過多種選項,而提供了2種信息:

• 聲明 – 它提供了受影響的行數和發生的條件數目
• 條件 – 它提供了由之前操作的所有條件所返回錯誤代碼和訊息

新的GET DIAGNOSTICS指令提供了一個標準的診斷區域的接口,能容易地透過CLI或從應用程序代碼來取得和處理的結果,例如:

1
2
3
4
5
6
7
8
9
10
mysql> DROP TABLE test.my_table;
ERROR 1051 (42S02): Unknown table 'test.my_table'
mysql> GET DIAGNOSTICS CONDITION 1
-> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
mysql> SELECT @p1, @p2;
+-------+-------------------------------+
| @p1   | @p2                           |
+-------+-------------------------------+
| 42S02 | Unknown table 'test.my_table' |
+-------+-------------------------------+

• 改進IPv6的支持

• MySQL 5.6改進了INET_ATON() 函數,使IPv6地址的字串在二進制轉換和儲存資料時,消耗最小的空間。

• MySQL 5.6將bind-address選項的預設值從“0.0.0.0”改為“0::0”,所以MySQL服務器接受連接所有的IPv4和IPv6地址。

10. 結論。

因為MySQL的性能,可靠性和易用性,使得MySQL成為最流行和最被廣泛使用的開源資料庫。這一個階段的MySQL 5.6資料庫,提供DBA和開發人員更高的性能,可擴展性並依據構建下一代基於網絡,基於雲計算和嵌入式應用與服務的需要而全面地改進。快速的摘要如下:

• 在MySQL5.6內部堆疊(stack)已被徹底重構,並充分利用現今計算機的運算能力和跨平台的多處理器的發展框架。

• 在優化性能和擴充性方面,InnoDB除去傳統的瓶頸和爭論。

• 創新的功能,如線上的綱要變更和NoSQL方式的資料庫存取,讓MySQL 不必妥協於ACID的承諾和SQL的查詢力量,而可以滿足最苛刻的應用需求。

• 為更有效地執行查詢和診斷報告而有更好的調校和分析相關的決策路徑,優化器進行了重構。
• 複製(replication)已做了改善,以確保主伺服器和從伺服器能保持同步,更好的自動恢復選項,更高的資料完整性和整體應用程式的高可用性。新的複製工具資料庫管理員提供配置,自動選擇,以確保最高等級的應用程式失敗轉移(fail-over)和恢復(recovery)。

• 最後,新的高性能綱要讓DBA和開發人員可以更容易地偵測與觀察到MySQL資料庫校能指標,進而知道應用程式花費了多少資源,以進行調整並獲得最佳校能。

在MySQL 5.6 中,做了很多效能上的改善,分別介紹如下:

1. 更好的效能和擴充性:提昇InnoDB儲存引擎。

MySQL 5.6在多執行緒和高線程CPU的系統中,提供了線性的效能和擴充性。效能提昇的主要關鍵是在於Oracle InnoDB儲存引擎的效率和同步處理的能力提昇而移除在InnoDB核心中過時的線程競爭和互斥鎖定。這樣的提昇使得MySQL可以完全的使用,現今以x86為架構的COTS(註一)多線程處理器的運算能力。

註一:COTS,commodity-off-the-shelf的縮寫,泛指通用在市場容易購得的商品,而非客制,價格昂貴且難以取得的硬體。

在Oracle內部的SysBench讀/寫和唯讀測試報告中,在同步處理從12個到60個讀寫交易的CPU線程環境中,5.6擴充表現仍是線性比率,遠比5.5的還穩定。

new-features-of-mysql-5-6-1

圖一:SysBench 讀/寫 TPS線性擴充性測試 - 60個CPU線程。
(圖來源:MySQL 5.6 新功能介紹白皮書)

new-features-of-mysql-5-6-2

圖二:SysBench 唯讀TPS線性擴充性測試 - 60個CPU線程
(圖來源:MySQL 5.6 新功能介紹白皮書)

更好的交易處理量

MySQL 5.6 提昇了InnoDB的效能及高同步的擴充性,交易能力和讀取的能力。這些使用案例是藉由測量應用程式在同時多人使用時,是如何的執行和擴充來取得效能的提昇。在支持這樣案例下,InnoDB有一個新重構後的架構可以減少互斥鎖的競爭,降低效能瓶頸和提供一個在資料存取中更一致的路徑。包括有:
• 在核心中,移除單一點的互斥鎖的競爭。
• 為flush的行為建立一個新的線程。
• 多線程的purge。
• 新的雜湊演算法。
• 減少緩衝池的競爭。
• 較好且更一致性的查詢執行,與更頻繁且可預測的時間間隔來持續統計資料的優化器。

上述的MySQL改善,反應在Sysbench的讀/寫的測試報告中:

new-features-of-mysql-5-6-3

圖三:SysBench讀寫比較 – MySQL 5.6 vs. MySQL 5.5
(圖來源:MySQL 5.6 新功能介紹白皮書)

較好的唯讀負載處理

針對InnoDB在唯讀狀態下的效能調校,讓網頁系統在同時處理大量的查詢及產生報表的交易行為中,有顯著的效能提昇。這些最佳化省略了自動提交的步驟,除非在設定了環境變數autocommit=1時才會再被使用,或是在開發者使用新的語法START_TRANSACTION_READ_ONLY的情況下,也可以省略自動提交。

1
2
3
4
SET autocommit = 0;
START_TRANSACTION_READ_ONLY;
SELECT * from test;
COMMIT;

 new-features-of-mysql-5-6-4

圖四:SysBench唯讀比較 – MySQL 5.6 vs. MySQL 5.5
(圖來源:MySQL 5.6 新功能介紹白皮書)

在Linux作業系統中,MySQL 5.6 在唯讀交易展現出來的效能是MySQL 5.5 的230%,相同的測試在Windows 2008則多出65%。

提供固態硬碟更好的效能

旋轉式磁盤硬碟是系統中最常見的瓶頸,最主要的因為是硬碟本身機械的部分,限制了系統擴展的能力。

結果,許多MySQL的應用程式開始佈署在可以提供類似記憶體讀取速度的SSD上,並在今日要求高可靠度的網頁應用系統中,提供了最高等級的同步處理能力。MySQL5.6包含了一些專門為SSD準備的重要增強功能,包括:
• 支援4K和8K page size以符合SSD的標準儲存演算法。
• 可攜式的 .ibd(InnoDB的資料)檔案。允許InnoDB的資料表可以容易的從預設的MySQL資料目錄上搬到SSD或網路儲存裝置
• 將undo日誌與表空間分開。將undo日誌從系統表空間搬到一個或多個獨立的表空間。undo日誌的密集讀取模式,讓這些新的表空間成為適合搬到SSD儲存的檔案,而讓系統表空間繼續留在一般硬碟中。

2. 更好的執行時間和診斷能力:提昇優化器。

在MySQL 5.6中,已經重構優化器,以取得更好的效率和性能,並提供了改進的功能,以獲得更好的查詢執行時間和自我診斷能力。MySQL 5.6主要的優化改進包括:

• 最佳化子查詢。MySQL優化器使用semi-join及子查詢實現(materialization)的方式來提昇子查詢的效能。具體來說,優化器是更有效率地處理FROM子句。目前,FROM子句中的子查詢實現並不會在執行SQL時立刻執行,而會延遲到當需要子查詢的內容時才執行,這樣就產生了極大地效能提昇。此外,優化器可能會添加一組索引來加快執行檢索的時間。

Oracle內部也使用DBT-3的第13號查詢來測試MySQL的運行效能,結果,執行時間從數天縮短到幾秒鐘,效能有顯著的提昇。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice,
sum(l_quantity)
from customer, orders, lineitem
where o_orderkey in (
select l_orderkey
from lineitem
group by l_orderkey
having sum(l_quantity) > 313
)
and c_custkey = o_custkey
and o_orderkey = l_orderkey
group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice
order by o_totalprice desc, o_orderdate
LIMIT 100;

• 小限制值(limit)資料文件排序的優化。對於查詢指令的ORDER BY和小的限制值(limit),現在,優化程序會產生一個已排序的結果集,再將其視為單一表格般的掃描資料。這樣的查詢,最常出現在Web的應用程序,從一個大的結果集中只顯示其中幾行,如:

1
SELECT ID, FIRTNAME, LASTNAME FROM STUDENT ORDER BY ID LIMIT 10;

經ORACLE內部測試後表示,這一類的查詢,可以提昇約4倍的執行速度,有助於改善整體用戶的體驗和減少反應時間。

索引條件下推(Index Condition Pushdown, ICP)。預設的情況下,優化器會將WHERE條件下推到存儲引擎以進行評估/表掃描,而將已排序的結果集返回到MySQL的服務器中。

1
2
SELECT ID, FIRSTNAME, LASTNAME FROM STUDENT
WHERE ID BETWEEN 10 AND 100;

經ORACLE內部測試後表示,這一類的查詢,在預設ICP的行為下,可以提昇約15倍的執行速度。

批次的索引鍵的讀取(Batched Key Access, BKA)和多範圍讀取(Multi-Range Read, MRR)。優化器批次提供主鍵給儲存引擎,使得儲存引擎可以讀取/排序,能更有效地縮短了查詢的執行時間。

new-features-of-mysql-5-6-5

圖五:MySQL 5.6 批次/區間的鍵讀取效能比較
(圖來源:MySQL 5.6 新功能介紹白皮書)

在Oracle內部作相同的測試,在硬碟是效能瓶頸的系統下使用DBT-3第十三號查詢,BKA和MMR的架構可以提昇查詢的效能約280倍。

• 更好的優化診斷。優化的MySQL5.6提供下列更好的診斷和調試:
• 解釋INSERT,UPDATE,DELETE的操作行為,
• 解釋計劃輸出更精確的優化指標和更好的JSON格式可讀性,
• 跟踪優化決策過程的優化軌跡。

3. 更好的應用系統可用性:線上DDL/Schema變更。

今天的Web應用程序大多都被設計為快速發展和滿足業務與產生營餘的要求。其結果是,系統的服務水平協議(Service Level Agreement,SLA)最多以分鐘測量基礎,而不是以數天或數週來計算。因此,當一個應用程序需要快速支持新的生產線或新產品時,後端數據庫綱要(schema)必須是一種符合一般性的架構設計,應用程式仍然可以進行正常的業務操作。 MySQL5.6支援了這樣的資料庫靈活性和敏捷性,並提供了以下新的ALTER TABLE DDL語法補充:

1
2
3
4
5
6
7
8
• CREATE INDEX
• DROP INDEX
• Change AUTO_INCREMENT value for a column
• ADD/DROP FOREIGN KEY
• Rename COLUMN
• Change ROW FORMAT, KEY_BLOCK_SIZE for a table
• Change COLUMN NULL, NOT_NULL
• Add, drop, reorder COLUMN

DBA和開發人員可以添加索引和執行標準的InnoDB表格改變而應用系統仍然可以對數據庫作資料更新。MySQL提供綱要的靈活性,以適應不斷變化的業務需求,這對特別需要快速發展應用程序的開發人員有很大的幫助。

4. 更好的敏捷開發:NoSQL方式存取InnoDB。

許多最新一代的網絡,雲計算,社交和移動應用需要快速行動,來處理簡單的鍵/值對。同時,他們必須保持能夠對相同的資料作複雜的查詢,並確保數據能在ACID下獲得保護。藉由InnoDB新的NoSQL API,開發者除了可以得到原本RDBMS交易保護的好處外,還可以再獲得NoSQL獲得儲存效能。

MySQL5.6通過熟悉的Memcached的API,以鍵/值方式與InnoDB溝通取得數據,並實作了一個新的Memcached的插件到mysqld中。新的memcached協議直接指向到InnoDB的API,使開發人員能夠使用現有的Memcached客戶端而省略查詢解析所需花費的時間,並直接進入InnoDB作資料查詢和交易更新。API使開發者可以重用標準的Memcached的函式庫和客戶端,並透過整合一個具備永續性,宕機保護,提供交易保護的後端數據庫,作為Memcached的功能延伸。

5. 更好的敏捷開發:延展InnoDB的使用案例。

新的MySQL5.6優化和功能,擴展InnoDB到更多的使用案例。因此,開發人員可以在單一存儲引擎的標準化下,簡化應用程式。

• 新的全文搜索 – 提供一個更好的替代MyISAM的全文檢索。現在,InnoDB可以讓開發人員在文字內容的資料表中建立全文索引,而加速了應用系統搜索的單字和片語的能力。InnoDB中的全文搜索支援自然語言/布林模式,鄰近搜尋和相關性排序。一個簡單的使用案例如下:

1
2
3
4
5
6
7
8
9
CREATE TABLE STUDENT (
id int unsigned auto_increment primary key,
firstname varchar(16),
lastname varchar(16),
address varchar(128),
fulltext(address)
) engine=innodb;
SELECT id, firstname, lastname, address
where match(address) against ('taipei' in natural language mode);

• 新的可攜式表空間 – 在每個資料表擁有各自的InnoDB的.ibd文件時,所創建的文件可以在任意存儲在不同的設備,不同的資料庫伺服器之中。當在創建資料表時,開發人員可以指定將.ibd的文件儲存在MySQL資料目錄之外。這可以讓無法負荷應用程式/資料庫的忙碌資料表可以很容易地轉移到外部的網絡存儲設備(SSD,HDD)。這個新功能藉由允許使用者輕易地在不同的MySQL伺服器之間匯出/匯入InnoDB資料表,而讓系統可以快速,無接縫地擴充應用程式,如下所示:

匯出範例:

1
2
3
4
5
mysql> use export_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> FLUSH TABLE my_table FOR EXPORT;
=> [innodb_data_home_dir]/export_database/my_table.cfg
mysql> UNLOCK TABLES;

匯入範例:

1
2
3
4
mysql> use import_database;
mysql> CREATE TABLE my_table(c1 INT) engine=InnoDB;
mysql> ALTER TABLE my_table DISCARD TABLESPACE;
mysql> ALTER TABLE my_table IMPORT TABLESPACE;

导出表结构

mysqldump -uroot -p --default-character-set=utf8 -d databasename > db.sql

修改db.sql内的字符集设置(notepad++编辑)

ENGINE=MyISAM DEFAULT CHARSET=latin1;
修改为
ENGINE=MyISAM DEFAULT CHARSET=utf8;

导出数据

mysqldump.exe -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 databasename >data.sql

//–default-character-set=latin1,使得导出的数据时中文,而不是乱码,当初改成utf8的做法是错误

修改data.sql

set names latin1;
改为
set names utf8;//使得客户端和链接使用utf8格式,使得数据以utf8的形式存储

创建数据库

create database databasename default charset utf8;

导入表结构

mysql -uroot -p databasename < db.sql

导入表数据

mysql -uroot -p databasename < data.sql

备注:原理流程

1、导出表结构,把latin1结构替换成utf8

2、用原有的latin1导出数据,并且将set names替换成utf8格式

3、创建数据库,默认字符集改成utf8

4、导入表结构、数据