A while back I wanted to test the ARCHIVE Engine in MySQL 5 on FreeBSD, well I had to do some jinkying around with the Makefile to add the options i needed to turn on the ARCHIVE Engine. I haven't seen it committed to the port yet so here it is:

# diff Makefile Makefile.orig
68,71d67
< .if defined(WITH_ARCHIVE_ENGINE)
< CONFIGURE_ARGS+=--with-archive-storage-engine
< .endif
<
150d145
< @${ECHO} " WITH_ARCHIVE_ENGINE=yes Enable Archive Table Engine Support."

After getting it compiled i ran a series of tests to see how it was working and to check the speed and how it compared to storing my data in a MyISAM table. Here is some of the stats...

mysql> SHOW TABLE STATUS LIKE 'campaign_16_long_myisam';
+-------------------------+--------+----------+
: Name : Engine : Rows :
+-------------------------+--------+----------+
: campaign_16_long_myisam : MyISAM : 28224724 :
+-------------------------+--------+----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS `campaign_16_long`;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE `campaign_16_long` (
-> `id` int(11) NOT NULL,
-> `timestamp` bigint(20) NOT NULL default '0',
-> `ad_name` varchar(25) NOT NULL default '',
-> `type` varchar(25) NOT NULL default '',
-> `referer` varchar(100) NOT NULL default '',
-> `path` varchar(255) NOT NULL default '',
-> `client_ip` varchar(15) NOT NULL default ''
-> ) ENGINE=ARCHIVE DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `ad_tracking`.`campaign_16_long` SELECT * FROM
`ad_tracking`.`campaign_16_long_myisam`;

...
# top
last pid: 85206; load averages: 0.99, 0.74, 0.37

up 8+12:37:29 03:02:10
66 processes: 2 running, 64 sleeping
CPU states: 25.0% user, 0.0% nice, 1.1% system, 0.0% interrupt, 73.9% idle
Mem: 113M Active, 3212M Inact, 184M Wired, 158M Cache, 214M Buf, 5368K Free
Swap: 2048M Total, 2048M Free

PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
85044 mysql 20 0 59756K 34092K kserel 1 6:52 98.10% 98.10% mysqld
...

Query OK, 28224724 rows affected (8 min 42.90 sec)
Records: 28224724 Duplicates: 0 Warnings: 0

mysql> SHOW TABLE STATUS LIKE 'campaign_16_long';
+------------------+---------+---------+------------+----------+
: Name : Engine : Version : Row_format : Rows :
+------------------+---------+---------+------------+----------+
: campaign_16_long : ARCHIVE : 10 : Compressed : 28224724 :
+------------------+---------+---------+------------+----------+
1 row in set (0.00 sec)

# ls -alh : grep campaign_16_long
-rw-rw---- 1 mysql mysql 19B Sep 16 02:54 campaign_16_long.ARM
-rw-rw---- 1 mysql mysql 508M Sep 16 03:04 campaign_16_long.ARZ
-rw-rw---- 1 mysql mysql 8.6K Sep 16 02:54 campaign_16_long.frm
-rw-r----- 1 mysql mysql 2.3G Sep 15 19:00 campaign_16_long_myisam.MYD
-rw-r----- 1 mysql mysql 249M Sep 15 21:35 campaign_16_long_myisam.MYI
-rw-r----- 1 mysql mysql 8.6K Jul 7 13:57 campaign_16_long_myisam.frm

The server moved 28,224,724 in 8 min 42.9 seconds. It compressed the
data from 2.3G to 508M AMAZING storage saver! I am truely happy,
running selects off it were just as fast as the MyISAM:

mysql> SELECT * FROM `campaign_16_long_myisam` LIMIT 0, 30;
...
30 rows in set (0.00 sec)

mysql> SELECT * FROM `campaign_16_long` LIMIT 0, 30;
...
30 rows in set (0.00 sec)


System Specs:
FreeBSD 5.4-RELEASE-p7 #0: Wed Sep 7 14:12:34 CDT 2005
CPU: Dual Core AMD Opteron(tm) Processor 265 (1792.85-MHz K8-class CPU)
FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs
cpu0 (BSP): APIC ID: 0
cpu1 (AP): APIC ID: 1
cpu2 (AP): APIC ID: 2
cpu3 (AP): APIC ID: 3

4096MB Ram
2 x 146GB SCSI/ RAID 1

you can read the full thread from the mailinglists here

All in all its a great storage option especially if you're storing lots and lots of data. The fact that you can only SELECT and INSERT is nice to, for stat logs and such its ideal and you get a little more peace of mind knowing your data is a little safer now!

Well we had to setup some hefty realtime backup solution for a database server here at work. After much research i decided hotcopy and mysqldump would not suffice, its too load intensive across the system and ties up the tables with LOCKs for too long. So in walked replication, my beautiful angel of salvation! It was pretty smooth to get setup once i fumbled through it a bit, though i'm not a pro yet, it seems to be running pretty smoothly.

Basically the steps go as follow:
1. Install MySQL on your two servers (I did matching versions [5.x] don't know how diff versions will work)

2. Configure your master my.cnf add the following:

log-bin
server-id=1


3. login to mysql as root and run this statement:

mysql> GRANT SELECT , RELOAD , LOCK TABLES , REPLICATION SLAVE , REPLICATION CLIENT ON *.* TO username@"%" IDENTIFIED '<password>';
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;

take note of the binary log file and its position you'll need these values when configuring your slave.

4. mysqlhotcopy.sh your databases from your master to your slave

> mysqlhotcopy db_name /path/to/backup/directory
> tar -czvf db_name.tgz /path/to/backup/directory
> scp db_name.tgz user@slave:~/db_name.tgz
> ssh -l user slave
> su
> tar -zvxf db_name.tgz
> mysqld stop
> mv db_name /path/to/mysql/data/directory
> mysqld start

I was very genirc on this stuff it will be up to you to figure out the exact details that match your operating system

5. Configure your my.cnf for your slave

log-bin
server-id=2
log-slave-updates
log-warning
replicate-ignore-db=mysql


6. restart mysql on your slave and run the following:

mysql> CHANGE MASTER TO MASTER_HOST='<master host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;

mysql> SLAVE START;


7. check it!

mysql> SHOW SLAVE STATUSG;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: master
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 24169757
Relay_Log_File: slave-bin.000032
Relay_Log_Pos: 12546280
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 24169757
Relay_Log_Space: 12546280
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.05 sec)

ERROR:
No query specified


Well that's the jist of it, hope it makes some sense, if not eh well maybe i'm crazy and mysql loves me. Here are a couple of links i used to help me get through it so maybe they'll shed more light on the situation.


MySQL Reference Manual :: 6 Replication in MySQL
Database Replication in MySQL
Live Backups of MySQL using Replication

Well this is it kids, i caved in and made a blog. I'm going to use it to document my journeys through the interweb, mainly covering LAMP development and some related topics. SO... if you wanna be as cool as me get on the bandwagon and start readin! Oh and i don't spell well or have good typing gramar so eat it! ok?