Hello guys, i have set up a master slave MySQL replications for dbmail many times, and never had any problem. I use the mysqldump way. NOTE: i only dump and synch the dbmail database, not the whole mysql instance.
0. check server-ids are set on slaves 1. (master) open a mysql sessions with root privileges (or the one needed for 'flush tables with read lock;', dunno what it is) 2. (master) 'flush tables with read lock;' and DON'T exit. 3 (master) 'show master status;' keep note of binlog file and position 4. (master) open a shell and 'mysqldump -p -u root --opt --routines --hex-blob --lock-tables --triggers --database dbmail_db > dbmail.sql' and copy & import it to your slave 4a. ..or for saving some downtime you can mysqldup ..... | ssh [email protected] 'mysql -u root -pMYPASS' if you use pubkeys 5. when done you can release locks on your master. Close the mysql session where you issued the flush tables 6. (slave) change master TO master_log_file='filename', master_log_pos=xx (the one you took note before) and other master connection stuff 7. (slave) start slave; Hope this helps in someway. Ettore Del Negro Il 13/1/2011 9:07 PM, Gordan Bobic ha scritto: > On 01/13/2011 06:39 PM, Paul J Stevens wrote: >> On 01/13/2011 06:34 PM, Larry H. wrote: >>> >>> So just to clarify I would be rsyncing the contents of the dbmail >>> database >>> folder (/var/lib/mysql/dbmail) to the slave. Then stopping the >>> master and >>> rsyncing the innodb log files in (/var/lib/mysql) to the slave. >>> Also, won't >>> rsync miss some of the new data coming in on the master as it completes >>> certain tables in the data directory during the rsync or is that >>> what the >>> second pass is for? >> >> >> Sucks! Don't do that! >> >> /var/lib/mysql/dbmail will not contain anything! >> >> If you need to resync your slave - assuming you need to do that - you >> don't need any downtime. >> >> On the slave: >> >> mysql> slave stop; >> # create a consistent snapshot of the master >> mysqldump -h $_host -u $_user -p$_pass --quick --single-transaction \ >> --flush-logs --master-data=2 --all-databases> $_file >> # extract the MASTER log filename and file position from the dumpfile: >> sql=`head -n30 /var/tmp/slave.sql|grep 'CHANGE MASTER'|cut -b3-|sed >> 's/;$//'` >> # the sql variable will now look like: >> # "CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', >> # MASTER_LOG_POS=98," >> # create a valid 'change master' query >> sql="$sql,master_host='$MASTERIP',master_user='replication',master_password='${REPLI_PW}';" >> >> # load the dump >> cat /var/tmp/slave.sql | mysql >> rm -f /var/tmp/slave.sql >> # make the slave catch-up from the logfile position >> echo $sql|mysql >> echo "slave start;" | mysql > > Of course that will work fine, too, but rsync is way quicker. Database > dump time isn't too bad, but the load time can be slow. MySQL is also > still buggy on occassions with regards to table dependencies - it will > sometimes dump the tables in such an order that loading will fail due > to referential integrity constraints. The rsync approach is quicker > and immune to this issue. > >> This is also one of the procedures mentioned in the mysql docs: >> >> http://dev.mysql.com/doc/refman/5.5/en/replication-howto-mysqldump.html >> >> except that using single-transaction means you don't need to lock the >> master. > > You'll find the master will grind to a halt anyway while you're doing > this, as soon as the write-ahead log fills up. With small-ish > databases it's not a big deal, but if the size is in the 100GB+ range > the rsync method will be a lot more workable. > > Gordan > _______________________________________________ > DBmail mailing list > [email protected] > http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail > _______________________________________________ DBmail mailing list [email protected] http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
