mysql主备双向复制

1、Mysql主备环境搭建1. 安装系统依赖CentOS-6.2-x86_64，BasicServer模式安装卸载系统自带mysqlrpm -qa|grep -i mysqlrpm -ev -nodepsmysql v 查看mysql版本信息关闭防火墙service iptables stop (现场应该开放相关端口即可)chkconfig -level 35 iptables off解压mysqltar -zxvf mysql-5.5.30-linux2.6-x86_64.tar.gz rm -rf /usr/local/mysql/mv mysql-5.5.30-linux2.6-x86_64

2、 /usr/local/mysql/拷贝配置文件cp /usr/local/mysql/support-files/my-f /etc/fvi /etc/ff server-id=1 (此项存在，其他项新增)binlog-do-db=test binlog-do-db=quartz_amsbinlog-ignore-db=mysqlreplicate-do-db=testreplicate-do-db=quartz_amsreplicate-ignore-db=mysqllog-slave-updatesslave-skip-errors=allsync_binlog=1auto_increm

3、ent_increment=2auto_increment_offset=1 说明：binlog-do-db为指定哪些database需要同步，一个server中可以有多个database，每个database中有多个用户注意：主备mysql的server-id，必须不同；主备的auto_increment_offset，必须不同，可以设置主为1，从为2，代表自增长数据的起始值；replicate-ignore-db为忽略的库，不同步；replicate-do-db为同步库。主f配置备f配置拷贝守护进程所需文件cp /usr/local/mysql/support-files/mysql.se

4、rver /etc/rc.d/init.d/mysqldchkconfig -add mysqldchkconfig -level 35 mysqld on添加mysql用户组及用户groupadd mysqluseradd mysql -g mysqlpasswd mysqlpasswd设为root_123456修改相关目录权限chown -R mysql:mysql /usr/local/mysql切换到mysql用户su mysql修改环境变量并立即生效vi /.bash_profile PATH=$PATH:$HOME/bin:/usr/local/mysql/bin. /.bash_

5、profile2. mysql设置初始化mysql数据并设置root密码cd /usr/local/mysql./scripts/mysql_install_db -user=mysql -datadir=/usr/local/mysql/data/启动mysql服务（以root用户启动，否则可能出现写文件权限问题造成不能同步）service mysqld restart./bin/mysqladmin -u root password 'rootpasswd'防止“Couldn't release acquired trigger: Lock wait timeout

6、 exceeded; try restarting transaction”错误：设置MYSQL 数据库的事务级别！SELECT global.tx_isolation;set global transaction isolation level read committed;主mysql(192.168.1.247)添加mysql同步用户，赋予相关权限，mysql u root p登陆后执行grant replication slave,file on *.* to 'mysql1''' identified by '123456'flush

7、privileges;create database quartz_ams;show databases;show master status;* 1. row * File: mysql-bin.000005(这是上面show命令 查询出来的信息，用于备的配置) Position: 107 Binlog_Do_DB: test,quarzt_amsBinlog_Ignore_DB: mysql说明：“mysql1”为给备创建的一个用户，“123456”为密码；备mysql(192.168.1.246)设置主mysql的IP和同步账户mysql -u root -p登陆后执行change ma

8、ster to master_host='',master_user='mysql1', master_password='123456', master_log_file='mysql-bin.000005', master_log_pos=107;start slave;show slave statusG;同样，也可以为备设置同步账户，主将备设置为master，即实现双向互为主备。(按下面配置即可)备mysql(192.168.1.246)添加mysql同步用户，赋予相关权限，mysql -u root -p登陆后执行gra

9、nt replication slave,file on *.* to 'mysql2''7' identified by '123456'flush privileges;create database quartz_ams;show databases;show master status;* 1. row * File: mysql-bin.000003 Position: 305 Binlog_Do_DB: test,quarzt_amsBinlog_Ignore_DB: mysql主mysql(192.168.1.247)设置备mysq

10、l的IP和同步账户mysql u root p登陆后执行change master to master_host='6',master_user='mysql2', master_password='123456', master_log_file='mysql-bin.000003', master_log_pos=305;start slave;show slave statusG;3. 安装keepalived 依赖安装yum install gccyum install openssl-develyum install p

11、opt-devel1. tar zxvf keepalived-.tar.gz2. cd keepalived-3. ./configure -prefix=/usr/local/keepalived4. make && make install  5. cp /usr/local/keepalived/sbin/keepalived /usr/sbin/6. cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/7. cp /usr

12、/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/8. mkdir /etc/keepalived9. cd /etc/keepalived/主mysql的keepalived.conf配置文件/etc/keepalieved/keepalived.conf! Configuration File for keepalivedglobal_defs router_id MYSQL_NODE_1vrrp_script chk_mysql_port script "</dev/tcp/127.0.0.1/3306&qu

13、ot; interval 3 weight -2 fail 1 rise 1vrrp_instance V_MYSQLSERVER state MASTER interface eth0 lvs_sync_daemon_inteface eth0 virtual_router_id 70 priority 100 advert_int 2 authentication auth_type PASS auth_pass 4321 track_script chk_mysql_port virtual_ipaddress 注：蓝色加粗字体部分，可根据实际情况调整 为虚拟IP（不能有冲突），客户端访

14、问时配成此IP，keepalieved会自动转到具体的server;priority的值：主为100，备为99主keepalived配置备keepalived配置/etc/init.d/keepalived start （系统重启后要启动，最好放入守护进行随系统一起启动）tail -f /var/log/messages4. 验证主mysql(192.168.1.247)建表并插入数据mysql u root p登陆后执行use quartz_ams;drop table if exists quartz_ams.person;create table quartz_ams.person(id

15、 int unsigned not null primary key auto_increment, name varchar(40),birthday date)engine=innodb ;insert into quartz_ams.person(name, birthday) values('Cat',now();select * from quartz_ams.person;从mysql6)插入数据mysql u root p登陆后执行select * from quartz_ams .person;insert into quartz_ams.person(name, birthday) values('Dog',now();select * from quartz_ams.person;主mysql(192.168.1.247)检查是否已经双向同步mysql u root p登陆后执行select * from quartz_ams.person;授权(服务器有访问限制)例如，你想myuser使用myp