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
