Installing mysql ports on FreeBSD and backing up mysql

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

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

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.

Now lets start the database (assuming you are logged in as root)
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:

However if you want to change (or update) a root password, then you need to use following command:

 

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

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

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)

So lets fix it and install the Archive/Zip perl module

Now when we run

We should have a mysqldump stored in /database/mysql-backup

Lets now add it to the crontab at the end of the file.

This will backup mysql everyday at 11:00 pm