Posted by Dan Sosedoff
on September 18, 2009
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
Posted by Dan Sosedoff
on May 02, 2009
Here is a simple example how to make native WebDAV client with Ruby sockets. No additional gems or extensions needed – just all basic classes.
class WebDAV
attr_reader :host, :port, :protocol, :chunk_size
@socket = nil
def initialize(host,port=80,protocol='HTTP/1.1',chunk=8096)
@host = host.to_s
@port = port.to_i
@protocol = protocol
@chunk_size = chunk.to_i
end
def build_header(method, path, content_length=nil)
header = "#{method} #{path} #{@protocol} \r\n"
header += "Content-Length: #{content_length}\r\n" if !content_length.nil?
header += "Host: #{@host}\r\n"
header += "Connection: close\r\n\r\n"
return header
end
def request(method, path)
open
header = build_header(method, path)
if @socket.write(header) == header.length then
return @socket.gets.split[1]
end
end
def delete(path)
request('DELETE', path)
end
def head(path)
request('HEAD', path)
end
def mkcol(path)
request('MKCOL', path)
end
def put(path, localfile, auto_head=true)
if !File.exists?(localfile) || !File.readable?(localfile)
raise "File not exists or not accessible for reading!"
end
open
datalen = File.size(localfile)
header = build_header('PUT', path, datalen)
begin
if @socket.write(header) == header.length then
written = 0
File.open(localfile,'r') do |f|
until f.eof? do
written += @socket.write(f.read(@chunk_size))
end
end
if written == datalen
close
if !auto_head
return true
else
return head(path)
end
end
end
rescue Exception => e
puts e
return false
end
end
def open
begin
@socket = TCPSocket.open(@host,@port)
return true
rescue Exception => e
puts e
return false
end
end
def close
begin
return @socket.close
rescue
return false
end
end
endThis class supports only basic http/dav methods (PUT, DELETE, MKCOL, HEAD) and can be extended very easily and designed to work with all files, reading them by small chunks (default is 8096 bytes).
Im using this class sometimes with nginx.
Deps:
require 'socket'
require 'digest'
Usage:
# create connection
conn = WebDAV.new('your.host.com')
# upload file (without autocheck), return true/false value
result = conn.put('/test.mp3','/home/.../..../..../file.mp3', false)
# upload file with autocheck, returns http response code (201, 404, ... ) so you`ll know what exactly happened
result = conn.put('/test2.mp3','/home/.../file.mp3')Also, here is a wrapper class to produce MD5, SHA1 file hashes that supports big files.
class FileHash
def self.md5(path)
d = Digest::MD5.new
File.open(path,'r') do |f|
d.update(f.read(8192)) until f.eof?
end
return d.hexdigest
end
def self.sha1(path)
d = Digest::SHA1.new
File.open(path,'r') do |f|
d.update(f.read(8192)) until f.eof?
end
return d.hexdigest
end
end
Usage:
FileHash.md5('/path/to/file')
FileHash.sha1('/path/to/file')This webdav class not pretending to be stable in production environment, but can be useful for some “one-time” tasks with less code.
Posted by Dan Sosedoff
on April 06, 2009
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.
Posted by Dan Sosedoff
on February 05, 2009
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.
Posted by Dan Sosedoff
on January 15, 2009
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)
endPaste: http://pastie.org/341839
