前面已经介绍了使用RPM方式安装MySQL5.5 ，下面将继续之前的内容，配置MySQL主主复制。在配置MySQL主主复制之前，需要先完成主从复制:)。
仅供参考：
一、准备工作
1.备份my.cnf
#mv /etc/my.cnf /etc/my.cnf.origin
#cat /etc/my.cnf.origin
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2.使用my-innodb-heavy-4G.cnf作为默认my.cnf配置文件(去掉my-innodb-heavy-4G.cnf配置文件内所有注释后另存为/etc/my.cnf):
#cat /usr/share/mysql/my-innodb-heavy-4G.cnf | egrep -v '^#|^$' > /etc/my.cnf
3.将/etc/my.cnf.origin中的内容补充到/etc/my.cnf中。
4.在my.cnf中添加各自的配置参数后，my.cnf文件内容如下：
[client]
port = 3366
socket = /var/lib/mysql/mysql.sock
[mysqld]
port = 3366
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
# Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0
back_log = 50
max_connections = 100
#max_connect_errors = 10
max_connect_errors = 500
table_open_cache = 2048
max_allowed_packet = 2048M
binlog_cache_size = 1M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
#default-storage-engine = MYISAM
default-storage-engine = innoDB
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
#enable binary logs
log-bin=mysql-bin
log-bin-index=mysql-bin.index
binlog_format=mixed
slow_query_log
#long_query_time = 2
long_query_time = 0.5
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
#主1添加内容
#master-1 start
#注释掉binlog-do-db，新建的数据库也可以复制。
#binlog-do-db=
server-id = 1
auto_increment_increment=2
auto_increment_offset=1
#master-1 end
#主2添加内容
#master-2 start
server-id= 2
read_only #普通用户使主2是只读的，read_only对root用户不起作用
auto_increment_increment=2
auto_increment_offset=2
#master-2 end
sync_binlog=1
skip_slave_start
log_slave_updates
binlog-ignore-db = mysql
binlog-ignore-db = information_schema
binlog-ignore-db = performance_schema
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
innodb_support_xa=1
innodb_additional_mem_pool_size = 16M
#innodb_buffer_pool_size = 2G
innodb_buffer_pool_size = 8G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
#innodb_lock_wait_timeout = 120
innodb_lock_wait_timeout = 30
#add
innodb_flush_method=O_DIRECT
innodb_file_format=barracuda
innodb_file_per_table=1
[mysqldump]
quick
max_allowed_packet = 2048M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open-files-limit = 8192
二、配置复制
master-1 IP：10.10.13.100
master-2 IP：10.10.13.200
1.先实现从master-2从masterr-1复制：
在master-1上登陆mysql后执行：
mysql>grant replication slave on *.* to 'repl_sy'@'10.10.13.200' identified by 'MkyQifxGEvCX';
Query OK, 0 rows affected (0.00 sec)
mysql>show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 | 107 | | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
第一句是在master-1上建立一个只有复制权限的用户repl_sy，并允许从master-2连接，密码为MkyQifxGEvCX。因为两台数据库服务器都是全新安装的，所以接下来我没有使用flush tables with read lock，直接使用了show master status。记录上面的File 和Position两个值，下面会用到。
在master-2上登陆mysql后执行：
mysql>change master to
->master_host='10.10.13.100',
->master_user='repl_sy',
->master_password='MkyQifxGEvCX',
->master_port=3366,
->master_log_file='mysql-bin.000001', #这里使用上面File 的值
->master_log_pos=107, #这里使用上面Position的值
->master_connect_retry=10;
Query OK, 0 rows affected (0.01 sec)
mysql>start slave; # 启动
Query OK, 0 rows affected (0.01 sec)
mysql>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.10.13.100
Master_User: repl_sy
Master_Port: 3366
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: sydb-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
mysql,information_schema,performance_schema
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: 408
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)
最后查询状态中Slave_IO_Running和Slave_SQL_Running同为Yes 就成功了。到这里实际上是完成了主从复制，master-1为主，master-2为从。接下来只要参考上面步骤，将master-2作为主库，master-1作为从库就可以了。
2.下面实现从master-1从masterr-2复制：
在master-2上登陆mysql后执行：
mysql>grant replication slave on *.* to 'repl_yk'@'10.10.13.100' identified by 'MkyQifxGEvCX';
Query OK, 0 rows affected (0.00 sec)
mysql>show master status;
+------------------+----------+--------------+---------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------------------------+
| mysql-bin.000001 | 107 | | mysql,information_schema,performance_schema |
+------------------+----------+--------------+---------------------------------------------+
1 row in set (0.00 sec)
在master-1上登陆mysql后执行：
mysql>change master to
->master_host='10.10.13.200',
->master_user='repl_yk',
->master_password='MkyQifxGEvCX',
->master_port=3366,
->master_log_file='mysql-bin.000001',
->master_log_pos=107,
->master_connect_retry=10;
Query OK, 0 rows affected (0.01 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: 10.10.13.200
Master_User: repl_yk
Master_Port: 3366
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: sydb-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
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: 408
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)
这样主主复制就完成了。分别在master-1和master-2中使用root帐号新建数据库，插入数据，都可以复制到另一方。
最后，在master-1的MySQL中建立一个普通用户，用普通用户创建数据库给应用使用，master-2中对应的用户也要手动建立。
本文结束。
Leave a comment