Broke my database

Last Friday, whilst I was fiddling with innodb performance settings, I apparently managed to totally hose any innodb tables. My blag and my dspam signatures were, thankfully, the only ones that ate it. Of course, I didn’t have backups, so that kinda sucked. I restored the blag from the most recent backup I had (February 12th) and then recreated manually the few posts I knew were ahead of that.

I hacked up some backup scripts in ruby, I probably could have done it in pure bash, but I felt that this was cleaner.

I’ve got two scripts, one for mysql, and one for the postgresql database. They’re almost exactly the same, except for the differences in how to talk to the database, and the interfacing objects. They create one .sql file for each database, and that will automatically adapt when you add more databases. It will keep around 7 copies of each file, so if you run it daily, you’ll have a week’s worth of database backups. They’re bzip2 –best’d so that it doesn’t waste too much space.

MySQL:

#!/usr/bin/ruby

require "mysql"
require "fileutils"

# designed to be run daily, will keep #{files} number of files around

backupDir = "/srv/database/backup/mysql"
username = "SOMEUSER"
password = "PASSNERD"
host = "localhost"
files = 7

FileUtils.mkdir_p backupDir

currentDir = FileUtils.pwd()
FileUtils.cd(backupDir)
begin
        dbh = Mysql.real_connect(host, username, password)
        result = dbh.query("show databases;")
        while row = result.fetch_row do
                begin
                        dbname = row[0]
                        # do file rotation stuffs
                        FileUtils.rm("#{dbname}.#{files}.sql.bz2", :force => true) # never will complain
                        (1..(files-1)).each do |a|
                                n = files -a
                                FileUtils.mv("#{dbname}.#{n}.sql.bz2", "#{dbname}.#{n+1}.sql.bz2", :force => true) #no errors
                        end
                        # move the one without a number to 1.sql.bz2
                        FileUtils.mv("#{dbname}.sql.bz2", "#{dbname}.1.sql.bz2", :force => true)

                        #dump the sql!
                        command = "mysqldump -u #{username} -p'#{password}' -h #{host} #{dbname} | bzip2 --best > #{dbname}.sql.bz2"
                        `#{command}`
                rescue Mysql::Error => err
                        # note it and continue with the next database
                        p "Database: #{dbname}\n\terr"
                end
        end
        result.free
rescue Mysql::Error => e
        puts "Error code: #{e.errno}"
        puts "Error message: #{e.error}"
        puts "Error SQLSTATE: #{e.sqlstate}" if e.respond_to?("sqlstate")
ensure
        dbh.close if dbh
        FileUtils.cd(currentDir)
end

Postgresql:

#!/usr/bin/ruby

require "pg"
require "fileutils"

# designed to be run daily, will keep #{files} number of files around

backupDir = "/srv/database/backup/postgres"
username = "SOMEUSER"
password = "PASSNERD"
host = "localhost"
files = 7

FileUtils.mkdir_p backupDir

currentDir = FileUtils.pwd()
FileUtils.cd(backupDir)
begin
        conn = PGconn.new(:user => username, :password=> password, :host=>host)
        result = conn.exec("select datname from pg_database")
        result.each do |row|
                begin
                        dbname = row['datname']
                        if !( /template.*/ =~ dbname)
                                # do file rotation stuffs
                                FileUtils.rm("#{dbname}.#{files}.sql.bz2", :force => true) # never will complain
                                (1..(files-1)).each do |a|
                                        n = files - a
                                        FileUtils.mv("#{dbname}.#{n}.sql.bz2", "#{dbname}.#{n+1}.sql.bz2", :force => true) #no errors
                                end
                                # move the one without a number to 1.sql.bz2
                                FileUtils.mv("#{dbname}.sql.bz2", "#{dbname}.1.sql.bz2", :force => true)

                                #dump the sql!
                                command = "PGPASSWORD='#{password}' pg_dump -U #{username} -h #{host} #{dbname} | bzip2 --best > #{dbname}.sql.bz2"
                                `#{command}`
                        end
                rescue PGError => err
                        # note it and continue with the next database
                        p "Database: #{dbname}\n\terr"
                end
        end
        result.clear
rescue PGError => e
        p e
ensure
        conn.close if conn
        FileUtils.cd(currentDir)
end

Be Sociable, Share!

•