Eric: Created page with "=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 ''; restart your mysql server, if necessary do a: Get a snapshot of current data fo..."

2022-04-05T09:34:33Z

Created page with "=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 fo..."

New page

=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

MySQL Replicatie - Revision history