The Keyboard Cowboys // TKC

Basic MySQL Commands

Securing MySQL First

Because MySQL leaves the root password empty by default, we should create a root password (Otherwise, it is extremely insecure).

shell> mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
mysql> FLUSH PRIVILEGES;

Or, you can do this: (I would highly recommend NOT using this technique as the password is printed on the command line.)

shell> mysqladmin -u root password new_password

Now, to make sure all has gone accordingly, let's attempt to log into MySQL as root:

shell> mysql -u root -p mysql
MySQL will ask for a password now, use the 'new-password'.

Add New MySQL Databases

Create a new MySQL database:

shell> mysqladmin -p create databasename
You should now be prompted to enter your MySQL root password which you have previously set up.

Now that the database is created, let's allow a non-root user to access it:

shell> mysql -u root -p mysql
mysql> grant all privileges on databasename.* to username@localhost identified by 'password';
mysql> FLUSH PRIVILEGES;

And now for the testing of the new database and user:

shell> mysql -u username -p databasename
Hopefully you're prompted for a password. Enter the password you set for the user and enjoy!

Questions and/or comments can posted on the TKC Forum or emailed to ek.