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.
Obtain it from http://www.mysql.com/ or any of the software repositories. The binaries and source files can be obtained for compilation and installation.
In the case of the binaries, use the Debian distribution, and select the packages mysql-* (client, server, common are required). Following a number of questions, the installation will create a mysql user and an entry in /etc/init.d/mysql in order to start/stop the server during boot. It can also be done manually using:
In order to access the database, we can use the mysql monitor from the command line. If we obtain the binaries (not Debian or RPM, with this simply use the common -apt-get, rpm-), for example gz from the MySQL website, we will have to execute the following commands in order to install the DB:
groupadd mysql useradd -g mysql mysql cd /usr/local gunzip < /path/to/mysql-VERSION-OS.tar.gz | tar xvf - ln -s full-path-to-mysql-VERSION-OS mysql cd mysql scripts/mysql_install_db --user=mysql chown -R root . chown -R mysql data chgrp -R mysql . bin/mysqld_safe --user=mysql &
This creates the user/group/directory, decompresses and installs the DB in /usr/local/mysql.
In the case of obtaining the source code, the steps are similar:
groupadd mysql useradd -g mysql mysql gunzip < mysql-VERSION.tar.gz | tar -xvf - cd mysql-VERSION ./configure --prefix=/usr/local/mysql make make install cp support-files/my-medium.cnf /etc/my.cnf cd /usr/local/mysql bin/mysql_install_db --user=mysql chown -R root . chown -R mysql var chgrp -R mysql . bin/mysqld_safe --user=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.
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.
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-10. Web site
For further information, see the documentation, commands and options. [Mys07]
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";
mysql> SELECT name, birth FROM pet ORDER BY birth;
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) ->
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
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]
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).