Here is another post moved over from old blog about mysql installation on FreeBSD.
Installing the mysql ports
I will be using mysql50-server from the ports section of FreeBSD 7.0
cd /usr/ports/databases/mysql50-server
make install clean
The above command will build and store the database in /var/db/mysql
One will also need to add to rc.conf file to start mysql
echo mysql_enable="YES" >> /etc/rc.conf
The scripts of where the database to start is stored at /usr/local/etc/rc.d/mysql-server
But before we start it, we will create a my.cnf file and store it in /etc/my.cnf
By default the install provides a list of my.cnf files stored in /usr/local/share/mysql
I will be using the my-large.cnf since I know I will be using large data tables, but choose one that you find fits your needs.
cd /usr/local/share/mysql
cp my-large.cnf /etc/my.cnf
Now lets start the database (assuming you are logged in as root)
cd /usr/local/etc/rc.d
./mysql-server start
Now that the database has started, lets set the root password for mysql
To setup root password for first time, use mysqladmin command at shell prompt as follows:
mysqladmin -u root password NEWPASSWORD
However if you want to change (or update) a root password, then you need to use following command:
mysqladmin -u root -p oldpassword newpass
Enter password:
Backup of Mysql Database
For backup of the database I will be using mysqlblasy.pl
On can download mysqlblasy.pl from http://pol.spurious.biz/projects/scripting/mysqlblasy.php#download
I will be using mysqlblasy verison 0.73
cd /usr/local/src
wget http://pol.spurious.biz/projects/scripting/mysqlblasy/mysqlblasy-0.73.tgz
tar zxvf mysqlblasy-0.73.tgz
cd mysqlblasy-0.73
cp mysqlblasy.pl /usr/local/sbin/mysqlblasy.pl
In order for mysqlblasy to run one needs to have the configuration file ready for it
Here is a sample configuration file that I placed in /etc/mysqlblasy.conf
dbusername = root
dbpassword = NEWPASSWORD
dbhost = localhost
backupdir = /database/mysql-backup/
loglevel = 2
mysql = /usr/local/bin/mysql
mysqldump = /usr/local/bin/mysqldump
use compression = yes
use syslog = yes
keep = 7
This will allow root user to use mysqldump with password provided above and it will rotate the logs 7 days, compress it and store it at /database/mysql-backup
Lets try to run the command now (I got an error since I have not got some of the Perl modules in)
/usr/local/sbin/mysqlbalsy.pl
We will get the error below:
Can't locate Archive/Zip.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.8/BSDPAN /usr/local/lib/perl5/site_perl/5.8.8/mach /usr/local/lib/perl5/site_perl/5.8.8 /usr/local/lib/perl5/site_perl /usr/local/lib/perl5/5.8.8/mach /usr/local/lib/perl5/5.8.8 .) at /usr/local/sbin/mysqlblasy.pl line 1340.
BEGIN failed--compilation aborted at /usr/local/sbin/mysqlblasy.pl line 1340.
So lets fix it and install the Archive/Zip perl module
cd /usr/ports/archivers/p5-Archive-Zip
make install clean
Now when we run
/usr/local/sbin/mysqlblasy.pl
We should have a mysqldump stored in /database/mysql-backup
Lets now add it to the crontab at the end of the file.
vi /etc/crontab
#Back up mysql database at 11:00 pm everyday
0 23 * * * root /usr/local/sbin/mysqlblasy.pl
This will backup mysql everyday at 11:00 pm