[实践OK]最新mysql,[ERROR] COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1',及mysql-5.7.12的初始化密码放error.log了。 不指定

jackxiang 2016-4-21 11:41 | |
背景:最近研究一下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


乍一看,是字符编码问题,着实在my.cnf配置文件上面设置了字符编码如下:      


再启动,出现新的报错:

可能是目录的权限问题,继续在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

这帮孙子现在是在抽风么,网上查到这密码放哪儿是一个折腾呐,那最新版本的放哪儿了?
MySQL 5.6 中,mysql_install_db 在数据库创建的时候提供选项来生成 random password。
MySQL 5.7.4 中,可以跳过 -skip-random-password 选项来默认生成随机密码。
MySQL 5.7.5 中,还是默认生成随机密码,但是选项修改为 –insecure

摸索下看:


tail日志时发现,mysql-5.7.12最新版本的密码放在error.log里面了,我去:


[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

[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应用 构架之美 --- 构架之美,在于尽态极妍 | 应用之美,在于药到病除
地址:http://jackxiang.com/post/8651/
版权所有。转载时必须以链接形式注明作者和原始出处及本声明!


最后编辑: jackxiang 编辑于2016-4-22 11:51
评论列表
2016-4-29 16:39 | cl
你好请问 这个data/logs/mysql/error.log 我找不到 我是mac系统 博主是怎么找到的
分页: 1/1 第一页 1 最后页
发表评论

昵称

网址

电邮

打开HTML 打开UBB 打开表情 隐藏 记住我 [登入] [注册]