Re: MySQL Replication
Jason Williard wrote: I am trying to understand exactly how replication works. So far, I see that changes made on a master server are replicated to the slave server(s). However, if a change is made on a slave server, is that replicated back to the master as well as all other slaves? It can be if you enable circular replication (A--B--A) . However, keep in mind that there is no way to prevent simultaneous updates (i.e., locking a table on A for update won't apply a lock to B..) I am asking this question as I try to develop a plan for more efficient web servers. Here is what I am planning. Please let me know if this sounds smart, or like a bad idea. Server 1: Redhat MySQL Master Servers 2 3: Load-Balanced Redhat Apache web servers w/MySQL Slaves Servers 2 3 will be serving the same content and will need access to the same data from the MySQL server(s). I am hoping that running MySQL on each of the web servers will help to reduce the overall load on the servers. Are there update operations occuring on servers 2 and 3? What's the bulk of your operations - selects or updates? There are a couple of ways to go with this. You can use clustering - in which case you can apply changes to the cluster and everything should stay in sync, or (the more straightfoward way) you can simply have all write operations performed on the master - use the slave servers for lookup only. If you use the second option (this assumes that the bulk of your operations are 'select' statements) you might be able to squeeze out some more performance by having the replica servers use a MyISAM table type (Assuming you use InnoDB on the master). If needed you can design a circular replication scheme - and have one of the slaves switch to a master role when the master is unavailable. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 http://www.otg-nc.com Phone: 877-258-8987/919-463-0999 Fax: 919-386-0158 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which Engine?
John Hoover wrote: I need some advice re my choice of a storage engine for transaction-safe processing and including tables that are not transaction-safe within transactions. The problem: We need to insert related records into several different tables and be sure that all the insertions succeeded. It seems that transactions are the recommended way of achieving this and I was thinking of using InnoDB tables. However, I'm not sure if that is the best engine to use - can anyone give me reasons for selecting a specific transaction-safe engine? You can use BDB or InnoDB - InnoDB provides row level locking, BDB provides page level locking. I'd say InnoDB is the way to go (usually). Also, some of my insertions will involve the mySQL tables (creation of a new user and granting privileges, for example). According to the manuals, those tables use the myISAM engine and can not be changed to any other engine. What is the best way to handle insertion errors on myISAM tables? I had planned to test for an error after each operation and, if one occurred, manually undo whatever previous operations had already succeeded. That's a lot of work if the operation involves multiple tables and I'd like to know if there is a better alternative. Finally, if I do handle errors manually, what should I do if there is an error in the error handler? For example, suppose I've inserted one record and then an error prevents insertion of the related record so that I have to delete the previously inserted record. Is it possible for the delete to fail? If so, I'll have a partial transaction that can't be completed and can't be undone - what should I do to clean up? What are your insertion operations? Typically, you would use GRANT statements to add users the these tables - and those statements (if they fail) won't do any GRANTing. I wouldn't grant access using insert statements - you'll be flushing your privilege tables regularly. Unless you are using the Host table, I'd recommend you do the following: 1. Prior to modifying a user, use the 'show grants' statement to find out what access the user has - store that. 2. Perform all your GRANT operations. 3. If a single operation fails, remove the user and execute the stored access (from step 1) for the user to restore his/her access. - if the user didn't already exist, just remove all their access. For users that won't connect to the database directly, you probably don't want to create individual accounts - as if the user connects directly they can perform operations outside the bounds of your application (where you may implement business logic). Assuming you stick with grant statements, it shouldn't be too difficult to maintain integrity when you want to do your pseudo-transactions. Use InnoDB everywhere else. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: removing ibdata1 if some/all tables are not InnoDB?
InnoDB does purge deleted rows from the ibdata files. Certain PostgreSQL advocates have been spreading a claim that InnoDB would not do that, but the claim is false. If your ibdata file keeps growing indefinitely, please check with SHOW INNODB STATUS that you do commit all your transactions. If a transaction stays open for months, then the purge cannot remove deleted rows. If you convert ALL your tables from InnoDB to MyISAM, then you can remove the ibdata files and ib_logfiles. If you put skip-innodb to my.cnf, then those files will not be created again. I don't think there is a question as to whether or not InnoDB will purge data and re-use space, the question is whether or not the ibdata files will be shrunk when that space is purged. My understanding (and experience) has always shown that ibdata files - while they may purge and re-use unused space, will not shrink themselves based on the actual space usage. Is that not correct? I.e., if I have 100 MB of table data, and say - delete 6 tables (which would result in InnoDB recovering all that space), that results in only 10MB of space being used, the file will be re-sized to 10MB - or something smaller than 100 MB. The practical example would be if I were to accidentally add 1GB of data to my InnoDB tablespace, and then remove it. Would my total ibdata file sizes total less than 1GB of space (now I'm just using 100MB)? If that were the case it would be a simple matter of switching to 'tablespace per table', migrating the data to the individual tables (which would shrink the ibdata files), re-structuring the ibdata files (to use other partitions, etc.) an the moving the data back into the tablespace. Thanks -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: Alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, January 03, 2006 6:55 AM Subject: Re: removing ibdata1 if some/all tables are not InnoDB? HI Carl, The ibdata file growth can be stopped by removing the autoextend keyword in the my.cnf file. In your my.cnf file the entry might be innodb_data_file_path = ibdata1:256M:autoextend If you want to stop the growth of that file and add another file then this is what you want to do. 1. Stop the mysql server 2. Get the size of the ibdata1 file in MB (Lets say its 5600MB in size) 3. edit the my.cnf file and replace innodb_data_file_path = ibdata1:256M:autoextend with innodb_data_file_path = ibdata1:5600M;ibdata2:256M:autoextend 4. Start the server. This will stop that file from growing and a new file will be added that can pushed on to a different disk and symlinked into the ibdata directory. Data growth is a problem in all table types. Even if you migrate to MyISAM you need space. See whether there is log_bin turned on the server. If so there might be lots of bin log files that you can do a cleanup on. Bin logs occupy a great deal of space. Thanx Alex, MySQL DBA Yahoo! On Tue, 03 Jan 2006 08:28:24 +0530, Carl Brewer [EMAIL PROTECTED] wrote: Hello, I'm stuck with a rapidly decreasing amount of available disk space and a requirement to keep a lid on the size of our databases. We're using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of transactions keeping short term track of webserver sessions, which we don't need to keep logs of for very long. I have a number of databases, almost all of which are using MyISAM or HEAP, and one database using InnoDB. As such (or at least, as I understand it) we have a ibdata1 file that will grow forever and AFAIK there's no way to stop it growing forever for as long as we have that InnoDB database. Am I correct? I'm no MySQL guru, my parsing of TFM and googling around and finding bug and feature requests for ibdata1 purging suggests that this is the case. If so, if I drop the InnoDB database, stop mysqld, delete (UNIX filesystem) the imdata1 file, restart mysqld and import a (modified to be MyISAM) dumped copy of the InnoDB database, will that work without damaging anything and then not leave me with another infinatly growing imdata1 file? Am I correct in assuming that InnoDB databases are meant for sites where disk space is not ever likely to be an issue, and MyISAM is a more suitable database engine for our much tighter disk space situation? I may have missed a section of the doco that discusses why one would choose an engine over another? Thanks for any advice, Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General
Re: Problem installing MySQL 5.0
You can force the installation (--nodeps) when installing the RPM package. Even if you build DBI (and not install it from RPM) it won't be in the RPM database, and you'll get the same error. You should be able to get a FC2 RPM for the DBI module - download that RPM and install it, then install MySQL - that would be the easiest course of action... -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com Francesco Vincenti wrote: Hello everybody, This is the first time I try to install MySQL 5.0, I think I followed the steps showed by the documentation in the right way, but when I run the command: rpm -i MySQL-server-Version-.i386.rpm I get back the error: error: Failed dependencies: perl(DBI) is needed by MySQL-server-standard-5.0.18-0.rhel3 I am trying to install MySQL on this platform: - CPU:AMD Athlon 800 Mhz - Operating System: Linux Fedora core 2, release 2.6.5-1.358 The packages I have downloaded are: - MySQL-server-standard-5.0.18-0.rhel3.i386.rpm - MySQL-client-standard-5.0.18-0.rhel3.i386.rpm After the error, I found a few information on internet about perl(DBI), so I downloaded it and installed it on my system. The test step (make test) ended with error 355 (??), but I run anyway make install which ended without problems, I think. At this point I do not know what to do, so I need your help to solve this problem. Greetings Francesco Vincenti * * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: logging issue
Jon Miller wrote: System: Red hat 7.2 My SQL modules: MySQL-devel-4.0.13-0 php-mysql-4.1.2-7.2.6 MySQL-shared-3.23.55-1 MySQL-server-4.0.13-0 MySQL-client-4.0.13-0 CAMysql-9.0-220 Msql-Mysql-DBI-perl-bin-1.1823-1 I would like to log or turn on the facility to do a verbose logging to troubleshoot an issue I'm having with a program on another (mail) server trying to access the mysql server. From the mail server I can issue the following: mysql -h 192.168.10.4 -u mailwatch -p and enter the password and it connects. Yet from within the initial program it does not work and in the mail logs it has: Jan 3 18:19:31 mail MailScanner[11376]: Unable to initialise database connection: Can't connect to MySQL server on '192.168.10.4' (110) I've ask the mailscanner list, the mailwatch list and the postfix list and we are not getting anywhere. If you're not using SSL, I'd recommend that you fire up ethereal on the client or the server . You can have ethereal show you the entire conversation - should help you filter out several different types of problems that could occur. A lot of things could be happening outside of what MySQL would see (for example, the mailscanner could be trying to connect to the wrong port on the server - the (110) makes me wonder if it's trying to connect to port 110). This would help diagnose a lot of those. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 Phone: 877-258-8987/919-463-0999 http://www.otg-nc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: removing ibdata1 if some/all tables are not InnoDB?
You can stop the auto-growth of your ibdata1 file, and add additional ibdata files (as needed) on different disks/partitions. However, you cannot currently shrink the file without some work.. Check out the MySQL documentation for innodb_data_file_path (that is the config setting that you would use in the my.cnf file to set things up). You'll have to find out the size in MB of your current file (ls -lh) when you do this (if you want to start a new innodb data file on a separate disk, etc.), since in my experience MySQL will complain if you specify the size of the file incorrectly. InnoDB is great when you have a lot of transactions going on, need rollback capability (batch operations that should either succeed as a whole or fail as a whole) - or you need ACID compliance. MyISAM is fast for lookups, but requires a table lock to be acquired for updates, and most inserts (except in certain cases) - so its fast for lookups, but not as good for updates. Each have their own distinct advantages... HEAP is good when you don't care if your data sticks around, and you need fast access to it (such as web cookies...) As far as purging - you'd be best off doing an export, trash your InnoDB tables, and then import . -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 http://www.otg-nc.com Phone: 877-258-8987/919-463-0999 Fax: 919-386-0158 Expert MySQL Training Carl Brewer wrote: Hello, I'm stuck with a rapidly decreasing amount of available disk space and a requirement to keep a lid on the size of our databases. We're using MySQL 4.1.12 as bundled with RHEL ES 4. We do a lot of transactions keeping short term track of webserver sessions, which we don't need to keep logs of for very long. I have a number of databases, almost all of which are using MyISAM or HEAP, and one database using InnoDB. As such (or at least, as I understand it) we have a ibdata1 file that will grow forever and AFAIK there's no way to stop it growing forever for as long as we have that InnoDB database. Am I correct? I'm no MySQL guru, my parsing of TFM and googling around and finding bug and feature requests for ibdata1 purging suggests that this is the case. If so, if I drop the InnoDB database, stop mysqld, delete (UNIX filesystem) the imdata1 file, restart mysqld and import a (modified to be MyISAM) dumped copy of the InnoDB database, will that work without damaging anything and then not leave me with another infinatly growing imdata1 file? Am I correct in assuming that InnoDB databases are meant for sites where disk space is not ever likely to be an issue, and MyISAM is a more suitable database engine for our much tighter disk space situation? I may have missed a section of the doco that discusses why one would choose an engine over another? Thanks for any advice, Carl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
