WSL2-在Ubuntu-22.04上安装MySQL（deb包）并配置ODBC

启用 systemd

通过链接启用 systemdhttps://learn.microsoft.com/zh-cn/windows/wsl/systemd#how-to-enable-systemd

sudo nano /etc/wsl.conf
#在文件中添加如下内容：
[boot]
systemd=true
#添加后重启WSL
wsl.exe --shutdown 
1
2
3
4
5
6

否则会出现如下错误：ERROR 2002 (HY000): Can‘t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’

下载相关deb包和配置文件

1. 从https://dev.mysql.com/downloads/connector/odbc/下载
   mysql-server_8.3.0-1ubuntu22.04_amd64.deb-bundle.tar和 mysql-connector-odbc_8.3.0-1ubuntu22.04_amd64.deb
2. 从[http://cz.archive.ubuntu.com/ubuntu/- http://cz.archive.ubuntu.com/ubuntu/pool/main/liba/libaio/](http://cz.archive.ubuntu.com/ubuntu/- http://cz.archive.ubuntu.com/ubuntu/pool/main/liba/libaio/)下载 libaio1_0.3.113-5_amd64.deb
3. 从http://cz.archive.ubuntu.com/ubuntu/pool/main/m/mecab/下载 libmecab2_0.996-14build9_amd64.deb

按顺序安装deb包

#解压下载的deb捆绑包
tar -xf mysql-server_8.3.0-1ubuntu22.04_amd64.deb-bundle.tar
#然后把所有的包在Ubuntu中都用sudo dpkg -i命令安装
mysql-community-client-plugins_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-client-core_8.3.0-1ubuntu22.04_amd64.deb
mysql-common_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-client_8.3.0-1ubuntu22.04_amd64.deb
libmysqlclient23_8.3.0-1ubuntu22.04_amd64.deb
libmysqlclient-dev_8.3.0-1ubuntu22.04_amd64.deb
mysql-client_8.3.0-1ubuntu22.04_amd64.deb
libaio1_0.3.113-5_amd64.deb
libmecab2_0.996-14build9_amd64.deb
mysql-community-server-core_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-server_8.3.0-1ubuntu22.04_amd64.deb
mysql-server_8.3.0-1ubuntu22.04_amd64.deb
libmysqlclient-dev_8.3.0-1ubuntu22.04_amd64.deb
libmysqlclient23_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-server-debug_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-test_8.3.0-1ubuntu22.04_amd64.deb
mysql-community-test-debug_8.3.0-1ubuntu22.04_amd64.deb
mysql-testsuite_8.3.0-1ubuntu22.04_amd64.deb
mysql-connector-odbc_8.3.0-1ubuntu22.04_amd64.deb

#测试是否安装成功
mysql -h localhost -u root -p
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

在Ubuntu22.04虚拟机上使用如下命令，也可以看到安装过程是按顺序安装deb包。

sudo apt update 
sudo apt upgrade
sudo apt-get install mysql-client mysql-server
1
2
3

配置ODBC

从https://downloads.mysql.com/archives/c-odbc/下载四个包并安装。

#如果直接使用sudo apt-get install mysql-client mysql-server安装MySQL，则需要在安装ODBC之前安装mysql-community-client-plugins
sudo dpkg -i mysql-community-client-plugins_8.3.0-1ubuntu22.04_amd64.deb

sudo dpkg -i mysql-connector-odbc_8.2.0-1ubuntu22.04_amd64.deb
sudo dpkg -i mysql-connector-odbc-setup_8.2.0-1ubuntu22.04_amd64.deb
sudo dpkg -i mysql-connector-odbc-dbgsym_8.2.0-1ubuntu22.04_amd64.deb
sudo dpkg -i mysql-connector-odbc-setup-dbgsym_8.2.0-1ubuntu22.04_amd64.deb
sudo apt-get install unixodbc unixodbc-dev

cat /etc/odbcinst.ini
#以下为文件odbcinst.ini的内容（自动生成）
[MySQL ODBC 8.3 Unicode Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
UsageCount=1

[MySQL ODBC 8.3 ANSI Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
UsageCount=1

[MySQL ODBC 8.2 Unicode Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8w.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1

[MySQL ODBC 8.2 ANSI Driver]
DRIVER=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8a.so
SETUP=/usr/lib/x86_64-linux-gnu/odbc/libmyodbc8S.so
UsageCount=1
#以上为文件odbcinst.ini的内容

sudo nano /etc/odbc.ini
#以下为文件odbc.ini的内容
[mysql_ssb_1]
Description     = MySQL-mysql_ssb_1 #随意
Driver          = MySQL ODBC 8.3 Unicode Driver #必须是这个
Server          = localhost 
Host            = localhost
Database        = mysql_ssb_1 #待连接数据库名
Port            = 3306
User            = root  
Password        = 123465 #数据库登录密码
#以上为文件odbc.ini的内容

#测试链接
sudo isql -v mysql_ssb_1
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

当然，前提是MySQL中要有mysql_ssb_1这个数据库，如果没有请创建：

#直接导入.sql文件
mysql -u root -p mysql_ssb_1 < Dump20240306.sql

#或者从头创建
mysql -u root -p123465
CREATE DATABASE mysql_ssb_1;
1
2
3
4
5
6

导入SSB数据集

1. 克隆ssb数据生成器 git clone https://github.com/electrum/ssb-dbgen.git
2. 修改makefile文件，修改内容为：MACHINE =LINUX
3. 在ssb-dbgen目录下，先make，生成dbgen可执行文件。

#-T指定表，-s指定扩展因子
cd ssb-dbgen
./dbgen -s 1 -T c		//生成customer表的数据
./dbgen -s 1 -T p		//生成part表的数据
./dbgen -s 1 -T s    	//生成supplier表的数据
./dbgen -s 1 -T d		//生成date表的数据
./dbgen -s 1 -T l		//生成lineorder表的数据
1
2
3
4
5
6
7

执行./dbgen -s 1 -T l时遇到错误：*** buffer overflow detected ***: terminated Aborted (core dumped)
参考https://github.com/electrum/ssb-dbgen/issues/6

4. 创建数据库

create database mysql_ssb_1；
use mysql_ssb_1;
create table customer(
	c_custkey     integer,
	c_name        varchar(25) not null,
	c_address     varchar(40) not null,
	c_city        varchar(10) not null,
	c_nation      varchar(15) not null,
	c_region      varchar(12) not null,
	c_phone       varchar(15) not null,
	c_mktsegment  varchar(10) not null);

create table dwdate(
	d_datekey          integer,
	d_date             varchar(18) not null,
	d_dayofweek        varchar(18) not null,
	d_month            varchar(9) not null,
	d_year             integer not null,
	d_yearmonthnum     integer,
	d_yearmonth        varchar(7) not null,
	d_daynuminweek     integer,
	d_daynuminmonth    integer,
	d_daynuminyear     integer,
	d_monthnuminyear   integer,
	d_weeknuminyear    integer,
	d_sellingseason    varchar(12) not null,
	d_lastdayinweekfl  integer,
	d_lastdayinmonthfl integer,
	d_holidayfl        integer,
	d_weekdayfl        integer);

create table supplier(
	s_suppkey     integer,
	s_name        varchar(25) not null,
	s_address     varchar(25) not null,
	s_city        varchar(10) not null,
	s_nation      varchar(15) not null,
	s_region      varchar(12) not null,
	s_phone       varchar(15) not null);

create table part(
	p_partkey     integer,
	p_name        varchar(22) not null,
	p_mfgr        varchar(6) not null,
	p_category    varchar(7) not null,
	p_brand       varchar(9) not null,
	p_color       varchar(11) not null,
	p_type        varchar(25) not null,
	p_size        integer not null,
	p_container   varchar(10) not null);

create table lineorder(
	lo_orderkey       bigint,
	lo_linenumber     bigint,
	lo_custkey        integer not null,
	lo_partkey        integer not null,
	lo_suppkey        integer not null,
	lo_orderdate      integer not null,
	lo_orderpriotity  varchar(15) not null,
	lo_shippriotity   integer,
	lo_quantity       bigint,
	lo_extendedprice  bigint,
	lo_ordtotalprice  bigint,
	lo_discount       bigint,
	lo_revenue        bigint,
	lo_supplycost     bigint,
	lo_tax            bigint,
	lo_commitdate     integer not null,
	lo_shipmode       varchar(10) not null);

create table if not exists `lineorder_flat` (
	`lo_orderkey` int(11) not null comment "",
	`lo_orderdate` date not null comment "",
	`lo_linenumber` tinyint(4) not null comment "",
	`lo_custkey` int(11) not null comment "",
	`lo_partkey` int(11) not null comment "",
	`lo_suppkey` int(11) not null comment "",
	`lo_orderpriotity` varchar(100) not null comment "",
	`lo_shippriotity` tinyint(4) not null comment "",
	`lo_quantity` tinyint(4) not null comment "",
	`lo_extendedprice` int(11) not null comment "",
	`lo_ordtotalprice` int(11) not null comment "",
	`lo_discount` tinyint(4) not null comment "",
	`lo_revenue` int(11) not null comment "",
	`lo_supplycost` int(11) not null comment "",
	`lo_tax` tinyint(4) not null comment "",
	`lo_commitdate` date not null comment "",
	`lo_shipmode` varchar(100) not null comment "",
	`c_name` varchar(100) not null comment "",
	`c_address` varchar(100) not null comment "",
	`c_city` varchar(100) not null comment "",
	`c_nation` varchar(100) not null comment "",
	`c_region` varchar(100) not null comment "",
	`c_phone` varchar(100) not null comment "",
	`c_mktsegment` varchar(100) not null comment "",
	`s_name` varchar(100) not null comment "",
	`s_address` varchar(100) not null comment "",
	`s_city` varchar(100) not null comment "",
	`s_nation` varchar(100) not null comment "",
	`s_region` varchar(100) not null comment "",
	`s_phone` varchar(100) not null comment "",
	`p_name` varchar(100) not null comment "",
	`p_mfgr` varchar(100) not null comment "",
	`p_category` varchar(100) not null comment "",
	`p_brand` varchar(100) not null comment "",
	`p_color` varchar(100) not null comment "",
	`p_type` varchar(100) not null comment "",
	`p_size` tinyint(4) not null comment "",
	`p_container` varchar(100) not null comment "");
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

5. 将SSB生成的.tbl文件load到MySQL中

！注意：.tbl文件必须放置在MySQL的安全目录下，否则会出现错误，用以下语句查看安全目录：

show variables like "%secure%";
1

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/lineorder.tbl' into table lineorder fields terminated by '|' lines terminated by '|\r\n';
load data infile '/var/lib/mysql-files/lineorder.tbl' into table lineorder fields terminated by '|' lines terminated by '|\r\n';

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/supplier.tbl' into table supplier fields terminated by '|' lines terminated by '|\r\n';
load data infile '/var/lib/mysql-files/supplier.tbl' into table supplier fields terminated by '|' lines terminated by '|\n';

load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\r\n';
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/part.tbl' into table part fields terminated by '|' lines terminated by '|\r\n';
load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/date.tbl' into table dwdate fields terminated by '|' lines terminated by '|\r\n';
1
2
3
4
5
6
7
8
9