mysql数据库搭建，主从同步搭建，双机热备(主主复制)

- 日理万妓 2023-10-07 20:16 50阅读 0赞

## 1. 京淘后端项目架构 ##

### 1.1 数据库主从同步 ###

#### 1.1.1 业务需求说明 ####

当数据库发生宕机的现象,如果配置了数据库主从同步,则可以从从库中获取现有记录,显示数据的恢复.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70]

### 1.2 数据库主从同步原理 ###

#### 1.2.1 数据库冷备份 ####

说明: 人工的将数据库文件**定期导出**,一式三份 如果其中一份丢失,另外2份可以留作替补.  
弊端: **数据不能实时备份.**  
解释: 工作中数据库的冷备份是恢复数据最后有效的手段(重大事故)

#### 1.2.2 数据库热备份 ####

说明: 最为主要的作用可以实现实时备份. 几乎保证数据不丢失.  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 1]

### 克隆2台服务器 ###

被克隆的服务器要处于关机状态，  
1、选中需要克隆的服务器，右键，管理，克隆，创建完整克隆，CentOS7-db01  
2、修改ip，

#编辑网卡 vi /etc/sysconfig/network-scripts/ifcfg-ens32
    IPADDR="192.168.111.131"
    #重启网卡
    service network restart

#### 1、安装mariadb数据库 ####

yum -y install mariadb-server

#### 2、启动mariadb数据库、开机启动 ####

systemctl start mariadb
    systemctl enable mariadb

#### 3、数据库初始化操作 ####

mysql_secure_installation

Enter current password for root (enter for none):
    输入root用户的当前密码（输入无）：这里直接回车
    
    Set root password? [Y/n]
    设置root密码？[是/否]：这里输入y
    
    New password:
    新密码：这里输入root
    
    Re-enter new password:
    重新输入新密码：这里输入root
    
    Remove anonymous users? [Y/n]
    删除匿名用户？[是/否]：这里输入y
    
    Disallow root login remotely? [Y/n]
    不允许root用户远程登录？[是/否]：这里输入n
    
    Remove test database and access to it? [Y/n]
    删除test数据库？[是/否]：这里输入y
    
    Reload privilege tables now? [Y/n]
    现在重新加载特权表吗？[是/否]：这里输入y

\#开启端口  
firewall-cmd --zone=public --add-port=3306/tcp --permanent  
\#重新加载防火墙：  
firewall-cmd --reload

mysql -u root -p
    输入密码 root
    use mysql;
    #将host='localhost' 改为 '%',表示任何ip地址都可以访问
    update user set host='%' where host='localhost';
    #刷新数据库权限
    flush privileges;

刚刚新建的数据库，再克隆一份  
1、选中需要克隆的服务器，右键，管理，克隆，创建完整克隆，CentOS7-db02  
2、修改ip，

#编辑网卡 vi /etc/sysconfig/network-scripts/ifcfg-ens32
    IPADDR="192.168.111.132"
    #重启网卡
    service network restart

### 1.3 数据库备份实现 ###

#### 1.3.1 实现数据库导入 ####

2个数据都创建相同的数据库名称和表名和字段： 测试数据库名mydatabase 和、测试表名 mytable 、一个int类型的id字段就可以了

#### 1.3.2 开启二进制日志文件 ####

1.  默认条件下 数据库的二进制日志文件是关闭的.如果需要备份则手动开启
2.  编辑二进制日志文件 **/etc/my.cnf**
3.  编辑配置文件，修改数据节点的唯一标识

新增2行，其中server-id用来区分节点的编号，唯一。log-bin设置二进制文件的名称。

server-id=1
    log-bin=mysql-bin

另外一台

server-id=2
    log-bin=mysql-bin

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 2]

1.  默认数据库文件位置 cat **/etc/my.cnf**  
    ![在这里插入图片描述][20210628200755806.png]
2.  重启数据库 让二进制文件生效即可

systemctl restart mariadb

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 3]

### 1.4 实现主从挂载 ###

#### 1.4.1 主库状态 ####

主库IP这里使用192.168.111.131  
检查主库的状态，主要查询 要同步的 文件名和位置

#在主库执行
    # 在主库131查询状态
    SHOW MASTER STATUS;

![8a056a5e9af44d4a896dca3867bc4c9d.png][]

#### 1.4.2 实现主从挂载 ####

这一步在 从库192.168.111.132 里面执行

# 在从库中执行
    /*132从库 从库链接主库
    IP/端口/用户名/密码/二进制文件名/pos位置
    */
    CHANGE MASTER TO 
    MASTER_HOST="192.168.111.131",#主库ip
    MASTER_PORT=3306,
    MASTER_USER="root",
    MASTER_PASSWORD="root",
    MASTER_LOG_FILE="mysql-bin.000001",
    MASTER_LOG_POS=245;

![ca7938d2945f48f088c90b68ae9c8a72.png][]

#### 1.4.3 启动主从 ####

启动主从服务，**在从库执行**下面命令

# 在从库中执行
    # 启动主从服务
    START SLAVE;

这一步在 从库192.168.111.132 里面执行，出现如下2个yes就成功了。

#检查主从状态
    SHOW SLAVE STATUS;

![6f558713b43c47e9bd696f1c0f4d383f.png][]

### 测试主从 ###

主从要达到的效果，修改主库的数据，从库的数据跟着改变。**千万不要手动去改从库的数据，去看主库变化没有，千万不要这样操作，这样主从就废了**。

创建数据库是编码要选择uft8，不然中文会乱码

![0aaf0054e69a4713a4b1cce8e1bb8ec6.png][]

#### 1.4.4 主库主从搭建问题 ####

1.  停止主从服务  
    stop slave
2.  排查错误
3.  重启进行主从挂载
4.  重置主从服务 reset slave
5.  重复挂载即可 如果不行 推倒重做

### 1.5 实现数据库读写分离/负载均衡 ###

主库：可读、可写。

从库：只可读。

#### 1.5.1 Mycat数据库代理 ####

前身是阿里的一个产品。这个要依赖jdk。[http://www.mycat.org.cn/][http_www.mycat.org.cn]

mycat 其实就是一种反向代理

基于心跳的自动故障切换，支持读写分离。支持数据的多片自动路由与聚合。

活跃的、性能好的开源数据库中间件!  
我们致力于开发高性能的开源中间件而努力！  
重要通告，最近有区块链项目借Mycat宣传,与Mycat毫无关系,大家不要相信!

#### 1.5.2 Mycat安装 ####

1). 解压安装包 如果所示.

[https://download.csdn.net/download/u014644574/86759510][https_download.csdn.net_download_u014644574_86759510]

![e4a8e35625da4773802ff3a1ecec6df3.png][]

解压mycat安装包

tar -xvf Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz

2. 目录结构  
bin: Mycat的可执行文件  
conf: Mycat的配置文件  
logs: Mycat日志 如果报错

这里需要改conf目录下的2个配置文件，自带的配置文件里面太复杂，先备份用自己上传的。

# 备份
    mv server.xml server.xml.bak
    mv schema.xml schema.xml.bak
    
    # 将上传的配置文件移动到conf目录下
    mv schema.xml mycat/conf/
    mv server.xml mycat/conf/

#### 1.5.3 编辑server.xml ####

说明: 该配置主要实现用户与代理之间的配置关系,  
端口号: 8066端口

配置代理的用户名和密码

修改配置文件: vi /root/mycat/conf/server.xml

末尾修改如下：

![2a5314c5866a4af6afead004f0773eef.png][]  
上面配置了2个代理用户(实际数据库这2个用户可以没有)，上面创建了一个代理数据库和2个代理用户。  
代理数据库名称 mydb  
读写代理用户/密码：root/root  
只读代理用户/密码：user/user  
可见代理的数据名、用户名、密码全都是虚拟的，根本没有，这样保护真实的数据库。

<?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mycat:server SYSTEM "server.dtd">
    <mycat:server xmlns:mycat="http://io.mycat/">
            <system>
            <property name="nonePasswordLogin">0</property> 
            <property name="useHandshakeV10">1</property>
            <property name="useSqlStat">0</property>  
            <property name="useGlobleTableCheck">0</property>  
    
                    <property name="sequnceHandlerType">2</property>
           
             
            
            
            
                    <property name="processorBufferPoolType">0</property>
                    
                    
                    
                    
                    
                    
                    
                    
                    <property name="handleDistributedTransactions">0</property>
    
                            
                    <property name="useOffHeapForMerge">1</property>
    
                    
            <property name="memoryPageSize">64k</property>
    
                    
                    <property name="spillsFileBufferSize">1k</property>
    
                    <property name="useStreamOutput">0</property>
    
                    
                    <property name="systemReserveMemorySize">384m</property>
    
    
                    
                    <property name="useZKSwitch">true</property>
    
                    
                    
    
                    
                    
    
            </system>
    
            
            
            
            
            
            
            
            
            
            <user name="root">
                    <property name="password">root</property>
                    
                    <property name="schemas">mydb</property>
            </user>
    
            <user name="user">
                    <property name="password">user</property>
                    <property name="schemas">mydb</property>
                    <property name="readOnly">true</property>
            </user>
    </mycat:server>

#### 1.5.4 编辑schema.xml ####

这一步是配置代理数据库，代理用户密码，和真实数据库用户名密码的映射。

vi /root/mycat/conf/schema.xml

用户写操作访问131,用户的读操作访问131/132

![5d911252381b4474bf396be91443cbcb.png][]

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
            
            <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="mynode"/>
    
            
            <dataNode name="mynode" dataHost="localhost1" database="testdb" />
                    
                    
                    
                    
                    
                    
                    
                    
                    
            <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                    <heartbeat>select 1</heartbeat>
    
                    
                    <writeHost host="hostM1" url="192.168.111.131:3306" user="root" password="root">
                            
                            <readHost host="hostS1" url="192.168.111.132:3306" user="root" password="root" />
                            
                            <readHost host="hostS2" url="192.168.111.131:3306" user="root" password="root" />
    
                    </writeHost>
    
                            
            </dataHost>
    </mycat:schema>

#### 1.5.5 mycat命令 ####

cd /root/mycat/bin

#启动
    ./mycat start
    #停止
    ./mycat stop
    #重启
    ./mycat restart
    #查看状态
    ./mycat status

![4267ba1fbac947e987d9859ed83c8f65.png][]

启动mycat默认端口8066

#开启端口
    firewall-cmd --zone=public --add-port=8066/tcp --permanent
    #重新加载防火墙：
    firewall-cmd --reload

#### 1.5.6 修改数据库地址 ####

<dependencies>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
            </dependency>
            
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-jdbc</artifactId>
            </dependency>
            
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <scope>runtime</scope>
            </dependency>
        </dependencies>

测试代码

@SpringBootApplication
    public class MydbApplication implements CommandLineRunner {
    
        @Autowired
        private JdbcTemplate jdbcTemplate;
    
        public static void main(String[] args) {
            SpringApplication.run(MydbApplication.class, args);
        }
    
        @Override
        public void run(String... args) throws Exception {
            String sql = "SELECT * FROM test_tb";
            List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
            for (int i = 0; i < list.size(); i++) {
                for (Map.Entry<String, Object> entry : list.get(i).entrySet()) {
                    System.out.println(entry.getKey() + "=" + entry.getValue());
                }
    
            }
        }
    
    
        @Service
        public class DemoService {
    
        }
    
    }

单机配置131

spring.datasource.url=jdbc:mysql://192.168.111.131:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
    spring.datasource.username=root
    spring.datasource.password=root

单机配置132

spring.datasource.url=jdbc:mysql://192.168.111.132:3306/testdb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
    spring.datasource.username=root
    spring.datasource.password=root

由于mycat安装在了131上面，所有ip连接131就可以了

读/写 数据库连接方式

spring.datasource.url=jdbc:mysql://192.168.111.131:8066/mydb?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf8&autoReconnect=true&allowMultiQueries=true
    spring.datasource.username=root
    spring.datasource.password=root

只读数据库连接方式，此连接方式，修改数据会失败。只能读数据。

**怎么测试负载均衡？**

我们手动修改主库的数据，从库必然会改变，这样测试不出来效果，  
现在我们修改从库（**生产千万不要修改从库，这样做不会导致主从失效，只会导致在从库修改的这一条数据和主库不一致，可以在主库或者从库直接把这条数据改回来就行了**），主库是数据不会变的，现在执行查询，发现数据一次是主库的，一次是修改后的从库的数据。说明负载均衡生效了。

#### 1.5.7 负载均衡实现 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 4]

### 1.6 高可用 -双机热备(主主复制) ###

#### 1.6.1 介绍 ####

当数据库主机宕机之后,要求实现高可用机制.用户程序将不会受到影响. 数据库高可用机制.

#### 1.6.2 双机热备思想 ####

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 5]

#### 1.6.3 反向搭建 ####

以前 131 是主机 132是从库。

现在，都是主库。互为主从。

在主从的基础上，再反向搭建一次主从，现在 131 是从库，132是主库。

1).在主库132 查询状态，主要是查询 要同步的 文件名和位置

#主库上执行
    SHOW MASTER STATUS;

![7b5b20177ac34fe0a3f77edfd1cfbcee.png][]  
  
2). 在从库131 执行，从库链接主库，挂载命令

# 在从库中执行
    # 从库链接主库
    CHANGE MASTER TO 
    MASTER_HOST="192.168.111.132", #要链接到哪里
    MASTER_PORT=3306,
    MASTER_USER="root",
    MASTER_PASSWORD="root",
    MASTER_LOG_FILE="mysql-bin.000003",
    MASTER_LOG_POS=472;
    
    # 在从库中执行
    #启动挂载主从
    START SLAVE;
    
    # 在从库中执行
    # 检查主从状态，查询是否挂载成功
    SHOW SLAVE STATUS;

![515dfba093194f0aa67f7fce082d24f4.png][]

出现2个yes 表示成功。

现在，在2台数据库之间修改，发现都可以相互修改了。

#### 1.6.4 修改Mycat配置文件 ####

server.xml 不用变

修改 schema.xml 实现 双主发生故障自动切换

![a9d7b3fe86b7437e9c3e7b1cc6d8a071.png][]

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema
        xmlns:mycat="http://io.mycat/">
        
        <schema name="mydb" checkSQLschema="false" sqlMaxLimit="100" dataNode="mynode"/>
        
        <dataNode name="mynode" dataHost="localhost1" database="testdb" />
        
        
        
        
        
        
        
        
        
        
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                              writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
            <heartbeat>select 1</heartbeat>
            
            <writeHost host="hostM1" url="192.168.111.131:3306" user="root" password="root">
                
                <readHost host="hostS1" url="192.168.111.132:3306" user="root" password="root" />
                
                <readHost host="hostS2" url="192.168.111.131:3306" user="root" password="root" />
            </writeHost>
            
            
            
            
            
            <writeHost host="hostM2" url="192.168.111.132:3306" user="root" password="root">
                
                <readHost host="hostS1" url="192.168.111.131:3306" user="root" password="root" />
                
                <readHost host="hostS2" url="192.168.111.132:3306" user="root" password="root" />
            </writeHost>
        </dataHost>
    </mycat:schema>

修改后需要重启mycat

![36b07e02769c4f8ea79a142d20e79fd3.png][]

注意：在双主模式下，虽然数据库之间已经可以相互修改了。  
但是mycat的user代理用户 始终是只读，比如下面的配置更新语句会报错，只能执行查询语句。

![9fa4d1ad29484ac29004edc1c9e854b2.png][]

上面的配置中第1台，在配置

测试1台数据库宕机

#停止第1台
    systemctl stop mariadb
    
    
    启动第1台
    systemctl start mariadb

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210628192349211.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210628194001937.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210628200657105.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[20210628200755806.png]: https://img-blog.csdnimg.cn/20210628200755806.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210628201140350.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[8a056a5e9af44d4a896dca3867bc4c9d.png]: https://img-blog.csdnimg.cn/8a056a5e9af44d4a896dca3867bc4c9d.png
[ca7938d2945f48f088c90b68ae9c8a72.png]: https://img-blog.csdnimg.cn/ca7938d2945f48f088c90b68ae9c8a72.png
[6f558713b43c47e9bd696f1c0f4d383f.png]: https://img-blog.csdnimg.cn/6f558713b43c47e9bd696f1c0f4d383f.png
[0aaf0054e69a4713a4b1cce8e1bb8ec6.png]: https://img-blog.csdnimg.cn/0aaf0054e69a4713a4b1cce8e1bb8ec6.png
[http_www.mycat.org.cn]: http://www.mycat.org.cn/
[https_download.csdn.net_download_u014644574_86759510]: https://download.csdn.net/download/u014644574/86759510
[e4a8e35625da4773802ff3a1ecec6df3.png]: https://img-blog.csdnimg.cn/e4a8e35625da4773802ff3a1ecec6df3.png
[2a5314c5866a4af6afead004f0773eef.png]: https://img-blog.csdnimg.cn/2a5314c5866a4af6afead004f0773eef.png
[5d911252381b4474bf396be91443cbcb.png]: https://img-blog.csdnimg.cn/5d911252381b4474bf396be91443cbcb.png
[4267ba1fbac947e987d9859ed83c8f65.png]: https://img-blog.csdnimg.cn/4267ba1fbac947e987d9859ed83c8f65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210629200617418.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20210629201132173.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzE2ODA0ODQ3,size_16,color_FFFFFF,t_70
[7b5b20177ac34fe0a3f77edfd1cfbcee.png]: https://img-blog.csdnimg.cn/7b5b20177ac34fe0a3f77edfd1cfbcee.png
[515dfba093194f0aa67f7fce082d24f4.png]: https://img-blog.csdnimg.cn/515dfba093194f0aa67f7fce082d24f4.png
[a9d7b3fe86b7437e9c3e7b1cc6d8a071.png]: https://img-blog.csdnimg.cn/a9d7b3fe86b7437e9c3e7b1cc6d8a071.png
[36b07e02769c4f8ea79a142d20e79fd3.png]: https://img-blog.csdnimg.cn/36b07e02769c4f8ea79a142d20e79fd3.png
[9fa4d1ad29484ac29004edc1c9e854b2.png]: https://img-blog.csdnimg.cn/9fa4d1ad29484ac29004edc1c9e854b2.png