MySQL5.7的新特性
来源:互联网 发布:windows pe安装win7 编辑:程序博客网 时间:2024/05/17 03:45
https://www.cnblogs.com/xuanzhi201111/p/5148113.html
MySQL 5.7版本据说已经在了很大的性能提升以及做得更加安全了,想了解更多MySQL 5.7的新特性可以参考我转载叶金荣老师的MySQL 5.7的新特性说明。这里我简单演示一下MySQL 5.7的安装已经一些新特性的使用,希望大家能一起进去,一起探讨技术。
MySQL 5.7的官方下载地址:http://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz
下面简单介绍一下安装:
[root@MySQL soft]# tar xf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz -C /data/service/[root@MySQL soft]# cd /data/service/[root@MySQL service]# mv mysql-5.7.10-linux-glibc2.5-x86_64/ mysql-5.7.10
下面进行数据目录的创建以及授权:
[root@MySQL service]# mkdir /data/{mysql3306,mysql3306log} -p[root@MySQL service]# groupadd mysql[root@MySQL service]# useradd -r -g mysql mysql[root@MySQL service]# chown -R mysql:mysql mysql-5.7.10/[root@MySQL service]# chown -R mysql:mysql /data/mysql3306*
基本操作已经完成,下面进行初始化操作,在MySQL 5.7的初始化操作与MySQL 5.6有点不同了,下面在MySQL 5.7的版本用MySQL 5.6的初始化方式进行操作一下,让大家看下会报什么错:
[root@MySQL mysql-5.7.10]# ./bin/mysql_install_db --user=mysql --datadir=/data/mysql33062016-01-21 11:29:05 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize2016-01-21 11:29:10 [ERROR] The bootstrap log isn't empty:2016-01-21 11:29:10 [ERROR] 2016-01-21T03:29:05.633658Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead2016-01-21T03:29:05.641584Z 0 [ERROR] Can't read from messagefile '/usr/share/mysql/english/errmsg.sys'[root@MySQL mysql-5.7.10]#
可以看到mysql_install_db is deprecated,说不赞同使用mysql_install_db,推荐使用的方法是:
Please consider switching to mysqld --initialize ,Please consider using --initialize instead
正确的初始方式如下:./bin/mysqld --initialize --user=mysql --basedir=/data/service/mysql-5.7.10/ --datadir=/data/mysql3306,如果datadir目录有文件,则会报以下错:
[root@MySQL mysql-5.7.10]# ./bin/mysqld --initialize --user=mysql --basedir=/data/service/mysql-5.7.10/ --datadir=/data/mysql33062016-01-21T05:43:56.355999Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2016-01-21T05:43:56.357796Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.2016-01-21T05:43:56.357814Z 0 [ERROR] Aborting
所以要把data directory文件删除掉再执行,如果删除目录下的文件还是报同样的错,可以试试把目录删除掉,再创建一个,然后授权:
[root@MySQL mysql-5.7.10]# ./bin/mysqld --initialize --user=mysql --basedir=/data/service/mysql-5.7.10/ --datadir=/data/mysql33062016-01-21T05:47:01.804937Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).2016-01-21T05:47:03.552899Z 0 [Warning] InnoDB: New log files created, LSN=457902016-01-21T05:47:03.816849Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.2016-01-21T05:47:03.883956Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 660686ae-c002-11e5-843e-00163e0217d7.2016-01-21T05:47:03.886131Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.2016-01-21T05:47:03.887120Z 1 [Note] A temporary password is generated for root@localhost: )vyd3aXj8hhC
MySQL 5.7初始化完后会生成一个临时的密码,A temporary password is generated for root@localhost: )vyd3aXj8hhC 如果想初始化表空间,在后面加上 --innodb_data_file_path=ibdata1:1G:autoextend即可。
启动MySQL 5.7,拷贝support-files/my-default.cnf ./
[root@MySQL mysql-5.7.10]# cp support-files/my-default.cnf ./my.cnf[root@MySQL mysql-5.7.10]# chown -R mysql:mysql my.cnf
编辑my.cnf加上基本选项:
[mysqld]# changes to the binary log between backups.log_bin# These are commonly set, remove the # and set as required.basedir = /data/service/mysql-5.7.10datadir = /data/mysql3306port = 3306server_id = 100socket = /tmp/mysqld.sock
编辑启动脚本:
[root@MySQL mysql-5.7.10]# cat start_mysql.sh #!/bin/bashnohup /data/service/mysql-5.7.10/bin/mysqld_safe --defaults-file=/data/service/mysql-5.7.10/my.cnf > /data/service/mysql-5.7.10/start_stop.log 2>&1 &
运行脚本启动 sh start_mysql.sh 。
登录MySQL 5.7,先添加MySQL 5.7的bin路径:
[root@MySQL mysql-5.7.10]# cat /etc/profile.d/mysql.sh export PATH=/data/service/mysql-5.7.10/bin:$PATH[root@MySQL mysql-5.7.10]# source /etc/profile.d/mysql.sh
登录时输入的密码是刚刚初始化完的密码:
[root@MySQL mysql-5.7.10]# mysql -uroot -p')vyd3aXj8hhC' -S /tmp/mysqld.sockmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.10-logCopyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
第一次登录,是必须要修改密码才能查看show databases;
mysql> show databases;ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.mysql>
从上面的信息可以看到,叫我们使用ALTER USER进行修改,下面我们修改一下密码,有关更多MySQL 5.7的用户密码设置可以参考:https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html
mysql> ALTER USER USER() IDENTIFIED BY '123456';Query OK, 0 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)mysql>
MySQL 5.7的mysql.user表已经没有password这一列了,所以查询用户和密码的方式如下:
mysql> select user,host,authentication_string from mysql.user;+-----------+-----------+-------------------------------------------+| user | host | authentication_string |+-----------+-----------+-------------------------------------------+| root | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 || mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |+-----------+-----------+-------------------------------------------+2 rows in set (0.00 sec)mysql> select password('123456');+-------------------------------------------+| password('123456') |+-------------------------------------------+| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-------------------------------------------+1 row in set, 1 warning (0.00 sec)
MySQL 5.7支持为表增加计算列
什么叫计算列呢,简单来说就是某一列的值是通过别的列计算得来,例如a列值为1,b列值为2,c列不需要操作手动插入,定义为a + b的结果为c的值,那么c就是计算列,是通过别的列计算得来,下面我们进行例子说明:
在MySQL 5.7之前的版本中,要实现这样的效果要利用触发器来完成,以下是以前的实现过程:
mysql> show create table tb1\G *************************** 1. row *************************** Table: tb1Create Table: CREATE TABLE `tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin1 row in set (0.01 sec)mysql>
创建一个插入的触发器:
mysql> create trigger insert_tb1 before insert on tb1 for each row set new.c3=new.c1+new.c2;Query OK, 0 rows affected (0.01 sec)
我们插入数据:
mysql> insert into tb1(c1,c2) values (1,2);Query OK, 1 row affected (0.00 sec)mysql> select * from tb1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 1 | 2 | 3 |+----+------+------+------+1 row in set (0.00 sec)mysql>
但如果我们更新了c1或者c2的值,c3是不会变动的,所以我们要加一个update的触发器:
mysql> select * from tb1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 1 | 2 | 3 |+----+------+------+------+1 row in set (0.00 sec)mysql> create trigger update_tb1 before update on tb1 for each row set new.c3=new.c1+new.c2; Query OK, 0 rows affected (0.01 sec)mysql> update tb1 set c1 = 3;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from tb1;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 3 | 2 | 5 |+----+------+------+------+1 row in set (0.00 sec)mysql>
可以看到,如果想实现计算列,那么就要借助触发器或者视图来实现,但在生产环境中,是不太建议使用触发器或者视图的。下面用MySQL 5.7来实现就变得很简单了。
MySQL 5.7计算列的实现:
在CREAE TABLE和ALTER TABLE 中都支持增加计算列的方式:
col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [NOT NULL | NULL] [[PRIMARY] KEY]
下面我们创建测试表tb2:
mysql> show create table tb2\G *************************** 1. row *************************** Table: tb2Create Table: CREATE TABLE `tb2` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` int(11) DEFAULT NULL, `c3` int(11) GENERATED ALWAYS AS ((`c1` + `c2`)) VIRTUAL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)mysql>
插入数据看效果:
mysql> select * from tb2;Empty set (0.00 sec)mysql> insert into tb2(c1,c2) values (1,2);Query OK, 1 row affected (0.00 sec)mysql> select * from tb2;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 1 | 2 | 3 |+----+------+------+------+1 row in set (0.00 sec)mysql>
更新数据看效果:
mysql> update tb2 set c1=10;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from tb2;+----+------+------+------+| id | c1 | c2 | c3 |+----+------+------+------+| 1 | 10 | 2 | 12 |+----+------+------+------+1 row in set (0.00 sec)mysql>
可以看到相当的方便,MySQL 5.7的很多新特性都给我们带来了便利和惊喜^.^
总结:
一、MySQL 5.7初始方法相比之前的版本都有所不同,做的相对更为安全了,以前版本初始化完root密码是空的,MySQL 5.7则有一个临时密码。
二、MySQL 5.7在CREAE TABLE和ALTER TABLE 中都支持增加计算列的方式了,所有可以少用触发器和视图来实现计算列了。
三、MySQL 5.7无论在性能还是安全性方面都得到了很大的提升,希望小伙伴们一起好好学习MySQL 5.7的一些新特性。
参考资料:
http://dev.mysql.com/doc/refman/5.7/en/binary-installation.html
https://dev.mysql.com/doc/refman/5.7/en/password-expiration-policy.html
http://dev.mysql.com/doc/refman/5.7/en/create-table.html
作者:陆炫志
出处:xuanzhi的博客 http://www.cnblogs.com/xuanzhi201111
您的支持是对博主最大的鼓励,感谢您的认真阅读。本文版权归作者所有,欢迎转载,但请保留该声明。
- mysql5.7的新特性
- mysql5.7的新特性
- MySQL5.7的新特性
- Mysql5.7新特性
- mysql5.7新特性
- mysql5.7新特性
- mysql5.7新特性
- mysql5.7 新特性
- MySQL5.7新特性
- mysql5.7新特性
- mysql5.7新特性
- MySQL5.7 新特性
- MySQL5.7版本新特性
- mysql5.7新特性探究
- mysql5.7新特性解析
- mysql5.7 innodb新特性
- MySQL5.7新特性:innodb-buffer-pool-size新特性
- mysql5.5和mysql5.6的新特性
- GuessingGame
- linux sed命令
- MapReduce简单入门 wordcount
- 6-7 另类循环队列(20 分)
- Linux中的软链接和硬链接区别
- MySQL5.7的新特性
- Linux为什么不能对目录进行硬链接?
- MFC中使用CPropertySheet实现Tab Control
- Linux中文件类型
- 求一定范围内的素数
- liaoxuefeng-js标记
- 网络基本功(八):细说TCP滑动窗口
- 关于Java的Hash算法的深入理解
- Hibernate总结