MySQL Replication

From Kolmisoft Wiki
Revision as of 23:08, 19 December 2007 by Admin (talk | contribs) (→‎Log files)
Jump to navigationJump to search

Server 1 my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock  
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
bind-address = 0.0.0.0
binlog-do-db = mor
server-id = 10
auto_increment_increment = 10
auto_increment_offset = 1
master-host = 111.111.111.111
master-user = rep1
master-password = rep1
master-port = 3306
master-connect-retry = 60
replicate-do-db = mor
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
#slave-skip-errors=all
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Server 2 my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock  
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
bind-address = 0.0.0.0
binlog-do-db = mor
server-id = 20
auto_increment_increment = 20
auto_increment_offset = 2
master-host = 222.222.222.222
master-user = rep2
master-password = rep2
master-port = 3306
master-connect-retry = 60
replicate-do-db = mor
log-bin = /var/lib/mysql/mysql-bin.log
log-bin-index = /var/lib/mysql/master-log-bin.index
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
#slave-skip-errors=all  
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/lib/mysql/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



Log files

In order for MySQL Replication to function properly you need to check log files in /var/lib/mysql

Open master-log-bin.index, it should contain names of the files which really exist in same folder. If there's some mismatch - edit this file: enter missing names for files or delete file names which does not represent real files in this folder.

Same with file slave-relay-log.index



Testing

Login to MySQL console on both servers:

mysql -u root -p 

Then:

SHOW SLAVE STATUS\G