Sync 2 live MySQL Databases
Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Any ideas, sugestion would be welcome. Thanks Cecil Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sync 2 live MySQL Databases
This is precisly the problem that I'm sitting with. It look like unless MySQL AB desides to build such a feature into one of the future releases this would beable to work. Mnr Cecil J.C. Brand Computer Services/ RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2940 Email :[EMAIL PROTECTED] Simon Garner [EMAIL PROTECTED] 5/26/2005 8:56:14 AM On 26/05/2005 6:40 p.m., Cecil Brand wrote: Hi, I was wandering if anyone know of a stable and reliabile way to sync 2 live mysql databases, both ways. I know I can use a master and slave, and yes have setup a few without any problem, but as all of us know this is just a one way downstream sync. I need to sync both ways and basicly live, the every 5 or 10 min cronjob sync with a perl/php/c++ app just won't cut it. Unfortunately, the simple answer is you can't. While it is possible to set up multi-master replication with MySQL, this is not useful for the vast majority of applications. If you are sending write queries to both masters then you have the possibility of the two servers becoming out of sync. This is particularly true with auto_increment primary keys - if you have a table and you send one insert statement to each server, both at the same time, then they might both assign the same auto_increment number to each row, and then they will both be forced to ignore the replicated inserts they receive from each other - screwing your table. Your options are to use MySQL Cluster (not an option for a lot of people as your entire database has to be stored in RAM), or if you're just after high availability, set up one-way replication with dynamic master failover (which I am currently trying to figure out how to do myself). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with Limit function in MySQL 4.1.9
Hi, I have the following problem, I'm converting from version 3.23.53 to 4.1.9 The following query was used SELECT * FROM news WHERE languages_id = 'english' AND expiry_ts = CURDATE() AND FCode = '999' AND DCode = 'Z999' ORDER by id DESC LIMIT 0, 3 it work perfectly on the version 3.23.53 (the tables are perfect copys on version 3 and 4) returning 3 results. when I run the query in version 4.1.9 nothing comes back. When I remove the LIMIT 0,3 version 4.1.9 returns all 5 rows. what I also observed was that if I change the query to SELECT * FROM news WHERE languages_id = 'english' AND expiry_ts = CURDATE() AND FCode = '999' AND DCode = 'Z999' ORDER by id DESC LIMIT 3 still nothing geet returned but if I change it to SELECT * FROM news WHERE languages_id = 'english' ORDER by id DESC LIMIT 0, 3 or SELECT * FROM news WHERE expiry_ts = CURDATE()ORDER BY id DESC limit 3 or any query with just one where clause it works. Can someone help/explain what is happening here?? It seems that with multiple where clauses and a limit that nothing get return. Thx for the help. Cecil Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] _ University of the Free State: This message and its contents are subject to a disclaimer. Please refer to http://www.uovs.ac.za/disclaimer for full details. Universiteit van die Vrystaat: Hierdie boodskap en sy inhoud is aan 'n vrywaringsklousule onderhewig. Volledige besonderhede is by http://www.uovs.ac.za/vrywaring beskikbaar. _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rebuilding Indexs on tables
Hi, I was wandering is there an easy way to rebuild indexs on tables I have the .frm and .MYD files. I read in the mysql manuals about backup_tables and Restore_table that rebuild the index but is there any other way to rebuild them. I'm using Mysql 3.23.53a on linux 2.1 AS thx Cecil Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete with Left Join???
Hi, I'm using: mysql Ver 11.18 Distrib 3.23.53a, for pc-linux-gnu (i686) I did the following query succesfully SELECT * FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL It returned all the row that was not in the right table. Know I want them deleted, but using: DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL gives me an error: mysql DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL; ERROR 1064: You have an error in your SQL syntax near 'LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL' at line 1 what is going on here and can someone help me?? thx Cecil Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: store JPG in MySQL DB
Wel it depends on what you want to do. If it's webpages, the file system idea is ideal, but if its a binary run by many people on different machines you'll have to map the directory holding the images to every machine. That a big no no, due to security risks getting higher. The db idea is then the best. Try to have a backup sever or two (if you can affort it ), for the change of the main one crashing. Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] Andrei Cojocaru [EMAIL PROTECTED] 05/20/02 05:45PM well, let's just say if it crashes it will mean longer recovery time, you are adding extra processing to the SQL server to handle data it wasn't meant to handle, storing it in the file system has the main advantage that if any changes occurs you can change it faster + easier, besides a database is supposed to store essential data so you don't overload it with things that can be calculated or made pretty easily, and I'd say an image fits under that category. And last time I checked my local hard drive is faster than my network connection :) The choice is yours, but if you have heavy load, I wouldn't use it. Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Sherzod B. Ruzmetov [EMAIL PROTECTED] To: Andrei Cojocaru [EMAIL PROTECTED] Cc: Sameer Maggon [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, May 20, 2002 9:37 AM Subject: Re: store JPG in MySQL DB Hi Andrei If I were you I would not store a binary file into the database, I'd store the information required to fetch it from somewhere else like the mySQL manual suggests But what difference does it make? Besides, I found storing it in the DB more convenient than in the file system. In that case, you will have to keep track ofboh the files in the file system, and their meta data in the mysql tables. Please advise Andrei Cojocaru [EMAIL PROTECTED] - Original Message - From: Sherzod B. Ruzmetov [EMAIL PROTECTED] To: Sameer Maggon [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Monday, May 20, 2002 12:26 AM Subject: Re: store JPG in MySQL DB For real-life example, check out http://cdbaza.ultracgis.com, and check out the filename of the thumbnails images. I'm SELECTing those images from the MySQL database. That's how it's done: 1_ Create a table to store your JPGs, and the column that holds JPG data should be declared as BLOB CREATE TABLE images ( image_id INT UNSIGNED NOT NULL AUT_INCREMENT PRIMARY KEY, image BLOB NOT NULL ); 2_ Now you are ready to load the JPG data into the image column. I use Perl to open the JPG file and dump the contents into the table, and the resulting query looks something like: INSERT INTO images SET image=here goes contents of the JPG file; Perl code that does this job would look like: local ($/); sysopen (JPG, test.jpg, O_RDONLY) or die $!; $dbh-do(qq|INSERT INTO images SET image=?|, undef, JPG); close (JPG); Sorry if you don't know Perl, but you should be able to do similar thing in any other language you might be using Good luck! -- Sherzod - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
