≡ Menu

database server

Razvan talk about hooking Amarok to MySQL database server and create an MP3 file server. I this is an excellent hack:

When it comes to playing music in Linux, Amarok is one of the best audio players out there. It offers almost everything you need, from a clean, intuitive interface to a range of useful scripts. You can even put it on a server and give it a Web interface.

ObsidianMusic was previously known as amaroK Web Frontend. It is a collection of scripts that, combined with a MySQL database and the Amarok player, forms an excellent way of sharing MP3 files over the Internet or a small office network. All the music files in your collection can be played, downloaded, or streamed by a regular Web browser. Furthermore, ObsidianMusic offers search capabilities and music sorting, and can be customized using themes.

=> Create an MP3 file server using Amarok and ObsidianMusic

MySQL Proxy Load balancing and Failover Tutorial

MySQL Proxy is a simple and new program that sits between your client and MySQL server(s) that can monitor, analyze or transform their communication. Its flexibility allows for a wide variety of use cases, including:
a) Load balancing
b) Failover
c) Query analysis
d) Query filtering and modification
e) and many more...

MySQL Proxy tutorial

Oreilly has published a nice tutorial using MySQL proxy application:

MySQL Proxy is a lightweight binary application standing between one or more MySQL clients and a server. The clients connect to the Proxy with the usual credentials, instead of connecting to the server. The Proxy acts as man-in-the-middle between client and server.

In its basic form, the Proxy is just a redirector. It gets an empty bucket from the client (a query), takes it to the server, fills the bucket with data, and passes it back to the client.

If that were all, the Proxy would just be useless overhead. There is a little more I haven't told you yet. The Proxy ships with an embedded Lua interpreter. Using Lua, you can define what to do with a query or a result set before the Proxy passes them along.

MySQL Proxy Load balancing and Failover Tutorial - Logo

Download MySQL proxy

You can download MySQL proxy here

nixCraft FAQ Roundup May 06, 2007

Recently updated/posted Linux and UNIX FAQ (mostly useful to Linux/UNIX new administrators or users) :

Enjoy!

LVM is an implementation of a logical volume manager for the Linux kernel. The biggest advantage is that LVM provides the ability to make a snapshot of any logical volume.

In a production environment many users access the same file (file is open) or database. Suppose you start backup process when a file is open, you will not get correct or updated copy of file i.e. inconsistent backup (see accurate definition of inconsistent backup).

Read-only partition - to avoid inconsistent backup

You need to mount partition as read only, so that no one can make changes to file and make a backup:
# umount /home
# mount -o ro /home
# tar -cvf /dev/st0 /home
# umount /home
# mount -o rw /home

As you see, the draw back is service remains unavailable during backup time to all end users. If you are using database then shutdown database server and make a backup.

Logical Volume Manager snapshot to avoid inconsistent backup

This solution will only work if you have created the partition with LVM. A snapshot volume is a special type of volume that presents all the data that was in the volume at the time the snapshot was created. This means you can back up that volume without having to worry about data being changed while the backup is going on, and you don't have to take the database volume offline while the backup is taking place.

# lvcreate -L1000M -s -n dbbackup /dev/ops/databases

Output:

lvcreate -- WARNING: the snapshot must be disabled if it gets full
lvcreate -- INFO: using default snapshot chunk size of 64 KB for "/dev/ops/dbbackup"
lvcreate -- doing automatic backup of "ops"
lvcreate -- logical volume "/dev/ops/dbbackup" successfully created

Create a mount-point and mount the volume:
# mkdir /mnt/ops/dbbackup
# mount /dev/ops/dbbackup /mnt/ops/dbbackup

Output:

mount: block device /dev/ops/dbbackup is write-protected, mounting read-only

Do the backup
# tar -cf /dev/st0 /mnt/ops/dbbackup

Now remove it:
# umount /mnt/ops/dbbackup
# lvremove /dev/ops/databases

Please note that LVM snapshots cannot be used with non-LVM filesystems i.e. you need LVM partitions. You can also use third party commercial proprietary (see below for discussion) or GPL backup solutions/software.

MySQL Backup: Using LVM File System Snapshot

Login to your MySQL server:
# mysql -u root -p
At mysql prompt type the following command to closes all open tables and locks all tables for all databases with a read lock until you explicitly release the lock by executing UNLOCK TABLES. This is very convenient way to get backups if you have a file system such as Veritas or Linux LVM or FreeBD UFS that can take snapshots in time.
mysql> flush tables with read lock;
mysql> flush logs;
mysql> quit;

Now type the following command (assuming that your MySQL DB is on /dev/vg01/mysql):
# lvcreate --snapshot –-size=1000M --name=backup /dev/vg01/mysql
Again, login to mysql:
# mysql -u root -p
Type the following to release the lock:
mysql> unlock tables;
mysql> quit;

Now, move backup to tape or other server:
# mkdir -p /mnt/mysql
# mount -o ro /dev/vg01/backup /mnt/mysql
# cd /mnt/mysql
# tar czvf mysql.$(date +"%m-%d%-%Y).tar.gz mysql
# umount /mnt/tmp
# lvremove -f /dev/vg01/backup

If you are using a Veritas file system, you can make a backup like this (quoting from the official MySQL documentation):

Login to mysql and lock the tables:
mysql> FLUSH TABLES WITH READ LOCK;
mysql> quit;

Type the following at a shell prompt
# mount vxfs snapshot
Now UNLOCK TABLES:
mysql> UNLOCK TABLES;
mysql> quit;

Copy files from the snapshot and unmount the snapshot.

Further reading: