配置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语句即可同步