On Tue, 2006-06-13 at 19:04, Dan Trainor wrote: > Jake Peavy wrote: > > On 5/24/06, *Dan Trainor* <[EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>> > > wrote: > > > > Hi - > > > > I would like to be able to replicate all queries from a live MySQL > > server, to a testing server at the office. > > > > The reason for doing this is to test load under [semi]real-world > > conditions with the new server. I think that by doing something like > > this, I would be able to fine-tune the new server in preparation for > > replacing the original server. > > > > So what I can't figure out right now is how to set up a situation like > > this, where the live server would replicate every one of it's > > queries to > > the testing machine, and have the testing machine not respond with > > anything - just simply mow through the queries. > > > > The testing server will have a snapshot of the live database, so I will > > ahve data to work with. However, the testing machine is on a private > > internal subnet, and I don't see how this type of setup would work from > > a logical MySQL standpoint. > > > > Keeping all this in mind, also remember that I cannot change any of the > > code which references the MySQL server. I need to be able to do this > > using some native MySQL function. > > > > Any feedback would be greatly appreciated. I look forward to all your > > responses. > > > > Thanks! > > -dant > > > > > > Big Dan T? > > > > I haven't done it, but I think what you need to do is > > > > 1. enable binlog on the live DB > > 2. transfer the binlogs from some period of time over to your test server > > 3. pipe the queries contained in the binlog into your test server, > > something along the lines of: $ mysqlbinlog > > bin_file_from_live_db.0001 | mysql > > > > HTH, > > > > -- > > -jp > > Hi, Jake - > > This would certainly work. However, I'd like to do all of this as close > to real-time as I possibly can. I don't think that I can get an > accurate representation of load if I just throw it on the new machine > that way. > > Thanks though! > -dant
The binlog would only give you the changes (updates, inserts, and deletes). The general log (--log) would be a better representation of the server, since it logs all actions, when they started, and from which host. The issue is in making the play-back real-time. I dont believe mysqlbinlog takes the start time into consideration, so the playback would be one single serial thread with no delays between transactions. Not realistic. A little scripting and parsing would create what your looking for though. You could also do a tcpdump at the DB server, only capturing the incoming mysql statements (providing your not doing SSL encapsulation). This would also give you the timing and all transactions, but I'm not sure how the server would handle TCP playback. In trying to do the same thing, we ended up creating a java based stress testing tool to mimic our DB traffic, using the basic info from TCP dumps as a model. Regards, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
