MySQL数据库从.gz mysqldump文件迁移到远程服务器
#网络开发人员 #教程 #database #mysql

介绍

几个月前，我已经在生产环境中发现了一个错误的原因，而该原因并未在开发中呈现，这主要是由于该应用程序的创建者和用户对如何略有不同应该使用应用程序。这意味着在制作应用程序时的所有测试中，我们从未看到过报告的错误。解决方案是获取生产数据库的副本，并在开发中使用该副本来尝试复制报告的问题。

该应用程序是一个CMS（内容管理系统），它具有一些额外的细节，引入了一些更复杂的数据库结构，因此这可能是开发团队很难抢占此问题的原因之一。同样，技术领先优势继续进行，制作了一个mysqldump文件blogs.sql.gz，并立即将其转发给我，以弄清楚事情。我们所有的开发工作都在专用开发远程服务器上完成，我在本地计算机上有文件，因此我需要将文件提取并上传到远程服务器上，这是我以前从未完成的事情。

我将介绍我进行的步骤，以了解如何确切地使用所需数据创建和填充数据库。当我使用我熟悉的工具以及对我的新工具时，这对我来说是一次发现的旅程，因此有一些可能被视为不必要的步骤，但是我当他们教给我一些东西时，将包括它们，我的目标是分享这些课程。

我遇到了一些具体的问题，我将在进一步详细解释，这导致了我采取的一些额外步骤，因此在这里不仅仅是上传数据，还需要学习更多。除了为自己的利益记录我的思考过程外，我希望以解决其他初级开发人员的指导，以解决我们启动时花费我们很多时间的问题的方式。

tl; dr

我经历了使我能够阅读转储文件的步骤可以在我的开发环境中具有确切的生产副本。

跳到Final working Command查看我最终得到的命令。

用例

在我在这里介绍的情况下;

  • 位于./Downloads/blogs.sql.gz中的一个拉链mysqldump文件（.gz）
  • 该文件包含许多mysqldump: [Warning]/Error...行（由于使用/没有密码制作）
  • 我们拥有SSH访问的远程服务器，由<user>@<hostname>表示
  • 上述服务器上安装的mysql

我们面临的特定问题

第一个绊脚石是在上传mysqldump文件时通常不会解析的警告。他们的存在打破了溪流，因此将读取它们之后的任何内容。最后，我最终以多种方式解决了这个问题。我的第一个“解决方案”引入了新问题，后来显而易见。

其他用途

没有理由我看到本文其余部分突出显示的工作知识对其他用例不利，例如，在没有任何警告的MySQL转储文件中，需要上传。我将分解我们检查的所有命令的每个部分，因此您可以决定需要申请哪些命令。

主题我们将涵盖

将涵盖许多工具，因此我将在此处简要介绍它们，并在适当时说明其特定用途和其他标志。

的简单SQL壳
命令 描述
gzip 压缩或展开文件
尾巴 输出文件的最后一部分
头 输出文件的第一部分
ssh OpenSSH远程登录客户端
mysql 一个支持交互式使用
sed 流式编辑器用于过滤和转换文本
\ （管道）

脚步

您可以通过单击here看到我所拥有的整个原始互动，并通过单击here来查看MySQL。

1.创建数据库

第一步是使用ssh访问开发服务器，并能够使用mysql Shell进行更改，以添加我们将从转储文件中填充的新数据库。

sam@Mizouzie:~$ ssh <user>@<hostname> -A

这将使我们进入。

<user>@<hostname>:$ mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15401
Server version: 10.6.12-MariaDB-1:10.6.12+maria~deb11 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

现在在开发服务器上，使用mysql命令打开shell客户端。

MariaDB [(none)]> show databases;
+-----------------------+
| Database              |
+-----------------------+
| information_schema    |
| content_generator     |
| example_laravel       |
| next_con_gen          |
+-----------------------+
4 rows in set (0.001 sec)

MariaDB [(none)]> create database example_laravel_dummy
    -> ;
Query OK, 1 row affected (0.000 sec)

MariaDB [(none)]> show databases;
+--------------------------+
| Database                 |
+--------------------------+
| information_schema       |
| content_generator        |
| example_laravel          |
| example_laravel_dummy    |
| next_con_gen             |
+--------------------------+
5 rows in set (0.001 sec)

使用show databases;检查现有数据库，在这种情况下，我们的开发数据库是 example_laravel ，因此我们将创建一个虚拟版本 example_laravel_dummy 我们将在以后使用。再次使用show databases;确认它是创建的。

2.尝试直接导入 

sam@MizouziE:~$ ssh <user>@<hostname> -A "mysql -u sam -p example_laravel_dummy" < ./Downloads/blogs.sql.gz 
Enter password: ERROR 1045 (28000): Access denied for user 'sam'@'localhost' (using password: YES)

在这里，我们使用的是ssh并通过在引号之间传递字符串来将命令馈送到它中。有效地，我们在上运行mysql -u sam -p example_laravel_dummy

我不应该使用-p标志作为密码，所以让我们省略。

sam@MizouziE:~$ ssh <user>@<hostname> -A "mysql -u sam example_laravel_dummy" < ./Downloads/blogs.sql.gz 
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: Sd'.

现在，文件格式是一个问题，因为我们尚未提取它。

sam@MizouziE:~$ ssh <user>@<hostname> -A "mysql -u sam example_laravel_dummy" < gzip -dk ./Downloads/blogs.sql.gz 
bash: gzip: No such file or directory

我们需要安装gzip，所以继续进行。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | ssh <user>@<hostname> -A "mysql example_laravel_dummy"
ERROR 1064 (42000) at line 1: 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 'mysqldump: [Warning] Using a password on the command line interface can be in...' at line 1

使用-c＆-d与gzip使用的两个标志将将输出写入标准输出和解压缩，这使我们可以将输出输出到我们的以下ssh命令中。

。

在

我们的新错误表示我们希望通过mysql命令运行的SQL语句中的语法错误。这将我们带入下一步。

3.阅读mysqldump文件

为了查看为什么和何处存在此问题，我们可以使用head命令在解压缩后读取文件的前10行。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | head
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.33, for Linux (aarch64)
--
-- Host: localhost    Database: blogs
-- ------------------------------------------------------
-- Server version   8.0.33

/*!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 */;

我们可以通过此输出来看到以mysqldump: [Warning]开头的行是有问题的，并且不能被认为是适当的SQL语法。此外，评论以下几行，因此也可以省略它们。

4.跳过前7行

因此，由于我们的前7行包含不良语法和无用的信息，因此我们发送相同的输出，但没有前7行。与head类似，我们可以使用tail在指定的行号之后通过使用带正（+）整数的-n标志来发送所有内容。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | tail -n +7 | ssh <user>@<hostname> -A "mysql example_laravel_dummy"
ERROR 1064 (42000) at line 12: 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 'mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS priv...' at line 1

新错误，但类似于我们以前遇到的错误。这意味着我们必须深入研究文件。

5.进一步阅读文件

我们可以在头上使用-n标志来指定显示的行数。如果没有此标志，则默认为10，因此我们将使用更高的数字来查看以前更多。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | head -n 25
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-- MySQL dump 10.13  Distrib 8.0.33, for Linux (aarch64)
--
-- Host: localhost    Database: blogs
-- ------------------------------------------------------
-- Server version   8.0.33

/*!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 */;
/*!50503 SET NAMES UTF8 */;
/*!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 */;
mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

--
-- Table structure for table `activity_log`
--

DROP TABLE IF EXISTS `activity_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;

在此输出中，我们看到还有另一个错误消息破坏语法规则。默认情况下，这是在许多可能可以安全假设的设置之后进行的，因此我们将尝试像以前一样尝试使用tail，但从更远的文件下方使用。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | tail -n +24 | ssh <user>@<hostname> -A "mysql example_laravel_dummy"
ERROR 1005 (HY000) at line 96: Can't create table `example_laravel_dummy`.`article_background_image` (errno: 150 "Foreign key constraint is incorrectly formed")

现在，我们看到这将行不通，因为我们省略了重要的东西。即行/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;。

6.设置重要的服务器变量

我们可以尝试设置缺失的服务器变量，并通过使用mysql之后的--init command=[command]来保持当前的馈送方式。我们将使用它来禁用外国密钥检查。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | tail -n +24 | ssh <user>@<hostname> -A "mysql --init-command=\"SET SESSION FOREIGN_KEY_CHECKS=0;\" example_laravel_dummy"
ERROR 1231 (42000) at line 2261: Variable 'time_zone' can't be set to the value of 'NULL'

在这里，我们还有一个不同的错误，但是原因与我们遇到的最后一个错误非常相似。从MySQLDUMP文件的开头省略了那些看似“安全省略”的服务器变量的原因。我们需要另一种方法。

7.那就是她的sed

sed现在使自己对我们有用，因为我们可以将其使用它的regexp匹配能力来 删除文件流中的某些行。我的意思是，我们将从引起我们头痛的mysqldump:开始掉下线。

我们通过将“ mysqldump：”放在删除以下任何线的删除行中的regexp中来实现这一目标； '/mysqldump:/d'。这是从事业务的小写“ D”。

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | sed '/mysqldump:/d' | ssh <user>@<hostname> -A "mysql --init-command=\"SET SESSION FOREIGN_KEY_CHECKS=0;\" example_laravel_dummy"
sam@MizouziE:~$

看一下，没有错误！这意味着它有效。

不过，这里没有一件事。如果您发现它在Twitter @mizouzie

上向我开枪

8.最终检查以确保

现在要做的就是在远程服务器上的MySQL实例上检查一切。


MariaDB [example_laravel_dummy]> show tables;
+------------------------------------+
| Tables_in_example_laravel_dummy |
+------------------------------------+
| activity_log                       |
| article_article                    |
| article_author                     |
| article_background_image           |
| article_category                   |
| article_revisions                  |
| article_site                       |
| article_slugs                      |
| articles                           |
| author_revisions                   |
| author_slugs                       |
| authors                            |
| background_images                  |
| background_images_revisions        |
| blocks                             |
| categories                         |
| category_revisions                 |
| category_site                      |
| category_slugs                     |
| failed_jobs                        |
| features                           |
| fileables                          |
| files                              |
| imports                            |
| linked_images                      |
| links                              |
| mediables                          |
| medias                             |
| menu_revisions                     |
| menus                              |
| migrations                         |
| password_resets                    |
| provider_revisions                 |
| provider_slugs                     |
| providers                          |
| ratings                            |
| redirects                          |
| related                            |
| setting_translations               |
| settings                           |
| site_revisions                     |
| site_user                          |
| sites                              |
| slider_revisions                   |
| sliders                            |
| string_translation_revisions       |
| string_translations                |
| tagged                             |
| tags                               |
| twill_password_resets              |
| twill_users                        |
| users                              |
+------------------------------------+
52 rows in set (0.001 sec)

MariaDB [example_laravel_dummy]> ^DBye

很好，我们有所有的桌子，然后使用Ctrl + D退出。

最终工作命令

我们进行了所有实验之后，我们得到了想要的东西，因此我们的最后命令要做我们想像的事情：

sam@MizouziE:~$ gzip -cd ./Downloads/blogs.sql.gz | sed '/mysqldump:/d' | ssh <user>@<hostname> -A "mysql example_laravel_dummy"

因此，对于任何其他用例，或者只是每个部分的故障，我们都有类似的东西：

<user>@<localhost>:~$ gzip -cd <location/of/mysqldump.sql.gz> | sed '/<beginning of line to remove>/d' | ssh <user>@<hostname> -A "mysql <name_of_database>"

所以命令执行此操作：

  1. 解压缩文件
  2. 通过sed输出文件流
  3. 用sed过滤出不希望的线
  4. 将其结果输送到ssh
  5. 使用ssh执行mysql命令并将管道流馈送到命名数据库

这一切都起作用，因为zipped mysqldump文件本质上是一个很大的SQL语句，可以重新创建所有表并插入从中获取的数据库中的所有数据。它们往往是很大的文件，因此需要将它们拉链。

概括

这是一系列步骤，最终给出了预期的结果。可能有多种不同的方法可以得出相同的结论，甚至可以得出不同的结论来得出相同的结果。这是在该领域工作的很酷的事情，我们有多种工具可以为问题解决方案的小部分。这样做是由我们进行这样的练习来学习如何将它们结合成带来结果的东西的。这就是我们软件工程师得到的报酬。练习这样的问题，您将学习负载！