Moving Mysql binary logs

In connection with the disc space problem of the binary logs, I found it was dead easy to put them on a different partition.

In /etc/mysql/my.cnf there is a line

log-bin

just change this to

log-bin = /newdirectory

Then just restart mysql.

You might want to purge the old logs first, by connecting to your database and running:

purge master logs to '.000001';

Just how simple is that!

How to clean old binary logs

Recently I had a problem with Disc space on my MythTV box. I found the the inary logs for MYSQL were over a gig in size. These are records of all database transactions. They wllo you to recreate the database if a backup is out of date, as you can replay all transactions. Anyway I was able to lose a lot of these, and regain disc space, without affecting safety.

mysql -u root --password mythconverg
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53 to server version: 4.1.21-log

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> show master logs;
+——————-+———–+
| Log_name | File_size |
+——————-+———–+
| mythtv-bin.000001 | 79 |
| mythtv-bin.000002 | 79 |
| mythtv-bin.000003 | 79 |
| mythtv-bin.000004 | 50504 |
| mythtv-bin.000005 | 2767417 |
| mythtv-bin.000006 | 42523 |
| mythtv-bin.000007 | 79 |
| mythtv-bin.000008 | 7099399 |
| mythtv-bin.000009 | 60102 |
| mythtv-bin.000010 | 23028247 |
| mythtv-bin.000011 | 13157557 |
| mythtv-bin.000012 | 3300816 |

mysql> purge master logs to ‘mythtv-bin.010’;

Repairing MythTV tables

MySQL 5.0 Reference Manual :: 5.10.4.3 How to Repair Tables

I’ve just been trying to backup my MythTV tables. Previously I’ve used PHPMyAdmin. However, I thought I would try command line and automate it.

mysqldump -u root -p mythconverg > mythconverg-`date +%F`.sql
However, I constantly got a error message
Can't open file: (errno: 145) when using LOCK TABLES mythtv

So i discovered I needed to repair the tables. The above docuimentation is pretty easy but basically.

cd /var/lib/mysql.mythconverg
myisamchk -e *.MYI

Then run the following command on each table that shows errors:
myisamchk -r -q program.MYI

Easy peaasy lemon squeezy!

My MythTV box spec!

  • Silverstone Lascala case. Very, very nice.
  • Asus K8N nForce3 250 AGP (Socket 754) Motherboard with a Sempron 3000+
  • 0.5 Gb generic ram
  • Ati Radeon 9250 (works fine and with a passive heatsink!
  • 320Gb Seagate SATA-II drive
  • Linksys PCI wifi WMP54GS card
  • A really cool wireless keyboard and trackball combo.
  • Oh and of course a pair of Nova-T Conexant chipset DVB-T cards.