Re: SAN and ibdata files
Rajan, In a SAN configuration, the lun/volume should appear just like a locally attached physical disk on your server. MySQL will not need any special configurations to access it, just poiint the datadir to the SAN disk. Regards, Scott Tanner On Mon, 2007-10-08 at 11:06 +0530, Ace wrote: Thanks Dan! We do know the permission problem. We tried chown but got Not a owner error. That is because of root squashing in which root becomes nobody when you cd to NFS directory. To overcome this we tried adding no_root_squash to export file. but still the error. I will overcome this somehow.but my Biggest worry now is - can I configure Mysql to use SAN location? if not, why? Thanks, Rajan On 10/7/07, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Oct 06), Ace said: Hi Experts, You all know the size problems with ibdata and log files. We plan to move those files to SAN to have maximum storage possible. We did so with configuration changes in my.cnf files to save log and ibdata files on SAN location. But got following error - InnoDB: Unable to lock /opsolnfs/mysql/ibdata1, error: 13 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. Any concrete steps to overcome this problem? Your input will add value in solving our problem. $ perror 13 OS error code 13: Permission denied Check permissions on your data directory. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SAN and ibdata files
Rajan, I believe you are talking about a NAS setup, which is different from the SAN configuration I was referring to. Check out http://www.nas-san.com/differ.html for a quick comparison. With NFS volumes, your adding several layers of overhead when accessing the disks which has a drastic effect on performance. I'm not sure what the exact locking issue would be, but I'd imagine it would have to do with the added latency. I have heard of a few people using NFS on small, mostly read databases though. So you may not have any issues depending on the type and amount of usage your server would be getting. Regards, Scott On Mon, 2007-10-08 at 18:06 +0530, Ace wrote: Yes Scott! Only thing that confused and still confusing me is the following note on mysql site - *Note* It is not a good idea to configure InnoDB to use datafiles or logfiles on NFS volumes. Otherwise, the files might be locked by other processes and become unavailable for use by MySQL. http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Why other processes will keep files locked if its on NFS volumes? and which are the processes they are referring? Thanks, Rajan On 10/8/07, Scott Tanner [EMAIL PROTECTED] wrote: Rajan, In a SAN configuration, the lun/volume should appear just like a locally attached physical disk on your server. MySQL will not need any special configurations to access it, just poiint the datadir to the SAN disk. Regards, Scott Tanner On Mon, 2007-10-08 at 11:06 +0530, Ace wrote: Thanks Dan! We do know the permission problem. We tried chown but got Not a owner error. That is because of root squashing in which root becomes nobody when you cd to NFS directory. To overcome this we tried adding no_root_squash to export file. but still the error. I will overcome this somehow.but my Biggest worry now is - can I configure Mysql to use SAN location? if not, why? Thanks, Rajan On 10/7/07, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Oct 06), Ace said: Hi Experts, You all know the size problems with ibdata and log files. We plan to move those files to SAN to have maximum storage possible. We did so with configuration changes in my.cnf files to save log and ibdata files on SAN location. But got following error - InnoDB: Unable to lock /opsolnfs/mysql/ibdata1, error: 13 InnoDB: Check that you do not already have another mysqld process InnoDB: using the same InnoDB data or log files. Any concrete steps to overcome this problem? Your input will add value in solving our problem. $ perror 13 OS error code 13: Permission denied Check permissions on your data directory. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Misbehavin'
Maybe I missed this in the text below, but are you trying to daisy chain the slaves (master - slave 1 - slave 2) or have multiple slaves connecting to one master? Is slave 1 configured with log-slave-updates? Regards, Scott On Wed, 2007-09-19 at 12:31 -0500, [EMAIL PROTECTED] wrote: Hi, [EMAIL PROTECTED] wrote: Howdy, I'm trying to add a second slave, slave2, running MySQL 5.0.22 on CentOS 5 to our system that currently has one master and one slave, slave1, running 4.0.24, and somehow slave2 somehow ends up with too many records in many of the 30 tables in the database. Steps taken: 1. Stopped new records from being inserted into the master, and confirmed with count(*)'s that both master and slave1 were in a static state. 2. Stopped mysqld and commented out in my.cnf the master connection parameters (user, host, password, port) on slave2. 3. Deleted master.info, all mysql-bin and relay-bin files from the mysql data directory on slave2. 4. Deleted all .MYD, .MYI, and .frm files from the replication database directory on slave2. 5. rsync'd the .MYD, .MYI, .frm files from slave1 to slave2. - And in the meantime, slave1's data is being changed because the master - is sending it replication events, no? You need to run STOP SLAVE on - slave1 before rsyncing it. After STOP SLAVE, run SHOW SLAVE STATUS and - record the output, then rsync, then START SLAVE on slave1 again. I don't think so. I stopped all activities on the master (step 1), and slave1 therefore shouldn't have any changes made to it. I should have noted that only inserts are done on the master - no updates or deletes. 6. Restarted mysqld on slave2 (now not running as a slave). 7. Confirmed that record counts were consistent across master, slave1 and slave2. 8. Stopped mysqld on slave2, uncommented master connection parameters in my.cnf, and restarted mysqld. 9. Got log file and log position parameters with 'show master status' on the master. - TOO LATE. The horse has left the barn and you're closing the door - behind it! You should instead get the replication coordinates from - slave1 with SHOW SLAVE STATUS during step 5. You're cloning slave2 from - slave1, so slave2 tells the truth, not the master, which has done a - whole bunch of work while you were going through these steps. No, slave1 can't do any work except as directed by the master, which has had all activities stopped on it. 10. Ran 'Change master to... with all fields filled in. 11. Ran 'slave start' on slave2. 12. Rechecked record counts on slave2, and they were too large and out of sync with slave1 and master. I poked around in the data on slave2 and found a number of records had been duplicated, and that accounted for the higher record counts. After starting the application that inserts data into the master, I determined that new records are being inserted correctly into slave2. Seriously out of ideas here. Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySql Host through Heartbeat
There's a 'report-host' option that can be set in the conf file to mask the host name. Sounds like this may be set. If you want to get the server's actual host name from within mysql, how about running a system command: mysql \! hostname; or mysql \! cat /etc/hostnames; (debian) mysql \! cat /etc/hosts; (CentOS/rhel) Regards, Scott On Wed, 2007-06-13 at 17:46 +0100, Ben Clewett wrote: What I know is that: Heartbeat with MySQL uses two IP's. That of the server, and that of the resource MySql. The former is fixed, the latter moves with MySQL when it's moved to another server. The one I need is the hostname of the physical server, not the resource. I've installed 5.0.41 and have found that the 'hostname' variable does report the hostname of the physical server. I have no idea how it does it :) I have my solution, thanks for the help, Ben Clewett. Baron Schwartz wrote: Gerald L. Clark wrote: Baron Schwartz wrote: Gerald L. Clark wrote: Ben Clewett wrote: Dear MySQL, I'm running 5.0.26 through Heartbeat. Which seems to work well, even as a replication slave and Heartbeat continously stopping and starting the server. The Heartbeat moves MySQL around from server to server when a failure occures. I am trying to find a way for MySQL to report the server host name on which it's currently sitting. Without any luck. Would any kind members know of a way of getting this information from MySQL? Many thanks, Ben Heartbeat moves the IP address around as well as the services. Your hostname should not change. DNS won't change, but the server's /etc/hostname will, right? Disclaimer: I'm no expert on this... I didn't even know the IP address moved too. I should read about Heartbeat. Baron Do you actually have /etc/hostname? RHEL and Centos do not. They do have an entry in /etc/sysconfig/network Debian and Gentoo have /etc/hostname and /etc/conf.d/hostname, respectively. I would think this is what /usr/bin/hostname uses, and probably where the hostname server variable gets set from in MySQL 5.0.41. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Diff between restore from tar and recover from power outage
If mysql has been shut down, and given enough time for the tables to be flushed to disk, then backing up the data dir using tar is fine. You can tar the data dir without shutting down, providing all transactions have been stopped and the tables have been flushed to disk. This is how we backup our servers, and we haven't had an issue yet (restoring daily to a testing environment). Regards, Scott Tanner On Mon, 2007-06-04 at 14:57 -0700, Wm Mussatto wrote: On Mon, June 4, 2007 14:21, murthy gandikota said: Hi When restoring a DB from a tar file, I had to repair some of the tables. Whereas when the DB is started after a power failure, everything works fine. I noticed this on RedHat linux and MySQL5. Any thoughts? Thanks Murthy What was in the tar file? If you tar'ed the data directory, it is almost guaranteed to fail since only part of the updated information may be actually written to disk. If the power failure occurred on a system with a correctly configured UPS, mysql probably got a shut down command so the disk files were in a stable state. Normally you should dump the databases and then tar that. (see mysqldump command for details). --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SAN backend for DB's
I think you'll need to do a lot of testing to yourself to find the right answer to that. The number of disks, type of disks, and raid configuration will have the most effect on performance. Personally, we had roughly 15% increase in performance from ditching our EMC clarion and going with external scsi arrays for each server. For us it was cost driven - 8 scsi disks (in raid 10 config) and the arrays were cheaper then 4 fiber disks. The extra heads and less latency made a noticeable difference - our database has a really high write rate. Regards, Scott On Thu, 2007-05-31 at 09:15 -0400, B. Keith Murphy wrote: So here is the brief situation. We have a coraid (www.coraid.com) SAN unit - the 1520 I believe. It is ATA-over-ethernet. Right now we have a about 500 gigs of data spread across five servers. To simplify things I would like to implement the coraid on the backend of these servers. Then all the data is served up out of the same place. Of course I would like to improve I/O throughput also. Googling shows that these units have good read speed but the write speed doesn't seem to be that impressive. Does anyone have any experience with these? Good? Bad? Maybe other SAN suggestions? Am I barking up the wrong tree? Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql creating lots of processes (not threads, linux processes)
I know I've run into this before, and it was a build issue. We build from source on redhat linux, so it may not be related to your issue. Anyways, I just did a test and built mysql from source with the flag --with-mysqld-ldflags=-all-static. I end up with the process spawning mysqld: ps -eaf | grep -c my 12 I removed that flag and rebuilt again, and now I have 2 processes. Just for reference: /configure --prefix=/usr/pkg/mysql5_test \ --with-libwrap=/usr/pkg/tcpd \ --with-mysqld-user=mydaemon \ --without-debug \ --with-unix-socket-path=/var/mysql/mysql.sock \ --with-client-ldflags=-all-static \ --enable-shared \ --enable-thread-safe-client \ --with-extra-charsets=none Scott On Wed, 2007-05-30 at 18:29 +0200, Quentin Gouedard wrote: Oh and by the way mysql works just fine on that machine. You can run queries without any problems. Only it keeps spawning new processes over again. It takes about 3h before the machine starts having problems due to memory getting full of useless mysql processes. I just got a new machine for my site, and it's happenning there too (although on none of my 5 other boxes), which is good cause i can do some more extensive testing before i actually use it. Another thing is, mysql won't stop. /etc/init.d/mysql stop just hangs. There are some processes that need to be killed manually in the end. I'm guessing this could have to do with the process owner being root. Some similar problems are reported here though: http://forums.gentoo.org//viewtopic-t-544730-highlight-mysql.html On 5/30/07, Quentin Gouedard [EMAIL PROTECTED] wrote: Merci Geoffroy, starting from the command prompt shows: # /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock 070530 18:01:28 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.38-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.38 However running a ps already shows multiple mysqld processes. Something i had not noticed so far: # ps -ef | grep mysqld mysql25752 27831 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 16560 25752 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock root 23390 16560 0 18:01 pts/2 00:00:00 /usr/sbin/mysqld --console --verbose --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock(etc.) the first process created is the only one with mysql owner. All the subsequent processes are own by root, and have as parent one of the previously created processes (not always the first one). Not sure if that helps in understanding this though. Thanks anyway for your help. On 5/30/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Try to start it with mysql_safe instead or try to start mysqld manually within a command prompt, without fork, to see what happen. ./mysqld --console --verbose --your_options Can you at least connect to mysql with a remote client on this server or not? Have a look on this page about starting issues: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html#starting- server Geoffroy -Message d'origine- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Quentin Gouedard Envoyé: mercredi 30 mai 2007 09:02 À: Scott Tanner Cc: mysql@lists.mysql.com Objet: Re: mysql creating lots of processes (not threads, linux processes) Nope, I'm using 5.0.38 on Gentoo, built via emerge in the exact same manner. Thanks for your answers guys. On 5/30/07, Scott Tanner
Re: mysql creating lots of processes (not threads, linux processes)
Sounds like your not using threaded libraries. Was mysql built differently, or are you using a different RPM on this server? Scott On Tue, 2007-05-29 at 22:49 +0200, Quentin Gouedard wrote: No, I have just collectd+mrtg, but i don't even use them to monitor mysql. I launch mysql via /etc/init.d/mysql start , and the script is the exact same as on the other servers. Even just after startup there's already 15-20 processes created. On 5/29/07, Geoffroy Cogniaux [EMAIL PROTECTED] wrote: Hi, It looks like automatic start-up called by a monitoring process (Nagios, ...). Have you such tools on your servers ? Geoffroy -Message d'origine- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Quentin Gouedard Envoyé: mardi 29 mai 2007 16:41 À: mysql@lists.mysql.com Objet: mysql creating lots of processes (not threads, linux processes) Hi, So I use mysql as the DB for a large site (up to 1 concurrent users at peaks). I have a front server as a reverse proxy and multiple (7) backend machines serving the site. Each machine has data strictly similar in nature and quantity. On 6 of these machines, I have 1 single mysqld process (process in linux terms): # ps -ef | grep mysqld | wc -l 2 There are generally 5-8 threads (processes as mysql means it) running when i do a show processlist; Now, on one of those machines there are huge number of processes for mysql. # ps -ef | grep mysqld | wc -l 34 Running just ps shows for each of these processes: mysql25952 10073 0 16:25 ?00:00:02 /usr/sbin/mysqld --defaults-file=/etc/mysql/my.cnf --basedir=/usr --datadir=/var/lib/mysql --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock This machine has no particular data, is doing nothing different than the others. The show processlist command also returns 5-8 processes. So where are these myqsld processes from ? There's like 20 at startup (instantly after launching mysql), but it keeps increasing, until i restart mysql or the server runs out of memory. I have compared the mysql configuration of this machine and the 6 other, variable by variable, and they are strictly identical. How come this server behaves differently ? What can I do to have the single-process behaviour on that machine too ? Thanks, Quentin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
On Fri, 2007-04-27 at 09:19 -0500, [EMAIL PROTECTED] wrote: So if one is doing a full mysqldump every night, all bin-logs can be deleted after this? On the slave - Yes. In fact I would highly recommend it before starting the slave processes again. This will reset the bin log's 'position' back to 0, giving you a clear starting point that will correspond to the backup. For the master server (if your backing up from the slave) you'll only want to purge the bin logs back to your slowest slave. If bin-logging is disabled, will master/slave syncing still occur? No, if you disable the bin-logs, replication wont be happening. Regards, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bin logs and mysql 4
Issuing a 'reset master' will purge all of the logs as well. I wouldn't just rm them, as they are being tracked in the index file. If you aren't running a slave, then these files are only good for data recovery purposes. Say a DBA goes crazy and deletes all of the databases mid-day (too much stress). You could restore the previous nights backup and run these bin logs up to the point of the delete command - a little bit of editing would be needed to do this, but you get the idea. For this to work smoothly, you need to reset the logs after every backup. If your using mysqldump, just add the --delete-master-logs option. If you want to turn the logs off, remove log-bin and log-bin-index from the conf file. Regards, Scott Tanner On Thu, 2007-04-26 at 09:47 -0700, Scott Haneda wrote: In the short term, see the manual page for PURGE MASTER LOGS. In the long term, write a cron job. innotop (http://sourceforge.net/projects/innotop) also has a new feature, unreleased because I just wrote it a few hours ago, which will help you figure out which binlogs can be purged s keystroke :-)--delete-master-logs I don't quite get this, if SHOW SLAVE STATUS shows empty result set, and I am just running one server, not a master + slave setup at all, its really rather simple. So, how would I ever know what logs I can safely delete or purge? Do I really need to use mysql to purge them or can I just `rm` them? I guess I could push this to cron? PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 31 DAY); My question is, what are these logs really good for, I assume restoration, and from what I read, but how do I know how far back I should keep? thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Replication Fallover
Ben, Check out Linux-ha ( http://linux-ha.org). We are using version 1 at my company, which is fairly easy to set up and there are a lot of good articles on it. With this you can create a virtual address to fail-over between systems, run commands / scripts during a fail over, and even kill the other node (stonith) if you have supporting hardware. For our setup, we have the client apps accessing the db through the virtual IP address. We have 2 mysql config files, one for the master and one for the slave (actually we have one m4'd config file, but you get the idea). During failover, we restart mysql on the slave using the master config file and transfer the virtual IP over. Here are a couple of key points for setting this up: 1. Slave must be running with the 'log-slave-updates' option. 2. After you run a back issue a reset master on the slave server. Hope this helps, Scott Tanner On Mon, Apr 02, 2007 at 08:43:35AM +0100, Ben Clewett wrote: I forgot to mention that I am running Linux. If anybody has some idea of software which can do this, I'd be very interested. Regards, Ben Ben Clewett wrote: Dear MySql, I'm looking into availability and wonder if any member might be able to help me. I have two databases, one Primary and one full Replication. Normally the primary is used for data input, reports are drawn from the replication. If I loose the Primary, do any members have any software they can recommend which: - Stops the replication daemon. - Sets the replication server to Read/Write. - Shuts down the primary. - Routes traffic to the replication. Any advise or ideas would be very useful... Regards, Ben Clewett. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: More than 4 CPUs?
From the articles I've read recently, 8 CPU's seems to be the point where the scalability ratio starts dropping. See the following articles for examples: http://developers.sun.com/solaris/articles/mysql_perf_tune.html http://corporateclub.mandriva.com/xwiki/bin/download/Main/Technology/mysql-performance-whitepaper.pdf I know there were some presentations at this years MySQL Conference that went over this (MySQL Performance Landscape comes to mind). You might be able to find a presentations on the mysql site. Regards, Scott Tanner On Fri, 2006-08-11 at 14:44 -0400, Ed Pauley II wrote: It seems like I once read that you don't get any performance gains in MySQL when you go above 4 CPUs per server. Is this correct? I was considering a 4 dual-core CPU machine. Should I go with a 2 dual-core machine instead? Thanks! -- Ed Pauley II -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don't understand why SCSI is preferred.
I am hoping the newer SATA II drives will provide SCSI performance at a reasonable price. It would be interesting to see if anyone has polled ISP's to see what they're using. I know they charge more (or at least they used to) for SCSI drives if you are renting a server from them. It would be interesting to see what their failure rate is on IDE vs SCSI vs SATA. Mike By newer SATA II drivers, are you referring to SAS drives? There is a great article on Tom's hardware on SAS drives as a replacement for standard SCSI: http://www.tomshardware.com/2006/04/07/going_the_sas_storage_way/index.html My company is in the process of switching to direct attached SAS arrays for our database servers, as part of a scale-out model. We've done testing between SATA, SCSI, and SAS arrays, and the SCSI and SAS systems were very comparative. The number of disks in the array seemed to have a larger effect then the type of disk. SAS also has more fiber like features then SCSI, making it better suited for HA environments. Just something else to consider. Regards, Scott Tanner Sys Admin www.amientertainment.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replicating queries to testing server
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]
Re: replication after editing bin logs
Can you post the failed SQL statement from your altered bin log, and maybe the preceding commands related to that table? Scott Tanner AMi Entertainment.net On Thu, 2006-03-09 at 08:28, Goldblatt, Eric wrote: Scott, The table already existed before the binlog. The strange thing is that there were inserts into this table earlier in the binlog, and they did not produce any error messages. Eric -Original Message- From: Scott Tanner [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 3:46 PM To: Goldblatt, Eric Subject: Re: replication after editing bin logs On Wed, 2006-03-08 at 14:51, Goldblatt, Eric wrote: snip- When I came to step 5, I submitted the SQL text file as a batch job: mysql -u root -p -e source binlogs_045_052_parsed After many hours I received the following error message: ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed': Table 'AB4539p2' is read only At this point, the mysql batch job terminated. If its saying the table is read only, it could be a simple permissions issue. Is this table being created from the binlog, or is it an existing table? Regards, Scott Tanner AMi Entertainment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Clarification on innodb status message
Does anyone know what the various lock modes mean in the show innodb status reports? We are investigating a dead-lock issue, and see lock_mode x and lock_mode s. We have transaction that locks a record in mode x, and is appears to be waiting for a lock on the same record in mode s - by the same transaction. Any info would be helpful. Thanks, Scott AMi Entertainment -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: host blocked, but can't see errors
I've been having this issue as well, happening more frequently to our production web /ejb servers. I've increased the logging to warning level, but my logs don't contain much (if any) information. Is there a way to increase logging to debug level, or get more information as to what is causing the problem? I want to make sure the application is not mishandling the connections before increasing the max_connection_errors. Thank you, Scott - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 25, 2004 12:06 AM Subject: Re: host blocked, but can't see errors Hello. In this context 'errors' means interrupted connections. Increase max_connect_errors. See: http://dev.mysql.com/doc/mysql/en/Server_system_variables.html Anne Ramey [EMAIL PROTECTED] wrote: Last week I got this message: Cannot connect DBI:mysql:formmail;myhost.net: Host 'myhost.net' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts' I couldn't find the errors anywhere. Not in any of the apache logs and not in the mysql error log: 041103 20:09:11 mysqld started InnoDB: The first specified data file ./ibdata1 did not exist: InnoDB: a new database to be created! 041103 20:09:11 InnoDB: Setting file ./ibdata1 size to 10 MB InnoDB: Database physically writes the file full: wait... 041103 20:09:12 InnoDB: Log file ./ib_logfile0 did not exist: new to be created InnoDB: Setting log file ./ib_logfile0 size to 5 MB InnoDB: Database physically writes the file full: wait... 041103 20:09:12 InnoDB: Log file ./ib_logfile1 did not exist: new to be created InnoDB: Setting log file ./ib_logfile1 size to 5 MB InnoDB: Database physically writes the file full: wait... InnoDB: Doublewrite buffer not found: creating new InnoDB: Doublewrite buffer created InnoDB: Creating foreign key constraint system tables InnoDB: Foreign key constraint system tables created 041103 20:09:13 InnoDB: Started /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.0.21-max' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-max binary I've also checked my syslog, messages log, and formmail log, nothing. It's only happening to this one host. it keeps happening over and over. I have to flush my host every day or so. Any ideas where to look? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- avast! Antivirus: Outbound message clean. Virus Database (VPS): 0448-1, 11/26/2004 Tested on: 11/29/2004 10:44:37 AM avast! - copyright (c) 2000-2004 ALWIL Software. http://www.avast.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
