welcome to xlongwei.com

欢迎大家一起学习、交流、分享


QQ群:162333776 邮箱:admin@xlongwei.com

MySQL 双主热备配置


分类 Java   关键字 分享   标签 java   mysql   发布 hongwei  1497344204005
注意 转载须保留原文链接,译文链接,作者译者等信息。  
MySQL双主热备配置,也就是两个MySQL实例互为主从,既支持了读写分离,又提高了可靠性。

下载合适的MySQL版本,http://www.360sdn.com/opensource-datasource/,这里以版本5.6.30为例。
# wget http://downloads.mysql.com/archives/get/file/MySQL-server-5.6.30-1.linux_glibc2.5.x86_64.rpm
# rpm -ivh MySQL-server-5.6.30-1.linux_glibc2.5.x86_64.rpm
# wget http://downloads.mysql.com/archives/get/file/MySQL-client-5.6.30-1.linux_glibc2.5.x86_64.rpm
# rpm -ivh MySQL-client-5.6.30-1.linux_glibc2.5.x86_64.rpm

安全设置:
# cat /root/.mysql_secret  //初始root密码保存在此文件中,初次登录mysql必须重置密码:set password = password(''); 可以设置空密码
# /usr/bin/mysql_secure_installation //按提示设置root密码,删除匿名账户和test测试库,限制root只能从本机登录

配置MySQL主从:两个MySQL实例的配置几乎相同,但server-id必须不同
# cp /usr/share/mysql/my-default.cnf /etc/my.cnf
# vi /etc/my.cnf
[client]
default-character-set = utf8
[mysqld]
character-set-server = utf8
default_storage_engine = InnoDB
server-id = 1 #两个MySQL实例的server-id必须不同
log_bin = mysql-bin

replicate-do-db = easylive
replicate-do-db = ginkgo

replicate-wild-ignore-table = mysql.%

配置复制用户权限
# mysql  //登录mysql
> GRANT REPLICATION SLAVE,file,select,super,reload ON *.* to 'slave'@'192.168.10.131' identified by 'password’; //配置用户
> show master status; //查询master_log_file和master_log_pos,然后切换至另一个MySQL实例
>
change master to master_host='内网ip',master_user='slave',master_password='copy',master_log_file='mysql-bin.000001',master_log_pos=123;
> slave start;
> show slave status \G; //Slave_IO_Running和Slave_SQL_Running值为YES,Master_Log_File和Read_Master_Log_Pos对应即可

配置Mycat
# wget http://tool.xlongwei.com/softwares/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# tar zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
# vi mycat/conf/server.xml //编辑之前先备份,密码可以加密,用于客户端连接mycat
<user name="ginkgo">
<property name="password">38c6ad2f824e5</property>
<property name="schemas">easylive,ginkgo</property>
</user>
# vi mycat/conf/schema.xml
<schema name="easylive" checkSQLschema="false" sqlMaxLimit="100" dataNode="easylive" />
<schema name="ginkgo" checkSQLschema="false" sqlMaxLimit="100" dataNode="ginkgo" />
<dataNode name="easylive" dataHost="localhost" database="easylive" />
<dataNode name="ginkgo" dataHost="localhost" database="ginkgo" />
<dataHost name="localhost" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="10.29.86.191:3306" user="ginkgo" password="38c6ad2f824e5">
<readHost host="hostS2" url="10.29.86.195:3306" user="ginkgo" password="38c6ad2f824e5" />
</writeHost>
<!-- hostM1宕机后,自动切换至hostS2,等待人工修复hostM1 -->
<writeHost host="hostS2" url="10.29.86.195:3306" user="ginkgo" password="38c6ad2f824e5" />
</dataHost>
# mycat/bin/mycat start|status|restart //启动mycat