linux：centos6.6
虚拟ip(vip)：192.168.20.199
mysql master：192.168.20.193
mysql slave：192.168.20.195
（这里只是为了后文方便区分，一个叫master，一个叫slave，其实在主主复制中，都是master也都是slave，没有主从之分）
二、分别在两台服务器上安装同版本MySQL
1、安装mysql
# yum -y install mysql-server mysql
# /etc/init.d/mysqld start #启动mysql 注意权限 非root使用sudo
# chkconfig mysqld on #设为开机启动
# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf #拷贝配置文件（注意：如果/etc目录下面默认有一个my.cnf，直接覆盖即可）
2、为root账户设置密码
# mysql_secure_installation
回车，根据提示输入Y
输入2次密码，回车
根据提示一路输入Y
最后出现：Thanks for using MySQL!
mysql密码设置完成，重新启动 mysql：
# /etc/init.d/mysqld restart #重启
# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
三、修改两台mysql配置文件
修改master的配置文件： vi /etc/my.cnf
#在[mysqld]添加如下内容#server-id = 193 #//只要主从不一样就行 我这里以ip地址后3位方便区分 log-bin = mysql-bin #开启二进制日志 binlog_format = MIXED #非必需 relay-log = mysqld-relay-bin #定义中继日志名，开启从服务器中继日志 #binlog-do-db=db_name #只复制db_name数据库 binlog-ignore-db = mysql #不复制mysql数据库 binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% #不复制mysql所有表 replicate-wild-ignore-table = information_schema.% replicate-wild-ignore-table = performance_schema.%
修改slave的配置文件： vi /etc/my.cnf
#在[mysqld]添加如下内容#
server-id = 195 log-bin = mysql-bin binlog_format = MIXED #非必需 relay-log = mysqld-relay-bin binlog-ignore-db = mysql binlog-ignore-db = information_schema binlog-ignore-db = performance_schema replicate-wild-ignore-table = mysql.% replicate-wild-igno
re-table = information_schema.% replicate-wild-ignore-table = performance_schema.%
四、手动同步数据库
如果master上已经有数据，那么执行主主互备之前，需要将master和slave上的两个mysql的数据保持同步，具体方法很多，可用工具Navicat for MySQL、phpmyadmin，也可直接命令行mysqldump、source；一般master备份前使用flush table with read lock;进行全局读锁，不能写；然后操作完后unlock tables;释放锁
master上执行
# mysqldump -uroot -p123456 --lock-all-tables --flush-logs hadoop > /data/hadoop.sqlslave上执行
mysql> create database hadoop default character set utf8 collate utf8_general_ci; mysql> use hadoop mysql> source /data/hadoop.sql
五、创建授权复制用户
master执行：
mysql> grant REPLICATION SLAVE ON *.* TO test@'192.168.20.193' IDENTIFIED BY '123456'; mysql> flush privileges;slave执行：
mysql> grant REPLICATION SLAVE ON *.* TO test@'192.168.20.195' IDENTIFIED BY '123456'; mysql> flush privileges;
六、启动互为主从的模式
在slave上执行下面命令，注意看Slave_IO_Running和Slave_SQL_Running，必须都是yes
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 | 334 | | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> change master to master_host='192.168.20.193', master_port=3306, master_user='test',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=334;
Query OK, 0 rows affected (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.193
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 334
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 334
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
在master上执行下面命令，注意看Slave_IO_Running和Slave_SQL_Running，必须都是yes
mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 | 334 | | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
mysql> change master to master_host='192.168.20.195', master_port=3306, master_user='test',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=334;
Query OK, 0 rows affected (0.10 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.20.195
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 334
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 334
Relay_Log_Space: 407
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
验证主主同步结果：
slave执行下面的sql语句
mysql> create database hadoop default character set utf8 collate utf8_general_ci; Query OK, 1 row affected (0.00 sec) mysql> use hadoop; Database changed mysql> create table user(id int(4) not null primary key auto_increment,name char (20) not null); Query OK, 0 rows affected (0.06 sec) mysql> insert into user values(null,1),(null,2),(null,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from user where id=2; Query OK, 1 row affected (0.00 sec) mysql> drop table user; Query OK, 0 rows affected (0.00 sec) mysql> create table user(id int(4) not null primary key auto_increment,name char (20) not null); Query OK, 0 rows affected (0.07 sec) mysql> insert into user values(null,1),(null,2),(null,3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from user where id=2; Query OK, 1 row affected (0.00 sec) mysql> update user set name="在195机器修改" where id=1; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> select * from user; +----+----------------------+ | id | name | +----+----------------------+ | 1 | 在195机器修改| | 3 | 3 | +----+----------------------+ 2 rows in set (0.00 sec) 在master上查看 mysql> use hadoop; Database changed mysql> show tables; Empty set (0.00 sec) mysql> select * from user; +----+----------------------+ | id | name | +----+----------------------+ | 1 | 在195机器修改 | | 3 | 3 | +----+----------------------+ 2 rows in set (0.00 sec)
在master上查看
mysql> use hadoop; Database changed mysql> show tables; Empty set (0.00 sec) mysql> select * from user; +----+----------------------+ | id | name | +----+----------------------+ | 1 | 在195机器修改 | | 3 | 3 | +----+----------------------+ 2 rows in set (0.00 sec)