MySQL under Linux: Creating a user and a database for her

When you install software that uses a database (e.g., a forum on a web server), you usually have to install a database server and prepare a database for it. This article explains how to do this for MySQL under linux.

The first step is to install the MySQL client and server, of course. MySQL should be be in the package system of your distribution and it’s easiest to use the version in there. Under SuSE, you can use zypper as root to install it:

zypper in mysql-community-server

(Under Debian, you would use apt-get instead.) The server should be started automatically after installation, but you can check it to be sure:

/etc/init.d/mysql status
and start it in case it is unused:
/etc/init.d/mysql start

If you want to make sure that the server is started at system start, use the chkconfig (SuSE) or update-rc.d (Debian) commands.

In most distributions, the default installation has a test user and database and is secure enough to be used as a production server. For example, usually, the mysql root user (the DB admin) has no password. You can run the interactive mysql_secure_installation command to fix this.

Now that the server is installed, it’s time to log in, create a database user with a password, and create a database for the user. To log in as mysql root, use the mysql -u root command:


server:/home/ts # mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.62-log SUSE MySQL RPM

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

If the mysql root user already has a password, type mysql -u root -p instead, you will then be asked for the password.

You can now type mysql and SQL commands. We will create a new user named ‘dbuser’ with password ‘dbuserpwd’ and a new database ‘db’ for the user below. We also grant him all priviledges on that database so he/she can create tables, insert data, etc:

mysql> CREATE DATABASE db;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE USER ‘dbuser’ IDENTIFIED BY ‘dbuserpwd’;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON db.* to dbuser;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
server:/home/ts #

Now you can connect to the database ‘db’ as user ‘dbuser’ like this:

mysql -u dbuser -D db -p

That’s it. You just installed MySQL and create a user and a database for her.

Note: If you want to ensure that the DB server is started in runlevels 3 and 5 (which makes sense), you can use the chkconfig utility under SuSE:

chkconfig mysql 35

Note: If you need only local access to the database (i.e., the DB user is NOT going to connect from other hosts), you can substitute the commands for user creation and access granting like this:


CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'dbuserpwd';
GRANT ALL PRIVILEGES ON db.* to 'dbuser'@'localhost';

Advertisements

About dfspspirit

PhD student in bioinformatics, interested in photography, level design, digital image manipulation, architecture and, of course, bioinformatics.
This entry was posted in databases, linux and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s