8.3. Mysql

MySQL [Mys] is (according to its authors) the most popular open SQL (DB), in other words free software (Open Source), and is developed and distributed by MySQL AB (a commercial enterprise that makes profit from the services it offers over the DB). MySQL is a database management system (DBMS). A DBMS is what can add and process the data stored inside the DB. Like PostgreSQL, MySQL is a relational database, which means that it stores data in tables instead of in a single location, which offers greater speed and flexibility. As it is free software, anyone can obtain the code, study it and modify it according to their requirements, without having to pay anything, since MySQL uses the GPL license. On its webpage, MySQL offers a set of statistics and features compared to other DBs to show users how fast, reliable and easy it is to use. The choice of a DB should be made carefully according to users' needs and the environment in which the DB will be used.

8.3.1. Installation

This creates the user/group/directory, decompresses and installs the DB in /usr/local/mysql.

It is important to pay attention when configuring, since prefix= /usr/local/mysql is the directory where the DB will be installed and it can be changed to locate the DB in any directory we wish.

8.3.2. Post-installation and verification

Once installed (whether from the binaries or the source code), we will have to verify if the server works properly. In Debian this can be done directly:

If installed from the source code, before making these checks we will have to execute the following commands in order to create the databases (from the distribution's directory):

./scripts/mysql_install_db cd InstallationDirectoryMysql ./bin/mysqld_safe --user = mysql &

If we install from the binaries (RPM, Pkg,...), we must do the following:

cd InstallationDirectoryMysql ./scripts/mysql_install_db ./bin/mysqld_safe user = mysql &

The script mysql_install_db creates the mysql DB and mysqld_safe starts up the mysqld server. Next, we can check all of the commands given above for Debian, except the first one which is the one that starts up the server. Plus, if the tests have been installed, these can be run using cd sql-bench and then run-all-tests. The results will appear in the directory sql-bech/results for comparison with other DBs.

8.3.3. The MySQL monitor program (client)

The MySQL client can be used to create and use simple DBs, it is interactive and can connect to the server, run searches and visualise results. It also works in batch mode (as a script) where the commands are passed onto it through a file). To see all the command options, we can run mysql --help. We will be able to make a connection (local or remote) using the mysql command, for example, for a connection via the web interface but from the same machine:

Example 8-9. Note

MySQL client (frontend):

mysql [DBName]

Example 8-10. Web site

For further information, see the documentation, commands and options. [Mys07]

http://dev.mysql.com/doc/refman/5.0/es/

mysql -h localhost -u mysql -p DBName

If we do not enter the last parameter, no DB is selected.

Once inside, mysql will show a prompt (mysql>) and wait for us to insert a command (own and SQL), for example help. Next, we will give a series of commands in order to test the server (remember always to type the ';' to end the command):

mysql> SELECT VERSION(), CURRENT_DATE;

We can use capital letters or small caps.

mysql> SELECT SIN(PI()/4), (4+1)*5; Calculator.
mysql> SELECT VERSION(); SELECT NOW();

Multiple commands on the same line.

mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;

Or on multiple lines.

mysql> SHOW DATABASES;

Shows the available DBs.

mysql> USE test

Changes the DB.

mysql> CREATE DATABASE nteum; USE nteum;

Creates and selects a DB called nteum.

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),

-> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

Creates a table inside nteum.

mysql> SHOW TABLES;

Shows the tables.

mysql> DESCRIBE pet;

Shows the table's definition.

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Loads data from pet.txt in pet. The pet.txt file must have one register per line separated by data tabs according to the table's definition (date in YYYY-MM-DD format).

mysql> INSERT INTO pet

-> VALUES ('Marciano','Estela','gato','f','1999-03-30',NULL);

Loads data inline.

mysql> SELECT * FROM pet;Shows table data.

mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Browser";

Modifies table data.

mysql> SELECT * FROM pet WHERE name = "Browser";

Selective sample.

mysql> SELECT name, birth FROM pet ORDER BY birth;

Ordered sample.

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

Selective sample with functions.

mysql> GRANT ALL PRIVILEGES ON *.* TO
martian@localhost -> IDENTIFIED BY 'passwd'
WITH GRANT OPTION;

Create user marciano in the DB. This has to be executed by the DB root user. Or it can also be done directly by using.

mysql> INSERT INTO user (Host,User,Password) ->

VALUES('localhost','marciano','passwd');

8.3.4. Administration

Mysql has a configuration file in /etc/mysql/my.cnf (in Debian), where the DB default options can be changed, for example, the connection port, user, password of remote users, log files, data files, whether it accepts external connections etc. In relation to security, we need to take certain precautions:

1) Not to give anyone (except the root user of MySQL) access to the user table within the MySQL DB, since this is where the user passwords are, which could be used for other purposes.

2) Verify mysql -u root. If we can access, it means that the root user does not have a password. To change this, we can type:

mysql -u root mysql mysql> UPDATE user SET Password = PASSWORD('new_password') -> WHERE user = 'root'; mysql> FLUSH PRIVILEGES;

Now, to connect as root:

mysql -u root -p mysql

3) Check the documentation concerning the security conditions and the network environment to avoid problems with attacks and/or intrusions.

4) To make copies of the database, we can use the following command:

mysqldump --tab = /DestinationDirectory \ --opt DBName

or also:

mysqlhotcopy DBName /DestinationDirectory

Likewise, we can copy the files *.frm', *.MYD', and *.MYI with the server stopped. To recover, execute:

REPAIR TABLE o myisamchk –r

which will work in 99% of cases. Otherwise, we could copy the saved files and start up the server. There are other alternative methods depending on what we want to recover, such as the possibility of saving/recovering part of the DB (see point 4.4 of the documentation). [Mys]

8.3.5. Graphic interfaces

There are a large number of graphic interfaces for MySQL, among which we should mention MySQL Administrator (it can be obtained from http://www.mysql.com/products/tools/administrator/). Also as tools we can have Mysql-Navigator (http://sourceforge.net/projects/mysqlnavigator/), or Webmin with the module for working with MySQL (packages webmin-core and webmin-mysql) although the latter is no longer included with some distributions. Similarly to PostgreSQL, Webmin also permits working with MySQL (we will need to install the webmin-mysql packages as well as webmin-core). During the installation, webmin will warn that the main user will be the root and will use the same password as the root of the operating system. To connect, we can type, for example, https://localhost:10000 on the URL bar of a navigator which will request acceptance (or denial) of the use of a certificate for the SSL communication and next it will show all the services that can be administered, among them the MySQL Data Base Server.

MySQL Administrator is a powerful application for administering and controlling databases based on MySQL. This application integrates DB management, control and maintenance in a simple fashion and in the same environment. Its main characteristics are: advanced administration of large DBs, fewer errors through "visual administration", greater productivity and a safe management environment. The following figure shows a view of MySQL Administrator (in http://dev.mysql.com/doc/administrator/en/index.html we can find all of the documentation for installing it and starting it up).