MySQL双主配置

MySQL双主配置准备环境：服务器操作系统为RHEL6.4 x86_64，为最小化安装。主机A和主机B均关闭防火墙和SELINUX ,IP地址分别为192.168.131.129和192.168.131.130，MySQL版本为5.6.26，为通用二进制包。1.安装MySQL1.主机A操作# tar xf mysql-5.6.26-linux-glibc2.5-x86_64.tar.gz# mv mysql-5.6.26-linux-glibc2.5-x86_64 /usr/local/mysql# useradd -M -s /sbin/nologin mysql# chown -R root:root /usr/local/mysql/*# chown -R mysql:mysql /usr/local/mysql/data/# yum -y install libaio# cd /usr/local/mysql/scripts/# ./mysql_install_db --datadir=/usr/local/mysql/data/ --user=mysql --basedir=/usr/local/mysql/# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld# echo "export PATH=$PATH:/usr/local/mysql/bin" >>/etc/profile# source /etc/profile# vim /etc/my.cnf[mysqld]datadir=/usr/local/mysql/datasocket=/tmp/mysql.sockuser=mysqlsymbolic-links=0[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid# service mysqld start#chkconfig mysqld on# ss -tunlp | grep 3306# mysqladmin -uroot password "123456" #设置数据库密码2.主机B操作（同上）二、配置双主1、主机A操作# vim /etc/my.cnfserver-id=20log-bin=mysql-binlog-bin-index=mysql-bin.indexreplicate-ignore-db=testbinlog-ignore-db=mysqlbinlog-ignore-db=information_schemaauto-increment-increment=2auto-increment-offset=1# service mysqld restart# mysql -uroot -p123456mysql> flush tables with read lock;再打开一个远程终端窗口，执行以下操作# mysqldump -uroot -p123456 -B test > test.sql返回之前的终端窗口，做以下操作mysql> unlock tables;mysql> grant replication slave on *.* to "wdd"@"192.168.131.130" identified by "123456";mysql> flush privileges;# scp test.sql 192.168.131.130:/root2、主机B操作# vim /etc/my.cnfserver-id = 21log-bin=mysql-binlog-bin-index= mysql-bin.indexreplicate-do-db=testbinlog-ignore-db=mysqlbinlog-ignore-db=information_schemaauto-increment-increment=2auto-increment-offset=2#service mysqld restart# mysql -uroot -p123456 < test.sql# mysql -uroot -p123456mysql> grant replication slave on *.* to "wdd"@"192.168.131.129" identified by "123456";mysql> flush privileges;3.主机A和主机B分别作如下操作主机A①# mysql -uroot -p123456mysql> SHOW MASTER STATUS;mysql> change master tomaster_host="192.168.131.130",master_user="wdd",master_password="123456",master_log_file=" mysql-bin.000002",master_log_pos=420;mysql> start slave;mysql> show slave statusG;②# mysql -uroot -p123456mysql> show master status;mysql> change master tomaster_host="192.168.131.129",master_user="wdd",master_password="123456",master_log_file="mysql-bin.000002",master_log_pos=689;mysql> start slave;mysql> show slave statusG;三、测试1、主机A进行一下操作mysql> use test;mysql> create table info（-> id int（10） not null auto_increment primary key,-> name char（20））;mysql> show tables;2、主机B进行操作mysql> use test;mysql> show tables;mysql> create table person （-> id int（20） unsigned not null auto_increment primary key,-> name char（20））;mysql> show tables;3.在主机A进行操作检验MySQL双主高可用架构之MMM实战 http://www.linuxidc.com/Linux/2012-11/74123.htmMySQL主主双机负载均衡 http://www.linuxidc.com/Linux/2014-11/109901.htm配置MySQL服务器实现主主复制 http://www.linuxidc.com/Linux/2014-11/109077.htmMySQL主主复制+Keepalived 打造高可用MySQL集群 http://www.linuxidc.com/Linux/2014-09/106570.htmMySQL数据库主从及主主复制配置演示 http://www.linuxidc.com/Linux/2015-08/120882.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本文永久更新链接地址