RDBMS: MySQL

Configuration

by ross at 00:40:30 on February 5, 2015

Install:

# cd /usr/ports/databases/mysql56-server
# make install clean
# rehash

Optionally you can move data dir from default /var/db/mysql to, for example, /home/mysql.

Add to /etc/rc.conf:

mysql_enable="YES"
mysql_dbdir="/home/mysql"

Initialize mysql datadir. Copy one of the /usr/local/share/my-*.cnf to /home/mysql.

# mysql_install_db --basedir=/usr/local --datadir=/home/mysql --user=mysql
# cp /usr/local/share/mysql/my-large.cnf /home/mysql/my.cnf
# chown -R mysql:mysql /home/mysql

Edit my.cnf, for example, uncomment skip-networking or at least set bind-address = 127.0.0.1. And probably increase query_cache_size to 64M and max_allowed_packet to 128M, for example.

By default binlog files are not limited. So in time there will be gigabytes of mysql-bin.nnnnnn files in the dbdir. To limit binlog add the following:

expire_logs_days        = 10
max_binlog_size         = 512M

Start the daemon:

# service mysql-server start

Secure installation by setting root's password and removing anonymous user and test database:

# mysql_secure_installation

Consider installing phpMyAdmin for administration of MySQL.

Root's password

Create /root/.my.cnf:

[mysql]
user=root
password=ROOTS_PASSWORD_HERE

[client]
user=root
password=ROOTS_PASSWORD_HERE
# chmod 600 /root/.my.cnf

This way mysql or mysqlbackup won't ask for root's password (useful for root's backup scripts).

Upgrading MySQL

After minor version upgrade run mysql_upgrade to check the databases:

# portupgrade mysql\*
# service mysql-server restart
# mysql_upgrade

On-server everyday backups

# cd /usr/ports/databases/mysqlbackup
# make install clean
# rehash

Add to /etc/periodic.conf:

mysqlbackup_enable="YES"
mysqlbackup_args="-a -u root -z bzip2"

This will keep 5 days of backups in /var/backups. man mysqlbackup will tell you more.

Reset forgotten root's password

Add to existing mysql lines in /etc/rc.conf the following line:

mysql_args="--skip-grant-tables --skip-networking"
# service mysql-server restart
# mysql -u root
mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

Now delete the mysql_args line from /etc/rc.conf and restart the mysql once again.

Comments