向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除|赢在IT，Playin' with IT,Focus on Killer Application,Marketing Meets Technology.

背景：最近研究一下rpmbuild打的mysql最新包出现初始化mysql-5.7.12时的数据库错误，[ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'，再涉及到mysql的密码问题居然放error.log里了。
1）init database and set password：
[root@localhost bin]#./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
2016-04-21T03:26:22.900370Z 0 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
2016-04-21T03:26:22.900452Z 0 [ERROR] Aborting
2)try directly start mysql server display:
[root@localhost bin]# ./mysqld_safe  
./mysqld_safe: line 541: /data/mysql/mysqld_safe.pid: No such file or directory
awk: (FILENAME=- FNR=1) warning: error writing standard output (Broken pipe)
2016-04-21T03:26:28.773480Z mysqld_safe Logging to '/data/logs/mysql/error.log'.
2016-04-21T03:26:28.814486Z mysqld_safe Starting mysqld daemon with databases from /data/mysql
2016-04-21T03:26:28.957913Z mysqld_safe mysqld from pid file /data/mysql/mysql.pid ended

so,tail -f ,tail -f /data/logs/mysql/error.log
2016-04-21T03:26:45.171898Z mysqld_safe Starting mysqld daemon with databases from /data/mysql 2016-04-21T03:26:45.303137Z 0 [Warning] Insecure configuration for --secure-file-priv: Current value does not restrict location of generated files. Consider setting it to a valid, non-empty path. 2016-04-21T03:26:45.303235Z 0 [Note] /usr/local/mysql/bin/mysqld (mysqld 5.7.12-log) starting as process 26969 ... 2016-04-21T03:26:45.305219Z 0 [ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

乍一看，是字符编码问题，着实在my.cnf配置文件上面设置了字符编码如下：      
[client] default-character-set = utf8 [mysqld] character_set_server = utf8

再启动，出现新的报错：
2016-04-21T03:34:25.386145Z 0 [ERROR] failed to set datadir to /data/mysql/ 2016-04-21T03:34:25.386164Z 0 [ERROR] Aborting 2016-04-21T03:34:25.386182Z 0 [Note] Binlog end 2016-04-21T03:34:25.386370Z 0 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete
可能是目录的权限问题，继续在rpmbuild里写上权限....
chmod -R mysql.mysql /data/logs/mysql /data/mysql

[root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql  
[root@localhost bin]#

日志跟踪，敬告没有Error：
2016-04-21T03:45:21.971611Z 1 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2016-04-21T03:45:21.971676Z 1 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2016-04-21T03:45:21.971712Z 1 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2016-04-21T03:45:21.971745Z 1 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2016-04-21T03:45:21.972041Z 1 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.


立即启动mysqld_safe：
[root@localhost bin]# ./mysqld_safe
2016-04-21T03:47:08.025776Z mysqld_safe Logging to '/data/logs/mysql/error.log'.
2016-04-21T03:47:08.075873Z mysqld_safe Starting mysqld daemon with databases from /data/mysql

tail -f /data/logs/mysql/error.log 端口成功启动：
2016-04-21T03:47:08.542210Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.12-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution


[root@localhost bin]# ./mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

重新初始化mysql用户名及密码：
[root@localhost bin]# ./bin/mysql_install_db --basedir=. --datadir=/data/mysql --user=mysql
2016-04-21 13:18:24 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize
2016-04-21 13:18:24 [ERROR]   The data directory needs to be specified.
之前版本mysql_install_db是在mysql_basedir/script下，5.7放在了mysql_install_db/bin目录下,且已被废弃。

shell>./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql
******mysql5.7会生成一个初始化密码，而在之前的版本首次登陆不需要登录。(我前面用的是root启动的)：
[root@localhost bin]# ./mysqld_safe --user=mysql    
2016-04-21T05:24:34.317097Z mysqld_safe Logging to '/data/logs/mysql/error.log'.
2016-04-21T05:24:34.463814Z mysqld_safe Starting mysqld daemon with databases from /data/mysql
shell> cat /root/.mysql_secret # Password set for user 'root@localhost' at 2015-04-22 22:13:23 ?G5W&tz1z.cN 修改默认密码 shell> bin/mysqladmin -h localhost -u root password '123@qwe' -p'?G5W&tz1z.cN' 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. 另外mysql5.7的mysql.user表结构被修改 修改mysql.user方式修改密码 mysql> update mysql.user set authentication_string=password('123qwe') where user='svoid' and Host = 'localhost'; Query OK, 1 row affected (0.01 sec) Rows matched: 1  Changed: 1  Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) 整理自网络
这帮孙子现在是在抽风么，网上查到这密码放哪儿是一个折腾呐，那最新版本的放哪儿了？
MySQL 5.6 中，mysql_install_db 在数据库创建的时候提供选项来生成 random password。
MySQL 5.7.4 中，可以跳过 -skip-random-password 选项来默认生成随机密码。
MySQL 5.7.5 中，还是默认生成随机密码，但是选项修改为 –insecure

摸索下看：
[root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql  --skip-random-password   //不认--skip-random-password, unknown option '--skip-random-password'.   2016-04-21T05:30:11.223901Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting. [root@localhost bin]# rm -Rf /data/mysql/* [root@localhost bin]# ./mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

tail日志时发现，mysql-5.7.12最新版本的密码放在error.log里面了，我去：
[root@localhost bin]# tail -f /data/logs/mysql/error.log ...... 2016-04-21T05:33:28.538633Z 1 [Note] A temporary password is generated for root@localhost: 7EEgNltAA1;/ ......

[root@localhost bin]# ./mysqld_safe --user=mysql &
[1] 18730

2016-04-21T05:36:56.824891Z 0 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.7.12-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

连接成功,这个新版本的msyql还真TM费劲呐：
[root@localhost ~]# mysql -uroot -p
Enter password: 7EEgNltAA1;/
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-log

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

强制要求你修改密码：
mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql> SET PASSWORD = PASSWORD('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create database jackTestDB;
Query OK, 1 row affected (0.00 sec)


不要密码初始化数据库的参数，参考自：https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization-mysqld.html
rm -Rf /data/mysql  //原来生成的给去掉 [root@localhost bin]# ./mysqld --initialize-insecure  --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql [root@localhost bin]#  ./mysqld_safe --user=mysql &
[1] 59478
无密码连接一下看：
[root@localhost bin]# ./mysql -uroot   //果然不需要密码就进去了
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.12-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> \q

直接mysql也成：[root@localhost x86_64]# mysql
进入后密码：SET PASSWORD = PASSWORD('123456');

[root@localhost bin]# ./mysql -uroot
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql> SET PASSWORD = PASSWORD('******');
Query OK, 0 rows affected, 1 warning (0.00 sec)
从另一个终端登录看下，不行了，说明直接就修改了，也就是说不用flushprivilege：
[root@localhost bin]# ./mysql -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)


首次参考：http://www.bubuko.com/infodetail-1173208.html

作者：jackxiang@向东博客 专注WEB应用 构架之美 --- 构架之美，在于尽态极妍 | 应用之美，在于药到病除
地址：https://jackxiang.com/post/8651/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明！