Basic MySQL server tasks

Basic MySQL server tasks

Even the most basic MySQL server installation for Linux will have you performing basic administrative tasks like creating users and granting permissions. We look at some of those tasks here.

---

Manipulating a database server

Running a well-tuned and efficient database server takes a lot of knowledge and hard work. We're not going to help you do that here.

No, this is a quick and dirty list of common tasks, using the most basic commands and syntax. Nothing too complicated, just enough to get a server running and working if "running and working" is all you really need. Once you have everything in place you can go out and research more about tuning and optimizing MySQL. The official documentation is a good starting point for that.

This article doesn't cover actually installing MySQL. If you need to do that first, start with this article.

Note that the commands are not case sensitive but are listed in all caps by convention. It makes the keywords easier to recognize.

 

The MySQL client

All the commands (except resetting the root password, at the end) listed here are run from the mysql shell. To access the shell run the client:

mysql -u root -p

That command launches the shell as user "root", and prompts for the password. If you're using a different user to work with the database, then of coure use that name instead.

Remember that 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 in the shell, run:

CREATE DATABASE databasename;

List databases

To list all the databases on a server, enter:

SHOW DATABASES;

Delete a database

To delete a database you "drop" it:

DROP DATABASE databasename;

The USE command

Typically you can 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:

USE databasename;

True to its name, the command tells MySQL to use that database for any commands where the database name is left out.

 

Users

Users in MySQL work like users in the Linux shell. Each user typically has a password and a certain set of permissions that allow or deny it access to various databases and tables.

 

Add a user

The user data for MySQL is kept in the "user" table in the "mysql" database. Adding a user is a matter of inserting a new piece of data into the user table like so:

INSERT INTO mysql.user (Host,User,Password) VALUES('localhost','demouser',PASSWORD('demopassword'));

Replace "demouser" and "demopassword" with the username and password. If you're only accessing the database server locally use "localhost" for the host value. If your user will be accessing the database remotely you'll need to add a username and host pairing for each host that user would connect from.

Change a user's password

To change a user's password you can run the "set password" command:

SET PASSWORD FOR 'username'@'localhost' = PASSWORD('password');

Then flush privileges to save the change:

FLUSH PRIVILEGES;

Grant user privileges

You use the "grant" command to give a user permission to read and write to a database or table. To give a user full access in a given database you can run:

GRANT ALL PRIVILEGES ON databasename.* TO username@localhost;

FLUSH PRIVILEGES;

Flush privileges

We've mentioned it already, but let's give flushing privileges an official entry:

FLUSH PRIVILEGES;

It's best to get in the habit of flushing the privileges after any change to the user or grant tables.

List users

You list the users on the server by "selecting" the entries in the user table to view:

SELECT User, Host, Password FROM mysql.user;

Delete a user

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

DROP USER 'username'@'localhost';

FLUSH PRIVILEGES;

 

Tables

Each database will contain one or more tables once an application starts storing data in it. The application should usually be what creates tables and modifies them.

 

List tables

Use the "show tables" command to list the tables in a database:

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:

SELECT COUNT(*) FROM databasename.tablename;

Show all data in a table

To list absolutely every entry in a table, run:

SELECT * FROM databasename.tablename;

Note that this will usually be a huge result. You can list just the fields you want to view from each entry by listing them in place of "*" above, separating them with commas. For an example, look at "List users" above - that's exactly what that command is doing.

Repair a table

Hopefully this will never happen, but if one of your tables gets corrupted you can try to repair it with:

REPAIR TABLE databasename.tablename;

This command only applies to tables using the MyISAM database engine (the default in MySQL 5.1 and earlier).

Optimize a table

The "optimize" command isn't always, well, optimal for a database.

OPTIMIZE TABLE databasename.tablename;

What that command does is defragment the table - it removes blank entries and reorganizes the ones that remain by their primary key. In many cases this can improve performance, possibly by quite a bit.

If your table uses more than just a primary key it's possible "defragmenting" the database could fragment the secondary keys dramatically. Sometimes it's better to just let the table organically work things out (the blank lines left in a table from deleting entries does get reused by new ones).

There isn't really a hard and fast rule on when optimizing a table is or isn't good. The best approach to this command is to backup your database or replicate it to a test server, benchmark it, then run the optimize and see if performance improved.

If that's too much trouble, run it once a month or "when you feel like it". Keep an eye on performance. If your database adds and removes variable-length entries a lot it will probably help.

Delete a table

Delete a table by dropping it:

DROP TABLE databasename.tablename;

 

Reset the root password

Finally, the task you hopefully will never need to perform: Resetting the root password.

It's a little more complicated than the other instructions. First you'll need to stop the MySQL server. Then run mysqld_safe with the "skip grant tables" option active:

mysqld_safe --skip-grant-tables &

The "&" tells it to run in the background.

Next run the mysql client to log on as root:

mysql -u root

Note that you're not telling mysql to ask for a password. We don't need to - by skipping the grant tables we remove limits on permissions and access. This is definitely not a state you want your server to be in for long.

Next run the following command to set a new root password:

UPDATE mysql.user SET password=PASSWORD("password") WHERE User='root';

The "update" command tells mysql it's changing a value in place instead of adding a new entry in the user table, and it's doing it for all matching entries (thus, all "root" entries regardless of the "host" value). Replace "password" with a better password than that. One you'll remember.

To make the changes stick we flush the privileges:

FLUSH PRIVILEGES;

Now quit the mysql shell, stop mysql, and start it back up again normally. The root password should be set to the new value.

 

Summary

Hopefully that's enough to get you started with MySQL. For more information on running MySQL, including the myriad commands at your disposal and their syntaxes, visit the official MySQL documentation.