MySQL/SQL Server 跨服务器增删改查（CRUD）方法

简介

在现代分布式系统中，跨服务器执行数据库操作（CRUD）是常见需求。无论是 MySQL 还是 SQL Server，都有方法实现跨服务器的数据库操作。本文将详细介绍如何在 MySQL 和 SQL Server 中实现跨服务器的增删改查（CRUD）操作。

MySQL 跨服务器 CRUD

1. 使用 Federated 存储引擎

MySQL 提供了 Federated 存储引擎，可以实现对远程 MySQL 服务器上的表进行操作。

配置步骤

1. 确保 Federated 引擎已启用

在 MySQL 配置文件 my.cnf 中添加以下内容：

[mysqld]
federated

重启 MySQL 服务器：

sudo systemctl restart mysql

1. 创建 Federated 表

假设远程服务器的 IP 地址为 192.168.1.100，数据库为 remote_db，表为 remote_table。

CREATE TABLE local_table (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    PRIMARY KEY (id)
) ENGINE=FEDERATED
CONNECTION='mysql://username:password@192.168.1.100:3306/remote_db/remote_table';

1. 执行 CRUD 操作

创建 Federated 表后，可以像操作本地表一样进行增删改查操作：

-- 插入数据
INSERT INTO local_table (name) VALUES ('John Doe');

-- 查询数据
SELECT * FROM local_table;

-- 更新数据
UPDATE local_table SET name='Jane Doe' WHERE id=1;

-- 删除数据
DELETE FROM local_table WHERE id=1;

2. 使用 MySQL 复制

MySQL 复制（Replication）可以将一个 MySQL 服务器上的数据复制到另一个服务器，从而实现跨服务器的数据同步和操作。

配置步骤

1. 配置主服务器（Master）

在 MySQL 主服务器的配置文件 my.cnf 中添加以下内容：

[mysqld]
server-id = 1
log-bin = mysql-bin

1. 创建复制用户

CREATE USER 'replica'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';
FLUSH PRIVILEGES;

1. 配置从服务器（Slave）

在 MySQL 从服务器的配置文件 my.cnf 中添加以下内容：

[mysqld]
server-id = 2

1. 启动复制

CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='replica',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=  4;

START SLAVE;

SQL Server 跨服务器 CRUD

1. 使用 Linked Server

SQL Server 提供 Linked Server 功能，可以连接和操作其他 SQL Server 实例或其他数据库。

配置步骤

1. 创建 Linked Server

在 SQL Server Management Studio (SSMS) 中，执行以下 SQL 语句：

EXEC sp_addlinkedserver
   @server='RemoteServer',
   @srvproduct='',
   @provider='SQLNCLI',
   @datasrc='192.168.1.100';
GO

EXEC sp_addlinkedsrvlogin
   @rmtsrvname='RemoteServer',
   @useself='false',
   @rmtuser='username',
   @rmtpassword='password';
GO

1. 执行 CRUD 操作

创建 Linked Server 后，可以使用四部分名称进行跨服务器操作：

-- 插入数据
INSERT INTO RemoteServer.RemoteDatabase.dbo.RemoteTable (name) VALUES ('John Doe');

-- 查询数据
SELECT * FROM RemoteServer.RemoteDatabase.dbo.RemoteTable;

-- 更新数据
UPDATE RemoteServer.RemoteDatabase.dbo.RemoteTable SET name='Jane Doe' WHERE id=1;

-- 删除数据
DELETE FROM RemoteServer.RemoteDatabase.dbo.RemoteTable WHERE id=1;

2. 使用分布式事务

SQL Server 支持分布式事务，可以在多个服务器上执行事务，确保数据一致性。

配置步骤

1. 启用分布式事务

确保 MSDTC 服务在所有参与服务器上运行。

net start msdtc

1. 执行分布式事务

使用 BEGIN DISTRIBUTED TRANSACTION 语句启动分布式事务：

BEGIN DISTRIBUTED TRANSACTION;

-- 本地操作
INSERT INTO LocalTable (name) VALUES ('Local User');

-- 远程操作
INSERT INTO RemoteServer.RemoteDatabase.dbo.RemoteTable (name) VALUES ('Remote User');

COMMIT TRANSACTION;

总结

通过本文的介绍，您可以在 MySQL 和 SQL Server 中实现跨服务器的增删改查（CRUD）操作。无论是使用 MySQL 的 Federated 存储引擎和复制功能，还是使用 SQL Server 的 Linked Server 和分布式事务，这些方法都可以帮助您实现分布式系统中的数据操作和同步。

思维导图

MySQL/SQL Server 跨服务器 CRUD
├── MySQL
│   ├── Federated 存储引擎
│   │   ├── 启用 Federated 引擎
│   │   ├── 创建 Federated 表
│   │   └── 执行 CRUD 操作
│   └── MySQL 复制
│       ├── 配置主服务器
│       ├── 创建复制用户
│       ├── 配置从服务器
│       └── 启动复制
└── SQL Server
    ├── Linked Server
    │   ├── 创建 Linked Server
    │   └── 执行 CRUD 操作
    └── 分布式事务
        ├── 启用分布式事务
        └── 执行分布式事务

这些方法可以根据具体需求选择和组合使用，以实现高效、可靠的跨服务器数据操作。