Installing and Securing MySQL

General Information

MySQL is one of the most popular databases on the Internet. adidas hamburg adidas en ligne new balance 574 Besides its undoubted advantages, such as easy of use and relatively high performance, MySQL offers simple, but very effective security mechanisms. Unfortunately, the default installation of MySQL, and in particular the empty root password and the potential vulnerability to buffer overflow attacks, makes the database an easy target for attacks.

This howto Covers:

  1. Installing MySQL
  2. Chrooting MySQL
  3. MySQL Permissions
  4. Configuring MySQL


Lets start ou/t by installing MySQL from the ports tree.

 # cd /usr/ports/databases/mysql323-server # make install clean 

This should work fine with the other versions of MySQL inside the ports tree. adidas superstar pas cher chaussure timberland femme nike air max 2012 asics nimbus blade and soul gold You may also want to go over what options you would like to include, such as building MySQL:


Whatever options you may pass don’t forget to put inside your pkgtools.conf file.

Now we need to make sure any portupgrades of MySQL does not overwrite our MySQL database.

 # vi /usr/local/etc/pkgtools.conf 

Search for the MAKE_ARGS section. nike air max 2018 You should make it look like this below:

 MAKE_ARGS = { 'databases/mysql323-server' => 'SKIP_INSTALL_DB=yes' } 

Save and exit. adidas ace Now we need to configure the MySQL client.

 # cp /usr/local/share/mysql/my-medium.cnf /etc/my.cnf 

Note: Replace my-medium.cnf with whatever suites your server’s enviroment. The following step is optional. bottes timberland nike air max 90 asics chaussures asics soldes Most of the time there is no real need to have an open port for MySQL unless you have other machines on the network that need quick access to the database server. nike flyknit lunar So, instead we are going to use a unix socket file.
 # vi /usr/local/etc/rc.d/ 

Change this line:

 safe_mysqld --user=mysql --datadir=${DB_DIR} ..etc..etc.. nike air max 2017 nike sb adidas pas cher 

to look like:

 safe_mysqld --user=mysql --skip-networking --datadir=${DB_DIR} ..etc..etc.. ugg pour homme pas cher asics gel quantum 360 nike air huarache pas cher 

Chrooting MySQL

Now we chroot our server.

 # mkdir -p /chroot/mysql/dev # mkdir -p /chroot/mysql/bin # mkdir -p /chroot/mysql/sbin # mkdir -p /chroot/mysql/etc # mkdir -p /chroot/mysql/tmp # mkdir -p /chroot/mysql/var/tmp # mkdir -p /chroot/mysql/var/db # mkdir -p /chroot/mysql/var/log # mkdir -p /chroot/mysql/var/run # mkdir -p /chroot/mysql/usr/local/bin # mkdir -p /chroot/mysql/usr/local/libexec # mkdir -p /chroot/mysql/usr/local/share/mysql # mkdir -p /chroot/mysql/usr/libexec # mkdir -p /chroot/mysql/usr/bin # mkdir -p /chroot/mysql/usr/sbin # mkdir -p /chroot/mysql/usr/lib 

Next, the following files have to be copied into the new directory structure:

 # cp /usr/local/libexec/mysqld /chroot/mysql/usr/local/libexec/ # cp -Rv /usr/local/share/mysql /chroot/mysql/usr/local/share/ # cp /etc/hosts /chroot/mysql/etc/ # cp /etc/resolv.conf /chroot/mysql/etc/ # cp /etc/group /chroot/mysql/etc/ # cp /etc/master.passwd /chroot/mysql/etc/passwords # cp /etc/my.cnf /chroot/mysql/etc/ 

MySQL Permissions

From the files: /chroot/mysql/etc/passwords and /chroot/mysql/etc/group we must remove all of the lines except the mysql account and group. chaussure tn pour homme Next, we have to build the password database as follows (this applies only to FreeBSD):

 # cd /chroot/mysql/etc # vi group 

Remove every entry except for the sys and mysql group

 # vi passwords 

Same again, remove every entry except for root and mysql. Then change the root shell to /sbin/nologin. Officiel Christian Louboutin new balance 2017 nike air max femme You also want to change your root password to something other than your system’s password.

 # pwd_mkdb -d /chroot/mysql/etc passwords 

You will receive an error of:

 pwd_mkdb: warning, unknown root shell 

That is fine.

 # rm -rf /chroot/mysql/etc/master.passwd 

Special Considerations

As in case of the Apache web server, we have to create a special device file /dev/null:

 # ls -al /dev/null 

 crw-rw-rw- 1 root sys 2, 2 Jun 21 18:31 /dev/null 

 # mknod /chroot/mysql/dev/null c 2 2 # chown root:sys /chroot/mysql/dev/null # chmod 666 /chroot/mysql/dev/null 

We must also copy the mysql database, which contains grant tables created during MySQL installation:

 # cp -Rv /var/db/mysql /chroot/mysql/var/db/ 


Now we need to copy needed files for MySQL to run inside the chrooted enviroment.

 # install -C /bin/cat /chroot/mysql/bin/ # install -C /bin/date /chroot/mysql/bin/ # install -C /bin/hostname /chroot/mysql/bin/ # install -C /bin/ls /chroot/mysql/bin/ # install -C /bin/rm /chroot/mysql/bin/ # install -C /bin/sh /chroot/mysql/bin/ # install -C /sbin/nologin /chroot/mysql/sbin/ # install -C /usr/bin/limits /chroot/mysql/bin/ # install -C /usr/bin/nohup /chroot/mysql/bin/ # install -C /usr/bin/sed /chroot/mysql/bin/ # install -C /usr/bin/tee /chroot/mysql/bin/ # install -C /usr/bin/touch /chroot/mysql/bin/ # install -C /usr/bin/umask /chroot/mysql/bin/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ 

Note: (Use if you are running FreeBSD 5x, If you are running 4x use
 # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/lib/ /chroot/mysql/usr/lib/ # install -C /usr/libexec/ /chroot/mysql/usr/libexec/ # cp /usr/local/bin/my* /chroot/mysql/usr/local/bin/ # cp /usr/local/bin/safe_mysqld /chroot/mysql/usr/local/bin/ # install -C /usr/sbin/chown /chroot/mysql/usr/sbin/ # install -C /var/run/ /chroot/mysql/var/run/ 

The access rights to the above directories should be set as follows:

 # chown -R root:sys /chroot/mysql # chmod -R 755 /chroot/mysql # chmod 1777 /chroot/mysql/tmp # chown -R mysql:mysql /chroot/mysql/var/db/mysql 

Now let’s startup our database for the first time and test out the enviroment.

 # chroot /chroot/mysql /bin/sh # /usr/local/bin/safe_mysqld & # exit 

Now, make sure the server is running.

 # ps -ax | grep mysql 60586 p0 S 0:00.01 /bin/sh /usr/local/bin/safe_mysqld 60601 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysql --pid-file=/var 60603 p0 S+ 0:00.00 grep mysql 

Now let’s tell our MySQL client how to access the server so we don’t need to chroot ourselves every time we wish to run a query.

 # vi /etc/my.cnf 

Inside the [client] section, modify the socket option.

 socket = /chroot/mysql/tmp/mysql.sock 

MySQL User Accounts

 # mysql mysql> drop database test; mysql> use mysql; mysql> DELETE FROM user WHERE NOT (host="localhost" and user="root"); mysql> SET PASSWORD FOR root@localhost=PASSWORD('new_password'); 

Now let’s create a different name for our super user and then remove the root user.

 mysql> INSERT INTO user VALUES ('localhost','db_admin',PASSWORD('password'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); mysql> DELETE FROM user WHERE (user="root"); mysql> flush privileges; mysql> commit; mysql> quit 

Now let’s be sure this worked.

 # mysql ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO) # mysql -u db_admin -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g.  bottes ugg Adidas Zx pas cher buy ff14 gil Your MySQL connection id is 4 to server version: 3.23.58-log Type 'help;' or 'h' for help. timberland earthkeepers bottes Type 'c' to clear the buffer. asics gel pas cher mysql> u mysql mysql> SELECT * FROM user; mysql> quit 

Startup Script

Now we need to make our startup file.

 # cd /usr/local/etc/rc.d # rm # vi 

 #!/bin/sh DB_DIR=/var/db/mysql PIDFILE=${DB_DIR}/`/bin/hostname -s`.pid case "$1" in start) if [ -x /usr/local/bin/safe_mysqld ]; then /usr/bin/limits -U mysql nohup /sbin/chroot /chroot/mysql /usr/local/bin/safe_mysqld --user=mysql --skip-networking \ --datadir=${DB_DIR} --pid-file=${PIDFILE} > /dev/null & echo -n ' mysqld' fi ;; stop) if [ -f /chroot/mysql/${PIDFILE} ]; then /bin/kill `cat /chroot/mysql/${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld' else echo "mysql-server isn't running" fi ;; *) echo "" echo "Usage: `basename $0` { start | stop }" echo "" exit 64 ;; esac 

Congratulations. Save, exit, kill your current running server, and start it up with the startup script. adidas en ligne FF14 gil Now, whenever there is a new portupgrade or buildworld, it’s important to keep the binaries inside the chroot updated.

Speak Your Mind