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.
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 TablesRead more...