1. 选择两台机器（这里选的centos6.5 final），安装相同版本的mysql
yum install mysql ;
yum install mysql-server;
2. 启动mysql
service mysqld start
3. 登录两个mysql，执行如下命令
GRANT REPLICATION SLAVE,REPLICATION CLIENT on *.* to repl@'mysql机器IP' identified by 'password';
复制用户并授权
4. 配置主mysql的/etc/my.cnf
[client]
port = 3306
socket = /dev/shm/mysql/mysql.sock
default-character-set = utf8
[mysqld_safe]
socket = /dev/shm/mysql/mysql.sock
nice = 0
[mysqld]
user = mysql
socket = /dev/shm/mysql/mysql.sock
port = 3306
basedir = /usr
datadir = /mysql/data
log-bin = mysql-bin
tmpdir = /tmp
skip-external-locking
bind-address = 172.16.1.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /mysql/log/mysqld.log
expire_logs_days = 10
max_binlog_size = 100M
log_bin = mysql-bin
binlog_format = ROW
server_id = 1
innodb_flush_log_at_trx_commit=1
innodb_support_xa = 1 [mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
5. 配置备的mysql
[client]
port = 3306
socket = /dev/shm/mysql/mysql.sock
default-character-set = utf8
[mysqld_safe]
socket = /dev/shm/mysql/mysql.sock
nice = 0
[mysqld]
user = mysql
socket = /dev/shm/mysql/mysql.sock
port = 3306
basedir = /usr
datadir = /mysql/data
log-bin = mysql-bin
tmpdir = /tmp
skip-external-locking
bind-address = 172.16.1.2
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
log_error = /mysql/log/mysqld.log
expire_logs_days = 10
max_binlog_size = 100M
log_bin = mysql-bin
binlog_format = ROW
server_id = 2 # id与主的不同
relay_log = mysql-relay-bin
log_slave_updates = 1
read_only = 1 # slave是read only [mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
6. 重启两个mysql
7. 登录主mysql，执行
show master status\G;
验证正确性
8. 登录备mysql，执行
CHANGE MASTER TO
MASTER_HOST='172.16.1.1',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106;
然后执行
start slave;
show slave status\G;
验证正确性
执行
mysql -urepl -h172.16.1.1 -ppassword
测试备mysql是否能连接到主库
9. 主备切换
修改my.cnf文件
read-only=1（主）
#read-only=1（备）
在备的mysql上执行
STOP SLAVE IO_THREAD;
SHOW PROCESSLIST;
再执行
STOP SLAVE;
RESET MASTER;
RESET SLAVE;
show master status \G；
在主的mysql上执行
RESET MASTER;
RESET SLAVE; CHANGE MASTER TO
MASTER_HOST='172.16.1.2',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=106;
start slave
10. 先重启新的主mysql， 在重启备mysql
service mysqld restart
参考： http://blog.csdn.net/liuzhoulong/article/details/48289115