dan.thoughts

Here is the bash shell script that makes archived dumps of your database server. All databases are separated from each other and stored into date based folders.

#!/bin/bash

MyUSER="root"
MyPASS=""
MyHOST="localhost"
NOW="$(date +"%d-%m-%Y")"
STOREDIR="/home/storage/backup/database/by_dates/$NOW"
DBLIST="$(mysql -u $MyUSER -h $MyHOST -Bse 'show databases')"

[ ! -d $STOREDIR ] && mkdir -p $STOREDIR || :

for db in $DBLIST
do
	FILE="$STOREDIR/$db.gz"
	mysqldump -u $MyUSER -h $MyHOST $db | gzip -9 > $FILE
done

Here is a completely useless filesystem based on MySQL database storage – mysqlfuse, implemented with Fuse.
I didnt find any way how i can use it, but meanwhile, this fs working. Not perfect of course, in that case its not maintained for a long time. Doesnt support information about free drive space, so any filemanager keeps saying ‘Error: No space left on device’. Such case making it more useless.

It`s really easy to set it up.

First, we need to install developer headers for fuse:

$ apt-get install libfuse-dev

Next, getting sources (32bit only, not working in 64bit):

$ wget http://voxel.dl.sourceforge.net/sourceforge/mysqlfs/mysqlfs-0.4.0-rc1.tar.bz2

Unpack it, and compile:

$ tar -xjvf mysqlfs-0.4.0-rc1.tar.bz2
$ cd mysqlfs-0.4.0-rc1
$ ./configure && make && make install

Next, we need to setup the database

CREATE DATABASE mysqlfs;
GRANT SELECT, INSERT, UPDATE, DELETE ON mysqlfs.* TO mysqlfs@"%" IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

And create database schema. SQL file located in root folder of the sources

$ mysql -uroot -p mysqlfs < schema.sql

And finally, mount filesystem to some folder:

$ mysqlfs -ohost=MYSQLHOST -ouser=MYSQLUSER -opassword=MYSQLPASS -odatabase=mysqlfs MOUNT_DIR

Now, its gonna be working. To use automatic configuration parameters you can create section [mysqlfs] in your mysql configuration file (my.cnf)

Parameters:

-ohost=
    MySQL server host

  -ouser=
    MySQL username

  -opassword=

    MySQL password

  -odatabase=
    MySQL database name

That`s it. Anyway, using FUSE there is a way to create so weird filesystems proxy. For example, there is SQLite over FUSE. And it is too old. Next time i`ll write about Amazon S3 over FUSE projects.

This is a simple piece of code which describing how to update special order field in table. For example, you have table ‘photos’:

+ --------------------------------- +
| ID    | UserID | OrderID | ImgURL |
+ --------------------------------- +
| 1132  | 1      | 0       | ...    |
| 2124  | 1      | 0       | ...    |
| 3456  | 1      | 0       | ...    |
.....................................
| N     | 1      | 0       | ...    |
+ --------------------------------- +

OrderID field value by default is 0. If some images has been deleted order lose its consistency.
To repair order we can use single transaction:

BEGIN;
 
SET @ORDER := 0;
UPDATE photos
SET order_id = (SELECT @ORDER := @ORDER + 1)
WHERE user_id = 1;
 
COMMIT;

After executing this on table ‘images’ we`ll get such result:

+ --------------------------------- +
| ID    | UserID | OrderID | ImgURL |
+ --------------------------------- +
| 1132  | 1      | 1       | ...    |
| 2124  | 1      | 2       | ...    |
| 3456  | 1      | 3       | ...    |
.....................................
| N     | 1      | M       | ...    |
+ --------------------------------- +

Some developers instead of using resources of mysql script language trying to solve this problem by executing numerous call, such ‘UPDATE table SET order_id = [value] WHERE id = [id] LIMIT 1;’. It`s wrong. MySQL language have a lot of ways to make it easy. At least UDF`s.

There is a small useful ruby script to backup your MySQL databases in small projects, where speed of backup not so important.
Source:

#!/usr/bin/ruby
# MySQL Backup Utility
# Usage: ./mysql_backup.rb or ruby mysql_backup.rb
 
$backup_archive = true # gzip files after processing
$backup_dir = "/home/storage/backup/" # output directory
$backup_template = "project-%s-%s.sql" # text-%dbname-%timestamp.sql
$backup_cmd = "mysqldump -u local_backup --add-drop-table --databases %s > %s"
$backup_dblist = [ # list of databases to backup
  'main',
  'users',
  'admin',
  'cards'
] 
 
def backup_database(database)
  time = Time.now()
  time_str = sprintf("%02i-%02i-%04i-%02i%02i%02i",time.day, time.month, time.year, time.hour, time.min, time.sec)
  filename = sprintf($backup_template,database,time_str)
  filename = "#{$backup_dir}#{filename}"
 
  cmd = sprintf($backup_cmd,database,filename)
  if system(cmd) then
    system("gzip --best #{filename}") if $backup_archive
  end
end
 
$backup_dblist.each do |db|
  puts "Processing database... #{db}"
  backup_database(db)
end

Paste: http://pastie.org/341839