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:
|
1 |
mysql -u root -p |
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:
|
1 |
CREATE DATABASE databasename; |
List databases
To list all the databases on the server, enter:
|
1 |
SHOW DATABASES; |
Delete a database
To delete a database you “drop” it:
|
1 |
DROP DATABASE databasename; |
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:
|
1 |
USE databasename; |
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:
|
1 |
INSERT INTO mysql.user (Host,User,Password) VALUES('hostname','newuser',PASSWORD('mynewpassword')); |
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:
|
1 |
SET PASSWORD FOR 'username'@'localhost' = PASSWORD('password'); |
Then flush privileges to save the change:
|
1 |
FLUSH PRIVILEGES; |
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):
|
1 2 |
GRANT ALL PRIVILEGES ON databasename.* TO user@hostname; FLUSH PRIVILEGES; |
Flush privileges
So what does the command FLUSH PRIVILEGES really mean? When executing this command, mySQL will reload the privileges from the grant tables:
|
1 |
FLUSH PRIVILEGES; |
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:
|
1 |
SELECT User, Host, Password FROM mysql.user; |
Delete a user
To delete a user we simply delete its entry from the user table:
|
1 2 |
DROP USER 'username'@'localhost'; FLUSH PRIVILEGES; |
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:
|
1 |
SHOW TABLES FROM databasename; |
Count the rows in a table
A simple query to count the number of rows (entries) in a table would look like:
|
1 |
SELECT COUNT(*) FROM databasename.tablename; |
Show all data in a table
To list every entry in a table, run:
|
1 |
SELECT * FROM databasename.tablename; |
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:
|
1 |
REPAIR TABLE databasename.tablename; |
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.
|
1 |
OPTIMIZE TABLE databasename.tablename; |
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:
|
1 |
DROP TABLE databasename.tablename; |
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”