(05:37:10) thebastedo: this project blows my nuts
(05:37:29) friend: hey
(05:37:33) thebastedo: huh?
(05:37:33) friend: i thought only i got to do that
(05:37:37) friend: lol
(05:37:40) thebastedo: nope
(05:37:43) thebastedo: work is above you
(05:37:47) thebastedo: cuz they pay my rent
(05:37:48) friend: loll
(05:37:52) thebastedo: they get what they want
(05:37:54) friend: who pays yer souP!!!
(05:37:55) friend: =P
(05:38:04) thebastedo: well you pay my rent too i'll let you be the only one
(05:38:10) friend: i should atleast get top priority below work
(05:38:19) thebastedo: well you get it whenever you want
(05:38:24) thebastedo: as long as i'm not currently engaged
(05:38:39) thebastedo: but my nuts are exclusive to you
(05:39:55) friend: hahaha
(05:39:56) friend: ok

sorry I just wanted to help save some of my funny comments with my friends from when I'm up way to late working.

So about a month ago we put in a new hosting infrastructure and me as the sole admin needed an easy way to monitor and keep an eye on all of my servers and the services and resources of each server.



So after some digging around I found that an application I use to use was now a new application Nagios. Now this application is great, took me about 2 days to set everything up as it is now, but once I found everything i needed it was great. Nagios uses SNMP Protocol to monitor almost anything on your server. It has a very OO configuration allowing you to reach some pretty complex setups. Mine didn't get too complex so i can't speak to all of that but it was pretty easy to setup once I got the hang of it. After I found these two Nagios Plugin pages I knew I was truely in heaven. NagiosExchange and Manubulon Nagios Plugins.



Nagios does a lot for me. It watches my partitions and lets me know if one gets close to full and notifies me before it gets full, watches RAM / Swap usage, checks to see that Apache, MySQL and SSHD are up and running, and even checks the status of my mysql replication to my backup server.

Though nagios was great, it didn't completely satisfy all my needs. Though it watched to make sure everything was up and running I couldn't see trends in usage and such. So I went out and installed MRTG on my monitor and began setting up MRTG stuff to log usage of network connections, cpu, ram, and hd usage on all my machines. This helps me to better see when I may need to upgrade things hopefully before even Nagios knows.
MRTG Page

All in all the key to a happy administrator is preparedness. Trying to keep this in more of a proactive environment than a responsive one. If anyone has any more questions about either of these apps let me know I'd be happy to answer some questions!

one of my coworkers sent me this link today, haven't had a ton of time to play with it but looks pretty sweet.

WWW SQL Designer

Which is a nice interface to GModeler that Grant Skinner made which runs in flash. Gmodeler seems a little more geared to software architecture than db modeling. Both fun and handy little tools

GModeler

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