View source

{{TutorialPhase4}}{{GermanTutorial|http://wolf-u.li/1425/php-und-mysql-auf-dem-conceptronic-ch3snas-bzw-d-link-dns-323-installieren/}}
[[Image:mysql_logo.jpg|frame|Logo of MySQL]]

[http://en.wikipedia.org/wiki/Mysql MySQL] is an open source [http://en.wikipedia.org/wiki/Relational_database relational database management system] (RDBMS) provided by [http://mysql.com a commercial company acquired by Sun]. Although the software is free, the company provides commercial support and consultancy (this is a similar model to certain Linux distributions).

An RDBMS is a software tool to store, access and update (often large) amounts of data structured as interrelated tables. Originally, databases were typically used for adminstrative purposes such as storing employee- or inventory information. Nowadays, databases are also widely used to store the raw content from which dynamic web sites are generated. This allows the same information to be presented in different ways. Because [http://en.wikipedia.org/wiki/SQL SQL] is a standardized language to update or access an RDBMS, it also avoids relying on proprietary storage formats with associated risks of obsolescence or lock-in to particular software. 

Although there are various other open source and commercial RDBMS systems [http://en.wikipedia.org/wiki/Relational_database_management_system#Market_structure available], <code>MySQL</code> is commonly used in web development in [http://en.wikipedia.org/wiki/LAMP_(software_bundle) conjunction with] <code>Linux</code>, <code>Apache</code> (or <code>lighttpd</code>), and <code>php</code>. [http://en.wikipedia.org/wiki/Main_Page WikiPedia], for example, runs on [http://www.mediawiki.org/wiki/Manual:Installation_requirements MediaWiki] software written in [[CH3SNAS:Tutorials/php|PHP]] and uses a MySQL database.

=Setting up MySQL=
==Installation==
[[User:Fonz|Fonz]], the creator of [[CH3SNAS:Tutorials/fun_plug|fun_plug]], kindly provided a packaged version of <code>MySQL</code> for the NAS in his [http://www.inreto.de/dns323/fun-plug/0.5/PACKAGES.html#mysql repository].

MySQL is not installed as part of fun_plug by default, but you should already have downloaded a copy as part of the general [[CH3SNAS:Tutorials/ffp-packages#Downloading_packages| tutorial]] on how to download, install and upgrade packages. Let's first make sure you still have the latest version (as [[User:Fonz|Fonz]] upgrades his repository regularly).

Note that the <code>rsync</code> command below could take a while because it may download multiple packages (depending on what is already in your <code>/ffp/pkg/packages</code> directory). 

 cd /ffp/pkg
 ls -al packages/mysql*
 rsync -av --delete inreto.de::dns323/fun-plug/0.5/packages .
 ls -al packages/mysql*
 funpkg -i /ffp/pkg/packages/mysql-*.tgz

If you have a version of <code>mysql</code> installed that is outdated, you will need to run in upgrade mode instead (see [[CH3SNAS:Tutorials/ffp-packages|here]] for help):

 funpkg -u /ffp/pkg/packages/mysql-*.tgz

==Configuration==

MySQL can be configured with a file called <code>my.cnf</code>. After installation you can configure several MySQL settings by copying an example-file from <code>/ffp/etc/examples/mysql/</code> to <code>/ffp/etc/</code>:
<pre>cp /ffp/etc/examples/mysql/my-small.cnf /ffp/etc/my.cnf</pre>

==Directories==
MySQL stores the data of its databases in files which are in turn stored in a directory named <code>/srv/mysql/</code>. Instead of creating the directory at that location, you may prefer to create a [http://en.wikipedia.org/wiki/Symbolic_link symbolic link] from /srv/ to the target-directory of your choice.
We assume you want to store your databases on the first hard drive (<code>/mnt/HD_a2/</code>) in a directory called <code>www</code>.
First we create it:
<pre>mkdir /mnt/HD_a2/www/</pre>
Now we link it to <code>/srv/</code>:
<pre>ln -s /mnt/HD_a2/www/ /srv</pre>
This link will be lost after rebooting the device, so you have to add the following two lines to the end of the file <code>/ffp/etc/fun_plug.init</code> to recreate the link every time the NAS boots. You can edit this file using an editor like [[CH3SNAS:Tutorials/nano|nano]]:
<pre># create custom link to the server-folder
ln -s /mnt/HD_a2/www/ /srv</pre>

==Initialization==
MySQL needs some internal databases for the initial startup which can be installed by issuing the <code>mysql_install_db</code> command:
<pre>
cd /srv/
ls -al
/ffp/bin/mysql_install_db
ls -al
</pre>
This results in several warnings (which you can ignore) about adjusted sizes system- and help tables. Typical partial output:
<pre>
081116 22:05:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
081116 22:05:32 [Warning] option 'max_join_size': unsigned value 18446744073709551615 adjusted to 4294967295
081116 22:05:32 [Warning] option 'myisam_max_extra_sort_file_size': unsigned value 2147483648 adjusted to 2147483647
081116 22:05:32 [Warning] option 'thread_stack': unsigned value 65536 adjusted to 131072
</pre>

Now we manually [[CH3SNAS:Tutorials/ffp-packages#Starting_Daemons_manually|start]] the MySQL server for further configuration:
<pre>sh /ffp/start/mysqld.sh start</pre>
Note that you will have to press Enter to get your prompt back (unlike other daemons).

After the first start, we have to secure the installation:
<pre>/ffp/bin/mysql_secure_installation</pre>
You will be asked several questions (shown below in abridged form) and can answer Y(es) for each of them.
For <code>"Enter current password for root (enter for none):"</code> you press enter because the default root password is empty.
For the new root password, it is best to use a different password than the user <code>root</code> of the system: this is just for owning the administration rights to the database and is unrelated to overall control over the machine.

<pre>
Enter current password for root (enter for none):
OK, successfully used password, moving on...

Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!

Remove anonymous users? [Y/n] Y

Disallow root login remotely? [Y/n] Y

Remove test database and access to it? [Y/n] Y

Reload privilege tables now? [Y/n] Y

All done!
</pre>

To [[CH3SNAS:Tutorials/ffp-packages#Permanent_activation_of_Daemons|activate this service permanently]] on every boot you need to enter this command:
<pre>chmod a+x /ffp/start/mysqld.sh</pre>

=Testing MySQL=
After MySQL has started, you can test your installation using the following ways:
==Command-line==
Enter the following command on the command-line:
<pre>mysql -p</pre>
This will open a special mysql-command-line, where you can enter regular SQL-Commands. Now change to the database "mysql":
<source lang="mysql">
USE mysql;
</source>
Then select the Host, User and Passwort from the Database:
<source lang="mysql">
SELECT Host, User, Password FROM user;
</source>
Finally exit the mysql-command-line:
<source lang="mysql">
exit;
</source>

A sample output will look like this:
<pre>root@CH3SNAS:/srv/mysql# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.67 Source distribution

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

mysql> USE mysql;

Database changed
mysql> SELECT Host, User, Password FROM user;

+-----------+------+-------------------------------------------+
| Host      | User | Password                                  |
+-----------+------+-------------------------------------------+
| localhost | root | *8D2414F01991E3B0B86E14D2469EACA0B6D78B99 |
| CH3SNAS   | root | *8D2414F01991E3B0B86E14D2469EACA0B6D78B99 |
| 127.0.0.1 | root | *8D2414F01991E3B0B86E14D2469EACA0B6D78B99 |
+-----------+------+-------------------------------------------+
3 rows in set (0.01 sec)

mysql> exit;
Bye</pre>
By the way: As you can see, passwords are crypted (in this case it was "<code>nas-tweaks.net</code>").

==PHP==
For the following, you need to install [[CH3SNAS:Tutorials/lighttpd|lighttpd]] and [[CH3SNAS:Tutorials/php|php]] in case you haven't already done so. 
You also need to enable the <code>mysql</code> module of <code>lighttpd</code> by editing the /ffp/etc/php.ini file:
<pre>
; Linux extensions
extension=calendar.so
;extension=ctype.so
;extension=ftp.so
;extension=gd.so
;extension=mbstring.so
extension=mysql.so
;etc
</pre>

Make sure you define the folder which contains all the above modules in line 536 of the <code>php.ini</code> file. Usually it should be:
<pre>extension_dir = "/ffp/lib/php/extensions/no-debug-non-zts-20060613/"</pre>

You will need to restart the web server if you enabled the my-sql extension, or changed the extension_dir using:
<pre>sh /ffp/start/lighttpd.sh restart</pre>

Then place a file called <code>testmysql.php</code> in the document-root (as configured [[CH3SNAS:Tutorials/lighttpd#Directories|here]] e.g. <code>/mnt/HD_a2/www/pages</code>) with the following content (replace <code>YOURROOTPASS</code> with the password of mysql user <code>root</code>):
<pre>
<?php
// Connect to the database
mysql_connect("localhost", "root","YOURROOTPASS");
// Select the database "mysql"
mysql_select_db("mysql");
// Query the database for the Users:
$result = mysql_query("SELECT Host, User, Password FROM user;");
// Print the results
while($row = mysql_fetch_object($result))
{
	echo $row->User . "@" . $row->Host . " has the encrypted password: " . $row->Password;
}
// Close the connection to the database
mysql_close();
?>
</pre>
If opening this page in your browser doesn't give the expected results, check the password, and if needed close and open the browser again.

When you are done, you may want remove the root password (or delete this .php file) to avoid exposing the mysql password in the line <code>mysql_connect("localhost", "root","YOURROOTPASS");</code>.

=Users and privileges=
==Adding additional Users==
As you should never use the root-password of your database, you can add additional users in the mysql-command-line (enter "<code>mysql -uroot -p</code>" on the command-line).
Please consult the [http://dev.mysql.com/doc/refman/5.1/en/adding-users.html mysql-manual] for more examples.

* A User with all privileges, who can only connect from localhost:
<pre>GRANT ALL PRIVILEGES ON *.* TO 'YOURUSERNAME'@'localhost' IDENTIFIED BY 'YOURPASSWORD' WITH GRANT OPTION;</pre>
* A User with limited privileges, who can only connect from localhost:
<pre>GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'YOUROTHERUSERNAME'@'localhost' IDENTIFIED BY 'YOUROTHERPASSWORD' WITH GRANT OPTION;</pre>
* A User with limited privileges on a certain database:
<pre>CREATE DATABASE databasename;
GRANT SELECT,INSERT,UPDATE,DELETE ON databasename.* TO 'YOURSPECIALUSERNAME'@'localhost' IDENTIFIED BY 'YOURSPECIALPASSWORD' WITH GRANT OPTION;</pre>

After you send add or alter the rights, please make sure, that these get loaded by executing the following command in the mysql-command-line:
<pre>FLUSH PRIVILEGES;</pre>

==Allowing external access==
Per default external access is not allowed as this is a security risk. But many tools like HeidiSQL or other external administrator-programs rely on access from the outside of your NAS.

'''Caution: You should explicitly check the rights of your users! All MySQL-users should have passwords!'''

First follow the section on "[[CH3SNAS:Tutorials/Mysql#Running_mysql_under_a_user_with_limited_rights|running mysql under a user with limited rights]]", then follow these instructions:

Stop the mysql-server:
<pre>sh /ffp/start/mysqld.sh stop</pre>

Edit <code>/ffp/etc/my.cnf</code> and add a comment to the line <code>skip-networking</code>, so that it looks like this:
<pre>#skip-networking</pre>

Edit <code>/ffp/start/mysqld.sh</code> and find the line beginning with <code>mysqld_flags</code> and remove "<code>--skip-networking</code>" between the two quotation marks. Save the file afterwards.

==Running mysql under a user with limited rights==
Per default the MySQL gets started with root-rights. This means, that if the MySQL-server is breached by a intruder, the system probably can be corrupted. Be advised to use the mysql-server only in secure areas (e.g. your local LAN without internet access) and to disable external access (default).

If you want to secure your installation please follow the following steps:

Stop the mysql-server:
<pre>sh /ffp/start/mysqld.sh stop</pre>

Add a new user with limited rights:
<pre>useradd -U -s /bin/false mysql
store-passwd.sh</pre>
This will create a user <code>mysql</code> who is in the group <code>mysql</code> (<code>-U</code> add a new group) and who cannot log in. It will probably show up in the Webinterface, but cannot be used!

This user needs access to the directories of MySQL:
<pre>cd /srv
chown -R mysql:mysql mysql
cd /ffp/var/run/
chown mysql:mysql mysql</pre>

Edit <code>/ffp/start/mysqld.sh</code> and find the line beginning with <code>mysqld_flags</code> and remove "<code>--user=root</code>" between the two quotation marks. Save the file afterwards.

Then start MySQL again:
<pre>sh /ffp/start/mysqld.sh start</pre>
With these changes, MySQL is started under the user <code>mysql</code>.

=Removing MySQL=
If you want to remove MySQL and its databases, you proceed like described in the general [[CH3SNAS:Tutorials/ffp-packages#Package_removals| tutorial]] on packages.
First, stop <code>mysql</code>:
<pre>sh /ffp/start/mysqld.sh stop</pre>
Then remove the package with funpkg:
<pre>
cd /ffp/pkg/packages
funpkg -r mysql*.tgz
</pre>
Afterwards you have to remove the databases (careful!). Change to the folder <code>/srv/</code> and delete the folder <code>mysql</code>:
<pre>
cd /srv/
rm -R mysql
</pre>
Furthermore, you should undo the change in <code>/ffp/etc/fun_plug.init</code>, possibly remove the (harmless) symbolic link <code>/srv/</code>, but especially disable execution of the startup script:
 chmod a-x /ffp/start/mysqld.sh

Voilá, MySQL is removed.