Basic MySQL commands explained

Many of us use mySQL on our dedicated and virtual servers but not everyone knows more then that it’s a database server.

This guide will cover some of the basic tasks that you can do from your SSH terminal and using the mySQL client. We will read about creating users and granting permissions, List and create/delete databases. 


Manipulating a database server

This guide will not show you how to run a well-tuned and efficient database server, it will only cover the most basic tasks that you might need. Once you have everything running you can go out and search for information about tuning and optimizing MySQL. Don’t forget that the official documentation is a good starting point for that.

This guide will give you a quick list of common tasks, using the most basic commands and syntax. Commands that are not too complicated, just enough to get your server running and working.

[alert style=”green”]The commands are not case sensitive but are listed in all caps by convention. It makes the keywords easier to recognize.[/alert]

The MySQL client

All the commands listed here are run from the mysql shell. To access the shell start the client by typing this in a terminal:

That command launches the shell as user “root”, and prompts for the password. Enter your mysql root password and the mysql client prompt will appear.

Once you’re in the shell semicolons are used to terminate commands. If you find yourself on a new line and your command hasn’t run yet enter “;” and it should work.

Databases

The database is the largest data grouping on a database server. You’ll usually have at least one database per application.

Create a database

To create a database, run this command:

List databases

To list all the databases on the server, enter:

Delete a database

To delete a database you “drop” it:

The USE command

According to the mySQL documentation,  you should refer to a database and table in the mysql shell with the name format “database.table”. That can become cumbersome if you’re running a lot of commands on the same database. To help, there is the “use” command which will set the default database if the name is not included in your statement:

For any commands where the database name is left out this command tells mySQL what database to use.

Users

Each user in mySQL has a password and a certain set of permissions that allow or deny it access to various databases and tables. Pretty much like users in the Linux shell.

Add a user

mySQL keeps this user information in the table “user” in the “mysql” database. Adding a user is a matter of inserting a new piece of data into the user table like so:

Replace “newuser” and “mynewpassword” with the username and password. If this user should only be able to access the database server locally, set the “hostname” to “localhost”. If your user will be accessing the database from another server or from your workstation, you’ll need to add a one user for each hostname since they are paired together.

Change a user’s password

To change a user’s password you can run the “set password” command:

Then flush privileges to save the change:

Grant user privileges

To give a user permission to read and write to a database or table you will have to “grant” that user access. To give a user full access in a given database run this command (change the user and hostname accordingly):

Flush privileges

So what does the command FLUSH PRIVILEGES really mean? When executing this command, mySQL will reload the privileges from the grant tables:

You should flush the privileges after any changes to the user or grant tables.

List users

You can list all the users on the server by “selecting” the entries in the user table:

Delete a user

To delete a user we simply delete its entry from the user table:

Tables

Each database usually contains one or more tables for your application to store its data in.  The application itself should usually be the one who creates tables and modifies them.

List tables

Use the “show tables” command to list the tables in a database:

Count the rows in a table

A simple query to count the number of rows (entries) in a table would look like:

Show all data in a table

To list every entry in a table, run:

The “*” in the command above tells mySQL to show everything in the table, you can choose to list just the fields you want from each entry by listing the name of the field instead of using the  “*” above. Separate each field with commas.

A perfect example would be the “List users” command above – That command tells mySQL to list the fields “User”, “Host”, “Password” from the mySQL.user table.

Repair a table

This is a command that you should hopefully never need to run. If one of your tables ever get corrupt (could happen if your server gets shutdown improperly) you can try to repair it with:

This command only works on MyISAM or ARCHIVE tables. If you are using innodb, please read the mySQL documentation on how to repair innodb tables.

Optimize a table

The “optimize” command will reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table.

Basicly, what that command does is defragment the table. Removing blank entries and reorganizes the data. In some cases it can improve performance. In some cases, when you have advanced indexes and secondary keys, it can in worst cases degrade  the performance.

It is hard to say if Optimize Table is or isn’t good in every case. The only way to be sure is to backup your database, banchmark the database (preferably on a testserver) both before and after the command is run.

Delete a table

Delete a table by “dropping” it:

Reset the root password

This actually has its own page here on this site, click the link to read it.

Summary

This is a short summary of some of the most used commands in mySQL. Take your chance and visit the  official MySQL documentation for more information on running MySQL.

2 comments for “Basic MySQL commands explained

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.