MySQL: Difference between revisions
Jump to navigation
Jump to search
(→Change) |
|||
| Line 4: | Line 4: | ||
== Passwords == |
== Passwords == |
||
{{red|!!! SECURITY HOLE !!!}} — don't forget to '''DISABLE THE HISTORY FILE''' prior running the commands below or else all passwords will be output to <tt>~/.mysql_history</tt> (note that it is chmod 600 however): |
|||
| ⚫ | |||
export MYSQL_HISTFILE=/dev/null |
|||
| ⚫ | |||
=== Change === |
=== Change === |
||
| Line 15: | Line 11: | ||
mysqladmin -u USERNAME password NEWPWD # Assumes no password set - use user=root for admin pwd |
mysqladmin -u USERNAME password NEWPWD # Assumes no password set - use user=root for admin pwd |
||
mysqladmin -u USERNAME -p'OLDPWD' password NEWPWD |
mysqladmin -u USERNAME -p'OLDPWD' password NEWPWD |
||
| ⚫ | |||
{{red|!!! SECURITY HOLE !!!}} — don't forget to '''DELETE YOUR BASH HISTORY FILE''' !!! |
|||
| ⚫ | |||
rm ~/.bash_history |
|||
</source> |
</source> |
||
Using '''MySQL commands'''. First we connect to MySQL server: |
Using '''MySQL commands'''. First we connect to MySQL server and select table <tt>mysql</tt> (don't forget to '''DISABLE HISTORY FILE''' !!!): |
||
<source lang="bash"> |
<source lang="bash"> |
||
% mysql -u root -p |
% MYSQL_HISTFILE=/dev/null mysql -u root -p mysql |
||
</source> |
</source> |
||
Here the script: |
Here the script: |
||
<source lang="mysql"> |
<source lang="mysql"> |
||
use mysql; |
|||
update user set password=PASSWORD("NEWPWD") where User='USERNAME'; |
update user set password=PASSWORD("NEWPWD") where User='USERNAME'; |
||
flush privileges; |
flush privileges; |
||
| Line 35: | Line 35: | ||
sudo /etc/init.d/mysql stop # Stop MySQL server |
sudo /etc/init.d/mysql stop # Stop MySQL server |
||
sudo mysqld_safe --skip-grant-tables & # Restart it with option not to ask for passwords |
sudo mysqld_safe --skip-grant-tables & # Restart it with option not to ask for passwords |
||
sudo mysql -u root |
sudo mysql -u root mysql # Connect to MySQL, table mysql |
||
< |
<source> |
||
Apply the MySQL script above, and restart the server: |
Apply the MySQL script above, and restart the server: |
||
<source lang="bash"> |
<source lang="bash"> |
||
Revision as of 16:35, 22 December 2010
References
- http://www.cyberciti.biz/faq/mysql-change-root-password/
- http://www.cyberciti.biz/tips/recover-mysql-root-password.html
Passwords
Change
Using mysqladmin:
mysqladmin -u USERNAME password NEWPWD # Assumes no password set - use user=root for admin pwd
mysqladmin -u USERNAME -p'OLDPWD' password NEWPWD
!!! SECURITY HOLE !!! — don't forget to DELETE YOUR BASH HISTORY FILE !!!
rm ~/.bash_history
Using MySQL commands. First we connect to MySQL server and select table mysql (don't forget to DISABLE HISTORY FILE !!!):
% MYSQL_HISTFILE=/dev/null mysql -u root -p mysql
Here the script:
update user set password=PASSWORD("NEWPWD") where User='USERNAME';
flush privileges;
quit
Recover root password
If the MySQL root password is lost, the same script can be used to define a new password, but it requires to restart the MySQL server with option --skip-grant-tables:
sudo /etc/init.d/mysql stop # Stop MySQL server
sudo mysqld_safe --skip-grant-tables & # Restart it with option not to ask for passwords
sudo mysql -u root mysql # Connect to MySQL, table mysql
<source>
Apply the MySQL script above, and restart the server:
<source lang="bash">
sudo /etc/init.d/mysql restart