MySQL

From miki
Revision as of 07:22, 24 June 2015 by Mip (talk | contribs) (→‎Passwords)
Jump to navigation Jump to search

References

Passwords

Use option file for password

Instead of giving password on the command-line with option -p PWD, a safer method is to use a password file:

  • Create a file ~/.my.cnf:
[mysl]
user=root
password="PASSWORD"
This file must contain an entry for all tools used. For instance:
[client]
user=root
password="PASSWORD"

[mysql]
user=root
password="PASSWORD"

[mysqldump]
user=root
password="PASSWORD"

[mysqldiff]
user=root
password="PASSWORD"
  • Change file permission:
 chmod 600 ~/.my.cnf
  • Now we can connect without exposing the password:
 mysql -u root

Change

Using mysqladmin:

unset HISTFILE                                       # <-- DO NOT FORGET IT, OR PWD WILL APPEAR IN ~/.bash_history
mysqladmin -u USERNAME password NEWPWD               # Assumes no password set - use user=root for admin pwd
mysqladmin -u USERNAME -p'OLDPWD' password NEWPWD

If you forget to unset HISTFILE, delete your history file immediately:

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 service mysql stop                       # Stop MySQL server
sudo mysqld_safe --skip-grant-tables &        # Restart it with option not to ask for passwords
sudo su
MYSQL_HISTFILE=/dev/null mysql -u root mysql  # Connect to MySQL, table mysql

Apply the MySQL script above:

update user set password=PASSWORD("NEWPWD") where User='root';
flush privileges;
quit

Then restart the server:

sudo /etc/init.d/mysql stop
sudo service mysql start