Setting up MySQL

Here's a quick guide to getting MySQL set up for the first time.

MySQL version check

# mysqladmin -u root version

Set the root password

# mysqladmin -u root password ThisIsMyPassword

If you do not place the password on the command line, you will be prompted to enter it, which keeps it off of the shell's executed commandline. That will also hold true with the MySQL monitor mysql.

Set + check the root password

# mysql -u root
mysql> SET password=PASSWORD('ThisIsMyPassword');
mysql> SELECT user, host, password FROM mysql.user;
mysql> exit

At this point, you should take note that you can make use of the '-p' flag to be prompted for a password and '-u User' can be put on the prompt to send your password and change your login name when necessary. You do not have to be root to be in MySQL as root. For clarity, this will be demonstrated in the next step, and left out from all other examples.

The next two steps, if combined, would make it so only root can login to MySQL, and can only do so from the localmachine.

Remove users other than root

# mysql -u root -p
mysql> DELETE FROM mysql.user WHERE user != 'root';

Remove logins other than localhost

mysql> DELETE FROM mysql.user WHERE host != 'localhost';

Add a local user

mysql> GRANT ALL ON *.* TO rick@localhost IDENTIFIED BY 'ThisIsMyPassword';

Add a user connecting from the 192.168.0 class C subnet

mysql> GRANT ALL ON *.* TO rick@'192,168.0.0/255.255.255.0' IDENTIFIED BY 'ThisIsMyPassword';

Add a user connecting from 'goodplace.com'

mysql> GRANT ALL ON *.* TO rick@'.goodplace.com' IDENTIFIED BY 'ThisIsMyPassword';

Not all passwords need to be the same. If you have been completing each of these steps, try setting different passwords and then verifying that the users are present and with differet passwords.

For more information, start reading about myisamchk, mysql, mysqladmin, mysqlbug, mysqldump, mysqlimport, and mysqlshow.