mysql双主配置及基于dump和binlog数据恢复

• 由 管建智创建, 最终由 张少南修改于 六月 16, 2023

一、mycat是什么

Mycat简单点说就是数据库中间件，用来连接java程序与数据库中的中间件。

为什么要使用MyCat。具体原因是java代码与数据库的高耦合，在高访问量和高并发时对数据库的压力会使得数据库 读写请求数据不一致，我们现在普遍使用的是java直接对数据库进行操作，

在配置文件中定义了mysql的数据源,直接连接到了我们的mysql软件，但是当某些情况下我们可能需要用到了多个数据库, 这个时候我们可能就需要配多个数据源去连接我们的多个数据库,这个时候我们进行sql操作的时候就会很麻烦，

如果我们在Java与mysql中间使用了MyCat，我们只需要访问mycat就可以了，至于数据源等问题，mycat会直接帮我们搞定。

二、基于kubernetes如何搭建mycat+多主mysql

1、创建deployments:

mycat镜像：192.168.3.204:5000/mycat_mysql:1.0.0

mysql镜像：192.168.3.204:5000/mysql_ms:5.7.40

mycat环境变量：

mysql环境变量：

mysql1：

mysql2：

2、配置configMap：

mycat的configMap：

schema.xml

<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/">     <schema name="pdl" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>     <schema name="information_schema" checkSQLschema="false" sqlMaxLimit="100" dataNode="information1"></schema>     <dataNode name="dn1" dataHost="localhost1" database="pdl" />     <dataNode name="information1" dataHost="localhost1" database="infomation_schema" />     <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">         <heartbeat>select user()</heartbeat>         <!-- can have multi write hosts -->         <writeHost host="hostM1" url="sql1.mysql-pl:3306" user="root" password="123456"></writeHost>         <writeHost host="hostM2" url="sql2.mysql-pl:3306" user="root" password="123456"></writeHost>     </dataHost> </mycat:schema>

server.xml:

<?xml version="1.0" encoding="UTF-8"?> <!-- - - Licensed under the Apache License, Version 2.0 (the "License");     - you may not use this file except in compliance with the License. - You     may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0     - - Unless required by applicable law or agreed to in writing, software -     distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT     WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the     License for the specific language governing permissions and - limitations     under the License. --> <!DOCTYPE mycat:server SYSTEM "server.dtd"> <mycat:server xmlns:mycat="http://io.mycat/">     <system>         <!-- 1 open, 0 close -->         <property name="useSqlStat">0</property>         <property name="useGlobleTableCheck">0</property>         <property name="sequnceHandlerType">2</property>         <!-- <property name="useCompression">1</property>-->         <!-- <property name="fakeMySQLVersion">5.6.20</property>-->         <!-- <property name="processorBufferChunk">40960</property> -->         <!-- <property name="processors">1</property>          <property name="processorExecutor">32</property> -->         <property name="processorBufferPoolType">0</property>         <property name="maxStringLiteralLength">65535</property>         <!--<property name="sequnceHandlerType">0</property>-->         <!--<property name="backSocketNoDelay">1</property>-->         <!--<property name="frontSocketNoDelay">1</property>-->         <!--<property name="processorExecutor">16</property>-->         <!--             <property name="serverPort">8066</property> <property name="managerPort">9066</property>             <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property>             <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>             -->         <property name="handleDistributedTransactions">0</property>         <!--off heap for merge/order/group/limit-->         <property name="useOffHeapForMerge">1</property>         <property name="memoryPageSize">1m</property>         <property name="spillsFileBufferSize">1k</property>         <property name="useStreamOutput">0</property>         <property name="systemReserveMemorySize">384m</property>         <property name="useZKSwitch">true</property>     </system>     <!--     <firewall>        <whitehost>           <host host="127.0.0.1" user="mycat"/>           <host host="127.0.0.2" user="mycat"/>        </whitehost>        <blacklist check="false">        </blacklist>     </firewall>     -->     <user name="root">         <property name="password">123456</property>         <property name="schemas">pdl,infomation_schema</property>         <!--                 <privileges check="false">             <schema name="TESTDB" dml="0110" >                 <table name="tb01" dml="0000"></table>                 <table name="tb02" dml="1111"></table>             </schema>         </privileges>              -->     </user> </mycat:server>

mysql的configMap：

mysql1:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL    skip-host-cache skip-name-resolve server-id=1 log-bin=mysql-bin binlog_format=MIXED log-slave-updates=on expire_logs_days = 1 max_binlog_size = 500M auto_increment_increment=2 auto_increment_offset=1 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema gtid_mode=ON enforce-gtid-consistency=ON character-set-server=utf8    max_connections=1000 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION    # Custom config should go here !includedir /etc/mysql/conf.d/

mysql2:

[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL    skip-host-cache skip-name-resolve server-id=2 log-bin=mysql-bin binlog_format=MIXED log-slave-updates=on expire_logs_days = 1 max_binlog_size = 500M auto_increment_increment=2 auto_increment_offset=2 replicate-ignore-db=mysql replicate-ignore-db=sys replicate-ignore-db=information_schema replicate-ignore-db=performance_schema gtid_mode=ON enforce-gtid-consistency=ON character-set-server=utf8 max_connections=1000 sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION    # Custom config should go here !includedir /etc/mysql/conf.d/

三、验证双M数据同步

1、创建demo数据

CREATE TABLE `tbl_user` (   `id` bigint(20) NOT NULL AUTO_INCREMENT,   `username` varchar(255) DEFAULT NULL COMMENT '用户名',   `status` tinyint(4) DEFAULT NULL COMMENT '状态(0:关闭，1：打开)',   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; insert into tbl_user(username, status) values('monday', 0); insert into tbl_user(username, status) values('vbirdbest', 1); 验证使用 insert into tbl_user(username, status) values('modely', 1);

master

slave

2、关闭节点mysql slave ，通过mysql master插入一条数据，然后启动mysql slave 观察两边数据是否一致

master

slave

五、数据恢复

30339数据正确，30338数据错误异常

• 在主机上备份：
  mysqldump -h 192.168.3.201 -P30339 -uroot -p123456 --single-transaction --master-data=1  -R --events --triggers=true --databases pdl >  ./`date +%F-%H`_mysql-pdl.sql 
  查看binlog位置 
  grep -i "CHANGE MASTER" 2023-06-16-16_mysql-pdl.sql  

• 停止slave

30339(mysql中)上的slave ：stop slave;

启动303389(mysql中) 并停止slave：stop slave; 

• 在主机通过dump恢复
  mysql -h 192.168.3.201 -P30338 -uroot -p  < 2023-06-16-11_mysql-pdl.sql 

• 30338执行
  先在30338(mysql中) 执行show master status\G 记录需要在30339中执行的 MASTER_LOG_FILE='mysql-bin.000419', MASTER_LOG_POS=286531727; 
  然后在30338上执行 grep命令查出来的 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000010', MASTER_LOG_POS=54617; 
  30338上启动：start slave 

• 30339执行

在30339(mysql中)上执行 CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=20397;
然后启动：start slave

（1、mysql容器中执行，生成dump文件）
mysqldump -uzeusroot -pzhanwanzhanchi  --single-transaction --master-data=1  -R --events --triggers=true --set-gtid-purged=off --databases iwork iwork_test iwork_test_sql iwork_wh iworkapp iwork-jx middle_office tm >  ./`date +%F%H`_mysql.sql

（2、复制容器中的dump文件到主机上：default是namespace）
kubectl cp default/mysql-7669c875d7-g827d:/2023-10-2613_mysql.sql /tmp/2023-10-2613_mysql.sql

（3、复制主机上的dump文件到mysql2容器内）
kubectl cp /tmp/2023-10-2613_mysql.sql default/mysql2-6889bdb9f8-fh9tf:/2023-10-2613_mysql.sql 

（4、mysql2容器中执行恢复dump文件）
mysql -h 192.168.3.201 -P30338 -uroot -p  < 2023-06-16-11_mysql-pdl.sql

（5、mysql2容器中执行个grep命令，查询mysql的MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=103457）
grep -i "CHANGE MASTER" 2023-10-2613_mysql.sql

（6、mysql2数据库中执行）
change master to master_host='mysql.default', master_user='zeusroot',master_password='zhanwanzhanchi',master_log_file='mysql-bin.000002',master_log_pos=103457;
show master status\G（查询mysql2的File: mysql-bin.000004 Position: 68923805 给mysql用）
start slave;
show slave status\G;

（7、mysql数据库中执行）
change master to master_host='mysql2.default', master_user='zeusroot',master_password='zhanwanzhanchi',master_log_file='mysql-bin.000004',master_log_pos=68923805 ;
start slave;
show slave status\G;

六、新增数据库配置

schema.xml中新增对应的数据库配置

    <schema name="pdl" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"></schema>
    <schema name="test" checkSQLschema="false" sqlMaxLimit="100" dataNode="test1"></schema>
    <dataNode name="dn1" dataHost="localhost1" database="pdl" />    
    <dataNode name="test1" dataHost="localhost1" database="test" />

server.xml中新增对应的数据库配置

      <user name="root">
        <property name="password">123456</property>
        <property name="schemas">pdl,test</property>
      </user>