标签 MySQL 下的文章

最近使用了MySQL 8的主从复制，利用数据同步，实现数据备份。

1. 概述

关于MySQL主从复制的原理和各种部署方式，参考：看完这篇还不懂 MySQL 主从复制，可以回家躺平了～。本文采用“一主一从”方式部署。

参考文章：

官方文档：

注意：从MySQL 8.0.22开始，大量涉及“SLAVE”的配置和命令，都改为“REPLICA”字样，详见官网说明。

2. 主服务器配置

主服务器是安装在Debian 12的MySQL 8，假设其IP地址为192.168.0.100。修改MySQL的配置文件/etc/mysql/conf.d/mysqld.cnf，在[mysqld]下添加以下配置。保存配置文件后重启MySQL服务。

[mysqld]
# 主从复制，设为主服务器
# 服务器ID
server-id = 1
# 开启二进制日志。事务提交时写日志到对应文件
log-bin = mysql-bin
# 日志过期删除的天数，延迟严重的话会导致日志文件占用磁盘
expire_logs_days = 14

利用mysql命令，连上MySQL主服务器，并执行以下语句。注意： MySQL 8 默认身份验证插件是caching_sha2_password，详见变量default_authentication_plugin的配置。

-- 创建用户，提供给从服务器访问
CREATE USER 'repl'@'%' IDENTIFIED WITH caching_sha2_password BY 'password';
-- 授权用户拥有所有数据库表的同步数据权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

再执行以下语句，显示主服务器的配置，记下File和Position的值。

SHOW MASTER STATUS\G;

3. 从服务器配置

从服务是安装在Debian 12的MySQL 8，假设其IP地址为192.168.0.101。修改MySQL配置文件/etc/mysql/conf.d/mysqld.cnf，在[mysqld]下添加以下配置。保存配置文件后重启MySQL服务。

# 主从复制，设为从服务器
# 服务器ID
server-id = 2
# 同步的数据库。多个数据库需配置多行
#replicate-do-db = db1
#replicate-do-db = db2
# 服务重启后自动开始同步数据，默认OFF
#skip-replica-start = false

利用mysql命令，连上MySQL从服务器，并执行以下语句。

  • 由于主服务器的身份验证插件采用caching_sha2_password，从服务器需要配置GET_SOURCE_PUBLIC_KEY=1才能通过身份验证。
  • SOURCE_LOG_FILE对应主服务器的File的值。
  • SOURCE_LOG_POS对应主服务器的Position的值。
-- 从服务器配置
STOP REPLICA;

CHANGE REPLICATION SOURCE TO SOURCE_HOST='192.168.0.100'
    ,SOURCE_PORT=3306
    ,SOURCE_USER='repl'
    ,SOURCE_PASSWORD='password'
    ,SOURCE_LOG_FILE='mysql-bin.000001'
    ,SOURCE_LOG_POS=123
    ,GET_SOURCE_PUBLIC_KEY=1
    ;

START REPLICA;

执行以下语句，查看从服务器的状态，检查从服务器是否正常运行。如果Replica_IO_Running和Replica_SQL_Running都显示Yes，则表示正常运行。

SHOW REPLICA STATUS\G;

1. 概述

由于历史原因，工作上，需要把Microsoft SQL Server指定数据库上定时更新的数据，同步到MySQL 8，大概一周一次。

解决方案有几个：

  • 1）采用现成的工具。

    • 但是，一时间没找到好的工具。

  • 2） 开发个程序，读取SQL Server的相关数据，再插入到MySQL。

    • 需要时间开发，且功能上具有针对性。
    • 作为长期使用的工具，这是最优的方案。

  • 3） 从SQL Server生成MySQL的insert语句，再到MySQL上执行。

    • 很多数据库管理工具都提供了数据迁移功能，例如：MySQL Workbench。
    • 如果数据结构不变，insert语句是稳定的，这方案也不错。
    • 要注意采用批量插入，提高导入性能。

  • 4） 从SQL Server导出格式化数据，例如CSV文件，再导入到MySQL。

    • 由于保存数据的CSV文件比较通用，相关的数据库管理工具都支持导入导出。
    • 但是要注意要处理数据格式、NULL数据等问题。

目前采用了第3个方案，CSV文件比较通用，也不用考虑怎么开发。但是偶尔会出现导入MySQL失败的问题，比如出现了NULL数据。后面应该会写个程序处理，直接生成insert语句。

2. SQL Server导入导出CSV

一般使用BCP命令。即Bulk Copy Program，是一个命令行工具，用于在SQL Server之间批量传输数据。由于微软推出了SQL Server for Linux，所以可以完全在Linux执行导入导出的操作。另外，微软提供了SQL Server for Linux的官方Docker镜像，比Windows上安装SQL Server Express更方便，非常适合开发测试使用（主要应付历史）。

官方介绍及参考文档如下：

2.1. BCP使用说明

直接运行bcp命令，会提示其用，如下：

> C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe

用法: bcp {dbtable | query} {in | out | queryout | format} 数据文件
  [-m 最大错误数]             [-f 格式化文件]       [-e 错误文件]
  [-F 首行]                   [-L 末行]             [-b 批大小]
  [-n 本机类型]               [-c 字符类型]         [-w 宽字符类型]
  [-N 将非文本保持为本机类型] [-V 文件格式版本]     [-q 带引号的标识符]
  [-C 代码页说明符]           [-t 字段终止符]       [-r 行终止符]
  [-i 输入文件]               [-o 输出文件]         [-a 数据包大小]
  [-S 服务器名称]             [-U 用户名]           [-P 密码]
  [-T 可信连接]               [-v 版本]             [-R 允许使用区域设置]
  [-k 保留 Null 值]           [-E 保留标识值]
  [-h"加载提示"]              [-x 生成 xml 格式化文件]
  [-d 数据库名称]

2.2. BCP导出CSV文件

CMD批处理命令参考如下：

set CUR_PATH=%~dp0
set BCP="C:\Program Files (x86)\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe"
set BCP_PARAM=-S "IP,端口" -U "用户名" -P "密码" -d "数据库名" -t \t -b 1000 -c -C 65001 -k

rem 导出指定数据库表的数据
%BCP% 数据库名.dbo.表名 out %CUR_PATH%table_export.csv %BCP_PARAM%

rem 导出指定查询语句的数据
%BCP% "select查询语句" queryout %CUR_PATH%query_export.csv %BCP_PARAM%

参数说明：

  • -S 服务器名称，服务器IP与端口之间，使用英文逗号（即“,”）分隔。
  • -t 字段终止符，默认是Tab符号（即“/t”）。
  • -b 批大小，如果导出数据太多，需要分页操作，默认是1000。
  • -c 字符类型，设置导出文件的字符编码为UTF-8时，设置为“-c -C 65001”，要注意大小写。

要注意，导出的CSV文件不带字段名称。需要记录字段名称时，目前只能把字段名称插入到CSV文件的第一行，并且以数据行的分隔符号进行分隔。

2.3. BCP导入CSV文件

要先创建对应的表，才能执行导入。格式如下：

set BCP_PARAM=-S "IP,端口" -U "用户名" -P "密码" -d "数据库名" -t \t -b 1000 -c -C 65001 -k
bcp 数据库名.dbo.表名 in 数据文件.csv %BCP_PARAM%

2.4. SQL语句执行BCP命令

要注意，用户需要授权可执行xp_cmdshell的权限。官方详细说明如下：xp_cmdshell (Transact-SQL) - SQL Server | Microsoft Learn

格式如下：

exec master..xp_cmdshell 'bcp ...'

2.5. SQL Server的其它导入导出方案

3. MySQL导入导出CSV

3.1. 相关参考

参考文章：MySQL导出数据为CSV的方法

MySQL官方文档：

3.2. MySQL导入CSV

假如导入数据到数据表user_table，该表有字段id、name、remark，其中remark数据可能为NULL。

-- 导入CSV文件的SQL语句
load data infile '/var/lib/mysql-files/import_data.csv' into table user_table fields terminated by '\t' escaped by '' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines (id,name,@remark) set remark=nullif(@remark,'');

说明：

  • CSV文件需要放在mysql用户有权限的目录，比如/var/lib/mysql-files/。
  • fields terminated by '\t'，表示CSV数据以TAB符号分隔。
  • escaped by ''，设置转义字符，默认的是反斜杠（backslash：\ ），设置空值（''）表示不适用转义。
  • optionally enclosed by '"'，以双引号包裹单一字段的数据。
  • lines terminated by '\n'，每行数据的结束符号。
  • ignore 1 lines，导入数据时跳过第一行，因为第一行是字段名称的说明。
  • (id,name,@remark)，把一行数据关联到对应的字段。其中@remark是把数据赋值到变量，后面有特殊处理。
  • remark=nullif(@remark,'')，表示remark字段的数据，根据@remark变量进行处理。这里是NULL数据转为空字符串。

3.3. MySQL导出CSV

-- 导出CSV文件的SQL语句
select * from user_table into outfile '/tmp/expor_data.csv' fields terminated by '\t' escaped by '\\'  optionally enclosed by '"' lines terminated by '\n' ;

说明：

  • fields terminated by "\t"，表示CSV数据以TAB符号分隔。
  • escaped by '\\'，设置转义字符，默认的是反斜杠（backslash：\ ），设置空值（''）表示不适用转义。
  • optionally enclosed by '"'，以双引号包裹单一字段的数据。
  • lines terminated by '\n'，每行数据的结束符号。

4. SQL语言的concat_ws函数

SQL的select语句可以使用concat_ws函数，可以实现一行数据的所有字段值合并成一个字符串，并指定分隔符号。然后把查询结果保存为文本文件（包括CSV），即实现了数据导出。参考文档如下：

近来遇到关于库存与并发的问题。由于一直接触的系统都是没有考虑过商品库存的并发，加上解决过的并发问题，也只是简单直接地采用锁表的方式。所以导致踩坑。

1 问题1，商品基础数据与库存数量，设计在同一个表。

商品基础数据，包括库存数量，主要用于查询。但库存数量，还要解决经常变化，且可能出现并发的情况。如果简单使用锁，即使只锁一行数据，也会导致正在进行下单（涉及扣减库存）的商品不能被浏览（因为锁住，不能查询）。

为了减轻这个情况，下单时，检查库存数量是否足够购买时，不锁数据，等到保存订单数据，真正扣减库存时才加锁。本想着通过减少锁数据的时间，减少商品数据不能查询的情况。但是系统采用Java开发，使用了Spring + Hibernate框架。而Hibernate在事务内使用了一级缓存，即事务内未提交时，查询到的业务数据都放到一级缓存。事务内查询时，会先查询一级缓存，若命中，则不再查询数据库。就导致了检查库存时已获取了商品数据，扣减库存时（从一级缓存获取）不能获取到最新库存（特别是两个客户同时下单同一个商品的情况），最后在并发情况下扣减库存，就出现库存扣少1了的问题。

解决方案很简单，把商品基础数据与库存数据分开两个表存放。库存数据在扣减时，不影响商品浏览。

2 问题2，库存数量，需减少锁定时间。

由于客户浏览商品，或者添加商品到购物车，都需要查询库存数据。如果使用悲观锁，即锁表或锁数据后不能查询，会导致客户不能浏览。参考了以下文章，决定使用乐观锁，即不使用数据库锁。

目前系统规模比较小，且没有涉及分布式，于是决定在扣减库存时直接更新数据的方式。即使用update语句扣减库存时，用where条件判断是否足够扣减，并返回是否扣减成功。

由于使用MySQL，update语句不能返回指定数据（但是，sql server可以使用update...output，PostgreSQL可用update...returning）。加上Hibernate不能同时执行update和select两个语句，最后采用存储过程。参考以下网址：

3 解决方案

总的来说，使用乐观锁（即没有使用数据库的锁），并利用MySQL存储过程实现扣减库存后返回结果。

1）库存表

create table `product_stock` (
    `productId` bigint not null comment '商品ID',
    `instock` int not null default '0' comment '库存数量',
    `createTime` datetime(3) default null comment '创建时间',
    `updateTime` datetime(3) default null comment '更新时间',
    primary key ( productId )
) engine=InnoDB default charset=utf8mb4 collate=utf8mb4_0900_ai_ci comment='商品库存';

2）扣减库存的存储过程

利用存储过程的out参数，返回扣减结果。当outUpdateQty返回的值大于零，扣减成功，否则失败。扣减成功，outStockAfter的值才是正确。

delimiter //
create procedure `product_reduce_instock`(
    in inProductId bigint, /*传入参数：商品ID*/
    in inReduceQty int, /*传入参数：扣减数量*/
    out outUpdateQty int, /*传出参数：实际扣减数量*/
    out outStockAfter int /*传出参数：更新后库存数量*/
)
begin
    -- 初始化返回的值
    set @updateQty=0;
    set @stockAfter=0;
    
    -- 执行扣减库存
    update product_stock 
    set instock = (@stockAfter := instock - (@updateQty := inReduceQty)), updateTime = now() 
    where productId = inProductId and instock >= inReduceQty;
    
    -- 传出参数赋值，即返回扣减结果
    set outUpdateStock=@updateQty;
    set outStockAfter=@updateQty;
end //

最近完成了一个小项目的数据库迁移，从微软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改为VARCHAR，BIT改为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

新的工作需要用到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

在128MB内存的VPS上折腾了一下MySQL，把过程总结一下记录下来。

1）配置文件
备份原配置文件，把官方配置样例中最低配置的配置文件复制过来，就可以了。命令如下：

mv /etc/mysql/my.cnf /etc/mysql/my.cnf.bak
cp /usr/share/doc/mysql-server-5.5/examples/my-small.cnf /etc/mysql/my.cnf

2）禁用InnoDB
禁用InnoDB，为了节省内存。

详细参考：
小内存VPS上MySQL5.5内存优化——禁用Innodb引擎
http://hxs.fd.fj.cn/?action=show&id=48

简单来说，就是修改my.cnf，在[mysqld]下增加以下配置：

default-storage-engine = MyISAM
loose-skip-innodb
innodb=OFF

文中还提到MySQL 5.5，还需要添加以下配置，否则程序会很容易退出的：

loose-innodb-trx = 0
loose-innodb-locks = 0
loose-innodb-lock-waits = 0
loose-innodb-cmp = 0
loose-innodb-cmp-per-index = 0
loose-innodb-cmp-per-index-reset = 0
loose-innodb-cmp-reset = 0
loose-innodb-cmpmem = 0
loose-innodb-cmpmem-reset = 0
loose-innodb-buffer-page = 0
loose-innodb-buffer-page-lru = 0
loose-innodb-buffer-pool-stats = 0

3）设置默认字符集UTF-8
登录MySQL后，用以下命令查看字符集设置：

show variables like ‘character%’;

默认字符集一般是latin1，最简单是修改my.cnf文件
[client]增加：

default-character-set = utf8

[mysqld]增加：

character-set-server = utf8

[mysql]增加：

default-character-set = utf8

4）重启MySQL服务

PS. MySQL的错误日志在 /var/lib/mysql/*.err

由于现在的公司有多个老旧系统，需要从一个系统访问到另一个系统的数据。一看到这个，就想到了SOA，但是几个小系统部署个SOA来整合，值得么？重点是没搞过SOA！于是怎么简单就怎么解决，例如实现跨数据库访问，即从SQL Server访问MySQL，程序层就从SQL Server读取数据。

Google了一下，找到一篇有用的教程：SqlServer2005 链接服务器用法（http://www.jb51.net/article/18484.htm）。文中提到用sp_addlinkedserver这个存储过程来添加链接服务器(要先配置好MySQL的ODBC数据源)，然后通过以下语句获取数据：

SELECT * FROM OPENQUERY (链接服务器名,'MySql查询语句');

按照这个方法配置好，并建立了视图，然后就可以进行查询了。但是有很多问题：例如SQL Server不支持MySQL的某些数据类型，导致查询结果出错，还有个致命的问题，查询速度很慢。

最后还是写了个ASP页面，直接访问MySQL数据库，返回查询结果。打印时，也是写个ASP页面来实现。这，比什么都简单。汗……

PS. 后来还发现如果是SQL Server之间的访问，可以直接用OpenRowSet函数来实现跨数据库访问。格式如下：

OPENROWSET ( 'provider_name', { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }, { [ catalog.] [ schema.] object | 'query' } )

太方便了，特别是两个数据库之间复制数据，或者进行数据对比。