MySQL: Difference between revisions

From miki
Jump to navigation Jump to search
 
(9 intermediate revisions by the same user not shown)
Line 3: Line 3:
* http://www.cyberciti.biz/tips/recover-mysql-root-password.html
* http://www.cyberciti.biz/tips/recover-mysql-root-password.html


== Passwords ==
== Install & Secure ==
After install, run <code>mysql_secure_installation</code> to tighten the installation:
{{red|!!! SECURITY HOLE !!!}} &mdash; 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):
<source lang="bash">
<source lang="bash">
/usr/bin/mysql_secure_installation
export MYSQL_HISTFILE=/dev/null
</source>

== Passwords ==

=== Use option file for password ===
Instead of giving password on the command-line with option <code>-p PWD</code>, a safer method is to use a password file:
* Create a file {{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:
<source lang=bash>
chmod 600 ~/.my.cnf
</source>
* Now we can connect without exposing the password:
<source lang=bash>
mysql -u root
</source>
</source>


Line 13: Line 46:
Using '''mysqladmin''':
Using '''mysqladmin''':
<source lang="bash">
<source lang="bash">
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 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
</source>
</source>
If you forget to unset <tt>HISTFILE</tt>, {{red|'''delete your history file immediately'''}}:
<source lang="bash">
rm ~/.bash_history
</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 33: Line 71:


<source lang="bash">
<source lang="bash">
sudo /etc/init.d/mysql stop # Stop MySQL server
sudo service 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 su
sudo mysql -u root # Connect to MySQL
MYSQL_HISTFILE=/dev/null mysql -u root mysql # Connect to MySQL, table mysql
</source>
</source>
Apply the MySQL script above, and restart the server:
Apply the MySQL script above:
<source lang="mysql">
update user set password=PASSWORD("NEWPWD") where User='root';
flush privileges;
quit
</source>
Then restart the server:
<source lang="bash">
<source lang="bash">
sudo /etc/init.d/mysql restart
sudo /etc/init.d/mysql stop
sudo service mysql start
</source>

== Backup / Restore ==
To backup / restore all mysql database over the network [https://www.marcus-povey.co.uk/2013/03/25/moving-a-mysql-database-between-servers-using-a-single-ssh-command/]:
<source lang=bash>
unset HISTFILE # Or use option file to avoid exposing the password
mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'
</source>
</source>

Latest revision as of 06:49, 21 August 2017

References

Install & Secure

After install, run mysql_secure_installation to tighten the installation: 

/usr/bin/mysql_secure_installation

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

Backup / Restore

To backup / restore all mysql database over the network [1]: 

unset HISTFILE                         # Or use option file to avoid exposing the password
mysqldump -u root -pPASSWORD --all-databases | ssh USER@NEW.HOST.COM 'cat - | mysql -u root -pPASSWORD'
Retrieved from "https://miki.immie.org/mediawiki/index.php?title=MySQL&oldid=9640"