Some people are born to be a database administrator, and others have database administration thrust upon them. If you’re in the former group and you need to perform some very basic operations, like creating and backing up databases, you’ll find it’s much easier than it sounds. Let’s take a look at some very basic MySQL administration to get started.
When I say basic, I do mean basic. This tutorial will not make you a database administrator or MySQL expert. But you’ll have enough MySQL to get by for some standard scenarios like setting up WordPress or doing a quick backup of a MySQL database.
You can also do these things with phpMyAdmin, and many folks do, so why look at the command line? The first reason is that using the command line client provides one less piece of software to install, maintain, and worry about. If you find yourself interacting with MySQL infrequently, there’s not much reason to worry about installing a separate piece of software to interact with it.
Also, knowing how to do things at the command line means being able to do them using scripts. You can’t script interactions with phpMyAdmin (not easily, anyway). So if you want to write a script to back up your database on a weekly or daily basis, you’ll need to know how to do that from the command line.
In this tutorial I’ll cover the very bare basics of working with MySQL — creating a database and tables, creating users, dropping a database and tables, repairing a database, and working with backups. The assumption here is that you already have MySQL installed, and just need to get started with the commands.
Logging In
Let’s start with the really easy stuff, logging into the MySQL shell. Just run mysql -u username -p. This will log you into the shell. To see what databases are available, run:
mysql> show databases;
Note the ; at the end. That tells MySQL that command is finished. Without it, it assumes another line of input is coming. This might be useful for entering longer SQL commands.
You’ll also note that the MySQL shell saves its history. Hit the up arrow and you’ll see the same command you just ran.
When you want to work with a specific database, run use database; where database is the name of the MySQL database you want to work with. What if you need to create a database? No problem.
Creating a Database and Tables
Creating a database is easy. At the MySQL shell, run create database database_foo. That will create database_foo, without any tables. You can also do this using from the bash shell using the mysqladmin utility, like so:
mysqladmin -u user -p create database
This is faster if you are just going to create the database from the commandline, but slower if you’re going to be using a bunch of commands. Why? Because you need to enter the password (-p) each time. Note that you can bypass this by creating a .my.cnf configuration file with your username and password. I don’t recommend this, though, as it’s less secure.
You can also use the mysqladmin utility to start, stop, and reload MySQL as well as many other administrative tasks. Need to shut MySQL down for some reason? Run mysqladmin -u user -p shutdown. Run mysqladmin –help for a list of commands.
To create a table, the syntax is similar to creating a database: create table table_foo and then the options for the table. Since each table can have quite a few fields with various data types, default information, etc., these commands can get lengthy. Here’s an example statement that creates the wp_users table for WordPress:
CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL auto_increment, `user_login` varchar(60) NOT NULL default '', `user_pass` varchar(64) NOT NULL default '', `user_registered` datetime NOT NULL default '0000-00-00 00:00:00', `display_name` varchar(250) NOT NULL default '', `spam` tinyint(2) NOT NULL default '0', `deleted` tinyint(2) NOT NULL default '0', PRIMARY KEY (`ID`), UNIQUE KEY `user_login` (`user_login`), KEY `user_login_key` (`user_login`), KEY `user_nicename` (`user_nicename`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
I omitted several of the statements for brevity, but the basic flavor is still there. After the create table statement, you see that you specify the field name, the data type, if a field can be NULL (i.e., have no value), and default values. The field statements are separated by commas, and the final line specifies the engine that MySQL will use (in this case, MyISAM), and other options for the table.
If that looks like gibberish, don’t worry. This gets way beyond 101 administration and into actually creating schema. If you’re working with applications that have pre-defined database schema, you shouldn’t need to know how to come up with any of those statements.
Setting up a User
To start with you should have a root user for MySQL, which is different from the root user for the system. But you don’t want to use the root account for everything, you should create a regular MySQL user as well. For instance, I have a different user for my WordPress database that has fewer privileges than the root user.
Creating a user is simple, just log into the MySQL shell and run the following:
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'password';
You will want to change the details, of course. Having a user doesn’t do any good, though, without granting permission to one or more databases. So if you want to give permission to a user to have all privileges for a given database, you’d do something like this:
GRANT ALL PRIVILEGES ON wordpress_db.* to 'bob'@'localhost';
You can limit the privileges that the user has to operations like select, delete, insert, etc. See the MySQL reference manual for more details there.
Creating a Database Dump
MySQL databases are stored on disk as binary files with your data, plus index files and a file that describes the format of the table. So for each database you’ll have a directory under /var/lib/mysql plus three files for every table, and a .opt file with options for your database.
I believe it’s possible to recover a database using the files on disk, but it’s easier to work with a database dump. How do you get one? Simple. MySQL has a utility called, quite descriptively, mysqldump. To create a dump of a database called wordpress, you’d run:
mysqldump -u user -p wordpress > wordpress.sql
This creates a text file, possibly quite large if your database is fairly large, that has your data and the instructions for MySQL to create a new database. As an example, my WordPress database is 39MB on disk, and the text file created with mysqldump is 41MB. So it's not much larger, but there will be some overhead.
Note that you can use this utility in a more fine-grained fashion. That is, mysqldump can be used to create a dump of individual tables.
To restore from a MySQL dump file is even easier. Let’s say you want to restore a database called “wordpress” from a dumpfile called wordpress.sql (as shown in the example above). All you need is the path to the dumpfile and the name of the database, plus a valid user with the correct privileges:
mysql -u user -p wordpress < wordpress.sql
The database does need to exist before you can restore the data. So if you’re moving a MySQL-based application from one system to another, you need to create the database (empty) before restoring the data.
Of course there’s much, much more to learn about MySQL administration and SQL statements, etc. As I mentioned, you can also do a great deal of MySQL administration using phpMyAdmin. We’ll look at that, and more advanced MySQL usage, in a future tutorial. Until then, I hope that this brief guide is useful for those users who just need to perform simple MySQL operations without needing to dive into DBA training.
