Please check the tutorial page for updated tutorials on this topic!
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
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.