tonyandcarol.com

I got a little sick of a bunch of outsourced dbas ‘working’ for us on a project so I pushed them out and decided it’d be quicker if I just did it myself.  (by working, they took over a month just to do what took me four hours — the company is bluewolf by the way.)

What you need to do this:

1. Reasonable UNIX system administrator skills.
2. The ability to use the mysql command line.
3. Root access everywhere, root db access
4. db1 -> An existing database to replicate, something with test data already in it, that you can already connet to it, run queries, connect via navicat, shell, etc. (for more information, learn about MySQL’s bind address property) For this tutorial, I’ll assume you’re going to replicate the “wordpress” and “wikidb” schemas.
5. db2 -> the replication instance, a brand new, installed from source, configure/make/make installed to /usr/local/mysql
6. I strongly suggest navicat [50 bucks]  and mysqladmin [free]
7. A mac, or a linux workstation!  [well, I'm just saying.. PC's suck, so get a mac, or a debian notebook or something. I am completely through in my life answering PC questions, so if you ask a question on this, make sure it has nothing to do with Windows.]

db1->my.conf

make sure it has something like this — server ID 1 means it’s going to be a master database, there’s some performance tuning stuff in here, and at the bottom it sets up the logs to bind to the replication db.

[mysqld]
user                                   = mysql
pid-file                              = /var/run/mysqld/mysqld.pid
socket                               = /var/run/mysqld/mysqld.sock
port                                   = 3306
basedir                             = /usr/local/mysql
datadir                             = /usr/local/mysql/var
tmpdir                             = /tmp
language                         = /usr/local/mysql/share/mysql/english
skip-external-locking
bind-address                 =192.168.1.2
key_buffer                     = 64M
max_allowed_packet  = 64M
thread_stack                 = 128K
thread_cache_size       = 8
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
server-id                         = 1
log_bin                           = /var/log/mysql/mysql-bin.master.log
expire_logs_days        = 14
max_binlog_size         = 1G
binlog_do_db            = wordpress
binlog_do_db            = wikidb

Create replication user on DB1

GRANT REPLICATION SLAVE, SUPER, RELOAD, SELECT
ON *.*
TO 'replication'@'db1'
IDENTIFIED BY 'replication';

Get the replication database to run as a regular database.

http://dev.mysql.com/doc/refman/5.1/en/quick-install.html

cd /usr/local/mysql
chown -R mysql:mysql ./*
./bin/mysql_install_db  –user=mysql
chown -R mysql:mysql ./*

#and of course, set up symlinks so it starts correctly.!
ln -s /usr/local/mysql/share/mysql/mysql.server /etc/init.d/mysql
ln -s /usr/local/mysql/share/mysql/mysql.server /etc/rc2.d/S91mysql

test that everything installed by just starting the db.

/etc/init.d/mysql start

Run mysql and create yourself as user to use MySQL Admin with.

mysql> grant all on *.* to ‘afraser’@'%’ identified by ‘acleartextpasswordstring’;

I suggest you add the same ‘user’ to the replication db that is used by the php host in case you ever need to shut off the slave and use it as a master.

mysql> grant all on *.* to ‘apache’@'%’ identified by ‘acleartextpasswordstring’;

/etc/init.d/mysql stop

Copy over the db1 database as a starting point for db2

db1 -> Shut down the database for the tar to avoid running a bunch of mysql commands.

db1 -> cd /usr/local/mysql/var

db1 -> tar cvf ~afraser/mysqlstuff.tar wikidb wordpress

db1 -> scp ~afraser/mysqlstuff.tar afraser@db2:~

db2 -> cd /usr/local/mysql/var

db2 -> cp ~afraser/mysqlstuff.tar .

db2-> tar xvf ./mysqlstuff.tar

db2-> set up the my.conf

This is pretty much straight defaults, or close to it, except you have to set the server ID.

[mysqld]
user                                              = mysql
pid-file                                        = /var/run/mysqld/mysqld.pid
socket                                          = /var/run/mysqld/mysqld.sock
port                                             = 3306
basedir                                       = /usr/local/mysql
datadir                                       = /usr/local/mysql/var
tmpdir                                        = /tmp
language                                    = /usr/local/mysql/share/mysql/english
skip-external-locking
key_buffer                                = 64M
max_allowed_packet            = 64M
thread_stack                           = 128K
thread_cache_size                 = 8
myisam-recover                     = BACKUP
query_cache_limit                = 1M
query_cache_size                 = 16M
server-id                                 = 2
expire_logs_days                 = 14
max_binlog_size                  = 1G

Now restart the DB2 DB, make sure it works by connecting to it with MySQL Administrator with your user.

Set db2’s master pointer to the D1 server.

First log into D1 and figure out what you need to set up.

mysql> show master status;
+------------------+----------+-----------------------------------------------------------------------+------------------+
| File             | Position | Binlog_Do_DB                                                          | Binlog_Ignore_DB |
+------------------+----------+-----------------------------------------------------------------------+------------------+
| mysql-bin.000015 |      106 | central,redtrax,redtrax_node,redtrax,wordpress,wikidb,phpsession_prod |                  |
+------------------+----------+-----------------------------------------------------------------------+------------------+
1 row in set (0.00 sec)

Now punch that into Db2’s database.

mysql> CHANGE MASTER TO
MASTER_HOST=”IP.Ad.Of.D1″,
MASTER_USER=”replication”,
MASTER_PASSWORD=”replication”,
MASTER_LOG_FILE=”mysql-bin.000015″,
MASTER_LOG_POS=106;

mysql>SLAVE START;

Then, you just go back and forth between DB1>MYSQL> Show Slave status; and DB2>Mysql>Show Master Status; until you figure out how it all works.

It’s really simple really, you just did a zendDB->fetchAll and you want to return the result array as an VO object back to flex.

You’d think that this would work.

$userObject = (UserVO) $zendDBResult;

But it doesn’t. That kind of casting is not built into PHP. Ok open source guys, this is kind of important, but that’s another discussion…
So you try this.
$zendDBResult = $statement->fetchObject();
$userObject = (object) $zendDBResult;

Of course this sort of works, but on the flex side, you realize that it just isn’t going to cast.  Everything comes across as a string and as an object of stype stadard class. Without throwing out all of your VO’s in the AS3 side and replacing everything with strings, you’re just flat hosed.

user = event.result.object as UserVO;  // yeah, forget it.

How do you fix it all and have it work for ALL of your VO’s?

It’s simple, use inheritance. Have your all your VO objects extend some kind of generic data object, and then just put this method into the parent.

public function populateMyself($dbResult) {
error_log(”Populating Myself”);
foreach ($dbResult as $key=> $value) {
$this->$key = $value;

}
}

To use it, just have the VO object call itself with the result set.

Then, just tell the object to ‘popluate itself’ by passing in the ZendDB result.

$userVO = new UserVO();
$zendDBResult = $statement2->fetchObject();
$userVO->populateMyself($zendDBResult);

Works like a charm.!