MySQL at OpenSUSE. The first steps.

This tutorial assumes that you have already installed OpenSUSE at your box and your capable of doing simple things with it, ie move around or use the everyday commands. So before we begin let’s check if MySQL is installed on your OpenSUSE machine:

dhcppc2:~ # rpm -qa | grep mysql
libmysqlclient_r15-5.0.51a-27.1
libmysqlclient16-5.1.36-61.1
libqt4-sql-mysql-4.4.0-4.1
libgda-3_0-mysql-3.1.2-42.1
libmysqlclient_r16-5.1.36-61.1
php5-mysql-5.2.5-66.1
qt3-mysql-3.3.8b-44.1
mysql-client-5.0.51a-27.1
kamailio-mysql-1.4.0-9.23
ruby-mysql-2.7.4-84.1
perl-DBD-mysql-4.006-41.1
mysql-5.0.51a-27.1
bytefx-data-mysql-1.9.1-6.
mysql-connector-java-3.1.12-239.1
libmysqlclient15-5.0.51a-27.1

I strikedthrough some software modules that are not needed by default. For sure we need to have mysql and myslq-client in our systems. If these packages are not installed you can downloaded them from the OpenSUSE repositories or leave this duty to yast. Yast will do it for you.  But usually mysql comes pre-installed and probably you will overcome this small obstacle. However when we install MySQL, this does not mean that this module in running in our system as well. If you are planning to use MySQL often a good habit is to configure your system to launch MySQL at the startup. First find out that MySQL is not running and second that is not configured to run at the startup.

dhcppc2:~ # ps -ef | grep mysql

The ps command will respond with the process id, if MySQL is up. In order to see if the process runs on startup execute the following:

dhcppc2:~ # chkconfig –list | grep on

acpid                       0:off  1:off  2:on   3:on   4:off  5:on   6:off
alsasound              0:off  1:off  2:on   3:on   4:off  5:on   6:off
auditd                     0:off  1:off  2:off  3:on   4:off  5:on   6:off
avahi-daemon     0:off  1:off  2:off  3:on   4:off  5:on   6:off
avahi-dnsconfd   0:off  1:off  2:off  3:on   4:off  5:on   6:off
consolekit              0:off  1:off  2:on   3:on   4:off  5:on   6:off
cron                          0:off  1:off  2:on   3:on   4:off  5:on   6:off
...

....
wondershaper     0:off  1:off  2:off  3:off  4:off  5:off  6:off
xdm                        0:off  1:off  2:off  3:off  4:off  5:on   6:off
xend                       0:off  1:off  2:off  3:on   4:off  5:on   6:off
xendomains        0:off  1:off  2:off  3:on   4:off  5:on   6:off

chkconfig is a small but very useful command at the administrator’s command vocabulary.  Most of the times this list does not contain mysql by default. This means that we need to enable mysql to run at the system startup. This is done by invoking the chkconfig command with the following syntax:

dhcppc2:# chkconfig –add mysql
mysql                     0:off  1:off  2:on   3:on   4:off  5:on   6:off

In case you need to see more things about the chkconfig command, here is a nice article at LinuxJournal.com.

Now type the following commands to first check if your service is running and the fire it up:

dhcppc2:/etc/init.d # rcmysql status
Checking for service MySQL:                                           unused
dhcppc2:/etc/init.d # rcmysql start
Starting service MySQL                                                done

The service is up and ready to be configured. Enter the following at your prompt:

dhcppc2:~ # mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we’ll need the current
password for the root user.  If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on…

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer ‘n’.

Change the root password? [Y/n] n
… skipping.

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n]
… Success!

Normally, root should only be allowed to connect from ‘localhost’.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n]
… Success!

By default, MySQL comes with a database named ‘test’ that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n]
- Dropping test database…
… Success!
- Removing privileges on test database…
… Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n]
… Success!

Cleaning up…

All done!  If you’ve completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!

The above dialogue with MySQL will help you configure your system and run a secure database server. If you are running this command for the first time, ie you haven’t configured anything yet, this dialogue will be a little bit different at the beginning. You will be prompted to enter a root password. Give the password that will protect the MySQL root account. This password it may be different from the system’s root password. Now restart your server:

dhcppc2:~ # rcmysql restart
Restarting service MySQL
Shutting down service MySQL                                   done
Starting service MySQL                                                done

Now that you have installed MySQL and have started it, you are ready to test your application. This is a simple process that is intended to verify that the MySQL server is running and that the mysql administrative database has been properly initialized. So first verify which version of MySQL is running:

cateof@dhcppc2:~> mysqladmin version -p
Enter password:
mysqladmin  Ver 8.41 Distrib 5.0.51a, for suse-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version          5.0.51a
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 26 min 0 sec

Threads: 1  Questions: 34  Slow queries: 0  Opens: 23  Flush tables: 1  Open tables: 17  Queries per second avg: 0.022

Information about the MySQL installation displays, including information about the version number and the edition of the MySQL server. Next, verify whether the MySQL databases have been properly installed and configured. You achieve this by running the mysql command line utility to access the databases.

cateof@dhcppc2:~> mysql -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.51a SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| openser            |
+--------------------+
3 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| func                      |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| proc                      |
| procs_priv                |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
17 rows in set (0.00 sec)

mysql> SELECT User,Host from Users;
ERROR 1146 (42S02): Table 'mysql.Users' doesn't exist
mysql> SELECT User,Host from user;
+-----------+-----------+
| User      | Host      |
+-----------+-----------+
| openser   | %         |
| openserro | %         |
| openser   | localhost |
| openserro | localhost |
| root      | localhost |
+-----------+-----------+
5 rows in set (0.00 sec)

Now let’s create a new user that have access to this database outside this Linux box. You need to go to your prompt and type:

dhcppc2:~ # mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.0.51a SUSE MySQL RPM

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> CREATE USER ‘netuser’@'localhost’ IDENTIFIED BY ‘a_password”;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘netuser’@'localhost’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘netuser’@'%’ IDENTIFIED BY ‘a_password”;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘netuser’@'%’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

This sequence of command will create a user called netuser that can access the database either from the console or outside the box, eg via a client from a  Windows machine. More on creating users can be found at http://dev.mysql.com/doc/refman/5.1/en/adding-users.html.