配置mysql双master slave结构

配置mysql双master slave结构

机器准备3台虚拟机 A，B，C

A（IP：192.168.56.201）

B（IP：192.168.56.202)

C（IP：192.168.56.203)

服务器采用 centos 5.5 x86

一、安装mysql

下载mysql5.5.8.tar.gz  到 /usr/local/src

执行 yum -y install gcc gcc-c++ bison patch unzip mlocate wget automake autoconf cpp gettext readline-devel libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel zlib zlib-devel glibc glibc-devel glib2 glib2-devel bzip2 bzip2-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel libidn libidn-devel openldap openldap-devel openldap-clients openldap-servers nss_ldap expat-devel libtool libtool-ltdl-devel (依自己需要选择安装)

cd /usr/local/src

编译安装需先安装cmake,下载cmake-2.8.3.tar.gz

tar zxvf cmake-2.8.3.tar.gz

cd cmake-2.8.3

./configure --prefix=/usr

gmake

gmake install

groupadd mysql

useradd mysql

mkdir -p /mydata/mysqldb

chown -R mysql:mysql /mydata/mysqldb

tar zxvf mysql5.5.8.tar.gz

cd mysql5.5.8

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/webserver/mysql -DMYSQL_DATADIR=/mydata/mysqldb -DWITHOUT_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_MYISAM_STORAGE_ENGINE=1 -DSYSCONFDIR=/etc/ -DWITH_SSL=yes -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_READLINE=on

make

make install

/usr/local/webserver/mysql/scripts/mysql_install_db --basedir=/usr/local/webserver/mysql/ --datadir=/mydata/mysqldb --user=mysql

cp /usr/local/webserver/mysql/support-files/my-large.cnf /etc/my.cnf

cp /usr/local/webserver/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld

chkconfig --add mysqld

service mysqld start

mysql -uroot -p

二、安装双master

A：

mysql> grant replication slave,file on *.* to 'repl1'@'192.168.56.202' identified by '123456';

mysql> grant slave on *.* to 'slave'@'192.168.56.203' identified by '123456';

mysql> flush privileges;

B:

mysql> grant replication slave,file on *.* to 'repl2'@'192.168.56.201' identified by '123456';

mysql> flush privileges;

service mysqld stop  停止所有mysql服务

vim /etc/my.cnf

A:

user=mysql

log-bin=mysql-bin

server-id=1

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

skip-name-resolve

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=1

B:

user=mysql

log-bin=mysql-bin

server-id=2

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

skip-name-resolve

sync_binlog=1

auto_increment_increment=2

auto_increment_offset=2

C:

user=mysql

log-bin=mysql-bin

server-id=3

binlog-do-db=test

binlog-ignore-db=mysql

replicate-do-db=test

replicate-ignore-db=mysql

log-slave-updates

slave-skip-errors=all

skip-name-resolve

sync_binlog=1

[root@DB1 ~]# /usr/local/webserver/mysql/bin/mysqld_safe &

进入mysql的shell

A：

mysql> flush tables with read lock;

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000012 |     129  | test         | mysql            |

+------------------+----------+--------------+------------------+

B:

同样操作

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000016 |     106  | test         | mysql            |

+------------------+----------+--------------+------------------+

A：

mysql> change master to

    -> master_host='192.168.56.202',

    -> master_user='repl2',

    -> master_password='123456',

    -> master_log_file='mysql-bin.000016',

    -> master_log_pos=106;

mysql> start slave;

B:

mysql> change master to

    -> master_host='192.168.56.201',

    -> master_user='repl1',

    -> master_password='123456',

    -> master_log_file='mysql-bin.000012',

    -> master_log_pos=129;

mysql> start slave;

C:

mysql> change master to

    -> master_host='192.168.56.201',

    -> master_user='slave',

    -> master_password='123456',

    -> master_log_file='mysql-bin.000012',

    -> master_log_pos=129;

mysql> start slave;

使用命令查看 show processlist\G  查看进程情况

在A或B 上test 数据库创建表 执行SQL语句即可同步