Installing mysql ports on FreeBSD and backing up mysql
Posted in FreeBSD on April 20th, 2009 by taswar
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
1 2 | 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
1 | 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.
1 2 3 | cd /usr/local/share/mysql cp my-large.cnf /etc/my.cnf |
Now lets start the database (assuming you are logged in as root)
1 2 | 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:
1 | mysqladmin -u root password NEWPASSWORD |
However if you want to change (or update) a root password, then you need to use following command:
1 2 3 | 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
1 2 3 4 5 6 7 8 9 | 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
1 2 3 4 5 6 7 8 9 10 | 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)
1 2 3 4 5 6 | /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
1 2 3 | cd /usr/ports/archivers/p5-Archive-Zip make install clean |
Now when we run
1 | /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.
1 2 3 4 | 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
Leave a Comment
