Archive for ‘mysql’ Category
Posted on 16:58, August 23rd, 2010 by Many Ayromlou
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):
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).
– 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).
– 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:
– Next we need to flush the tables and issue a read lock (yes, just because we can):
– Still on the master we find out the current binary log file name and position:
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:
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:
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:
– Last but not least on the slave, turn on the slave mode:
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):
Transfer “goodies.sql” text file from your production server to the master server (ssh/scp/ftp….whatever).
Posted on 15:32, August 19th, 2010 by Many Ayromlou
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:
The output of this command is something like this:
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.
Posted on 13:54, June 19th, 2009 by Many Ayromlou
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:
Posted on 12:55, September 25th, 2008 by Many Ayromlou
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.
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.
Posted on 12:35, June 17th, 2008 by Many Ayromlou
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:
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: