彭荣俭-MYSQL主主详细配置(每一个步骤都有).doc

环境描述：OS：RH6.5master机：7backup机：8一、 配置两台Mysql主主同步（两台配置基本一样，除了/etc/f里的server-id =）1.Master详细配置：rootmaster # yum install mysql-* mysql -yrootmaster # service mysqld startrootmaster # mysqladmin -u root password mysql2012rootmaster # vim /etc/f #开启二进制日志，设置idserver-id = 1 #backup这台设置2 这个选项有的注意1和2log-bin = mysql-bin #这个选项有的binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库auto-increment-increment = 2 #字段变化增量值auto-increment-offset = 1 #初始字段ID为1slave-skip-errors = all #忽略所有复制产生的错误rootmaster # service mysqld restartchkconfig mysqld on #设置开机自动启动 两台机都要设置rootmaster #mysql uroot pmysql2012 以root用户进入数据库 Mysqlshow master status; 会看到以下图，记住框出来的部分mysql-bin.000002 106 这些不固定，现在要去到backup那台机上，因为master这台机下面的操作要用到对面的上面那图框出来的部分,当然backup那台机也会用到master机的这个图的log bin日志pos值位置 （mysql-bin.000002 106）,总之都用这个指向对方MysqlGRANTALLPRIVILEGESON*.*TOroot%IDENTIFIEDBYmysql2012WITHGRANTOPTION; #赋予所有网段远程登录权限 Mysqlflush privileges; #刷新权限2.backup详细配置：rootbackup # yum install mysql-* mysql -yrootbackup # service mysqld startrootbackup # mysqladmin -u root password mysql2012rootbackup # vi /etc/f #开启二进制日志，设置idmysqldserver-id = 2 #master这台设置1log-bin = mysql-binbinlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库auto-increment-increment = 2 #字段变化增量值auto-increment-offset = 1 #初始字段ID为1slave-skip-errors = all #忽略所有复制产生的错误rootbackup #service mysqld restart #一定要重启不然show不到log bin日志pos值位置rootbackup #mysql uroot pmysql2012 Mysqlshow master status; #获取log bin日志pos值位置，master这台机要根据这个来指向backup来获取信息 获取的结果是 Mysql-bin.000003 106mysqlGRANTALLPRIVILEGESON*.*TOroot%IDENTIFIEDBYmysql2012WITHGRANTOPTION;mysql flush privileges;#刷新权限mysql change master to - master_host=7, #master的ip - master_user=root, - master_password=mysql2012, - master_log_file=mysql-bin.000002, #对端(master)状态显示的值 - master_log_pos=106; #对端(master)状态显示的值 mysql start slave;3.master详细配置继续:mysql change master to - master_host=8, #backup的IP地址 - master_user=root, - master_password=mysql2012, - master_log_file=mysql-bin.000003, #对端(backup)状态显示的值- master_log_pos=106; #对端(backup)状态显示的值mysql start slave; #启动同步mysql exit最后：在master backup两台机分别看状态 Mysqlshow slave statusG #两台机都执行去看#主主同步配置完毕，查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功测试：在master(7)创建个 data_linkapp 数据库 myysqlcreate database data_linkapp;去到backup(8)这台机看，master新建的data_linkapp 数据库会同步到backup(8)这台机再测：在master这台机的test数据库创建个表：Mysql create table user (number INT(10),name VARCHAR(255);Mysql insert into user values(01,