Continuing with the MySQL mini posts I ran into this error today while setting up our redundancy servers. I moved the mysql directory out of it’s default location of /var/lib. So when I went to start Mysql it would fail. When I looked in the /var/log/mysqld.log I seen the following:
Fatal error: Can’t open and lock privilege tables: Table ‘mysql.host’ doesn’t exist
To fix this issue you simply just have to tell mysql where to look not that the default installation is moved. You can do that with:
mysql_install_db –user=mysql –ldata=/newlocation
once you do that you can restart mysql
and that is all.
The first thing one should do after starting MySQL is to set the root pass which by default is set to null. Leaving MySQL as Null is not such a good idea So here is how it can easily be done from a terminal.
Like I said above if you are starting up MySQL for the first time the root pass is set to null so you can run the command below:
mysqladmin -u root password YOURPASS
If you you just want to change or update the password then you can use
mysqladmin -u root -p’oldpass’ password ‘NEWPASS’
Note: Dont forget the single quotes ‘ ‘
If you want to change another users password you would just replace root with the user you want to change the password for.
mysqladmin -u lewis -p ‘oldpass’ password NEWPASS
This is just one way to change the password of users in mysql. You can also log into mysql with a user with super user privileges such as root and change them but this way is much easier.