~jg3

My MySQL cheat sheet

August 23rd, 2007 | by jg3 |

How many times have I set up MySQL? (gobs) And how many times have I slogged through finding the handfull of commands I needed to get things made orderly (each). And how many times have I bothered to document it so I didn’t have to do all that again? (none)

Let this be the last time I have to scour the web at large for this information, because it will be here

The first thing to do upon installation of a new MySQL server (after starting the server) is to set the password for the root user (which is initially blank) like this:

shell> mysql -u root mysql
mysql> SET PASSWORD FOR root=PASSWORD('new_password');

Then, we need to create a database for storing our application’s information:

mysql> CREATE DATABASE bigdatabase;
--------------
CREATE DATABASE bigdatabase
--------------
Query OK, 1 row affected (0.00 sec)

Now, you can create a user account for your application and restrict that user account’s access to just the database you created. In this example we are going to create a user account called andre and allow that account to do anything to the database we just created, bigdatabase, but only that database, and only when connecting to the server from localhost. By the way, I haven’t seen documentation on how to do this using the mysqladmin tool. Also note that we specify to use the database called mysql because that’s where the user account information for the server lives. Here goes:

mysql> use mysql
Database changed
mysql> GRANT ALL PRIVILEGES ON bigdatabase.* TO 'andre'@'localhost'
-> IDENTIFIED BY 'pasWURd3' WITH GRANT OPTION;
--------------
GRANT ALL PRIVILEGES ON bigdatabase.* TO 'andre'@'localhost'
IDENTIFIED BY 'pasWURd3' WITH GRANT OPTION
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
--------------
FLUSH PRIVILEGES
--------------
Query OK, 0 rows affected (0.00 sec)
mysql> \q
Bye

If you have been provided a database schema you can

shell> mysql -vu root -p < SCHEMAFILE

Now you can explore with


mysql> show databases;
mysql> use bigdatabase;
mysql> show tables;
mysql> describe tableone;


So now you’ve got a user account and a databse, you should be ready to populate that with a schema and some data. That’s enough to get me going most of the time and if it isn’t I’ll keep adding notes here.

Filed under: documentation

Related stuff

• Zone File (0)
• webserver logprocessor notes (0)
• webserver logprocessor notes (0)
• vim converts to HTML (0)
• they said Please (0)
• SSL-Explorer (0)
• Serial Console (1)