一、 架构 
二、 安装mysql server下面以在db1（192.168.0.10）的配置为例，db2的配置基本一样，只要修改my.cnf中的server_id =2
1. 安装mysql-server
db1# apt-get install mysql-server
注：
? 如果安装有问题，执行apt-getupdate更新源后再重试
? 安装过程中需要输入root密码，设置后记住（123456）
2. 验证数据库安装
db1# mysql -p
enter password:
welcome to the mysql monitor. commands end with ; or \g.
your mysql connection id is 42
server version: 5.5.38-0ubuntu0.12.04.1 (ubuntu)
copyright (c) 2000, 2014, oracle and/or its affiliates. all rights reserved.
oracle is a registered trademark of oracle corporation and/or its
affiliates. other names may be trademarks of their respective
owners.
type 'help;' or '\h' for help. type '\c' to clear the current input statement.
(db1)mysql> show databases;
+--------------------+
| database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
(db1)mysql>
3. 创建测试数据库
create database /*!32312 if not exists*/`test` /*!40100 default character set latin1 */;
use `test`;
/*table structure for table `user` */ 
drop table if exists `user`; 
create table `user` (
`name` varchar(16) default null,
`age` int(11) default null
) engine=innodb default charset=utf8;
此步骤略
4. my.cnf配置修改
以下红色部分为修改的配置
the mysql database server configuration file.
#
# you can copy this to one of:
# - /etc/mysql/my.cnf to set global options,
# - ~/.my.cnf to set user-specific options.
#
# one can use all long options that the program supports.
# run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# for explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
# this will be passed to all mysql clients
# it has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain # chars...
# remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock 
# here is entries for some specific programs
# the following values assume you have at least 32m ram 
# this was formally known as [safe_mysqld]. both versions are currently parsed.
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0 
[mysqld]
#
# * basic settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
bind-address =0.0.0.0
#
# * fine tuning
#
key_buffer = 16m
max_allowed_packet = 16m
thread_stack = 192k
thread_cache_size = 8
# this replaces the startup script and checks myisam tables if needed
# the first time they are touched
myisam-recover = backup
#max_connections = 100
#table_cache = 64
#thread_concurrency = 10
#
# * query cache configuration
#
query_cache_limit = 1m
query_cache_size = 16m
#
# * logging and replication
#
# both location gets rotated by the cronjob.
# be aware that this log type is a performance killer.
# as of 5.1 you can enable the log at runtime!
#general_log_file = /var/log/mysql/mysql.log
#general_log = 1
#
# error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# here you can see queries with especially long duration
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# the following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see readme.debian about
# other settings you may need to change.
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100m
#binlog_do_db = include_database_name
#binlog_ignore_db = include_database_name
#
# * innodb
#
# innodb is enabled by default with a 10mb datafile in /var/lib/mysql/.
# read the manual for more innodb related options. there are many!
#
# * security features
#
# read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# for generating ssl certificates i recommend the openssl gui tinyca.
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem 
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
log_bin_index = /var/log/mysql/mysql-bin.log.index
relay_log = /var/log/mysql/mysql-relay-bin
relay_log_index = /var/log/mysql/mysql-relay-bin.index
expire_logs_days = 10
max_binlog_size = 100m
log_slave_updates = 1
auto_increment_increment = 2
auto_increment_offset = 1 
[mysqldump]
quick
quote-names
max_allowed_packet = 16m 
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition 
[isamchk]
key_buffer = 16m 
#
# * important: additional settings that can override those from this file!
# the files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
5. 创建三种角色的用户
表格 1
角色
功能
权限
monitor user
mmm（multi-master replication manager of mysql）监控各主控的健康状况
replication client
agent user
mmm代理用来设置只读属性，复制主控等
super, replication client, process
replicate user
用来复制
replication slave
表格 2
(db1)mysql> grant replication client on *.* to 'mmm_monitor'@'115.29.198.150' identified by '123456';
query ok, 0 rows affected (0.00 sec)
(db1)mysql> grant super, replication client, process on *.* to 'mmm_agent'@'42.96.%.%' identified by '123456';
query ok, 0 rows affected (0.00 sec)
(db1)mysql> grant replication slave on *.* to 'replication'@'42.96.%.%' identified by '123456';
query ok, 0 rows affected (0.00 sec)
三、 同步db1和db2数据库首先假设db1包含正确的数据（即使是空数据库），进行db1和db2直接的数据同步。
1. 以下在db1所在服务器上执行数据导出
(db1)mysql> flush tables with read lock;
(db1)mysql> show master status;
+------------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 616 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
(db1)mysql>
2. 另开一个命令窗口导出数据
(db1)# mysqldump -u root -p --all-databases > /tmp/database-backup.sql
3. 解锁第一个窗口
(db1)mysql> unlock tables;
query ok, 0 rows affected (0.00 sec)
(db1)mysql>
4. 将db1导出的数据导入db2
1) 拷贝到db2
(db1)# scp database-backup.sql root@192.168.0.11:/tmp/
the authenticity of host '192.168.0.11 (192.168.0.11)' can't be established.
ecdsa key fingerprint is 55:84:03:9e:d9:74:cc:cd:03:59:23:3f:df:d9:77:a5.
are you sure you want to continue connecting (yes/no)? yes
warning: permanently added '192.168.0.11' (ecdsa) to the list of known hosts.
root@192.168.0.11's password:
database-backup.sql 100% 528kb 527.9kb/s 00:00
(db1):/tmp#
2) 导入db2
(db2)# mysql -u root -p
enter password:
(db2)#
3) 应用权限
(db2)mysql> flush privileges;
query ok, 0 rows affected (0.00 sec)
4) 拷贝debian.cnf
将/etc/mysql/debian.cnf 从 db1拷贝到db2, 这个文件是用来启动和关闭mysql用的。
(db1)# scp debian.cnf root@192.168.0.11:/tmp/
在db2上备份原来的debian.cnf，然后使用从db1拷贝过来的debian.cnf
(db2)# mv /etc/mysql/debian.cnf /etc/mysql/debian.cnf.orign
(db2)# cp -f debian.cnf /etc/mysql/debian.cnf
上述步骤完成后准备工作都做好了，可以开始配置复制。
四、 复制配置1. 在db2上执行：
(db2)mysql> change master to master_host='192.168.0.10', master_port=3306, master_user='replication', master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=616;
query ok, 0 rows affected (0.04 sec)
注：master_log_file='mysql-bin.000002', master_log_pos=616 信息来自于在db1上执行
mysql> show master status;
2. 在db2上启动slave
(db2)mysql> start slave;
query ok, 0 rows affected (0.00 sec)
3. db2上检查复制进程
(db2)mysql> show slave status\g
*************************** 1. row ***************************
slave_io_state: waiting for master to send event
master_host: 192.168.0.10
master_user: replication
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 616
relay_log_file: mysql-relay-bin.000002
relay_log_pos: 253
relay_master_log_file: mysql-bin.000002
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:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 616
relay_log_space: 409
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:
replicate_ignore_server_ids:
master_server_id: 1
1 row in set (0.00 sec)
4. 配置从db2复制到db1
1) db2状态
(db2)mysql> show master status;
+------------------+----------+--------------+------------------+
| file | position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 107 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)
mysql>
2) db1复制的配置、启动和检查
(db1)mysql> change master to master_host = '192.168.0.11', master_port=3306, master_user='replication',
-> master_password='123456', master_log_file='mysql-bin.000002', master_log_pos=107;
query ok, 0 rows affected (0.05 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.0.11
master_user: replication
master_port: 3306
connect_retry: 60
master_log_file: mysql-bin.000002
read_master_log_pos: 107
relay_log_file: mysql-relay-bin.000002
relay_log_pos: 253
relay_master_log_file: mysql-bin.000002
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:
last_errno: 0
last_error:
skip_counter: 0
exec_master_log_pos: 107
relay_log_space: 409
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:
replicate_ignore_server_ids:
master_server_id: 2
1 row in set (0.00 sec)
mysql>
上述步骤完成master-master的复制配置，下面进行测试。
五、 复制验证1. 在db1上插入一条数据
(db1)mysql> select * from user;
empty set (0.00 sec)
(db1)mysql> insert into user(name,age) values('user1',20);
query ok, 1 row affected (0.03 sec)
(db1)mysql>
2. 在db2上检查
(db2)mysql> select * from user;
empty set (0.00 sec)
(db2)mysql> select * from user;
+-------+------+
| name | age |
+-------+------+
| user1 | 20 |
+-------+------+
1 row in set (0.00 sec)
(db2)mysql>
表面在db1插入的（user1,20）这条记录已经被复制到db2中。
3. 在db2上插入一条数据
(db2)mysql> insert into user(name,age) values('user2',30);
query ok, 1 row affected (0.02 sec
4. 在db1上进行检查
(db1)mysql> select * from user;
+-------+------+
| name | age |
+-------+------+
| user1 | 20 |
| user2 | 30 |
+-------+------+
2 rows in set (0.00 sec)
表面在db2插入的（user2,30）这条记录已经被复制到db1中。
上述测试表面，db1db2的mm配置完全成功。