MySQL主从复制配置案例

一份MySQL主从复制配置案例，环境为主库是111.111.111.110 从库为111.111.111.111。1. 主库创建数据库复制专用用户--在主库上执行,创建用户replmysql –urootgrant replication slave,replication client on *.* to repl@"111.111.111.%" identified by "repl";exit;--修改配置文件/etc/my.cnfcp /etc/my.cnf /etc/my.cnf.bakvi /etc/my.cnf修改主库参数如下[mysqld]datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqllog-bin=mysql-binold_passwords=1server_id = 110sync_binlog = 1relay_log = /var/lib/mysql/mysql-relay-bin# enable log_slave_updateslog_slave_updates = 1skip_slave_start[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid2.关闭数据库--在主库上执行service mysqld stop3.备份主库--在主库上执行tar zcvf /tmp/mysql.tar.gz /var/lib/mysql4.将主库的备份传送到从库--在主库上执行rsync -auzvP --bwlimit=10240 /tmp/mysql.tar.gz 111.111.111.111: /tmp/mysql.tar.gzrsync –auzvP --bwlimit=1024 /etc/my.cnf 111.111.111.111:/etc/my.cnf5.在备库恢复主库的备份--在备库执行mv /var/lib/mysql /var/lib/mysqlbakmv /tmp/mysql.tar.gz /var/lib/tax xvf /var/lib/mysql.tar.gz6.在备库修改配置文件/etc/my.cnf--在备库执行--修改配置文件/etc/my.cnfvi /etc/my.cnf修改参数如下[mysqld]server_id = 1117.在备库启动数据库--在备库执行service mysqld start8.在主库上启动数据库service mysqld startmysql –uroot –pshow master status;9.根据上一步获取到的bin log文件名和position，在备库启用复制--在备库执行（将命令master_log_file="mysql-bin.0000099",master_log_pos=5中的值mysql-bin.0000099和0 修改为上一步获取到的值）change master to master_host="111.111.111.110",master_user="repl",master_password="repl",master_log_file="mysql-bin.00000xx",master_log_pos=0;--启动复制进程start slave;show slave statusGshow processlistGUbuntu下Nginx做负载实现高性能WEB服务器5---MySQL主主同步 http://www.linuxidc.com/Linux/2012-06/61687p5.htm生产环境MySQL主主同步主键冲突处理 http://www.linuxidc.com/Linux/2013-07/86890.htmMySQL主从失败错误Got fatal error 1236 http://www.linuxidc.com/Linux/2012-02/54729.htmMySQL主从复制，单台服务器上实施 http://www.linuxidc.com/Linux/2013-03/81913.htm搭建MySQL代理服务器实现读写分离+主从同步 http://www.linuxidc.com/Linux/2014-05/102265.htmMySQL 5.5 主从双向同步 http://www.linuxidc.com/Linux/2012-12/75973.htmMySQL 5.5主从同步排错 http://www.linuxidc.com/Linux/2014-08/105416.htmMySQL主从复制异步半同步实例 http://www.linuxidc.com/Linux/2014-10/107608.htm本文永久更新链接地址