how to change / fix the root password in MySQL
First: If you install MySQL through an X-window GUI application, like System >> Administration >> Add-Remove Software, then, it will install without prompting, and the one mysql user that is created, “root” will have a blank password. From a terminal command line, type
[you@localhost ~]$ mysql -u root -p
Enter password: [just hit enter since it is a blank password, or, of course enter your password]
then, type
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
On the other hand, if you don’t know the password and need to re-set it
If you have root authority on your server, login as root (su -) in a terminal session and at a command prompt, stop mysql and then restart it in a controlled mode:
root:/]# /etc/init.d/mysql stop
or service mysql stop
then /usr/sbin/mysqld --skip-grant-tables --skip-networking &
–skip-grant-tables is the key. It keeps mysql from checking your authority to login to it. now, you can login without the password and set it to something you know:
root:/]# mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('new-password') WHERE User='root';
mysql> select * from mysql.user; // if you want to see it
mysql> exit
root:/]# /etc/init.d/mysql stop
root:/]# /etc/init.d/mysql start // normal restart
Now that it is back to running normally, a person can go in and see the list of databases, add a database, see the list of character set definitions, etc.
root:/]# mysql -u root -p
mysql> show databases;
mysql> create database dbname;
mysql> show variables like 'char%';
Because of the international presence of the Internet, and the need to standardize, utf-8 is becoming universal:
“Having UTF-8 characters in Latin-1 encoded tables means you can potentially create mysqldumps of your data which are not restore-able. This can happen transparently – mysqldump will create the dump with no errors or warnings, but you won’t know that the dump has syntax errors in it until you attempt to do a restore. (And this is usually exactly the time you don’t want to find out that your backups have serious problems.)”
Background situation: Someone had installed mysql on an old server (now running Ubuntu 10.4) where the only access I had was to remotely login as a user or as root. PhpMyAdmin was not available, I could not even see the site; it was not available/visible on the internet. I was the one to install Apache2 and php on it. I tried to install mysql only to discover that it was already installed – it was being used by some installed software (“BigBlueButton”). I installed Apache and php and pointed Apache to the folder where I wanted to put a backup copy of a production web site.