MariaDB 10.3.32 mysqldump不转义字符串中的单引号

iibxawm4  于 2023-10-20  发布在  Mysql
关注(0)|答案(1)|浏览(144)

我有一个bash脚本，它在我的备份NAS上运行mysqldump（我有MariaDB 10.3.32），以便在我的主NAS上备份我的MariaDB 11。
从阅读许多文章中可以看出，mysqldump默认情况下应该对字符串中的单引号进行转义，但在我的例子中却没有这样做。有人可以帮助我理解为什么并帮助我修复上传吗？dump似乎工作正常，没有转义单引号，但由于单引号未被转义，上传失败：
ERROR 1064 (42000) at line 153: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 's','{Blue/Yellow} ','2015-04-09 12:56:48','2015-04-09 14:16:14',NULL,'01:19:2...' at line 1
要创建备份，请执行以下操作：mysqldump --defaults-extra-file=$SCRIPT_DIR/my.cnf --defaults-group-suffix=.nas1 $DB > $BACKUP_DIR/$DB.sql
defaults-group-suffix.nas1包含MariaDB 11的主机、端口和登录详细信息以及以下mysqldump选项：

[mysqldump]
allow-keywords
comments
databases
events
force
routines
single-transaction
verbose

要加载备份，请执行以下操作：mysql --defaults-extra-file=$SCRIPT_DIR/my.cnf --defaults-group-suffix=.nas2 --database=$DB < $BACKUP_DIR/$DB.sql
使用相同的my.cnf，其中.nas2包含MariaDB 10的登录详细信息以及上述mysqldump选项。
输出文件中的示例行，其中单引号未转义：

('816962786',NULL,'1st Run with Kayano's','{Blue/Yellow} ','2015-04-09 12:56:48','2015-04-09 14:16:14',NULL,'01:19:25','01:19:26','01:19:25','14.39','10.88','10.8761','21.30','00:05:31','00:05:31','00:02:49','210',NULL,'61','113','0',NULL,NULL,'1252',NULL,NULL,NULL,'93','112','130','149','167','00:00:26','00:00:58','00:51:05','00:01:12','00:00:00',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'+02:00','50.988255','5.879338',NULL,NULL,'242')

这是更大的数据集/.sql-file/values的一部分：

-- MySQL dump 10.19  Distrib 10.3.32-MariaDB, for Linux ()
--
-- Host: 192.168.1.104    Database: garmin_connect
-- ------------------------------------------------------
-- Server version   11.0.2-MariaDB-1:11.0.2+maria~ubu2204

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `activities_gc_export`
--

DROP TABLE IF EXISTS `activities_gc_export`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `activities_gc_export` (
  `id` bigint(20) NOT NULL,
  `url` varchar(128) DEFAULT NULL,
  `name` varchar(128) DEFAULT NULL,
  `description` varchar(128) DEFAULT NULL,
  `time_start` datetime DEFAULT NULL,
  `time_end` datetime DEFAULT NULL,
  `location_name` varchar(128) DEFAULT NULL,
  `duration` time DEFAULT NULL,
  `duration_elapsed` time DEFAULT NULL,
  `duration_moving` time DEFAULT NULL,
  `distance` float(5,2) unsigned DEFAULT NULL,
  `speed_avg` float(5,2) unsigned DEFAULT NULL,
  `speed_avg_moving` float unsigned DEFAULT NULL,
  `speed_max` float(5,2) unsigned DEFAULT NULL,
  `pace_avg` time DEFAULT NULL,
  `pace_avg_moving` time DEFAULT NULL,
  `pace_max` time DEFAULT NULL,
  `elevation_gain` smallint(6) unsigned DEFAULT NULL,
  `elevation_loss` smallint(6) unsigned DEFAULT NULL,
  `elevation_min` smallint(6) DEFAULT NULL,
  `elevation_max` smallint(6) DEFAULT NULL,
  `elevation_corrected` tinyint(1) unsigned DEFAULT NULL,
  `hr_max` tinyint(4) unsigned DEFAULT NULL,
  `hr_avg` tinyint(4) unsigned DEFAULT NULL,
  `calories` mediumint(8) unsigned DEFAULT NULL,
  `vo2max` tinyint(4) unsigned DEFAULT NULL,
  `training_effect_aerobic` float(2,1) unsigned DEFAULT NULL,
  `training_effect_anaerobic` float(2,1) unsigned DEFAULT NULL,
  `hr_zone_1_low` tinyint(4) unsigned DEFAULT NULL,
  `hr_zone_2_low` tinyint(4) unsigned DEFAULT NULL,
  `hr_zone_3_low` tinyint(4) unsigned DEFAULT NULL,
  `hr_zone_4_low` tinyint(4) unsigned DEFAULT NULL,
  `hr_zone_5_low` tinyint(4) unsigned DEFAULT NULL,
  `hr_zone_1_time` time DEFAULT NULL,
  `hr_zone_2_time` time DEFAULT NULL,
  `hr_zone_3_time` time DEFAULT NULL,
  `hr_zone_4_time` time DEFAULT NULL,
  `hr_zone_5_time` time DEFAULT NULL,
  `cadence_avg` float(5,2) unsigned DEFAULT NULL,
  `cadence_max` float(5,2) unsigned DEFAULT NULL,
  `stride_length` float(5,2) unsigned DEFAULT NULL,
  `steps` mediumint(6) unsigned DEFAULT NULL,
  `strokes` mediumint(6) unsigned DEFAULT NULL,
  `temp_avg` float(3,1) DEFAULT NULL,
  `temp_min` float(3,1) DEFAULT NULL,
  `temp_max` float(3,1) DEFAULT NULL,
  `device` varchar(64) DEFAULT NULL,
  `gear` varchar(128) DEFAULT NULL,
  `activity_type_key` varchar(64) DEFAULT NULL,
  `activity_type` varchar(64) DEFAULT NULL,
  `activity_parent` varchar(64) DEFAULT NULL,
  `event_type_key` varchar(32) DEFAULT NULL,
  `event_type` varchar(32) DEFAULT NULL,
  `privacy` varchar(32) DEFAULT NULL,
  `file_format` varchar(3) DEFAULT NULL,
  `tz` varchar(32) DEFAULT NULL,
  `tz_offset` varchar(6) DEFAULT NULL,
  `start_lat` float(20,6) DEFAULT NULL,
  `start_lon` float(20,6) DEFAULT NULL,
  `end_lat` float(20,6) DEFAULT NULL,
  `end_lon` float(20,6) DEFAULT NULL,
  `sample_count` smallint(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `activities_gc_export`
--

LOCK TABLES `activities_gc_export` WRITE;
/*!40000 ALTER TABLE `activities_gc_export` DISABLE KEYS */;
INSERT INTO `activities_gc_export` VALUES ("example_above_and_many_more_rows_of_data")

在@O。琼斯的评论，我跟踪我的.cnf文件：
/usr/local/mariadb10/etc/mysql/my.cnf：

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
#
[client]
socket = /run/mysqld/mysqld10.sock

[mysqld]
bind-address = 0.0.0.0
socket = /run/mysqld/mysqld10.sock
pid-file = /run/mysqld/mysqld10.pid
skip-external-locking
key_buffer_size = 16K
max_allowed_packet = 1M
table_open_cache = 4
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 240K
innodb_data_home_dir = /var/packages/MariaDB10/target/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/packages/MariaDB10/target/mysql
innodb_buffer_pool_size = 16M
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
innodb_file_per_table = 1
synology_password_check = FORCE_PLUS_PERMANENT

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
tmpdir=/var/services/tmp
key_buffer_size = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

[mariadb]
plugin_load_add = synology_password_check

!include /var/packages/MariaDB10/etc/my.cnf
!include /var/packages/MariaDB10/etc/my_port.cnf
!include /var/packages/MariaDB10/etc/synology.cnf

~/.my.cnf: No such file or directory
/var/packages/MariaDB10/etc/my.cnf: No such file or directory
/var/packages/MariaDB10/etc/my_port.cnf

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
port=3306
[client]
port=3306

/var/packages/MariaDB10/etc/synology.cnf：

# DO NOT EDIT THIS FILE !!!
# You can change the port on user interface of MariaDB10.
# Please add other custom configuration to /var/packages/MariaDB10/etc/my.cnf
[mysqld]
skip_networking=0
mariadb

1条答案

按热度按时间
yqkkidmi

yqkkidmi1#

会不会是sed去掉了反斜杠？
看看，* 在sed之前 *，这条线看起来像

...,'1st Run with Kayano\'s',...

注意反斜杠转义了嵌入的撇号。

赞(0）分享  回复(0）举报  2023-10-20
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com