MySQL Command

Navicat and Sqlyog are two best administrator tools i have used for mysql.

# [mysql dir]/bin/mysql -h hostname -u root -p

Creating a new user. Login as root. Switch to the MySQL db. Make the user. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO user (Host,User,Password) VALUES('%','username',PASSWORD('password'));
mysql> flush privileges;

Change a users password from unix shell.

# [mysql dir]/bin/mysqladmin -u username -h hostname.blah.org -p password 'new-password'

Change a users password from MySQL prompt. Login as root. Set the password. Update privs.

# mysql -u root -p
mysql> SET PASSWORD FOR 'user'@'hostname' = PASSWORD('passwordhere');
mysql> flush privileges;

Recover a MySQL root password. Stop the MySQL server process. Start again with no grant tables. Login to MySQL as root. Set new password. Exit MySQL and restart MySQL server.

# /etc/init.d/mysql stop
# mysqld_safe --skip-grant-tables &
# mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
# /etc/init.d/mysql stop
# /etc/init.d/mysql start

Give user privilages for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

# mysql -u root -p
mysql> use mysql;
mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
mysql> flush privileges;

or

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

MySQL UDF Execution - strong password

For mysql 5.5.8 or earlier on Windows, there is such danger about "User-defined-functions" execution.

So help, there is a need to 

1. Ensure the password is strong

2. Delete the "root" account for hostname "%" (i.e. others cannot use "root" account at other host)

A good habit:

1. Create new user privilege only on specific database it belongs to.

2. Have a strong password for the user account

What is a strong password:

Hint:

• It needs to contain special characters such as @#$%^&
• It must be at least 8 characters long.
• It must not have any common words such as 123, password, your birth date, your login name and any words that can be found in the dictionary.
• a variation of capitalization and small letters

Todo:

1. A password manager is needed to remember for you.