From: Christian Rabe Date: October 23 2000 10:22pm Subject: Re: Replication List-Archive: http://lists.mysql.com/internals/46 Message-Id: <001801c03d3f$c79f1720$b401a8c0@gis.priv> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit >Hi, > >Cristian, for future reference, please always CC your emails regarding MySQL to >the appropriate list in addition to e-mailing the developer. Otherwise, if the >developer is sick or travelling, it will be a while before you get a response >:-) Ok ;) >>are there any plans to make the replication more customizable ? >>My idea would be a table like the grant-tables to tell MySQL which tables to bin-log. >>Tablename: replicate >>Database: mysql >>+--------------+--------+ >>| Database | Table | >>+--------------+--------+ >>| mydb1 | % | >>| mydb2 | table1| >>+--------------+--------+ >>Then you would need a command 'flush replication' or so, to apply changes. >>I know it's a lot of work, and I would try it myself if I had more time and >>c-experience. >The problem with that is that it would make it replication slower (as it takes >more work to figure out that a certain table should not be replicated, but it is >much simpler for the whole database) Yep, and I'm the last who would want it to get slower. >, and will not work very well for queries >that modify more than one table. There are querys that modify more than table ? >If you do not want certain queries to be >replicated, you can currently do one of the following: > * give your clients the PROCESS privilege and exectue SET SQL_LOG_BIN = 0 >before queries that should not be replicated >* create a separate database, no_repl_db, run master mysqld with >binlog-ignore-db=no_repl_db, and exectue use no_repl_db before queries that >should not be replicated ( in this case the tables should be referenced with >their full name including the name of the database > * create no_repl_db, use no_repl_db; run the slave with >--replicate-ignore-db=no_repl_db Yep, the first point will do fine. The other points we used allready, but there are some tables we would hate to separate. >>AND the replication runs much better since I disabled the code with the 'goto err;' near line 928 in >slave.cc (3.23.26) ;) >In other words, just barge along if the data on the slave is inconsistent as if >nothing happened. This generally not a good idea - a query that succeeded on the >master should never fail on the slave. If it does, one should investigate why, >and fix the bug. This usually results from modifying the slave table under the >feet of the slave thread. Yep, it's exactly what we do. We simply droped all tables we didn't want to replicate. Helps by the way with temporary tables too;) Only the bin-log is still growing. >>BTW: It's cool that MySQL is so fast. Oracle 8i died as we tried to store our realtime-stockdata in it. 200 >inserts/second was too >>much for it. MySQL made it up to 3000 ;) >So did MySQL actually survive and just did not increase in throughput as you >kept increasing the load, or did it die? If it did, it would be nice to >investigate why - it should give messages if it is out of resources, and still >continue working the best it can. No, both didn't die, but we could not get any more out of them. >>Only the replication fails now an then with something about >bogus-data :( >See my comments above - if your slave tables are thrown out of sync with the >master by some extrenal updates, you need to figure out who is doing that and >punish him :-) If you do not have any external updates, then it is a bug that we >need to find and fix. Another reason for this could be if you are using >temporary tables. There are surely no updates on the slave. We started developing on one DB (now Master) and later added the Slaves and redirected all selects to them. In the error-log it says... Error reading packet from server: Lost connection to MySQL server during query (0) ...and this every 60 seconds Error reading packet from server: bogus data in log event (0) At this point (during development) the DB is running with an average of 160 querys/second, surely not the problem. There are about 2GB binlog/day at the moment. After we set the page online it will be twice to thrice this much. ONE other thing I forgot, log-rotation. We do it actually with an cron-triggered sh-script but it would be more comfortable (secure) to do via mysql. /*********************************************************/ #!/bin/sh OLDPWD=`pwd` cd /mysql/dbs INDEX_FILE=/mysql/dbs/oracle-bin.index TMP_FILE=/var/tmp/mysql_rotate.tmp umask 117 for i in `cat $INDEX_FILE`; do LAST_BIN=$i done echo "Aktuelles File: $LAST_BIN" FDATE=`date -r $INDEX_FILE +%d` if [ $FDATE -ne `date +%d` ]; then echo "I've to flush(Date)" /mysql/bin/mysqladmin -umysql -pXXXXX flush-logs elif [ `ls -s $LAST_BIN | cut -d" " -f1` -gt 524288 ]; then echo "I've to flush(Size)" /mysql/bin/mysqladmin -umysql -pXXXXX flush-logs else # Nach dem Flushen sollte man nicht gleich versuchen, das Index-File zu bearbeiten echo "I don't have to flush ..." echo " ... try to delete old files" echo echo "---> IndexFile (davor) ---------------------------------------->" cat $INDEX_FILE echo "---> ls -l ---------------------------------------------------->" ls -l /mysql/dbs/oracle* echo "--------------------------------------------------------------->" echo for i in `find . -name "oracle-bin.[0-9][0-9][0-9]"`; do if [ `date +%Y%m%d%H` -gt `date -r $i +%Y%m%d%H` ]; then echo "Lösche: $i" grep -v "$i" $INDEX_FILE >$TMP_FILE chown mysql:mysql $TMP_FILE cp $TMP_FILE $INDEX_FILE chown mysql:mysql $INDEX_FILE rm -f $i rm -f $TMP_FILE else echo "Halte: $i" fi done echo echo "---> IndexFile (danach) --------------------------------------->" cat $INDEX_FILE echo "---> ls -l ---------------------------------------------------->" ls -l /mysql/dbs/oracle* echo "--------------------------------------------------------------->" echo fi cd $OLDPWD /*********************************************************/ Regards, Christian "Maverick" Rabe IT-Development wallstreet:online AG ------------------------------------------ http://www.wallstreet-online.de Tel.: 03 37 01 5 29 16 Fax.: 03 37 01 5 29 19 -- MySQL Development Team __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Sasha Pachev / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Provo, Utah, USA <___/ --------------------------------------------------------------------- Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before posting. To request this thread, e-mail internals-thread45@stripped To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail internals-unsubscribe@stripped instead.