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

601.373336

已于 2024-03-17 15:04:57 修改

阅读量1.2k

点赞数 6

于 2024-03-06 15:06:07 首次发布

本文链接：https://blog.csdn.net/q2972112/article/details/136504260

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

启用 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

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

下载相关deb包和配置文件

从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
从[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
从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

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

sudo apt update 
sudo apt upgrade
sudo apt-get install mysql-client mysql-server

最好在导入数据集之前检查数据库编码格式。

#检查数据库编码格式
SHOW VARIABLES LIKE 'character_set_%';

#如果要修改数据库编码格式，修改/etc/mysql/my.cnf
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[client]
default-character-set=utf8

[mysqld] 
character-set-server=utf8

[mysql]
default-character-set=utf8

#重启MySQL
sudo systemctl restart mysql

配置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

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

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

#或者从头创建
mysql -u root -p123465
CREATE DATABASE mysql_ssb_1;

导入SSB数据集

Error: buffer overflow detected

错误描述：
如果接下来在执行./dbgen -s 1 -T l时遇到错误：*** buffer overflow detected ***
解决方法：
参考https://github.com/electrum/ssb-dbgen/issues/6
修改shared.h中第120行的#define MAXAGG_LEN 10为#define MAXAGG_LEN 32，再重新make即可。

具体步骤

克隆ssb数据生成器 git clone https://github.com/electrum/ssb-dbgen.git
修改makefile文件，修改内容为：MACHINE =LINUX
在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表的数据

创建数据库

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 ""
);

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

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

show variables like "%secure%";

sudo cp customer.tbl /var/lib/mysql-files/customer.tbl
sudo cp part.tbl /var/lib/mysql-files/part.tbl
sudo cp supplier.tbl /var/lib/mysql-files/supplier.tbl
sudo cp date.tbl /var/lib/mysql-files/date.tbl
sudo cp lineorder.tbl /var/lib/mysql-files/lineorder.tbl

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 '|\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 '/var/lib/mysql-files/customer.tbl' into table customer fields terminated by '|' lines terminated by '|\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 '/var/lib/mysql-files/part.tbl' into table part fields terminated by '|' lines terminated by '|\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';
load data infile '/var/lib/mysql-files/date.tbl' into table dwdate fields terminated by '|' lines terminated by '|\n';