分类 数据库相关 下的文章

最近完成了一个小项目的数据库迁移,从微软SQL Server 2016迁移到MySQL 8。过程没什么复杂,只是需要注意一下数据类型和SQL语法的转换。

1 环境

原数据库是SQL Server 2016。迁移的目标环境,操作系统为Debian 11,安装了MySQL 8。

2 还原SQL Server数据库备份

拿到手的是SQL Server数据库备份,需要还原出来再迁移。幸好微软推出了SQL Server的Linux版,而且官方提供了可用于开发测试的Docker镜像,几个步骤就部署并还原好SQL Server数据库。

参考资料:

1)在Debian上安装Docker的官方教程:
Install Docker Engine on Debian
https://docs.docker.com/engine/install/debian/

2)运行SQL Server 2019 Docker镜像的官方教程:
Quickstart: Run SQL Server container images with Docker
https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker?view=sql-server-ver15&pivots=cs1-bash

3)SQL Server 2019的微软官方Docker镜像:
dockerhub - Microsoft SQL Server
https://hub.docker.com/_/microsoft-mssql-server

3 MySQL的准备

由于SQL Server的数据库表名不区分大小写,MySQL为了兼容相关SQL语句,也需要设置表名不区分大小写。即设置MySQL的参数lower_case_table_names=1,MySQL在存储和查询时,都把表名转为小写后再执行处理。

这里最麻烦的是,如果MySQL原来设置了lower_case_table_names=0(一般Linux上安装MySQL的默认值),需要把data文件夹清空,更新设置后重新初始化MySQL的数据。如果直接更改该值,MySQL重启后会报错。

关键的操作步骤:

1)修改MySQL的配置文件(Debian的默认路径为:/etc/mysql/mysql.conf.d/mysql.cnf),在[mysqld]节点下,加入一行lower_case_table_names=1

2)重新初始化MySQL(已有数据库的话,先做好备份,初始化后再还原),先清空数据文件夹(Debian的默认路径:/var/lib/mysql),然后执行以下命令:

mysqld --user=root --initialize --lower-case-table-names=1

初始化成功后,root用户的密码会记录在/var/log/mysql/error.log

4 迁移数据库定义

即导出原数据库表的create语句。一般推荐使用MySQL Workbench的Migration功能,官方文档如下:

MySQL Workbench - Using the MySQL Workbench Migration Wizard
https://dev.mysql.com/doc/workbench/en/wb-migration-wizard.html

但是我所安装的MySQL Workbench不能连接到Docker部署的SQL Server,所以使用了已安装的HeidiSQL,导出原数据库表的create table语句,然后手工修正为MySQL的语法。一些修改操作如下:

  • 修正字符编码,特别是设置了COLLATE的,需求改为COLLATE utf8mb4_0900_ai_ci
  • 修正默认值设置,例如DEFAULT '(0)'改为DEFAULT '0'DEFAULT getDate()改为DEFAULT CURRENT_TIMESTAMP
  • 自增型字段会被忽略,需要加上AUTO_INCREMENT
  • 字段类型转换,例如NVARCHAR改为VARCHARBIT改为TININY(1)MONEY改为DECIMAL(19,4)
  • 需要补上索引设置。

5 迁移数据库的数据

即导出所有数据的insert语句,然后在目标数据库利用source命令进行导入。一般也是推荐使用MySQL Workbench操作,不用担心语法和数据类型的问题。

我使用了DBeaver导出所有表的insert语句,然后手工修正为MySQL语法。需要注意:

  • 一般一条insert语句包含10000行数据,已提高导入时的效率。
  • 所有表名以数据库名.dbo开头的,都改为以数据库名开头。
  • 列名以中括号“[]”括住的,要改为“`”。

6 修改程序的SQL语句

主要是把SQL Server的语法,改为MySQL的语法。总结如下:

  • TOP改为LIMIT
  • getDate()改为CURRENT_TIMESTAMP
  • 去掉表名前的dbo.
  • WITH(NOLOCK)的处理。SQL Server加了WITH(NOLOCK)的语句,如果MySQL的InnoDB设置innodb_autoinc_lock_mode=0,需要特殊处理该语句,否则直接去掉WITH(NOLOCK)。关于InnoDB的设置说明如下:

MySQL innodb_autoinc_lock_mode 详解
https://www.cnblogs.com/JiangLe/p/6362770.html

最近接触了两款开源、跨平台、支持多种SQL数据库的数据库管理工具,值得记录一下。

DBeaver
官网:https://dbeaver.io/
2020年疫情期间,在家办公,想找个数据库管理工具,可以在Linux上访问SQL Server数据库,于是遇到DBeaver。界面像Eclipse,容易上手;基于Java,可以跨平台使用;使用JDBC,几乎支持所有数据库。在Linux上,几乎是万能的数据库管理工具了。

HeidiSQL
官网:https://www.heidisql.com/
在Windows上安装MariaDB 10.4.12时,发现自带了HeidiSQL数据库管理工具。界面及操作都跟MySQL Workbench相似,清晰明了,而且支持各种SQL数据。比较意外的是,其基于Delphi开发,所以Linux上需要利用Wine运行。

新的工作需要用到MySQL。Leader让我复制一个数据库用来测试。以前用SQL Server都是直接图形化操作,从A数据库导出到B数据库就可以。Oracle就用expdp和impdp命令进行导出导入。MySQL的话,都是导出SQL脚本,再新建个数据库来执行。

网上找个这个教程:MySQL快速复制数据库的方法 http://www.tudaxia.com/archives/357

教程中只需两部:
1)新建数据库(shell命令)

# mysql -uroot -ppassword
mysql> CREATE DATABASE `newdb` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI;

2)复制数据。通过管道的方式把两条命令合并为一条。(shell命令)

# mysqldump db1 -uroot -ppassword --add-drop-table | mysql newdb -uroot -ppassword

当数据很大的时候,建议还是老老实实地先把源数据库的数据备份成文件,再导入新的数据。(shell命令)

# mysqldump db1 -uroot -ppassword --add-drop-table --default-character-set=utf8 > /home/db_export/db1.sql
# mysql newdb -uroot -ppassword < /home/db_export/db1.sql

用Oracle导出导入数据,以前用exp和imp总是报字符集的错误。后来查了下,Oracle 10g新增了expdp(导出)和impdp(导入)两个工具,而它们会自动根据数据库的配置来设置导出文件的字符集,就是可以无视客户端的字符集设置了。但是有个缺点,导出文件只能放在服务器上。这个问题不大,只要设好目录及相关权限就可以了。

以下例子,假设把USER_A的数据库,复制成USER_B的数据库。

首先,看一下已定义好的目录。如果有需要就新增一个。新增前要授权用户拥有“create any directory”的权限。

--查看已定义的文件夹
select * from dba_directories;

--查看用户“USER_A”的权限
select * from dba_sys_privs where grantee='USER_A';

--授创建文件夹的权限
grant create any directory to USER_A;

--创建目录
create or replace directory NEW_DIR as '/tmp';

进行导入导出之前,要授权相关用于拥有读写文件夹的权限。USER_A要导出数据,那他应该有“写”文件夹的权限;USER_B要导入数据,他要有“读”文件夹的权限。嫌麻烦的话,可以同时赋予文件夹的读写权限。

--授权用户“USER_A”拥有文件夹“NEW_DIR”的读写权限
grant read,write on directory NEW_DIR to USER_A;

--授权用户“USER_A”只有文件夹“NEW_DIR”的写权限
grant write on directory NEW_DIR to USER_A;

--授权用户“USER_B”只有文件夹“NEW_DIR”的读权限
grant read on directory NEW_DIR to USER_B;

导出导入的相关参数,查一下就知道了,这里只是操作该用户的所有对象。其中impdp的参数中,remap_schema是把源用户转换成目标用户(格式是 源用户:目标用户),remap_tablespace是把源表空间转换为目标表空间(格式是 源表空间:目标表空间)。以下命令需要在服务器的命令窗口执行。

--导出备份文件,USER_A的数据库
expdp USER_A/PASSWORD@sid schemas=USER_A directory=NEW_DIR dumpfile=user_a_backup.dmp

--导入备份文件,把USER_A的数据库导入到USER_B的数据库
impdp USER_B/PASSWORD@sid remap_schema=USER_A:USER_B remap_tablespace=USER_A_TS:USER_B_TS directory=NEW_DIR dumpfile=user_a_backup.dmp

参考资料:
exp/imp 与 expdp/impdp 对比 及使用中的一些优化事项
http://blog.csdn.net/tianlesoftware/article/details/6093973

ORACLE expdp/impdp详解(原创)
http://czmmiao.iteye.com/blog/2041703

oracle 查看 用户,用户权限,用户表空间,用户默认表空间
http://it4j.iteye.com/blog/2002433