MySQL Replicatie

Setup Master Server

You'll need this in your my.cnf on your server:

log-bin   
server-id = 1

log bin turns on logging and server-id (which must be unique on your network) is requried for replication to work. I made the master in this case #1.

Then, setup the replication user (assumes you're running mysql 4.

mysql> GRANT REPLICATION SLAVE ON *.* TO repl@"%" IDENTIFIED '<password>'; 

restart your mysql server, if necessary do a:

Get a snapshot of current data for the slave after restarting with log-bin enabled, tar up your /var/lib/mysql databases that you are going to want to replicate and copy them over to the slave. i replicated all but the 'mysql' database.

If you are using MyISAM tables, flush all the tables and block write queries by executing FLUSH TABLES WITH READ LOCK command.

mysql> FLUSH TABLES WITH READ LOCK; 

tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir 

then, on your master, do a

mysql > SHOW MASTER STATUS; 

and note the 'File' name and the 'Position' number

then you can allow writes again on your master by doing an

unlock tables

Setup the slave server

add server-id to the my.cnf file (other things i added to my.cnf:

log-bin
server-id       = 2

Optionally:

log-slave-updates 
log-warnings 
replicate-ignore-db=mysql

restart the mysqld then:

mysql> CHANGE MASTER TO MASTER_HOST='<master host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>; 

replacing the values in <> with the actual values relevant to your system. Start the slave threads:

mysql> START SLAVE;

Once a slave is replicating, you will find a file called `master.info' and one called `relay-log.info' in the data directory. These two files are used by the slave to keep track of how much of the master's binary log it has processed. Do not remove or edit these files, unless you really know what you are doing. Even in that case, it is preferred that you use CHANGE MASTER TO command. NOTE : the content of `master.info' overrides some options specified on the command-line or in `my.cnf'

Copy crap

Comment Discussion icon some real world scenarios Posted by: sjwillis at 2004-01-05

.3 is my slave .6 is my master

so, on .6 i:

find -type d -maxdepth 1 > 6_dbase_listing

then flush with read lock, then i edit that file to remove the . and the mysql lines

tar -cvpf /usr/src/dbases.tar -T 6_dbase_listing,

then i grab the master info (file name, position) and unlock tables then i scp that tarball over to the master and

run a shell script called setup_replication.sql which populates the master.info file

then untar the tarball and restart the slave