Recent Posts

Sponsors
![]() |
Installing MySQL 5.1 on FreeBSDDawn Rossi, 05-02-2008 |
This step by step tutorial is everything you need to install MySQL 5.1 (from source) on a virgin FreeBSD 4.x - 7.x machine.
Step 1: Install wget
Step 2: Download MySQL 5.1 source code
Step 3: Create mysql user
Follow the prompts to create a mysql user and mysql group with a default directory of /usr/local/mysql
Step 4: Compile and Install MySQL with InnoDB support
Step 5: Install default MySQL tables
Step 6: Startup MySQL and connect to it
Step 7: Set MySQL to start automatically when the machine reboots
Save the file below under /usr/local/etc/rc.d and call it mysqlstart.sh
Mark it as an executable
Step 8: Customizations
The following steps are optional.
By this point you should have MySQL installed and running on your new FreeBSD machine.
The customizations below are things that we do here at SoftwareProjects and are designed to get the most juice out of MySQL 5.1 in our environment.
All of our MySQL database machines run on FreeBSD 6/7 with a minimum of 4GB memory.
Shutdown the MySQL database
Move the data directory to /usr/local/mysql/data
Save our custom MySQL configuration file under /etc/my.cnf
Note: Be sure to assign a unique server-id and note the auto-increment field - this is useful for master/master setups.
Step 9: Lift process size restrictions
FreeBSD limits process max size in memory to 512MB. We're going to want to use more for MySQL.
To lift FreeBSD process size restrictions, we have to update /boot/loader.conf with:
And then reboot the machine
Step 10: Populate database with data
Typically when we install a new database server, we want to populate it with data from another database.
The absolute easiest way to populate a secondary MySQL database machine with data from another MySQL database, is by using a script called InnoDBBackup by the makers of InnoDB.
Install both ibbackup and the Perl innobackup script on the master database machine (the one you are looking to transfer data from)
create a backup of the database (the great thing about this is you never have to take the database down):
Once the backup completes, transfer all the data over from the master to this new machine we are setting up -
On the new machine we are setting up, create a new directory to hold the backup:
On the master machine, issue this tar command:
Once the tar is done, copy ibbackup and innobackup to the destination machine under /usr/tmp and run these two commands:
Replace the 2008-05-02... with the name of your backup folder
Step 1: Install wget
cd /usr/ports/ftp/wget
make
make install
make
make install
Step 2: Download MySQL 5.1 source code
mkdir /usr/tmp
cd /usr/tmp
wget "http://www.softwareprojects.com/files/auto/mysql-5.1.40.tar.gz"
tar xvzf mysql-5.1.40.tar.gz
cd /usr/tmp
wget "http://www.softwareprojects.com/files/auto/mysql-5.1.40.tar.gz"
tar xvzf mysql-5.1.40.tar.gz
Step 3: Create mysql user
adduser
Follow the prompts to create a mysql user and mysql group with a default directory of /usr/local/mysql
Step 4: Compile and Install MySQL with InnoDB support
cd /usr/tmp/mysql-5.1.40
./configure --with-innodb
make all
make install
./configure --with-innodb
make all
make install
Step 5: Install default MySQL tables
cd /usr/local/bin
./mysql_install_db
cd /usr/local
chown -R mysql:mysql var
./mysql_install_db
cd /usr/local
chown -R mysql:mysql var
Step 6: Startup MySQL and connect to it
/usr/local/bin/mysqld_safe &
/usr/local/bin/mysql
/usr/local/bin/mysql
Step 7: Set MySQL to start automatically when the machine reboots
Save the file below under /usr/local/etc/rc.d and call it mysqlstart.sh
/usr/local/bin/mysqld_safe &
Mark it as an executable
chmod 755 /usr/local/etc/rc.d/mysqlstart.sh
Step 8: Customizations
The following steps are optional.
By this point you should have MySQL installed and running on your new FreeBSD machine.
The customizations below are things that we do here at SoftwareProjects and are designed to get the most juice out of MySQL 5.1 in our environment.
All of our MySQL database machines run on FreeBSD 6/7 with a minimum of 4GB memory.
Shutdown the MySQL database
/usr/local/bin/mysqladmin shutdown
Move the data directory to /usr/local/mysql/data
mv /usr/local/var /usr/local/mysql/data
Save our custom MySQL configuration file under /etc/my.cnf
Note: Be sure to assign a unique server-id and note the auto-increment field - this is useful for master/master setups.
Step 9: Lift process size restrictions
FreeBSD limits process max size in memory to 512MB. We're going to want to use more for MySQL.
To lift FreeBSD process size restrictions, we have to update /boot/loader.conf with:
sl_aacu_load="YES"
kern.maxdsiz="2073741824" # 2GB
kern.dfldsiz="2073741824" # 2GB
#kern.maxssiz="536870912" # 512MB
kern.maxdsiz="2073741824" # 2GB
kern.dfldsiz="2073741824" # 2GB
#kern.maxssiz="536870912" # 512MB
And then reboot the machine
Step 10: Populate database with data
Typically when we install a new database server, we want to populate it with data from another database.
The absolute easiest way to populate a secondary MySQL database machine with data from another MySQL database, is by using a script called InnoDBBackup by the makers of InnoDB.
Install both ibbackup and the Perl innobackup script on the master database machine (the one you are looking to transfer data from)
create a backup of the database (the great thing about this is you never have to take the database down):
./innobackup /etc/my.cnf /usr/local/mysql/databackup
Once the backup completes, transfer all the data over from the master to this new machine we are setting up -
On the new machine we are setting up, create a new directory to hold the backup:
mkdir /usr/local/mysql/databackup
chmod a+rw /usr/local/mysql/databackup
chmod a+rw /usr/local/mysql/databackup
On the master machine, issue this tar command:
tar -czpf - /usr/local/mysql/databackup/* | ssh -lUSERNAME NEWHOST.com tar -xzpf - -C /usr/local/mysql/databackup/
Once the tar is done, copy ibbackup and innobackup to the destination machine under /usr/tmp and run these two commands:
./innobackup --apply-log /etc/my.cnf /usr/local/mysql/databackup/2008-05-02_09-34-03/backup-my.cnf
./innobackup --copy-back /etc/my.cnf /usr/local/mysql/databackup/2008-05-02_09-34-03/backup-my.cnf
./innobackup --copy-back /etc/my.cnf /usr/local/mysql/databackup/2008-05-02_09-34-03/backup-my.cnf
Replace the 2008-05-02... with the name of your backup folder
![]() |
Michelle, 12-10-2008 |
./configure --with-plugins=innobase
![]() |
R. Owens, 12-29-2008 |
Thanks for clear description. If I am updating to a new version of MySQL, instead of installing from scratch, how would the above be modified in order to preserve existing database contents. Thanks
![]() |
Mike Peters, 02-02-2009 |
Important: If you have a lot of databases, you may get "Too many open files" EMFILE error 24.
To fix this, you need to increase the number of file descriptors.
Update your /etc/sysctl.conf with the settings below and reboot the machine before retrying:
To fix this, you need to increase the number of file descriptors.
Update your /etc/sysctl.conf with the settings below and reboot the machine before retrying:
kern.maxfiles=65535
kern.maxfilesperproc=56384
kern.maxproc=50000
vfs.vmiodirenable=1
net.inet.tcp.msl=2000
net.inet.tcp.rfc1323=1
net.inet.tcp.delayed_ack=0
net.inet.tcp.restrict_rst=1
kern.ipc.maxsockbuf=2097152
kern.ipc.somaxconn=4096
kern.ipc.maxsockets=52328
net.inet.ip.portrange.first=20000
net.inet.ip.portrange.last=65535
net.inet.ip.portrange.hifirst=20000
net.inet.ip.portrange.hilast=65535
kern.maxfilesperproc=56384
kern.maxproc=50000
vfs.vmiodirenable=1
net.inet.tcp.msl=2000
net.inet.tcp.rfc1323=1
net.inet.tcp.delayed_ack=0
net.inet.tcp.restrict_rst=1
kern.ipc.maxsockbuf=2097152
kern.ipc.somaxconn=4096
kern.ipc.maxsockets=52328
net.inet.ip.portrange.first=20000
net.inet.ip.portrange.last=65535
net.inet.ip.portrange.hifirst=20000
net.inet.ip.portrange.hilast=65535
![]() |
Chris, 04-15-2010 |
Here's a pretty good live backup procedure for mysql under FreeBSD. Dont known if it work with linux.
https://dev.mantor.org/doku.php?id=mysqlbackup
https://dev.mantor.org/doku.php?id=mysqlbackup
|

Subscribe Now to receive new posts via Email as soon as they come out.
Comments
Post your comments