Tag: Database

MySQL is an open source database management system (RDBMS) provided by a commercial company acquired by Oracle. Although the software is free, the company provides commercial support and consultancy (this is a similar model to certain Linux distributions).

Although there are various other open source and commercial RDBMS systems available, MySQL is commonly used in web development in conjunction with Linux, Apache (or lighttpd), and php. Wikipedia, for example, runs on MediaWiki software written in PHP and uses a MySQL database.

Contents

• 1 Setting up MySQL
  • 1.1 Installation
  • 1.2 Configuration
  • 1.3 Directories
  • 1.4 Initialization
• 2 Testing MySQL
  • 2.1 Command-line
  • 2.2 PHP
• 3 Users and privileges
  • 3.1 Adding additional Users
  • 3.2 Allowing external access
  • 3.3 Running mysql under a user with limited rights
• 4 Removing MySQL

Setting up MySQL

Installation

Uli kindly provided a packaged version of MySQL for the NAS in his 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 tutorial on how to download, install and upgrade packages. Let’s first make sure you still have the latest version (as Uli upgrades his repository regularly).

Note that the installation command below could take a while:

funpkg -i /ffp/pkg/additional/*/mysql-*.tgz

If you have a version of mysql installed that is outdated, you will need to run in upgrade mode instead (see here for help):

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

Configuration

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

cp /ffp/etc/examples/mysql/my-small.cnf /ffp/etc/my.cnf

Directories

MySQL stores the data of its databases in files which are in turn stored in a directory named /srv/mysql/. Instead of creating the directory at that location, you may prefer to create a symbolic link from /srv/ to the target-directory of your choice.
First we create it:

mkdir -p /ffp/opt/srv/mysql
mkdir -p /ffp/opt/srv/tmp/mysql

Now we link it to /srv/:

ln -s /ffp/opt/srv/ /srv

This link will be lost after rebooting the device, so you have to add the following two lines to the end of the file /ffp/etc/fun_plug.init to recreate the link every time the NAS boots. You can edit this file using an editor like nano:

# create custom link to the server-folder
ln -s /ffp/opt/srv/ /srv

Initialization

MySQL needs some internal databases for the initial startup which can be installed by issuing the mysql_install_db command:

cd /srv/
ls -al
/ffp/bin/mysql_install_db
ls -al

This results in several warnings (which you can ignore) about adjusted sizes system- and help tables. Typical partial output:

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

Now we manually start the MySQL server for further configuration:

sh /ffp/start/mysqld.sh start

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:

/ffp/bin/mysql_secure_installation

You will be asked several questions (shown below in abridged form) and can answer Y(es) for each of them.
For "Enter current password for root (enter for none):" 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 root of the system: this is just for owning the administration rights to the database and is unrelated to overall control over the machine.

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!

To activate this service permanently on every boot you need to enter this command:

chmod a+x /ffp/start/mysqld.sh

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:

mysql -p

This will open a special mysql-command-line, where you can enter regular SQL-Commands. Now change to the database “mysql”:

USE mysql;

Then select the Host, User and Passwort from the Database:

SELECT Host, User, Password FROM user;

Finally exit the mysql-command-line:

exit;

A sample output will look like this:

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

By the way: As you can see, passwords are crypted (in this case it was “nas-tweaks.net“).

PHP

For the following, you need to install lighttpd and php in case you haven’t already done so.
You also need to enable the mysql module of lighttpd by editing the /ffp/etc/php.ini file:

; Linux extensions
extension=calendar.so
;extension=ctype.so
;extension=ftp.so
;extension=gd.so
;extension=mbstring.so
extension=mysql.so
;etc

Make sure you define the folder which contains all the above modules in line 536 of the php.ini file. Usually it should be:

extension_dir = "/ffp/lib/php/extensions/no-debug-non-zts-20060613/"

You will need to restart the web server if you enabled the my-sql extension, or changed the extension_dir using:

sh /ffp/start/lighttpd.sh restart

Then place a file called testmysql.php in the document-root (as configured here e.g. /srv/www/pages) with the following content (replace YOURROOTPASS with the password of mysql user root):

<?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();
?>

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 mysql_connect("localhost", "root","YOURROOTPASS");.

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 “mysql -uroot -p” on the command-line).
Please consult the mysql-manual for more examples.

• A User with all privileges, who can only connect from localhost:

  GRANT ALL PRIVILEGES ON *.* TO 'YOURUSERNAME'@'localhost' IDENTIFIED BY 'YOURPASSWORD' WITH GRANT OPTION;

• A User with limited privileges, who can only connect from localhost:

  GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO 'YOUROTHERUSERNAME'@'localhost' IDENTIFIED BY 'YOUROTHERPASSWORD' WITH GRANT OPTION;

• A User with limited privileges on a certain database:

  CREATE DATABASE databasename;
  GRANT SELECT,INSERT,UPDATE,DELETE ON databasename.* TO 'YOURSPECIALUSERNAME'@'localhost' IDENTIFIED BY 'YOURSPECIALPASSWORD' WITH GRANT OPTION;

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:

FLUSH PRIVILEGES;

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 “running mysql under a user with limited rights“, then follow these instructions:

Stop the mysql-server:

sh /ffp/start/mysqld.sh stop

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

#skip-networking

Edit /ffp/start/mysqld.sh and find the line beginning with mysqld_flags and remove “--skip-networking” 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:

sh /ffp/start/mysqld.sh stop

Add a new user with limited rights:

useradd -U -s /bin/false mysql
store-passwd.sh

This will create a user mysql who is in the group mysql (-U 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:

cd /srv
chown -R mysql:mysql mysql
cd /ffp/var/run/
chown mysql:mysql mysql

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

Then start MySQL again:

sh /ffp/start/mysqld.sh start

With these changes, MySQL is started under the user mysql.

Removing MySQL

If you want to remove MySQL and its databases, you proceed like described in the general tutorial on packages.
First, stop mysql:

sh /ffp/start/mysqld.sh stop

Then remove the package with funpkg:

funpkg -r /ffp/pkg/additional/*/mysql*.tgz

Afterwards you have to remove the databases (careful!). Change to the folder /srv/ and delete the folder mysql:

cd /srv/
rm -R mysql

Furthermore, you should undo the change in /ffp/etc/fun_plug.init, possibly remove the (harmless) symbolic link /srv/, but especially disable execution of the startup script:

chmod a-x /ffp/start/mysqld.sh

Voilá, MySQL is removed.