Install and configure MySQL on FreeBSD

install_mysql_freebsd.md

MySQL

Install

Using the path to the current mysqlXX-server port, download, install and clean (note the MySQL version here is 5.6, but this can change as future versions get released):

sudo make config-recursive install distclean -C /usr/ports/databases/mysql56-server

Configure

To add MySQL to startup, edit the rc.conf file with:

sudo ee /etc/rc.conf

Now add the following to the files, with the bind-address argument that sets MySQL to only allow connections on the localhost. Save and exit (alternatively, use sudo sysrc mysql_enable=yes command to add each line to the end of the file):

mysql_enable="YES"
mysql_args="--bind-address=127.0.0.1"

Startup MySQL now with the following command:

sudo service mysql-server start

Once MySQL has started, configure MySQL for a production enviroment with the following (be sure to generate a password for root user when prompted, for the rest hit RETURN for the default settings):

sudo mysql_secure_installation

Next start the MySQL terminal interface with the following (inputting the password when prompted):

mysql -u root -p

Change the root user's username to something a little more entropic and flush the currently loaded priveledges for it to take effect with:

UPDATE mysql.user set user = '[username]' where user = 'root';
flush privileges;

Exit the MySQL CLI (exit) and login again (entering password again when prompted) using the new username to ensure the change was successful:

mysql -u [username] -p

MySQL does not create a config file (.cnf) for customisation by default, but it does have a default config file that should not be edited directly. To setup a customisable config file for MySQL, copy my-default.cnf to /var/db/mysql/my.cnf:

sudo cp /usr/local/share/mysql/my-default.cnf /var/db/mysql/my.cnf

To see what the currently loaded MySQL variables are, use the following MySQL command (will need to do in terminal with the command above to enter mode, or perhaps a MySQL program like Sequel Pro)(use SHOW VARIABLES LIKE '%variable_name%' to show a specific loaded variable):

SHOW VARIABLES

Edit the MySQL config file using the below command, then remove the warning at the top of the file about editing this file:

sudo ee /var/db/mysql/my.cnf

Uncomment and change the innodb_buffer_pool_size to the required size (note the MySQL commentation that recommends if the system is used as a dedicated server it is set to 70% of the total server RAM. i.e. 512mb / 100 * 70 = ~358mb)

innodb_buffer_pool_size = 358M

Add max_connections directive with a required value to set the number of concurrent MySQL requests (note max_connections is max_connections + 1 for admin usage). Note that there is no easy way to calculate this, MySQL say it "depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time":

max_connections = 501

Add query_cache_size directive and set it to either 0 to disable the query cache size limit, or very small value (Warning: setting this too high can slow down the system. Benchmarking and research will be needed):

query_cache_size = 0

Restart MySQL with:

sudo service mysql-server restart

install_mysql_freebsd.sh

#!/bin/sh

MYSQL_PORT_DIR="/usr/ports/databases/mysql56-server";
MYSQL_CNF_DIR="/var/db/mysql/my.cnf";
RC_CNF_DIR="/etc/rc.conf";

# set_line()
#
# Searches a file for an old string in each line of the file. If the
# old string is found within a line, the entire contents of the line
# gets replaced with a new string. Else (if the old string is not
# found) the new string gets added to the last line of the file.
#
# Uses sed command (BSD version, not GNU)
# @author Will Squire <will_squire@hotmail.co.uk>
#
# @example set_line "max_connections =" "max_connections = 501" /var/db/mysql/my.cnf
#
# @param $old_string
# @param $new_string
# @param $file
set_line() {
   sed -i '' '/.*'"$1"'.*/{
h
s/.*/'"$2"'/
}
${
x
/^$/{
s//'"$2"'/
H
}
x
}' $3
}

#####################################
# Installation
#####################################

# Install MySQL if not already installed
if ! pkg info mysql56-server; then
    # Port's config file has already been run through. This is the result
    # of running the config command (to avoid choosing compile options
    # interactively)
    cat <<"MAKEFILE" > "$MYSQL_PORT_DIR/Makefile"
    # Created by: Alex Dupre <ale@FreeBSD.org>
    # $FreeBSD: head/databases/mysql56-server/Makefile 413746 2016-04-21 16:43:14Z swills $

    PORTNAME?=	mysql
    PORTVERSION=	5.6.30
    PORTREVISION?=	0
    CATEGORIES=	databases ipv6
    MASTER_SITES=	MYSQL/MySQL-5.6
    PKGNAMESUFFIX?=	56-server

    MAINTAINER=	ale@FreeBSD.org
    COMMENT?=	Multithreaded SQL database (server)

    BROKEN_powerpc64=	Does not build

    SLAVEDIRS=	databases/mysql56-client
    USES=		cmake shebangfix

    CMAKE_ARGS+=	-DINSTALL_DOCDIR="share/doc/mysql" \
            -DINSTALL_DOCREADMEDIR="share/doc/mysql" \
            -DINSTALL_INCLUDEDIR="include/mysql" \
            -DINSTALL_INFODIR="info" \
            -DINSTALL_LIBDIR="lib/mysql" \
            -DINSTALL_MANDIR="man" \
            -DINSTALL_MYSQLDATADIR="/var/db/mysql" \
            -DINSTALL_MYSQLSHAREDIR="share/mysql" \
            -DINSTALL_MYSQLTESTDIR="share/mysql/tests" \
            -DINSTALL_PLUGINDIR="lib/mysql/plugin" \
            -DINSTALL_SBINDIR="libexec" \
            -DINSTALL_SCRIPTDIR="bin" \
            -DINSTALL_SHAREDIR="share" \
            -DINSTALL_SQLBENCHDIR="share/mysql" \
            -DINSTALL_SUPPORTFILESDIR="share/mysql" \
            -DWITH_EDITLINE=system \
            -DWITH_LIBWRAP=1

    SHEBANG_FILES=	scripts/*.pl* scripts/*.sh

    .ifdef USE_MYSQL
    .error You have `USE_MYSQL' variable defined either in environment or in make(1) arguments. Please undefine and try again.
    .endif

    # MySQL-Server part
    .if !defined(CLIENT_ONLY)
    USE_MYSQL=	yes
    WANT_MYSQL_VER=	56

    USES+=		perl5
    USE_PERL5=	run

    CONFLICTS_INSTALL=	mysql5[0-57-9]-server-* \
                mariadb*-server-* \
                percona*-server-*

    USE_RC_SUBR=	mysql-server

    USERS=		mysql
    GROUPS=		mysql

    MMAN1=		my_print_defaults.1 myisam_ftdump.1 myisamchk.1 myisamlog.1 myisampack.1 \
            mysql.server.1 mysql_convert_table_format.1 mysql_fix_extensions.1 \
            mysql_install_db.1 mysql_plugin.1 mysql_secure_installation.1 mysql_setpermission.1 \
            mysql_tzinfo_to_sql.1 mysql_upgrade.1 mysql_zap.1 mysqlbug.1 \
            mysqld_multi.1 mysqld_safe.1 mysqldumpslow.1 mysqlhotcopy.1 mysqlman.1 \
            mysqltest.1 perror.1 replace.1 resolve_stack_dump.1 resolveip.1

    CMAKE_ARGS+=	-DWITH_EMBEDDED_SERVER="ON"
    .else
    USES+=		libedit
    .endif

    post-patch:
        @${REINPLACE_CMD} 's/*.1/${MMAN1}/' ${WRKSRC}/man/CMakeLists.txt

    .include <bsd.port.pre.mk>

    .if ${ARCH} == "armv6"
    EXTRA_PATCHES+=		${FILESDIR}/extra-patch-config.h.cmake
    .endif

    .include <bsd.port.post.mk>
MAKEFILE

    # Download and install
    make install distclean -DBATCH -C $MYSQL_PORT_DIR;
fi

#####################################
# Configuration
#####################################

## Set environment variables in rc.conf
set_line "mysql_enable=" 'mysql_enable="YES"' $RC_CNF_DIR;
set_line "mysql_args=" 'mysql_args="--bind-address=127.0.0.1"' $RC_CNF_DIR;

# Start MySQL if not already running
if ! service mysql-server status; then
    service mysql-server start;
fi

# Copy MySQL's default configuration file for editing
sudo cp /usr/local/share/mysql/my-default.cnf $MYSQL_CNF_DIR;

# Set MySQL variables in my.cnf
set_line "innodb_buffer_pool_size =" "innodb_buffer_pool_size = 358M" $MYSQL_CNF_DIR;
set_line "max_connections =" "max_connections = 501" $MYSQL_CNF_DIR;
set_line "query_cache_size =" "query_cache_size = 0" $MYSQL_CNF_DIR;

# Restart MySQL
service mysql-server restart

can i suggest a tiny change?

:4 MYSQL_CNF_DIR="/usr/local/etc/my.cnf"; <-- easyer to find after install
:141 set_line "mysql_optfile='" $MYSQL_CNF_DIR; <-- not sure if this is the way shell script isnt my thing :P
:148 sudo cp /usr/local/share/mysql/my-default.cnf $MYSQL_CNF_DIR;