Install and configure MySQL on FreeBSD
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
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
#!/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