List:Internals« Previous MessageNext Message »
From:Christian Rabe Date:October 23 2000 10:22pm
Subject:Re: Replication
View as plain text  
>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 <sasha@stripped>
 / /|_/ / // /\ \/ /_/ / /__  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.

Thread
Re: Replicationsasha23 Oct
Re: ReplicationChristian Rabe24 Oct
Re: Replicationsasha24 Oct
Re: ReplicationChristian Rabe24 Oct