We recently upgraded our servers from old XServe G5′s running Tiger to the latest greatest running Snow Leopard. In this small howto I will deal with the procedure I followed to setup mysql master-slave replication between two new servers. I start out with two (master, slave) empty DB’s, setup the replication and then import my data from a third server (my old G5 X-Serve). Of course like anything else I will try to show you how to get out of — what I like to call — Steve Jobs Hell Holes . There are other ways of doing this procedure, for more info check out this page.

- First things first….the magic command that stops mysql server from command line, just in case you screw something up and need to restart (This should be used on your master and slave servers prior to them going live…..DO NOT USE THIS ON A LIVE SERVER):

sudo launchctl unload /System/Library/LaunchDaemons/org.mysql.mysqld.plist

This will stop the launch Daemon from continously launching mysql when trouble is brewing. This usually happens when you screw something up badly and the symptom is that the Server Manager Status for MySQL will say “Starting up“, but never changes to “Running“.

- Next I want to blow away my DB files on the master and slave (remember these are NOT production servers yet…..I’m still rebuilding them. You DO NOT want to remove the DB’s on your production servers). You’ll need to find the path in the “Settings” tab of the server manager, under “Database Location“. I removed everything in that directory (rm -rf *) from the command line. AGAIN, I CAN NOT STRESS THIS ENOUGH, BE CAREFUL WHERE YOU ISSUE THESE COMMANDS.

- I did this on both the master and the slave.

- Now on the Master change the following in /etc/my.cnf (You should have this file, if you’ve got a my.cnf.default, copy it my.cnf).

[mysqld]
log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1

- On the Master still, check the “Allow Network Connections” check box in Server Manager and set the root password (for mysql) by pressing the “Set MySQL Root Password…” button. Then press “Save” followed by “Start MySQL“. This will initialize mysql with the default tables and setup the root password.

- Now on the Slave change the following in /etc/my.cnf (You should have this file, if you’ve got a my.cnf.default, copy it my.cnf).

[mysqld]
log-bin=mysql-bin
server-id=2

- Back on the Master, you need to create a user (I call it repl) that has REPLICATION SLAVE privilege. Use the following two commands (make sure you replace mydomain.com and slavepass….LEAVE repl as the userid:

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';

- Next we need to flush the tables and issue a read lock (yes, just because we can):

mysql> FLUSH TABLES WITH READ LOCK;

- Still on the master we find out the current binary log file name and position:

mysql> show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000004 |      340 |              |                  |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

Note down the filename and the Position number. We will need them later.

- Back on the Slave, go to Server Manager and set the root password (for mysql) by pressing the “Set MySQL Root Password…” button. Then press “Save” followed by “Start MySQL“. Connect to the DB as root and issue the following command:

CHANGE MASTER TO MASTER_HOST='master.mydomain.com', MASTER_USER='repl', MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=340;

This will setup the slave to talk to the Master and do it’s thing. While we are here, we might as well verify that the repl user can log into the master from the slave:

mysql -h maste.mydomain.com -u repl -p

should prompt you for password and if you type in the ‘slavepass’ you assigned above, you should be able to get in.

- Back on the master unlock the tables:

mysql> UNLOCK TABLES;

- Last but not least on the slave, turn on the slave mode:

mysql> START SLAVE;

Done…..Now you can go to your production server and suck it’s brains (ahemm…..DB’s) out and import it into your master. Your slave should follow and replicate whatever you import into the master Server. I used the following command (you mileage might/will vary):

mysqldump -x -c --add-drop-table --add-drop-database -u root -p --databases dbname1 dbname2 dbname3 >goodies.sql

Transfer “goodies.sql” text file from your production server to the master server (ssh/scp/ftp….whatever).

mysql -u root -p < goodies.sql

So this was a great big mystery this morning. How the heck do you transfer the users and their privileges out of a old mysql server and “import” them into a new server. We recently upgraded from OSX 10.4.11 to a couple of spanking new Snow Leopard servers and during the mysql export/import cycle this issue came up. Well the simple answer is…..DON’T USE mysqldump on your mysql DB (you know the default DB that stores all your users and privileges. It’s a bad idea and will probably do more harm than good. Instead use the following procedure:

1) On your old server (the one that has your data/users/tables on it) issue the following command (replace YOUR dbadmin/root username and  password in the 2 appropriate places:

mysql -B -N --user=admin --password=yourpassword -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" |mysql --user=admin --password=yourpassword | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

The output of this command is something like this:

## Grants for admin@127.0.0.1 ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
## Grants for fabrik@localhost ##
GRANT USAGE ON *.* TO 'fabrik'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT ALL PRIVILEGES ON `fabrikdb`.* TO 'fabrik'@'localhost' WITH GRANT OPTION;
## Grants for nerdlogger@localhost ##
GRANT USAGE ON *.* TO 'nerdlogger'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT ALL PRIVILEGES ON `nerdlogger`.* TO 'nerdlogger'@'localhost' WITH GRANT OPTION;
## Grants for research@localhost ##
GRANT USAGE ON *.* TO 'research'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `research`.* TO 'research'@'localhost';
## Grants for admin@localhost ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD 'HASHEDPASSWORD' WITH GRANT OPTION;
## Grants for root@server.domain ##
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'server.domain' WITH GRANT OPTION;

2) Now you’re ready to selectively cut and paste the appropriate users and associated grant into a new mysql session (which you have to open) on the new server.

Goodluck….

So after yesterdays rant, I went back and figured out how to install the Cacti monitoring software (OSS, Free) onto a Ubuntu 9.04 “Jaunty Jackalope” Desktop installation. This guide uses packages only, no compiling, no Makefiles or anything like that…..You should be able to just follow this and get a fully functioning Cacti installation in about 30 minutes. Here are the steps:

1. install ubuntu 9.04 (“Jaunty Jackalope“) Desktop Edition on your machine
2. Login, open a Shell window and install ubuntu LAMP (Linux/Apache/MySQL/PHP) server stack on your machine
   “sudo tasksel install lamp-server”.
   Note: Make sure you remember the password for “root” user in mysql Database, write it down somewhere, we will need it later on.
3. Get a superuser shell started since it will make for less typing.
   “sudo -i”
   followed by your password. Be carefull from now on, you’re ROOT and can literally destroy your system if you issue the wrong command. Follow along by typing the commands in the rest of this document and answering the prompts where appropriate.
4. Issue:
   “apt-get install rrdtool snmp php5-snmp php5 php5-gd”
   This will get all of the prereqs installed on your system. Answer “yes” when prompted for additional packages. 
5. Issue:
   “apt-get install cacti-cactid”
   This will get cacti and cacti server installed. Again answer “yes” when prompted for additional packages.
6. You’ll be presented with a bunch of ANSI screens that ask for information or give you choices to configure “libphp-adodb” package. Follow as per below:
• Click “Okay” on php.ini update path (screen 1).
• Choose “Apache 2″ from the pull down on next screen (screen 2).
• Click “Okay” on cacti and spine configuration screen (screen 3).
• At this point some config scripts will run and you’ll see a bunch on jiberish on the screen. Let it go, don’t touch nothing.
• Click “yes” on the dbconfig-common screen and provide the password from step 2. (above) for the mysql “root” user (screen 4).
• Now you’re prompted to choose a password for a new mysql user known as “cacti”. I used the same password as “root” user since my system is single user only. You will need to confirm the password on the next screen (screen 5,6).
• Almost there……..
7. Now the hard part is over. Start your browser and point it at http://localhost/cacti — assuming you’re running the browser on the cacti machine — or the appropriate IP address instead of localhost.
8. Click “Next” on the first screen (might want to read it too).
9. Select “New Install” on screen 2 and Click “Next”
10. On the next screen (Path Check screen) make sure everything is found and make 100% sure to select “RRDTool 1.2.x” from the RRDTool utility version pull down. Click “Finish” when you’re done.
11. You’ll see the login screen. Use Username “admin” and Password “admin” to login. On the next screen you’re forced to change the password for user admin. This is a good thing. Change the password to something complicated and easy to remember (does that exist?). Click “Save”.
12. Make sure under Configuration Settings/Paths that “Spine Poller file path” is correctly set to “/usr/sbin/spine”, and its found.
13. Make sure under Configuration Settings/Poller you select “Poller type” and set it to “spine” and Click “Save”. You’re done……Please RTFM for more Cacti info (or come back here and you might potentially find another episode of my ramblings). Have Fun!!

 I’ve had this question a couple of times in the past few months (since I posted the command line version of this method here ). To make it short and sweet, yes you can use Putty in a windows environment to setup ssh tunnels. Here is the specific scenario with pics for setting up a tunnel to your mysql server (assuming mysql server is running on a machine that you have ssh access to) using putty. This allows you to run mysql-gui-tools under windows and connect thru ssh to your server, without having to open the server to accept connections from the network.

1. Download Putty full install package 
2. Run putty and your’ll see the following screen. Fill in the hostname of the DB server and choose SSH as protocol.

3. Go down to SSH and Tunnels options. Fill in 3306 for Source port, 127.0.0.1:3306 for local Destination port and click Add. This will forward (through SSH) all traffic sent to 127.0.0.1:3306 (aka. localhost:3306) to the remote host’s (DB Server) port 3306. You’ll see how this works in a second.

4. Now go back to Sessions, Give the session a meaningful name (eg: MYSQLTunnel) and click Save so that it’s saved.

So now you’re done. If you need to access your mysql server, first run putty, load the session we just saved, connect and you’re good to go. You can use any network based mysql frontend and even the ODBC connector under windows and point them at host: 127.0.0.1 and port: 3306, the SSH tunnel will then take the traffic and safely transfer it to your mysql server box. This way you can run your mysql server in local mode — where it will not accept connections from outside network — for safety reasons and have network access to it when you need.

Running and administrating mysql can sometimes be a hassle especially if you’re running a semi-secure environment. This usually means that your mysql server will not accept connections from outside and only localhost connections are allowed. There is a quick way of getting around this if you’re stuck somewhere and really need to use that graphical admin/browser tool to get to your DB server. All you really need to do is forward port 3306 on your local machine to port 3306 on the DB server through a ssh tunnel. Here is the ssh command you need to issue to start things up:
ssh -L 3306:127.0.0.1:3306 yoursshloginid@yourserver.yourdomain.com
Once you supply the password for the ssh session you’re in business, the encrypted tunnel is up and running. All you need now is to point Mysql Administrator graphical tool at host 127.0.0.1 (localhost) and port 3306 like the picture below:The only thing you want to make sure you get right is the 127.0.0.1, DO NOT use localhost. The tools you’re using automatically assume a local socket connection to the DB when you use “localhost” as the Server Hostname. Another thing is that all checks that mysql administrator does locally on the server files will not work (ie: the interface will report that the server is down since it can’t find mysqld.pid), but all users/schema manipulation works fine since they are network based.

If you have mysql daemon installed on your local machine (the machine you initiated ssh from) you need to change the local port to something else other than 3306 and the command will look something like this:
ssh -L 7777:127.0.0.1:3306 yoursshloginid@yourserver.yourdomain.com
In this case I’m using local port 7777 which means I also have to tell mysql administrator to connect through port 7777. You get the idea……