MySQL includes all the basic methods needed to secure your user accounts. However, the syntax and style used to manage it is often confusing to MySQL novices. Here are a couple tips to get you started, however I strongly suggest your read the MySQL User Account Management documentation.
If you need to change your password for your localhost root account, here is the proper syntax.
mysql -u root
mysql> SET PASSWORD FOR 'ROOT'@'LOCALHOST' = PASSWORD('new_password');
You can also grant access to all remote servers as well by using the following syntax.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
By default, MySQL only allows access by the ‘root’ account from the host running the database server (‘localhost’).
The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost. You need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.
If you are setting up your server and require NO remote access, add skip-networking to your my.cnf file.
skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should be removed from my.cnf or put it in comment state.
