Mysql 5.7.9 安装错误小记

二进制安装，前面都一样，过程就不写了

 mysql_install_db --defaults-file=/data/mysqldata/3306/my.cnf --datadir=/data/mysqldata/3306/data --basedir=/usr/local/mysql --user=mysql2015-12-04 21:18:14 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize2015-12-04 21:18:14 [ERROR]   Child process: /usr/local/mysql/bin/mysqldterminated prematurely with errno= 322015-12-04 21:18:14 [ERROR]   Failed to execute /usr/local/mysql/bin/mysqld --defaults-file=/data/mysqldata/3306/my.cnf --bootstrap --datadir=/data/mysqldata/3306/data --lc-messages-dir=/usr/local/mysql/share --lc-messages=en_US --basedir=/usr/local/mysql-- server log begin --/usr/local/mysql/bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

mysql_install_db 被废弃了，取而代之的是 mysqld –initialize

报错还提示找不到libaio.so.1

error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

>官网文档中： Warning     MySQL has a dependency on the libaio library.      Data directory initialization and subsequent server startup steps      will fail if this library is not installed locally. If necessary,      install it using the appropriate package manager. For example, on      Yum-based systems:    >shell> yum search libaio  # search for infoshell> yum install libaio # install library     Or, on APT-based systems:    >shell> apt-cache search libaio # search for infoshell> apt-get install libaio1 # install library>Mysql 对 libaio library有依赖关系。安装好libaio后

root@Fan:/data# /usr/local/mysql/bin/mysqld –initialize –defaults-file=/data/mysqldata/3306/my.cnf –datadir=/data/mysqldata/3306/data –basedir=/usr/local/mysql –user=mysql
2015-12-04T13:22:01.848446Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details).
2015-12-04T13:22:03.639997Z 0 [Warning] InnoDB: New log files created, LSN=45790
2015-12-04T13:22:04.002719Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2015-12-04T13:22:04.167369Z 0 [ERROR] unknown variable ‘defaults-file=/data/mysqldata/3306/my.cnf’
2015-12-04T13:22:04.167408Z 0 [ERROR] Aborting

unknown variable ‘defaults-file=/data/mysqldata/3306/my.cnf’
o initialize the data directory, invoke mysqld with the–initialize or –initialize-insecure option, depending on whether you want the server to generate a random initial password for the ‘root’@’localhost’ account.

       On Unix and Unix-like systems, it is important to make sure that the database directories and files are owned by themysql login account so that the server has  read and write access to them when you run it later. To ensure  this if you run mysqld as root, include the--user option as shown here:      重要的是要确保数据库的目录和文件都属于MySQL登录账号，使服务器对他们拥有读写权限。如果你通过root用户运行mysqld, 带上--user参数shell> bin/mysqld --initialize --user=mysqlshell> bin/mysqld --initialize-insecure --user=mysqlRegardless of platform, use--initialize for “secure by default” installation (that is, including generation of a random initialrootpassword). In this case, the password is marked as expired and you will need to choose a new one. With the--initialize-insecure option, noroot password is generated; it is assumed that you will assign a password to the account in timely fashion  before putting the server into production use.       --initialize 与 --initialize-insecure不同的是，他会创建一个随机的root密码Then invoke mysqld as follows (enter the command on a single line with the--defaults-file option first):      shell> bin/mysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf         --initialize --user=mysql--defaults-file选项应在最前继续执行，又报错。重要的是You have enabled the binary log, but you haven't provided the mandatory server-id. Please refer to the proper server start-up parameters documentation启用了binlog，但是没有设置server-id，于是我在my.cnf中添加server-id

root@Fan:/data/mysqldata/3306# /usr/local/mysql/bin/mysqld –defaults-file=/data/mysqldata/3306/my.cnf –initialize-insecure –user=mysql
2015-12-04T13:27:38.504948Z 0 [Warning] ‘NO_ZERO_DATE’, ‘NO_ZERO_IN_DATE’ and ‘ERROR_FOR_DIVISION_BY_ZERO’ sql modes should be used with strict mode. They will be merged with strict mode in a future release.
2015-12-04T13:27:38.504999Z 0 [Warning] ‘NO_AUTO_CREATE_USER’ sql mode was not set.
2015-12-04T13:27:38.508994Z 0 [ERROR] You have enabled the binary log, but you haven’t provided the mandatory server-id. Please refer to the proper server start-up parameters documentation
2015-12-04T13:27:38.509070Z 0 [ERROR] Aborting
root@Fan:/data/mysqldata/3306# /usr/local/mysql/bin/mysqld –defaults-file=/data/mysqldata/3306/my.cnf –initialize-insecure –user=mysql

成功了，但是没有任何提示，很奇怪尝试启动mysql，成功mysql@Fan:~$ mysqld_safe --defaults-file=/data/mysqldata/3306/my.cnf &[1] 28843mysql@Fan:~$ 151204 21:32:05 mysqld_safe Logging to '/data/mysqldata/3306/data/../mysql-error.log'.151204 21:32:05 mysqld_safe Starting mysqld daemon with databases from /data/mysqldata/3306/datamysql@Fan:/data/mysqldata/3306$ mysql -uroot -p -S /data/mysqldata/3306/mysql.sock Enter password:   ---我并未输入密码，而是直接回车，因为之前用的参数是--initialize-insecureWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 4Server version: 5.7.9-log MySQL Community Server (GPL)Copyright (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> mysql> mysql> select user,host from mysql.user;+-----------+-----------+| user      | host      |+-----------+-----------+| mysql.sys | localhost || root      | localhost |+-----------+-----------+2 rows in set (0.00 sec)改密码

mysql@Fan:/data/mysqldata/3306$ mysqladmin -S /data/mysqldata/3306/mysql.sock -uroot -p password mysql
Enter password:
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

Mysql.sys用户

MySQL 5.7.7 and higher includes the sys schema, a set of objects that helps DBAs and developers interpret data collected by the Performance Schema. sys schema objects can be used for typical tuning and diagnosis use cases. Objects in this schema include:

Views that summarize Performance Schema data into more easily understandable form.Stored procedures that perform operations such as Performance Schema configuration and generating diagnostic reports.Stored functions that query Performance Schema configuration and provide formatting services. 

For new installations, the sys schema is installed by default during data directory initialization if you use mysqld with the –initialize or –initialize-insecure option, or if you use mysql_install_db. To permit this behavior to be suppressed, mysql_install_db has a –skip-sys-schema option. mysqld has no such option, but if you initialize the data directory using mysqld –initialize (or –initialize-insecure) rather than mysql_install_db, you can drop the sys schema manually after initialization if it is unneeded.

For upgrades, mysql_upgrade installs the sys schema if it is not installed, and upgrades it to the current version otherwise. To permit this behavior to be suppressed, mysql_upgrade has a –skip-sys-schema option.

mysql_upgrade returns an error if a sys schema exists but has no version view, on the assumption that absence of this view indicates a user-created sys schema. To upgrade in this case, remove or rename the existing sys schema first.

As of MySQL 5.7.9, sys schema objects have a DEFINER of ‘mysql.sys’@’localhost’. (Before MySQL 5.7.9, the DEFINER is ‘root’@’localhost’.) Use of the dedicated mysql.sys account avoids problems that occur if a DBA renames or removes the root account.

想要修改mysql.sys用户密码，直接修改mysql.user表，但是mysql.user表已经没有passowrd列了mysql> update mysql.user set password=password('mysql') where user='mysql.user';ERROR 1054 (42S22): Unknown column 'password' in 'field list'mysql> desc mysql.user    -> ;+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Field                  | Type                              | Null | Key | Default               | Extra |+------------------------+-----------------------------------+------+-----+-----------------------+-------+| Host                   | char(60)                          | NO   | PRI |                       |       || User                   | char(32)                          | NO   | PRI |                       |       || Select_priv            | enum('N','Y')                     | NO   |     | N                     |       || Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       || Update_priv            | enum('N','Y')                     | NO   |     | N                     |       || Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       || Create_priv            | enum('N','Y')                     | NO   |     | N                     |       || Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       || Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       || Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       || Process_priv           | enum('N','Y')                     | NO   |     | N                     |       || File_priv              | enum('N','Y')                     | NO   |     | N                     |       || Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       || References_priv        | enum('N','Y')                     | NO   |     | N                     |       || Index_priv             | enum('N','Y')                     | NO   |     | N                     |       || Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       || Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       || Super_priv             | enum('N','Y')                     | NO   |     | N                     |       || Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       || Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       || Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       || Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       || Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       || Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       || Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       || Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       || Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       || Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       || Event_priv             | enum('N','Y')                     | NO   |     | N                     |       || Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       || Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       || ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       || ssl_cipher             | blob                              | NO   |     | NULL                  |       || x509_issuer            | blob                              | NO   |     | NULL                  |       || x509_subject           | blob                              | NO   |     | NULL                  |       || max_questions          | int(11) unsigned                  | NO   |     | 0                     |       || max_updates            | int(11) unsigned                  | NO   |     | 0                     |       || max_connections        | int(11) unsigned                  | NO   |     | 0                     |       || max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       || plugin                 | char(64)                          | NO   |     | mysql_native_password |       || authentication_string  | text                              | YES  |     | NULL                  |       || password_expired       | enum('N','Y')                     | NO   |     | N                     |       || password_last_changed  | timestamp                         | YES  |     | NULL                  |       || password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       || account_locked         | enum('N','Y')                     | NO   |     | N                     |       |+------------------------+-----------------------------------+------+-----+-----------------------+-------+45 rows in set (0.00 sec)mysql> set password for 'mysql.sys'@'localhost'=password('mysql');

Query OK, 0 rows affected, 1 warning (0.03 sec)

修改后提示 1 warnings，查看

mysql> show warnings;+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level   | Code | Message                                                                                                                                                                                 |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1287 | 'SET PASSWORD FOR <user> = PASSWORD('<plaintext_password>')' is deprecated and will be removed in a future release. Please use SET PASSWORD FOR <user> = '<plaintext_password>' instead |+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

得知set password for ‘mysql.sys’@’localhost’=password(‘mysql’)已经废弃，使用 SET PASSWORD FOR = ‘’ instead 使用明文替代
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)
尝试用mysql.sys登陆

mysql@Fan:/data/mysqldata/3306$ mysql -S /data/mysqldata/3306/mysql.sock -umysql.sys -pEnter password: ERROR 3118 (HY000): Access denied for user 'mysql.sys'@'localhost'. Account is locked.提示account locked

解锁

mysql@Fan:/data/mysqldata/3306$ mysql -S /data/mysqldata/3306/mysql.sock -uroot -pEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 5.7.9-log MySQL Community Server (GPL)Copyright (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> alter user 'mysql.sys'@'localhost' account unlock;Query OK, 0 rows affected (0.04 sec)mysql> flush privileges;Query OK, 0 rows affected (0.06 sec)mysql> quit