http://anothermysqldba.blogspot.com/2014/05/a-look-at-mysql-57-dmr.html

Original post: http://anothermysqldba.blogspot.com/2014/05/a-look-at-mysql-57-dmr.html

所以我想它是關於時間我看著的MySQL 5.7。 這是一個高層次的概述，但我一直在尋找在MySQL的5.7簡而言之文件： 

• http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html

於是我開始用新鮮的Fedora 20（Xfce的）安裝。 
總體來說，我會檢討，我發現好奇和有趣與MySQL 5.7的幾個項目。 果殼有很多信息，因此非常值得檢討。 

我下載了MySQL的-5.7.4-M14-1.linux_glibc2.5.x86_64.rpm-bundle.tar 

安裝，計劃做好以下 

# tar -vxf MySQL-5.7.4-m14-1.linux_glibc2.5.x86_64.rpm-bundle.tar 
# rm -f mysql-community-embedded* 
]# ls -a MySQL-*.rpm 
MySQL-client-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-embedded-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-shared-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-devel-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-server-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
MySQL-test-5.7.4_m14-1.linux_glibc2.5.x86_64.rpm 
# yum -y install MySQL-*.rpm 
Complete! 

雖然它說完整的我還注意到一個錯誤。 它應該還沒有完成安裝，如果它發現一個錯誤，但確定.... 

FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: 
Data::Dumper 

這個錯誤被證實.. 

# /etc/init.d/mysql start 
Starting MySQL............ ERROR! The server quit without updating PID file 
# tail /var/lib/mysql/fedora20mysql57.localdomain.err 
ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist 
# /usr/bin/mysql_install_db 
FATAL ERROR: please install the following Perl modules before executing /usr/bin/mysql_install_db: 
Data::Dumper 
# yum -y install perl-Data-Dumper 
# /usr/bin/mysql_install_db 
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. 
# chown -R mysql:mysql /var/lib/mysql/mysql/ 
# cat /root/.mysql_secret 
# mysql -u root -p 
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('somepassword'); 
Query OK, 0 rows affected (0.01 sec) 

mysql> select @@version; 
+-----------+ 
| @@version | 
+-----------+ 
| 5.7.4-m14 | 
+-----------+ 

更健壯的程序升級等被記錄在這裡： 
http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html 
檢查以確保你有GLIBC_2.15 如果您計劃在您的操作系統上安裝此。 

行，所以現在，它被安裝，我們有什麼。 

• 安全方面的改進
• 安裝不創建匿名用戶帳戶

mysql> select User , Host,plugin from mysql.user \G 
*************************** 1. row *************************** 
User: root 
Host: localhost 
plugin : mysql_native_password 

• 安裝不創建test數據庫。

mysql> show databases; 
+--------------------+ 
| Database | 
+--------------------+ 
| information_schema | 
| mysql | 
| performance_schema | 
+--------------------+ 

• 密碼過期策略

mysql> SELECT @@default_password_lifetime \G 
*************************** 1. row *************************** 
@@default_password_lifetime: 360 

這些都是姍姍來遲的改進，並感謝大家的改進。 
所以現在來看看，在休息，我們至少需要某種形式的數據和架構。 因此，我將安裝世界數據庫測試。 

# wget http://downloads.mysql.com/docs/world_innodb.sql.gz 
# gzip -d world_innodb.sql.gz 
# mysql -u root -p -e "create database world"; 
# mysql -u root -p world < world_innodb.sql 

• 在線
• ALTER TABLE。 現在ALTER TABLE支持重命名索引的RENAME INDEX子句

# mysql -u root -p world 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` char(35) NOT NULL DEFAULT '', 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY ` CountryCode ` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB 
mysql> ALTER TABLE City ALGORITHM=INPLACE, RENAME KEY CountryCode TO THECountryCode; 
Query OK 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` char(35) NOT NULL DEFAULT '', 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY ` THECountryCode ` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB 


• InnoDB增強功能
  • VARCHAR的大小可以使用就地ALTER TABLE增加
  
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` char(35) NOT NULL DEFAULT '', 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY `THECountryCode` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB 
mysql> ALTER TABLE City ALGORITHM=COPY , CHANGE COLUMN Name Name VARCHAR(35); 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` varchar(35) DEFAULT NULL, 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY `THECountryCode` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB 
mysql> ALTER TABLE City ALGORITHM=INPLACE , CHANGE COLUMN Name Name VARCHAR(255); 
mysql> show create table City; 
CREATE TABLE `City` ( 
`ID` int(11) NOT NULL AUTO_INCREMENT, 
`Name` varchar(255) DEFAULT NULL, 
`CountryCode` char(3) NOT NULL DEFAULT '', 
`District` char(20) NOT NULL DEFAULT '', 
`Population` int(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`ID`), 
KEY `THECountryCode` (`CountryCode`), 
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) 
) ENGINE=InnoDB
  • InnoDB的臨時表的元數據不再存儲在InnoDB的系統表 。 當第一個創建表SELECT語句運行反對。
  
mysql> select * from INNODB_TEMP_TABLE_INFO; 
mysql> show create table INNODB_TEMP_TABLE_INFO; 
CREATE TEMPORARY TABLE `INNODB_TEMP_TABLE_INFO` ( 
`TABLE_ID` bigint(21) unsigned NOT NULL DEFAULT '0', 
`NAME` varchar(202) DEFAULT NULL, 
`N_COLS` int(11) unsigned NOT NULL DEFAULT '0', 
`SPACE` int(11) unsigned NOT NULL DEFAULT '0', 
`PER_TABLE_TABLESPACE` varchar(64) DEFAULT NULL, 
`IS_COMPRESSED` varchar(64) DEFAULT NULL 
) ENGINE=MEMORY 
CODE HERE 
  • 現在的InnoDB支持MySQL支持的空間數據類型
  • 這是巨大的。 因為它已經被人撿到的PostgreSQL超過MySQL的原因之一 。
  • http://www.oracle.com/us/corporate/press/2180737
  • http://dev.mysql.com/doc/refman/5.7/en/mysql-spatial-datatypes.html
  • http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
  • http://www.mysqlperformanceblog.com/2013/10/21/using-the-new-spatial-functions-in-mysql-5-6-for-geo-enabled-applications/
  • 現在有所有非壓縮的InnoDB的臨時表一個單獨的表空間
  mysql> select @@innodb_temp_data_file_path \G 
*************************** 1. row *************************** 
@@innodb_temp_data_file_path: ibtmp1:12M:autoextend

• 條件處理 :: 
  GET STACKED DIAGNOSTICS

mysql> DROP TABLE test.no_such_table; 
ERROR 1051 (42S02): Unknown table 'test.no_such_table' 
mysql> GET DIAGNOSTICS CONDITION 1 @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT; 
Query OK, 0 rows affected (0.45 sec) 

mysql> SELECT @p1, @p2 \G 
*************************** 1. row *************************** 
@p1: 42S02 
@p2: Unknown table 'test.no_such_table' 
1 row in set (0.01 sec)

• 觸發器 
  該觸發器的限制已經解除和多個觸發器是允許的。 請參閱文檔，因為他們給一個很好的例子。 我將演示它的一些在這裡只是為了顯示在一個表上的多個觸發器是可能的。

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2)); 
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount; 
mysql> SET @sum = 0; 
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00); 
SELECT @sum AS 'Total amount inserted'; 
+-----------------------+ 
| Total amount inserted | 
+-----------------------+ 
| 1852.48 | 
+-----------------------+ 
mysql> CREATE TRIGGER ins_transaction BEFORE INSERT ON account 
-> FOR EACH ROW PRECEDES ins_sum 
-> SET 
-> @deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), 
-> @withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0); 
mysql> SHOW triggers \G 
*************************** 1. row *************************** 
Trigger: ins_transaction 
Event: INSERT 
Table: account 
Statement: SET 
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0), 
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0) 
Timing: BEFORE 
Created: 2014-05-14 21:23:49.66 
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 
Definer: root@localhost 
character_set_client: utf8 
collation_connection: utf8_general_ci 
Database Collation: latin1_swedish_ci 
*************************** 2. row *************************** 
Trigger: ins_sum 
Event: INSERT 
Table: account 
Statement: SET @sum = @sum + NEW.amount 
Timing: BEFORE 
Created: 2014-05-14 21:22:47.91 
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION 
Definer: root@localhost 
character_set_client: utf8 
collation_connection: utf8_general_ci 
Database Collation: latin1_swedish_ci

• 全球化的改進
• MySQL的5.7.4包括支持中國國家標準GB18030字符集GB18030字符集。
• http://dev.mysql.com/doc/refman/5.7/en/charset-examples.html

mysql> CREATE TABLE t1 
-> ( c1 CHAR(10) CHARACTER SET latin1 
-> ) DEFAULT CHARACTER SET gb18030 COLLATE gb18030_chinese_ci ; 
Query OK 


• 更改複製主不STOP SLAVE
• http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
• Read more about this on via the two links above. This could be another very nice improvement. The documentation gives many examples. 


• HANDLER與分區表

mysql> HANDLER City OPEN AS city_handle; 
mysql> HANDLER city_handle READ FIRST; 
+----+-------+-------------+----------+------------+ 
| ID | Name | CountryCode | District | Population | 
+----+-------+-------------+----------+------------+ 
| 1 | Kabul | AFG | Kabol | 1780000 | 
+----+-------+-------------+----------+------------+ 
mysql> HANDLER city_handle READ NEXT LIMIT 3; 
+----+-----------+-------------+---------------+------------+ 
| ID | Name | CountryCode | District | Population | 
+----+-----------+-------------+---------------+------------+ 
| 5 | Amsterdam | NLD | Noord-Holland | 731200 | 
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 | 
| 7 | Haag | NLD | Zuid-Holland | 440900 | 
+----+-----------+-------------+---------------+------------+ 
mysql> CREATE TABLE `t2` ( 
-> `t2_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
-> `inserttimestamp` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, 
-> `somevalue` int(10) unsigned DEFAULT NULL, 
-> `rowLastUpdateTime` datetime DEFAULT NULL, 
-> PRIMARY KEY (`t2_id`,`inserttimestamp`) 
-> ) ENGINE=InnoDB; 
mysql> ALTER TABLE t2 
-> PARTITION BY RANGE ( TO_DAYS(inserttimestamp) ) ( 
-> PARTITION Jan2014 VALUES LESS THAN (TO_DAYS('2014-02-01')), 
-> PARTITION Feb2014 VALUES LESS THAN (TO_DAYS('2014-03-01')), 
-> PARTITION Mar2014 VALUES LESS THAN (TO_DAYS('2014-04-01')), 
-> PARTITION Apr2014 VALUES LESS THAN (TO_DAYS('2014-05-01')), 
-> PARTITION May2014 VALUES LESS THAN (TO_DAYS('2014-06-01')), 
-> PARTITION Jun2014 VALUES LESS THAN (TO_DAYS('2014-07-01')), 
-> PARTITION Jul2014 VALUES LESS THAN (TO_DAYS('2014-08-01')), 
-> PARTITION Aug2014 VALUES LESS THAN (TO_DAYS('2014-09-01')), 
-> PARTITION Sep2014 VALUES LESS THAN (TO_DAYS('2014-10-01')), 
-> PARTITION Oct2014 VALUES LESS THAN (TO_DAYS('2014-11-01')), 
-> PARTITION Nov2014 VALUES LESS THAN (TO_DAYS('2014-12-01')), 
-> PARTITION Dec2014 VALUES LESS THAN (TO_DAYS('2015-01-01')), 
-> PARTITION Jan2015 VALUES LESS THAN (TO_DAYS('2015-02-01')) 
-> ); 
mysql> INSERT INTO t2 VALUES (NULL,NOW(),1,NOW()); 
mysql> HANDLER t2 OPEN AS t_handle; 
mysql> HANDLER t_handle READ FIRST; 
+-------+---------------------+-----------+---------------------+ 
| t2_id | inserttimestamp | somevalue | rowLastUpdateTime | 
+-------+---------------------+-----------+---------------------+ 
| 1 | 2014-05-14 21:53:28 | 1 | 2014-05-14 21:53:28 | 
+-------+---------------------+-----------+---------------------+ 

• 與將mysqlbinlog數據庫名重寫
• 重命名由數據庫將mysqlbinlog
• 這是一種功能，Percona的已經具備了年齡。
• http://www.percona.com/doc/percona-server/5.1/release-notes/Percona-Server-5.1.62-13.3.html#5.1.62-13.3

mysql> select @@binlog_format\G 
*************************** 1. row *************************** 
@@binlog_format: ROW 

# mysqlbinlog --database=world mysql-bin.000002 | grep world | wc -l 
22543# mysqlbinlog --rewrite-db='world->renameddb' mysql-bin.000002 | grep renameddb | wc -l 
22542