mysql8模拟千万条数据gtid主从分布环境

本文链接：https://blog.youkuaiyun.com/Sendsunshine/article/details/131945431

一、mysql8安装：

1. 设置yum源

[mysql-cluster-8.0-community]
name=MySQL Cluster 8.0 Community
baseurl=http://repo.mysql.com/yum/mysql-cluster-8.0-community/el/7/$basearch
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022
       file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

2. 安装指定版本mysql

yum install mysql-community*8.0.27-1.el7

3. 重置密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

4. 设置远程登陆

use mysql;
update user set Host='%' where User='root';
flush privileges;

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
flush privileges;

二、配置gitd主从分布环境

1. 主从库my.cnf增加配置

#GTID:
gtid_mode=on
enforce_gtid_consistency=on
server_id=20
#从库不能同步主库,备份主库打开从库此配置
#slave-skip-errors=all

##binlog
log-bin=slave-binlog
log-slave-updates=1
binlog_format=row      #强烈建议，其他格式可能造成数据不一致

##relay log
skip_slave_start=1

2. 配置从库（默认方式主从）

CHANGE MASTER TO  
      MASTER_HOST='192.168.215.132',    
      MASTER_USER='root',    
      MASTER_PASSWORD='Aa123456##',    
      MASTER_PORT=3306,    
      MASTER_AUTO_POSITION = 1;

3. 查看从库状态

show slave status \G ###可以看到复制工作已经开始且正常

4. 关闭从库同步

stop slave;

5. 主从库设置GTID方式主从同步 (重启主从库)

SET @@global.read_only = ON;

6. 从库配置

CHANGE MASTER TO
         MASTER_HOST = '192.168.215.132',
         MASTER_PORT = 3306,
         MASTER_USER = 'root',
         MASTER_PASSWORD = 'Aa123456##',
         MASTER_AUTO_POSITION = 1;

7. 开启从库同步

start slave;

三、数据库中添加多库多表多数据

导入连接驱动：

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>

jdbc工具类：

package mysql;

import java.sql.*;

public class JdbcUtils {
    protected static Connection conn=null;
    //获得连接工具类
    public static Connection getConn() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        DriverManager.setLoginTimeout(0);
        conn = DriverManager.getConnection("jdbc:mysql://192.168.215.132:3306?serverTimezone=UTC", "root","Aa123456##");
        return conn;
    }
    //获得连接工具类,连接数据库
    public static Connection getDataBaseConn(String dataBaseName) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn= DriverManager.getConnection("jdbc:mysql://192.168.215.132:3306/" + dataBaseName + "?useSSL=true","root","Aa123456##");
        return conn;
    }
}

主类：

package mysql;

import java.sql.*;

public class MysqlTestData {

    public static void main(String[] args) {
        //创建数据库 5个
        for (int i = 1 ; i < 6; i ++ ) {
            createDatabase(i);
        }
    }

    public static void createDatabase (int math) {
        Connection connection = null;
        Statement statement = null;
        String databaseName = "database"+ "-" + math;
        try {
            connection = JdbcUtils.getConn();
            statement = connection.createStatement();
            int i = statement.executeUpdate("create database if not exists `" + databaseName + "` default character set utf8mb4 COLLATE utf8mb4_general_ci");
            if (i != 0) {
                System.out.println("创建数据库" + databaseName + "成功");
            } else {
                return;
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.close();
                statement.close();
                return;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }

        //创建表 2000个
        new Thread(new Runnable() {
            @Override
            public void run() {
                for (int i = 1 ; i < 2001; i ++ ) {
                    try {
                        Thread.sleep(100);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }

                    createTable(databaseName, i);
                }
            }
        }).start();
    }

    public static synchronized void createTable (String dataBaseName, int math) {
        Connection connection = null;
        String tableName = "table" + "-" + math;
        try {
            connection = JdbcUtils.getDataBaseConn(dataBaseName);
            String sql = "CREATE TABLE IF NOT EXISTS `"+ tableName +"`(\n" +
                    "   `runoob_id` INT UNSIGNED AUTO_INCREMENT,\n" +
                    "   `runoob_title` VARCHAR(100) NOT NULL,\n" +
                    "   `runoob_author` VARCHAR(40) NOT NULL,\n" +
                    "   `submission_date` DATE,\n" +
                    "   PRIMARY KEY ( `runoob_id` )\n" +
                    ")ENGINE=InnoDB DEFAULT CHARSET=utf8;\n";
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.executeUpdate();
            System.out.println("添加表" + tableName + "成功！");
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.close();
                return;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
        //添加数据 2000条
        new Thread(new Runnable() {
            @Override
            public void run() {
                for (int i = 1 ; i < 2001; i ++ ) {
                    try {
                        Thread.sleep(100);
                    } catch (InterruptedException e) {
                        e.printStackTrace();
                    }
                    insertData(dataBaseName, tableName, i);
                }
            }
        }).start();
    }

    public static synchronized void insertData (String dataBaseName, String tableName, int math) {
        Connection connection = null;
        try {
            connection = JdbcUtils.getDataBaseConn(dataBaseName);
            String sql = "INSERT INTO `" + tableName + "` (runoob_title, runoob_author, submission_date) VALUES (\"MySQL\", \"xxx\", NOW());";
            PreparedStatement stmt = connection.prepareStatement(sql);
            int i = stmt.executeUpdate();
            if (i != 0) {
                System.out.println(tableName + "添加" + math + "条数据成功!");
            } else {
                return;
            }
        } catch (Exception e) {
            e.printStackTrace();
            try {
                connection.close();
                return;
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                connection.close();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }
}