mysql主从备份及读写分离配置

主从备份

• 环境配置

1. mysql：5.7.31（主从版本一致）。主：192.168.100.140；从：192.168.100.15
2. Linux：centos7.3

• 配置过程

3. 修改主数据库配置：vim /etc//my.cnf。添加如下内容
   [mysqld]
   log-bin=mysql-bin #开启二进制日志
   server-id=1 #设置server-id

   另外，master开启二进制日志后默认记录所有库所有表的操作。如果只想同步部分库，可以添加如下配置。
   #不同步哪些数据库
   binlog-ignore-db = mysql
   #只同步哪些数据库，除此
   binlog-do-db = game

4. 重启mysql：service mysqld restart

5. 创建用于同步的用户账号
   mysql> CREATE USER ‘repl’@‘192.168.100.15’ IDENTIFIED BY ‘P@ssword@qxmz123’;#创建用户
   mysql> GRANT REPLICATION SLAVE ON *.* to ‘rep1’@‘192.168.100.15’ identified by ‘P@ssword@qxmz123’;#分配权限
   mysql>flush privileges; #刷新权限

6. 查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：
   SHOW MASTER STATUS;
   | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   ±-----------------±---------±-------------±-----------------+
   | mysql-bin.000003 | 73 | test | manual,mysql |

7. 修改从数据库配置：vim /etc//my.cnf
   添加如下内容
   [mysqld]
   server-id=2 #设置server-id，必须唯一，与主数据配置不一样

8. 重启mysql：service mysqld restart

9. 执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)
   CHANGE MASTER TO MASTER_HOST=‘192.168.100.140’,MASTER_USER=‘rep1’,MASTER_PASSWORD=‘P@ssword@qxmz123’,MASTER_LOG_FILE=‘mysql-bin.000001’,MASTER_LOG_POS=5024;

10. 启动slave同步进程，并查看状态
    start slave;
    show slave status\G;
    如果Slave_IO_Running和Slave_SQL_Running都是YES，则主从配置成功。反之失败，排查原因。

• 同步主库已有数据到从库

1. 停止主库的数据更新操作：flush tables with read lock;
2. 新开终端，生成主数据库的备份（用客户端工具也行）：mysqldump -uroot -ptest123 cmdb > cmdb.sql
3. 将备份文件传到从库：scp cmdb.sql root@192.168.8.11:/root/
4. 主库解锁：unlock tables;
5. 停止从库slave：stop slave;
6. 新建数据库cmdb：create database cmdb default charset utf8;
7. 导入数据：mysql -uroot -ptest123 cmdb<cmdb.sql
8. 开启从库slave：stop slave;

• 测试
  1.用命令行或客户端工具新建表，插入数据在主数据库中
  2.查看从库是否同步更新

• 参考文档
  https://www.cnblogs.com/lelehellow/p/9633315.html
  https://blog.csdn.net/daicooper/article/details/79905660
  https://www.cnblogs.com/zhoujie/p/mysql1.html

---

读写分离配置

• 环境配置
  1.mysql：5.7.31（主从版本一致）。主：192.168.100.140；从：192.168.100.15
  2.Linux：centos7.3
  3.mycat：Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz（下载地址：http://dl.mycat.org.cn/）

• 配置过程
  1.将部署包解压在/user/local目录下
  cd /usr/local/ && ar -zvxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
  2.修改配置文件（核心及重点）
  server.xml：配置mycat逻辑数据库名、登录用户及密码
  schema.xml：Mycat对应的物理数据库和数据库表的配置、主从关系及读写关系配置

读写分离及主从切换配置示例如下，可以直接使用

	<?xml version="1.0"?>
	<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
	<mycat:schema xmlns:mycat="http://io.mycat/">

		<schema name="logic_qxmz" checkSQLschema="false" sqlMaxLimit="100" dataNode="db_node">
		</schema>

		<dataNode name="db_node" dataHost="db_host" database="qxmz" />
		
		<dataHost name="db_host" maxCon="1000" minCon="10" balance="3"
				  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
			<heartbeat>show slave status</heartbeat>
			<!-- can have multi write hosts -->
			<writeHost host="hostM1" url="192.168.100.140:3306" user="root"
					   password="P@ssword@qxmz123">
				<!-- can have multi read hosts -->
				<readHost host="hostS1" url="192.168.100.15:3306" user="root" password="123456" />
			</writeHost>
			<writeHost host="hostS1" url="192.168.100.15:3306" user="root" password="123456">
			</writeHost>
		</dataHost>
	</mycat:schema>

3.配置修改完，即可启动测试
./mycat start      #开启
./mycat stop       #关闭
./mycat restart    #重启
./mycat status     #查看启动状态
4.启动后查看进程或端口
ps -ef|grep cat
lsof -i:8066
netstat -antl|grep 8066

• 测试
  1.用户命令行或客户端工具测试读写请求，是否在对应表中
  2.开启log4j2.xml日志，将info改为debug后重启。执行读写sql后，查看logs/mycat.log中的路由日志

• 问题
  1.本人用客户端工具测试读写分离，写可以，能写入主库，同时数据备份到从库。但读请求不行，一直读的是主表。排查半天不解决问题。
  2.后来用代码测试，发现是可以的。写都是走主库，读都是走从库，不知道为啥客户端工具不行。
  3.测试后发现，从库挂掉后，接下来的读写都走主库。从库再次启动后，会同步挂掉后的所有写入操作，另外读写分离仍然生效。
  4.主库挂掉后，情况变得复杂。首先接下来的读写都会走从库，但是主库再次重启后，是不加入到mycat中的。即启动后，读写仍然继续走从库。
  对应方案：
  1.切换主从、把从库提升为主库，原主库变为从库
  2.继续使用原来主库。但此时要修改几个地方：
  a.从库数据手动同步到主库
  b.conf/dnindex.properties，将其中db_host=0改为原来主库对应数值（第一个为 0，第二个为 1，依次类推）
  c.查看从库slave状态：show slave status \G;
  其中，Slave_SQL_Running: NO。修改方法如下
  stop slave ;
  set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
  start slave ;
  这些命令可能要执行多次才可以。

• 参考文档
  https://www.cnblogs.com/kevingrace/p/9365840.html
  http://blog.itpub.net/28916011/viewspace-2685622/
  https://www.jianshu.com/p/f704af1deb5c

  • 卸载
    查看包：rpm -qa|grep mysql
    删除包：rpm -e mysql-community-libs-5.7.31-1.el7.x86_64 mysql-community-client-5.7.31-1.el7.x86_64 mysql-community-server-5.7.31-1.el7.x86_64 mysql-community-common-5.7.31-1.el7.x86_64
    查找并删除安装目录：find / -name mysql