mysqld_safe 在哪个位置 mysqld和mysqld_safe

转载

网络安全守护神 2024-08-23 10:35:10

文章标签 mysqld_safe 在哪个位置数据库 mysql MySQL 文章分类 MySQL 数据库

mysqld_safe模式修改root密码(>=5.7.6)

错误示例：

# mysqladmin -u root -p password 'newpassword'
 Enter password:
 mysqladmin: connect to server at 'localhost' failed
 error: 'Access denied for user 'root'@'localhost' (using password: YES)'

MySQL版本>=5.7.6

注意：mysql-5.7.6及更新的版本， mysql.user表已经用authentication_string替代了原来的Password字段，所以网上的更新方法可能还更新不及时

以OS X10.11.4, mysql-5.7.9为例

http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

http://dev.mysql.com/doc/refman/5.7/en/assigning-passwords.html

1.安装

brew install mysql
ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents/

2.创建init-file文件

MySQL 5.7.6 and later:
1. ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';  MySQL 5.7.5 and earlier: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

根据mysql版本不同init-file的内容也不能，如，我的是5.7.6，所以用ALTER方式

"ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';" >~/mysql-init

3.安全模式启动mysql

jlive@MacBook-Pro:~ $mysqld_safe --init-file=~/mysql-init &

如果没有问题，mysql root密码已经更新，只需要重启mysql即可正常登录

说明：就我个人测试下来，没有效果，怎么办？

大招，直接修改mysql.user表，屡试不爽

jlive@MacBook-Pro:~ $mysqld_safe --user=mysql  --skip-grant-tables  --skip-networking &
jlive@MacBook-Pro:~
 Enter password:   #因为--skip-grant-tables直接忽略了权限验证，输入密码时直接回车即可
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 2

5.7.9

Copyright (c) 2000, 2015, 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.

USE mysql;

Reading table information for completion of table and column names
 You can turn off this feature to get a quicker startup with -A
 
  
 Database changed
SELECT User,Host,authentication_string FROM user;
 +-----------+-----------+-------------------------------------------+
authentication_string
 +-----------+-----------+-------------------------------------------+
 | root      | localhost | *CE0195981C35A19371383BF832EA27F7D0732D38 |
 | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
 +-----------+-----------+-------------------------------------------+
 2 rows in set (0.00 sec)
 
UPDATE mysql.user SET authentication_string=Password('root')
 Query OK, 0 rows affected, 1 warning (0.01 sec)
 Rows matched: 0  Changed: 0  Warnings: 1
 
FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec)
 
QUIT;
  
 Bye
 
jlive@MacBook-Pro:~ $ killall mysqld
jlive@MacBook-Pro:~ $ launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

MySQl版本<=5.7.5

mysql官方文档：

http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

http://dev.mysql.com/doc/refman/5.5/en/assigning-passwords.html

1.停掉mysql

# /etc/init.d/mysqld stop

2. 安全模式启动mysql

让mysql在安全模式下以mysql的身份运行，因为在/etc/my.cnf的配置文件中默认就定义了myql这个用户

# mysqld_safe --user=mysql  --skip-grant-tables  --skip-networking & 
--skip-grant-tables #略过mysql 权限审查
--skip-networking #禁止外来网络访问

3.root身份登录mysql

以root身份直接登录到控制mysql-server服务器投入使用后的用户,主机,DB权限等的默认数据库mysql中,更新root密码后退出

# mysql -u root mysql
 mysql>UPDATE mysql.user SET Password=PASSWORD('MyNewPass')mysql> FLUSH PRIVILEGES;
mysql> QUIT;
 Write the UPDATE and FLUSH statements each on a single line. The UPDATE statement resets the password for all root accounts, and the FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.

4.重启mysql

就可以用刚才修改好密码登录了

# /etc/init.d/mysqld  restart
 # mysql  -u  root  -p
 Enter password: <输入新设的密码newpassword>

对于全新安装的mysql, 如果还是报权限拒绝的话，可以把/var/lib/mysql/mysql底下的初始数据库文件给全删了，再执行mysql_install_db重新初始化数据库，再

mysqladmin -u root -p password 'newpassword'

附：正常修改密码的常用语句　

通过直接操作mysql.user表里的记录来新增或修改用户密码及权限

mysql> INSERT INTO mysql.user (Host,User,Password) VALUES('%','system', PASSWORD('manager'));
 mysql> FLUSH PRIVILEGES 　　

 mysql> REPLACE INTO mysql.user (Host,User,Password)
 VALUES('%','system',PASSWORD('manager'));
 mysql> FLUSH PRIVILEGES 　
 　　
 mysql> SET PASSWORD FOR system@"%" = PASSWORD('manager');

你也必须使用PASSWORD()函数，但是不需要使用FLUSH PRIVILEGES来执行确认。　　
　　
mysql> GRANT USAGE ON *.* TO system@"%" IDENTIFIED BY 'manager'; 　　
这里PASSWORD()函数是不必要的，也不需要使用FLUSH PRIVILEGES来执行确认。　　
注：PASSWORD()函数作用是为口令字加密，在程序中MySQL自动解释　

5.7.6及以后的版本又增加了一种修改密码的方式

jlive@MacBook-Pro:~
 Enter password: 
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 4
5.7.9
 
 Copyright (c) 2000, 2015, 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.
 
ALTER USER 'root'@'localhost' IDENTIFIED BY 'foo123';
 Query OK, 0 rows affected (0.00 sec)
 
FLUSH PRIVILEGES;
 Query OK, 0 rows affected (0.00 sec)
 
QUIT;
 Bye

常用授权语句

mysql>GRANT ALL PRIVILEGES ON *.* TO system@localhost IDENTIFIED BY
 'manager' WITH GRANT OPTION; 　　
或mysql>INSERT INTO user VALUES('localhost','system',PASSWORD('manager'),
  'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

对于3.22.34版本的MySQL,这里共14个"Y",其相应的权限如下(按字段顺序排列): 　　

--------------------------------------------------------------------------------------

权限表列名称相应解释使用范围

--------------------------------------------------------------------------------------

select Select_priv 只有在真正从一个表中检索时才需要select权限表

insert Insert_priv 允许您把新行插入到一个存在的表中表　

update Update_priv 允许你用新值更新现存表中行的列表　

delete Delete_priv 允许你删除满足条件的行表　　

create Create_priv 允许你创建新的数据库和表数据库、表或索引　　

drop Drop_priv 抛弃(删除)现存的数据库和表数据库或表

reload Reload_priv 允许您告诉服务器再读入授权表服务器管理

shutdown Shutdown_priv 可能被滥用(通过终止服务器拒绝为其他用户服务) 服务器管理　