Mysql your way out of a wet paper bag.

For someone who doesn't have to deal with the subject in depth, I have found myself dealing with mysql very often.  Getting servers migrated and creating services for users requires me to have at least a very basic tool kit for dealing with mysql.

This could be a road map for starting administrators that haven't dealt with mysql.  I never have to deal with tables, but I do need to create databases, make users that have permissions on those databases (and not the whole sql server), back up databases, and restore databases; the following represents my expertise on this subject.

This example assumes a unix-based system with a current (5.X) mysql installation and system root access.  If you want to know how to actually set the server up send me a message; sooner or later I'll post something like that.

1. Creating databases.

Before you can create a database you have to log in to the database server as the root mysql user.  This is not the same as the root user for the host system.  If this is a fresh install, the root account for mysql has no password, and you can omit the '-p' switches.  Mysql has to be running before you can log in.  Each system has different scripts for starting the mysql service, but if you run a 'top' or 'ps aux| grep mysqld' you should be able to see if the 'mysqld' process is running.  To log in type:

mysql -u root -p

The system will then prompt you for the root password.  If you have never set a root password for mysql, leave out the -p.  You'll be greeted with the 'mysql>' prompt instead of your regular system prompt.  You can see what's there by typing the following:

mysql> show databases;

It will most likely show you a list like this:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| intranet           |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

You might not have one that says 'intranet' if you do, then we have something in common.  If you didn't and really wanted one, type:

mysql> create database intranet;

Notice on both commands there is a semi-colon ';' at the end of each command; those are important.

2. Creating users.

In order for the database to be of any use to anyone except the root mysql user, we need to add a mysql user with privileges to that database.  If you're bringing the database over from another machine (we'll get to the restore in a moment) you'll need to make sure that the mysql user name and passwords match exactly.  How we do this is with the following statement:

mysql> grant all privileges on databasename.* to 'mysqluser'@'localhost' identified by 'mysqlpassword';

For our specific example it would look like:

mysql> grant all privileges on intranet.* to 'intranetguy'@'localhost' identified by 'd0ntTe3ll';

That will give the user 'intranetguy' access to write to the database 'intranet' and all of its tables as long as the user logs in from the machine 'localhost' (which usually means it's a process running already on the local box, like a web-server environment would be) with a password of 'd0ntTe3ll'.

It is a good idea to flush the privileges after creating users or databases; then exit the mysql shell.

mysql > flush privileges;
mysql > exit

3. Backing up databases.

I'm a passionate guy, and that passion extends to backing up data.  Back up your databases.  I use 'mysqldump' to make an easy to move sql file that we can use as a backup, or a package to move the database to a new server.  The 'mysqldump' command is run on the host system shell, NOT the mysql> shell.  The statement would look something like this:

# mysqldump -hlocalhost -uintranetguy -pd0ntTe3ll intranet > /path/to/sqlbackupfolder/intranet.sql

Without spaces, the switches are '-h' host - almost always localhost -, '-u' the user you specified earlier, and '-p' the password.  I know what you're thinking: 'I have to type that all the time?' No, we're going to add it to the system cron.  As the system (not mysql) root user, type 'crontab -e' to edit the cron daemon's task list.  This will bring up the cron file in the editor 'vi'; if you don't know how to use 'vi' then you need to go learn.  We need to find the path of 'mysqldump' with the 'which' command, create the backup directory, then add the following line after you get into 'crontab -e':

# which mysqldump
/usr/local/bin/mysqldump
# mkdir /var/sqlbackup
# crontab -e
0 3 * * * /usr/local/bin/mysqldump -hlocalhost -uintranetguy -pd0ntTe3ll intranet > /var/sqlbackup/intranet.sql

And every morning at 3am, you'll be backing up the intranet database.

4. Restoring databases.

So now you have to restore the database.  Super easy. From the host system shell:

$ mysql -u intranetguy -p intranet < /var/sqlbackup/intranet.sql

The system will prompt you for the intranetguy's password and the intranet database will be overwritten with the contents of the backup sql file.