 |
||||||||
 |
|
|
|
|
|
|
|
|
|
RitmarkFS: | FileSystem Storage Engine for | MySQL | |||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
| site last updated: November 21, 2006 |
| Application Examples |
|
This page is focused on presenting some examples and ideas of where RitmarkFS-enabled system can be
used to give an advantage over existing solutions.
An FTP Alternative Moved here. Filesystem Backup This is fairly simple. First you need to create a table that covers the part of filesystem which needs to be backed-up:
-- Windows
mysql> CALL mysql.ritmarkfs_create_legacy('test.c$$ritmarktest');
Query OK, 0 rows affected (0.00 sec)
-- Linux
mysql> CALL mysql.ritmarkfs_create_legacy('test.$tmp$ritmarktest');
Query OK, 0 rows affected (0.00 sec)
Now it's possible to make backups using usual MySQL backup tools, such as mysqldump:
c:\projects\mysql-5.1.9-beta\client_debug>mysqldump --user=root test > fs.sql Finding Files with Duplicate Content Basically this is done via a join. If to find duplicate files in the same table the table needs to be self-joined as in the following example. Suppose there's a directory c:\ritmarktest with a subdirectory and a FILESYSTEM table that covers c:\ritmarktest (look at the previous example for how to create such a table) C:\ritmarktest>dir 05.10.2006 09:39 <DIR> . 05.10.2006 09:39 <DIR> .. 04.10.2006 23:33 45 678 bar 04.10.2006 23:36 19 663 foo 05.10.2006 09:39 <DIR> sub1 C:\ritmarktest>dir sub1 05.10.2006 09:39 <DIR> . 05.10.2006 09:39 <DIR> .. 04.10.2006 23:33 45 678 bar_copy 04.10.2006 23:36 19 663 foo_copyThe query:
mysql> SELECT a.filename, b.filename FROM test.c$$ritmarktest a, test.c$$ritmarktest b
-> WHERE CONCAT(a.path, '/', a.filename) <> CONCAT(b.path, '/', b.filename)
-> AND (a.size <> -1)
-> AND (a.content = b.content);
+----------+----------+
| filename | filename |
+----------+----------+
| bar_copy | bar |
| foo_copy | foo |
| bar | bar_copy |
| foo | foo_copy |
+----------+----------+
4 rows in set (0.02 sec)
concat(a.path, '/', a.filename) <> concat(b.path, '/', b.filename) is needed to filter out rows where
a file matches itself. (a.size <> -1) condition is to remove directories.
Filesystem Replication Setup Using RitmarkFS you can easily setup local or remote filesystem replication. Let's say you have 2 Linux hosts named host_master and host_slave (Windows setup is similar except directory names) and want to replicate data from host_master directory /tmp/r1 to host_slave into directory /tmp/r2. 1. Install RitmarkFS enabled MySQL server binary on host_master. Follow usual MySQL server setup steps. Make sure you have enabled the binlog, which is essential for replication. 2. Create a table that will correspond to /tmp/r1:
mysql> CALL mysql.ritmarkfs_create_legacy('test.$tmp$r1');
3. Enable replication slave account:
mysql> GRANT REPLICATION SLAVE ON *.* TO 'root'@'host_slave' IDENTIFIED BY 'slavepass';Now that your master is set up and ready to replicate data to the slave, it's time to setup slave. 4. Install RitmarkFS enabled MySQL server binary on host_slave. Follow usual MySQL server setup steps. Binary logging is not nesessary, in fact you may want to disable it to make things work faster and to save some disk space. 5. Create table that corresponds to the target directory.
mysql> CALL mysql.ritmarkfs_create_table('test.$tmp$r2');
6. Create mapping view. Why is it needed? When you update table `$tmp$r1` on the master the slave will also update
the table with the same name. But we want to replicate to another directory, so we create a view, that will do the proper
name mapping:
mysql> CREATE VIEW test.`$tmp$r1` AS SELECT * FROM test.`$tmp$r2`;So all the queries that update table `$tmp$r1` on the master will update table `$tmp$r2` on the slave. You don't need to create this view if source and target table (directory) names are the same. 7. Initialize slave: mysql> CHANGE MASTER TO MASTER_HOST='host_master', MASTER_USER='root', MASTER_PASSWORD='slavepass';8. Start the slave: mysql> START SLAVE;From this point all changes made to host_master:/tmp/r1 will also be applied to host_slave:/tmp/r2. This is true regardless of whether you do modify /tmp/r1 via MySQL interface or via filesystem, by copying, updating or deleting files. Below is a sample session log: vkolesnikov@host_master:/tmp/r1> ls -l total 0 vkolesnikov@host_master:/tmp/r1> touch foo vkolesnikov@host_master:/tmp/r1> ls -a total 0 -rw-r--r-- 1 vkolesnikov users 0 2006-06-07 04:42 foo vkolesnikov@host_slave:/tmp/r2> ls -a total 0 -rw-r--r-- 1 vkolesnikov users 0 2006-06-07 04:42 foo |