（一）安装Mysql8.0
1、下载安装包
复制代码
1
2
3
4
5
6
7
8
9
10
11
12[root@VM-24-15-centos ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm 警告：mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY Verifying... ################################# [100%] 准备中... ################################# [100%] 正在升级/安装... 1:mysql80-community-release-el7-3 ################################# [100%] [root@VM-24-15-centos ~]# yum repolist enabled | grep mysql mysql-connectors-community MySQL Connectors Community mysql-tools-community MySQL Tools Community mysql80-community MySQL 8.0 Community Server
2、rpm安装依赖包
复制代码
1
2
3
4
5
6
7
8[root@VM-24-15-centos ~]# rpm -ivh mysql80-community-release-el7-3.noarch.rpm 警告：mysql80-community-release-el7-3.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY Verifying... ################################# [100%] 准备中... ################################# [100%] 正在升级/安装... 1:mysql80-community-release-el7-3 ################################# [100%]
3、查看yum仓库中Mysql服务
复制代码
1
2
3
4
5
6[root@VM-24-15-centos ~]# yum repolist enabled | grep mysql mysql-connectors-community MySQL Connectors Community mysql-tools-community MySQL Tools Community mysql80-community MySQL 8.0 Community Server
4、关闭默认mysql模块
复制代码
1
2
3[root@VM-24-15-centos ~]# yum module disable mysql
5、yum安装服务
复制代码
1
2
3
4
5[root@VM-24-15-centos ~]# yum install -y mysql-community-server 完毕！ ## 看到success表示安装成功！
6、修改etc/my.cnf
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17[root@VM-24-15-centos ~]# vim /etc/my.cnf ## 添加以下配置 [mysqld] #配置文件根据环境自行配置，或者保持默认 #例如添加下面几行，设置默认引擎编码和排序规则(根据情况设置合适的) default-storage-engine=INNODB character-set-server = utf8mb4 collation-server = utf8mb4_general_ci skip-character-set-client-handshake secure_file_priv='' [client] default-character-set=utf8mb4 [mysql] default-character-set=utf8mb4
utf8mb4和utf8的区别：
utf8mb4是utf8的超集，utf8mb4能够存储4个字节，utf8只能存储3个字节。
7、启动mysql服务
复制代码
1
2
3
4
5
6
7
8
9[root@VM-24-15-centos ~]# systemctl start mysqld ## 查看状态 [root@VM-24-15-centos ~]# systemctl status mysqld ## 设置开机启动 [root@VM-24-15-centos ~]# systemctl enable mysqld ## 查看监听端口，默认 3306 [root@VM-24-15-centos ~]# ss -natl |grep 3306
8、查看默认密码并且修改
复制代码
1
2
3
4[root@VM-24-15-centos ~]# grep 'temporary password' /var/log/mysqld.log 2022-01-17T03:03:23.091948Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: C&,WHskkn6zV ## 默认密码：C&,WHskkn6zV
登录mysql
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16[root@VM-24-15-centos ~]# mysql -uroot -p'C&,WHskkn6zV' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 Server version: 8.0.27 Copyright (c) 2000, 2021, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql>
修改密码：
复制代码
1
2
3
4mysql> alter user root@localhost identified by 'xxx'; Query OK, 0 rows affected (0.01 sec) ## xxx表示自己的密码
9、开放3306端口和云服务器入站规则
查看防火墙状态
复制代码
1
2
3
4
5
6
7[root@VM-24-15-centos ~]# systemctl status firewalld.service ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: inactive (dead) Docs: man:firewalld(1) ## dead说明防火墙是关闭的
开启防火墙
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13[root@VM-24-15-centos ~]# systemctl start firewalld.service [root@VM-24-15-centos ~]# systemctl status firewalld.service ● firewalld.service - firewalld - dynamic firewall daemon Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled) Active: active (running) since Mon 2022-01-17 11:24:46 CST; 1s ago Docs: man:firewalld(1) Main PID: 259131 (firewalld) Tasks: 2 (limit: 11426) Memory: 32.6M CGroup: /system.slice/firewalld.service └─259131 /usr/libexec/platform-python -s /usr/sbin/firewalld --nofork --nopid ## Active: active (running)表示防火墙已经开启
开放防火墙端口
复制代码
1
2
3
4
5[root@VM-24-15-centos ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent success [root@VM-24-15-centos ~]# firewall-cmd --reload success
查看防火墙已经开放的端口
复制代码
1
2
3
4[root@VM-24-15-centos ~]# firewall-cmd --zone=public --list-ports 3306/tcp ## 3306端口已经开放
开放云服务器的入站规则（具体参照云服务器提供商操作手册）
Navcat连接问题：
（1）host is not allowed to connect to this mysql server
解决方案：
复制代码1
2
3
4use mysql; update user set host = '%' where user ='root'; flush privileges;（2）1251 client does not support authentication protocol requested by server;consider upgrading Mysql client
解决方案：
复制代码1
2
3
4USE mysql; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'xxx'; ## xxx表示自己的密码
（二）搭建Mysql主从同步
1、建Mysql主从
主从复制：
Master节点将DB操作写入Binary log(二进制文件)，Slave节点通过I/O读取二进制文件写入中继文件（Relay log）,然后将Realy log内容刷入到DB中。
注意：
主从复制需要保证DB的版本一致
修改Master节点 /etc/my.cnf配置
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13[root@hecs-36968 ~]# vim /etc/my.cnf ## 加入以下配置 server-id=100 ## 开启二进制文件 log-bin=mysql-bin ## 主从复制忽略mysql数据库 binlog-ignore-db=mysql ## binlog的内存大小 binlog_cache_size=1M ## 主从复制策略 binlog_format=mixed
修改Slave节点 /etc/my.cnf配置
复制代码
1
2
3
4
5
6
7
8
9
10
11
12server-id=102 log-bin=mysql-slave-bin ## slave节点二进制文件 relay_log=edu-mysql-relay-bin binlog-ignore-db=mysql ## binlog的内存大小 binlog_cache_size=1M ## 主从复制策略 binlog_format=mixed ## 排除主键引发的问题报错 slave_skip_errors=1062
重启Master节点数据库使配置生效
复制代码
1
2
3[root@hecs-36968 ~]# systemctl restart mysqld
Master节点连接Mysql，查看状态
复制代码
1
2
3
4
5
6
7
8
9mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 156 | | mysql | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
重启Slave节点数据库使配置生效
复制代码
1
2
3[root@hecs-36968 ~]# systemctl restart mysqld
Slave节点配置连接Master节点
复制代码
1
2
3
4
5
6
7
8
9
10
11
12mysql> change master to master_host='xxx', master_user='root', master_password='xxx', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 156, master_connect_retry=30; Query OK, 0 rows affected, 10 warnings (0.03 sec) ## master_host ：Master的地址 ## master_port：Master的端口号 ## master_user：用于数据同步的用户 ## master_password：用于同步的用户的密码 ## master_log_file：指定 Slave 从哪个日志文件开始复制数据 ## master_log_pos：从哪个 Position 开始读 ## master_connect_retry：如果连接失败，重试的时间间隔
Slave节点启动主从复制
复制代码
1
2
3mysql> start slave; Query OK, 0 rows affected, 1 warning (0.01 sec)
Slave节点查看主从状态
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67mysql> show slave status G *************************** 1. row *************************** Slave_IO_State: Waiting for source to send event Master_Host: 119.3.176.65 Master_User: root Master_Port: 3306 Connect_Retry: 30 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 156 Relay_Log_File: edu-mysql-relay-bin.000002 Relay_Log_Pos: 324 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 156 Relay_Log_Space: 537 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: b0441377-75ab-11ec-8a87-fa163e61614f Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 1 row in set, 1 warning (0.00 sec) ## Slave_IO_Running: Yes，Slave_SQL_Running: Yes ## 这两个状态表示主从配置完成！
Salve节点主从启动失败常见错误：
（1）Slave_IO_Running: Connecting
解决思路：网络不通、ip、端口、用户名密码不对、pos不对
（2）Slave_SQL_Running: No
解决方案 1：
复制代码1
2
3
4
5mysql> stop slave; mysql> set global sql_slave_skip_counter=1; mysql> start slave; mysql> show slave statusG; 解决方案 2：
复制代码1
2
3
4
5
6
7## 查看Master状态 mysql> show master status; ## Slave节点重新绑定 mysql> stop slave; mysql> change master to master_host='119.3.176.65', master_user='root', master_password='xxx', master_port=3306, master_log_file='mysql-bin.000001', master_log_pos= 156, master_connect_retry=30; mysql> start slave;
测试主从复制：
Master节点创建sharding_order数据库
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19mysql> create database sharding_order; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | febs_nacos | | geekbang_coupon_db | | information_schema | | mysql | | performance_schema | | sharding_order | | sys | | xxl_job | +--------------------+ 8 rows in set (0.00 sec)
查看Slave节点
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sharding_order | | sys | +--------------------+ 5 rows in set (0.00 sec)
测试结果表示主从复制搭建成功！
2、JAVA版Sharding-JDBC DEMO
主从pom文件
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128<?xml version="1.0" encoding="UTF-8"?> <project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd"> <modelVersion>4.0.0</modelVersion> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>2.5.9-SNAPSHOT</version> <relativePath/> <!-- lookup parent from repository --> </parent> <groupId>com.xuexiangban</groupId> <artifactId>demo</artifactId> <version>0.0.1-SNAPSHOT</version> <name>sharding</name> <description>Demo project for Spring Boot</description> <properties> <java.version>1.8</java.version> <mybatis.plus.version>3.3.2</mybatis.plus.version> <druid.version>1.1.9</druid.version> <sharding.jdbc.version>4.1.0</sharding.jdbc.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <!--mybatis-plus依赖--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>${mybatis.plus.version}</version> </dependency> <!--druid依赖--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>${druid.version}</version> </dependency> <!--sharding-jdbc依赖--> <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>sharding-jdbc-spring-boot-starter</artifactId> <version>${sharding.jdbc.version}</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.4</version> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> <configuration> <excludes> <exclude> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </exclude> </excludes> </configuration> </plugin> </plugins> </build> <repositories> <repository> <id>spring-milestones</id> <name>Spring Milestones</name> <url>https://repo.spring.io/milestone</url> <snapshots> <enabled>false</enabled> </snapshots> </repository> <repository> <id>spring-snapshots</id> <name>Spring Snapshots</name> <url>https://repo.spring.io/snapshot</url> <releases> <enabled>false</enabled> </releases> </repository> </repositories> <pluginRepositories> <pluginRepository> <id>spring-milestones</id> <name>Spring Milestones</name> <url>https://repo.spring.io/milestone</url> <snapshots> <enabled>false</enabled> </snapshots> </pluginRepository> <pluginRepository> <id>spring-snapshots</id> <name>Spring Snapshots</name> <url>https://repo.spring.io/snapshot</url> <releases> <enabled>false</enabled> </releases> </pluginRepository> </pluginRepositories> </project>
(1)读写分离yaml文件配置
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41server: port: 8080 spring: main: allow-bean-definition-overriding: true ## 打印执行sql shardingsphere: props: sql: show: true datasource: names: ds0,ds1 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 sharding: ## 默认数据源，用于写 default-data-source-name: ds0 masterslave: ## 主从配置名称，随便写 name: ms ## 主节点名称，只用于写 master-data-source-name: ds0 ## 从节点名称，只用于读，多个用逗号分隔 slave-data-source-names: ds1 ## 负载均衡类型 random、round_robin load-balance-algorithm-type: round_robin
(2)分库分表yaml配置
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52server: port: 8080 spring: main: allow-bean-definition-overriding: true ## 打印执行sql shardingsphere: props: sql: show: true datasource: names: ds0,ds1 ds0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 ds1: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 sharding: ## 用于分库分表 tables: ## 逻辑表名 user_order: ## 主键生成策略 key-generator: column: id type: SNOWFLAKE ## 配置要分的库和表 actual-data-nodes: ds$->{0..1}.user_order$->{0..2} ## 分库策略 database-strategy: ## 标准分片 standard: sharding-column: birthday precise-algorithm-class-name: com.xuexiangban.sharding.conf.BirthdayPreciseShardingAlgorithm ## 分表策略 table-strategy: inline: sharding-column: sex algorithm-expression: user_order$->{sex % 2}
(3)读写分离+分库分表yaml配置
复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59server: port: 8080 spring: main: allow-bean-definition-overriding: true ## 打印执行sql shardingsphere: props: sql: show: true datasource: names: m0,s0 m0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 s0: type: com.alibaba.druid.pool.DruidDataSource driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://xxx:3306/sharding_order?characterEncoding=utf8&connectTimeout=1000&socketTimeout=10000&autoReconnect=true&serverTimeZone=GMT%2b8 username: root password: xxx maxPoolSize: 100 minPoolSize: 5 sharding: master-slave-rules: ds0: ## Master节点 master-data-source-name: m0 ## Slave节点 slave-data-source-names: s0 ## 负载均衡策略 load-balance-algorithm-type: ROUND_ROBIN ## 用于分库分表 tables: ## 逻辑表名 user_order: ## 配置要分的库和表 actual-data-nodes: ds0.user_order$->{0..2} ## 主键生成策略 key-generator: column: id type: SNOWFLAKE ## 分库策略 database-strategy: ## 标准分片 standard: sharding-column: birthday precise-algorithm-class-name: com.xuexiangban.sharding.conf.BirthdayPreciseShardingAlgorithm ## 分表策略 table-strategy: inline: sharding-column: sex algorithm-expression: user_order$->{sex % 2}
配置雪花算法主键注意点：
1、long类型
2、主键不能自增
分片方式:
（1）inline : 行表达式分片
复制代码
1
2
3
4inline: # 行表达式分片 sharding-column: test_id # 分片列名称 algorithm-expression: master_test_${test_id % 2} # 分片表达式,根据指定的表达式计算得到需要路由
（2）standard : 标准分片
复制代码
1
2
3
4
5standard: # 单列sharidng算法,需要配合对应的preciseShardingAlgorithm,rangeShardingAlgorithm接口的实现使用,目前无生产可用实现 shardingColumn: # 列名,允许单列 preciseShardingAlgorithm: # preciseShardingAlgorithm接口的实现类 rangeShardingAlgorithm: # rangeShardingAlgorithm接口的实现类
（3）hint : 标记分片**(不推荐)**
复制代码
1
2
3hint: #基于标记的sharding分片 shardingAlgorithm: # 需要是HintShardingAlgorithm接口的实现,目前代码中,仅有为测试目的实现的OrderDatabaseHintShardingAlgorithm,没有生产环境可用的实现
（4）complex : 多列分片**(不推荐)**
复制代码
1
2
3
4complex: # 支持多列的shariding,目前无生产可用实现 shardingColumns: # 逗号切割的列 shardingAlgorithm: # ComplexKeysShardingAlgorithm接口的实现类
官网链接：
https://shardingsphere.apache.org/document/legacy/3.x/document/cn/manual/sharding-jdbc/configuration/config-yaml/
3、事务配置
（1）添加Maven依赖
复制代码
1
2
3
4
5
6
7## sharding-transaction-spring-boot-starter <dependency> <groupId>io.shardingsphere</groupId> <artifactId>sharding-transaction-spring-boot-starter</artifactId> <version>${sharding-sphere.version}</version> </dependency>
（2）业务逻辑通过注解方式开启XA事务
复制代码
1
2
3
4@ShardingTransactionType(TransactionType.XA) @Transactional public void demo(){}
最后
以上就是幸福人生最近收集整理的关于采用sharding-jdbc分库分表的全部内容，更多相关采用sharding-jdbc分库分表内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供，作为学习参考使用，或来自网络收集整理，版权属于原作者所有。
发表评论 取消回复