kimbs.cnkimbs.cn

Environment:
RHEL: 5.3
MySQL: 5.1.54

Master :

1. MySQL user and group

[codesyntax lang=”bash”]

$ groupadd mysql
$ useradd -g mysql mysql

[/codesyntax]

2. Download and install MySQL

[codesyntax lang=”bash”]

$ wget http://mysql.ntu.edu.tw/Downloads/MySQL-5.1/mysql-5.1.54.tar.gz
$ tar xzf mysql-5.1.54.tar.gz
$ cd mysql-5.1.54
$ ./configure --prefix=/usr/local/webserver/mysql-5.1.54-master --enable-assembler --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-extra-charsets=all --enable-thread-safe-client --with-big-tables --with-readline --with-ssl
$ make
$ make install

[/codesyntax]

3. Create database
[codesyntax lang=”bash”]

$ cd /usr/local/webserver/mysql-5.1.54-master/
# database data located at
$ ./bin/mysql_install_db --datadir=/data/mysql-5.1.54/master

[/codesyntax]

4. Master config
[codesyntax lang=”bash”]

$ cp /data/soft_misc/mysql-5.1.54/support-files/my-huge.cnf /data/mysql-5.1.54/master/my.cnf
$ vi /data/mysql-5.1.54/master/my.cnf
port            = 3406
socket          = /tmp/mysql.sock
datadir         = /data/mysql-5.1.54/master
# databases that need to backup
binlog-do-db     = appdb1
binlog-do-db     = appdb2
binlog-ignore-db = mysql
binlog-ignore-db = test

[/codesyntax]

5. Password for root
[codesyntax lang=”bash”]

$ /usr/local/webserver/mysql-5.1.54-master/bin/mysqladmin -uroot -hlocalhost -P3406 -p password root

[/codesyntax]

6. Launch master
[codesyntax lang=”bash”]

$ chown -R mysql.mysql /data/mysql-5.1.54/master/
$ /bin/sh /usr/local/webserver/mysql-5.1.54-master/bin/mysqld_safe --defaults-file=/data/mysql-5.1.54/master/my.cnf &

[/codesyntax]

7. Create account for replication
[codesyntax lang=”bash”]

$ mysql -hlocalhost -uroot -p
mysql> GRANT REPLICATION SLAVE ON *.* TO 'mysqlrepl'@'localhost' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;

[/codesyntax]

Slave :

1. Copy master application and data as slave

[codesyntax lang=”bash”]

$ cd /usr/local/webserver/
$ cp -Rp mysql-5.1.54-master/ mysql-5.1.54-slave1/
$ cd /data/mysql-5.1.54/
$ cp -Rp master/ slave1/

[/codesyntax]

2. Slave Config
[codesyntax lang=”bash”]

$ vi /data/mysql-5.1.54/slave1/my.cnf
port            = 3416
socket          = /tmp/mysql-slave1.sock
datadir         = /data/mysql-5.1.54/slave1

server-id            = 11
master-host          = localhost
master-port          = 3406
master-user          = mysqlrepl
master-password      = 123456
# connection retry time
# master-connect-retry = 60
# read only for slave if needed
# read-only           = 1

# databases which need to backup
replicate-do-db     = appdb1
replicate-do-db     = appdb2

# databases ignored
replicate-ignore-db = mysql
replicate-ignore-db = test

[/codesyntax]

3. Launch Slave
[codesyntax lang=”bash”]

$ /bin/sh /usr/local/webserver/mysql-5.1.54-slave1/bin/mysqld_safe --defaults-file=/data/mysql-5.1.54/slave1/my.cnf &

[/codesyntax]

Test :

1. Check both master and slave were launched
[codesyntax lang=”bash”]

$ ps aux | grep mysql

[/codesyntax]

2. Try creating something in master
[codesyntax lang=”bash”]

# go to master
$ mysql -hlocalhost -uroot -p
mysql> SHOW MASTER STATUS;
mysql> CREATE DATABASE IF NOT EXISTS appdb1;
mysql> USE appdb1;
mysql> CREATE TABLE abc (id int(4));

[/codesyntax]

3. Check slave to see table ‘abc’
[codesyntax lang=”bash”]

$ mysql -hlocalhost -uroot -P3416 -S /tmp/mysql-slave1.sock -p
mysql> SHOW DATABASES;
mysql> USE appdb1;
mysql> SHOW TABLES;

[/codesyntax]