FreeBSD Blog

MySQL Backup and my.cnf

October 21, 2008 Posted by KP

Check MySQL version when you restore database.

Recently I switched to a different hosting provider, I always directly tar the MySQL data directory, but this time I got lots of problems. Some websites worked well, some gave lots of table-corrupted errors. Later I found it’s due to the MySQL versions :-(. On my old server it’s 5.0, and 5.1 on the new one. Reinstalling 5.0 solved the problem.

Where is my.cnf on FreeBSD?
I got a MySQL error: “Got a packet bigger than ‘max_allowed_packet’ bytes.” The default value of ‘max_allowed_packet’ is 1MB, you can check with:
# mysqladmin variables | less

So I need to create my.cnf and change max_allowed_packet, ‘mysql –help’ has the following text:

Default options are read from the following files in the given order:
/etc/my.cnf /usr/local/etc/my.cnf /usr/local/etc/mysql/my.cnf ~/.my.cnf

Following FreeBSD standard, I put it under /usr/local/etc/. Without any other tuning, I only created a very simple my.cnf:

[mysqld]
max_allowed_packet=4M

Restart MySQL:
/usr/local/etc/rc.d/mysql-server restart

‘mysqladmin variables’ confirmed the change.

• MySQL Optimization
• Optimizing MySQL
• MySQL Start Script
• Monitor MySQL Log File
• MySQL Log File
• Using Perl and MySQL
• Upgrade to PHP 5 and MySQL 5
• Create MySQL User