MySQL on FreeBSD 6.2

Prerequisites:

 FreeBSD (Base + Autoconf, Automake, Bash and GCC)
OpenSSL v0.9.8a or higher

Overview:
 Step #1 - Download, Unzip, Untar, Configure, Compile and Install MySQL Server
 Step #2 - Add users, manually start the server and perform basic tests
 Step #3 - Copy and test the "rc" boot time startup script
 Step #4 - Tuning

Step #1

MySQL – The open source database server of choice

Home: 

 http://www.mysql.com

# Add a “mysql” user to your system: (MySQL should not need to run as root!)
 pw groupadd mysql
 pw useradd mysql -n mysql -G mysql -s /usr/sbin/nologin

# Download (Notes: Users behind a Proxy Server should read this and users without Internet but have a CDRom read this.)
cd /usr/src
fetch http://mysql.he.net/Downloads/MySQL-5.0/mysql-5.0.27.tar.gz

# Unzip and Untar
tar xvf mysql-?.?.??.tar.gz

# Configure
cd mysql-?.?.??
./configure --prefix=/opt/mysql --with-mysqld-user=mysql --with-mysqlmanager --enable-thread-safe-client --enable-assembler --with-tcp-port=3306 --without-debug --with-openssl=/usr/bin/openssl --with-openssl-includes=/usr/include --with-openssl-libs=/usr/lib

# Compile
make all

# Install
make install

 

Step #2

Add users, manually start the server and perform basic tests

# Install the base database
/opt/mysql/bin/mysql_install_db

# Set permissions to correct owner
chown -R mysql:mysql /opt/mysql/

# Start the server
/opt/mysql/bin/mysqld_safe &

# Perform basic checks
/opt/mysql/bin/mysqladmin version
/opt/mysql/bin/mysqladmin variables

# Set a Password!
/opt/mysql/bin/mysqladmin -u root password mysqlpassword

# Add a "database" user with password and grant that user permission to connect
/opt/mysql/bin/mysql --user=root --password mysql
mysqlpassword

GRANT ALL PRIVILEGES ON *.* TO mysql@localhost IDENTIFIED BY 'mysqlpassword' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO mysql@"%" IDENTIFIED BY 'mysqlpassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;
exit

# Run some tests before going into production (optional)
# /opt/mysql/mysql-test/mysql-test-run --force

 

Step #3

Copy and test the "rc" boot time startup script

# Copy the "rc" startup script
cp /usr/src/mysql-5.0.22/support-files/mysql.server /usr/local/etc/rc.d/mysql.sh
chmod +x /usr/local/etc/rc.d/mysql.sh

# Test the "rc" script
/usr/local/etc/rc.d/mysql.sh restart
/usr/local/etc/rc.d/mysql.sh stop
/usr/local/etc/rc.d/mysql.sh start
/usr/local/etc/rc.d/mysql.sh reload

# Clean up
cd /usr/src/mysql-5.0.22
make clean

# Perform basic checks using the password
/opt/mysql/bin/mysqladmin --user=root --password version
mysqlpassword
/opt/mysql/bin/mysqladmin --user=root --password variables
mysqlpassword

# Edit your /etc/my.cnf
# See my example my.cnf

# Get the free MySQL Administrator to optimize and test your settings! UNTESTED AT THIS POINT DO NOT USE
 # cd /usr/ports/devel/libglade2
# make install clean
# cd /usr/ports/x11-toolkits/gtkmm24
# make install clean
# cd /usr/ports/databases/mysql-administrator
# make install clean
# fetch http://mysql.he.net/Downloads/MySQLGUITools/mysql-gui-tools-5.0r2.tar.gz
 

Step #4

Tune up MySQL to boost performance

# Edit /etc/my.cnf
vi /etc/my.cnf
max_connections = 1000
max_heap_table_size = 128M

key_buffer = 32M
query_cache_size = 32M
innodb_buffer_pool_size = 32M
myisam_sort_buffer_size = 64M
read_buffer_size = 2M
sort_buffer_size = 2M
table_cache = 512
thread_cache = 12

# Restart MySQL
/usr/local/etc/rc.d/mysql.sh restart

# Check out your MySQL settings and tune /etc/my.cnf do more as desired
/opt/mysql/bin/mysqladmin -u root -p var ext stat ver