Master-Slave MySQL-5.1 Replication

Environment:
RHEL: 5.3
MySQL: 5.1.54



Master :

1. MySQL user and group

$ groupadd mysql
$ useradd -g mysql mysql


2. Download and install MySQL

$ 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


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


4. Master config
$ 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


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


6. Launch master
$ 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 &


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




Slave :

1. Copy master application and data as slave

$ 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/


2. Slave Config
$ 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


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



Test :

1. Check both master and slave were launched
$ ps aux | grep mysql


2. Try creating something in master
# 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));


3. Check slave to see table ‘abc’
$ mysql -hlocalhost -uroot -P3416 -S /tmp/mysql-slave1.sock -p
mysql> SHOW DATABASES;
mysql> USE appdb1;
mysql> SHOW TABLES;

Posted in MySQL, RHEL | Tagged , | 2 Comments
  1. BOSS,新年快乐。。