MySQL tips on Linux

Reseting Mysql root password

Have you ever forgotten the root password on one of your MySQL servers? No? Well maybe I’m not as perfect as you. This is a quick h00tow (how to) reset your MySQL root password. It does require root access on your server. If you have forgotten that password wait for another article.

First things first. Log in as root and stop the mysql daemon. Now lets start up the mysql daemon and skip the grant tables which store the passwords.

mysqld_safe --skip-grant-tables

You should see mysqld start up successfully. If not, well you have bigger issues. Now you should be able to connect to mysql without a password.

mysql --user=root mysql
update user set Password=PASSWORD('new-password') where user='root';
flush privileges;
exit

Now kill your running mysqld, then restart it normally. You should be good to go. Try not to forget your password again.

Allow remote access to mysql database

In most cases you need to manage your mysql database by remote using SQLYog or any third party software to make easy database management, instead of using the pure text command in linux, now here is procedure to allow remote access to your mysql on linux server

1. login to your linux shell with root access and on the the shell command type this command

$ mysql --user=your-user-name --password=your-password 
mysql> GRANT ALL ON foo.* TO bar@'IP' IDENTIFIED BY 'PASSWORD';

where foo is your database and IP is the ip address of your computer

Here is a sample iptables rule to open Linux iptables firewall

# /sbin/iptables -A INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT
# service iptables save

some references link can also be browse here