MySQL复制实战宝典：教你如何秒变数据迁移大师，轻松应对各种复杂场景！

复制旧表的结构到新表

创建一个名为 old_table 的表，并复制其结构到一个名为 new_table 的新表中。

建表语句

-- 创建旧表并插入模拟数据  
CREATE TABLE old_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    salary DECIMAL(10, 2)  
);  

模拟数据

INSERT INTO old_table (name, age, salary) VALUES  
('Alice', 30, 75000.00),  
('Bob', 25, 60000.00),  
('Charlie', 35, 85000.00);

操作语句

CREATE TABLE new_table LIKE old_table;

运行结果

新表中只有相同的表结构但是没有旧表的数据

注意事项

CREATE TABLE ... LIKE ... 语句会复制旧表的结构（包括列定义、索引、默认值等），但不会复制数据。

复制旧表的数据到新表中

在已经复制了旧表结构的新表中插入旧表的数据。

 建表语句

-- 创建旧表并插入模拟数据  
CREATE TABLE old_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    salary DECIMAL(10, 2)  
);  

模拟数据

INSERT INTO old_table (name, age, salary) VALUES  
('Alice', 30, 75000.00),  
('Bob', 25, 60000.00),  
('Charlie', 35, 85000.00);

操作语句

INSERT INTO new_table SELECT * FROM old_table;

把old_table表中的数据复制到new_table表中，new_table以第一个复制旧表的结构到新表为例，要求先创建好

运行结果

已经成功把old_table表中的数据复制到new_table中了

注意事项

• INSERT INTO ... SELECT ... 语句会复制旧表中的所有数据到新表中。
• 确保新表和旧表的列数和数据类型一致，否则会导致错误。

复制旧表的结构和数据到新表

创建一个新表，并同时复制旧表的结构和数据到新表中。

建表语句

-- 创建旧表并插入模拟数据  
CREATE TABLE old_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    salary DECIMAL(10, 2)  
);  

模拟数据

INSERT INTO old_table (name, age, salary) VALUES  
('Alice', 30, 75000.00),  
('Bob', 25, 60000.00),  
('Charlie', 35, 85000.00);

操作语句

其实就是合并了前两个步骤的SQL语句

CREATE TABLE new_table_full AS SELECT * FROM old_table;

运行结果

可以看到结果中新表跟旧表一样的结构和数据

注意事项

• 使用 CREATE TABLE ... AS SELECT ... 语句虽然可以同时复制结构和数据，但不会自动复制索引、主键、外键等约束。
• 如果需要完全相同的表结构（包括索引等），应使用 CREATE TABLE ... LIKE ... 和 INSERT INTO ... SELECT ...。

复制旧表数据到新表（表结构不一样的情况下）

创建一个新表，其结构与旧表不同，然后将旧表的数据插入到新表中。

旧表的建表语句

-- 创建旧表并插入模拟数据
CREATE TABLE old_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(50),  
    age INT,  
    salary DECIMAL(10, 2)  
);  

旧表的模拟数据

INSERT INTO old_table (name, age, salary) VALUES  
('Alice', 30, 75000.00),  
('Bob', 25, 60000.00),  
('Charlie', 35, 85000.00);

新表的建表语句

-- 创建新表，结构跟旧表不同  
CREATE TABLE new_table_diff (  
    user_id INT PRIMARY KEY,  
    fullname VARCHAR(100),  
    years_old INT,  
    monthly_salary DECIMAL(10, 2)  
);

操作语句

INSERT INTO new_table_diff (user_id, fullname, years_old, monthly_salary)  
SELECT id, name, age, salary / 12 FROM old_table;

运行结果

注意事项

• 这种情况下，需要明确指定新表和旧表的列之间的映射关系。
• 如果列名或数据类型不匹配，需要手动调整 SELECT 语句中的列名和可能的转换逻辑。
• 确保转换后的数据符合新表的约束条件，例如数据类型、长度限制等。

使用场景

一、数据备份与恢复

• 场景描述：企业为了保证数据的可靠性和安全性，需要定期对数据库进行备份。MySQL复制可以实现数据的实时或定时备份，将数据从一个主服务器复制到一个或多个从服务器，从而确保数据的完整性和可恢复性。
• 实现方式：通过配置主从复制，将主服务器的数据实时同步到从服务器。当主服务器出现故障时，可以从从服务器中恢复数据。

二、读写分离

• 场景描述：在高并发的数据库应用场景中，读写分离是一种常见的优化手段。通过将读操作和写操作分离到不同的服务器上，可以提高系统的吞吐量和响应速度。
• 实现方式：配置主从复制后，所有的写操作（如INSERT、UPDATE、DELETE等）都在主服务器上执行，而所有的读操作（如SELECT等）都在从服务器上执行。这样，主服务器可以专注于处理写操作，而从服务器则可以专注于处理读操作，从而实现读写分离。

三、数据迁移与合并

• 场景描述：在企业合并、收购或重组过程中，可能需要将多个数据库系统的数据迁移到一个统一的平台上。MySQL复制可以大大简化这一过程，减少数据迁移的复杂性和风险。
• 实现方式：通过配置多源复制（一个从服务器同时从多个主服务器复制数据），可以将多个数据库系统的数据迁移到一个统一的MySQL平台上。此外，MySQL复制还可以用于数据合并，将不同来源的数据整合到一个数据库中。

四、数据分析与报表生成

• 场景描述：在数据分析领域，实时或近实时的数据访问对于生成准确的报表和进行业务决策至关重要。MySQL复制可以用于将实时数据从主服务器复制到从服务器，以便在不影响主服务器性能的情况下进行分析和报表生成。
• 实现方式：配置主从复制后，可以在从服务器上创建用于数据分析和报表生成的视图、索引和存储过程等对象。这样，分析人员可以在从服务器上执行查询和分析操作，而无需访问主服务器。

五、高可用性和故障切换

• 场景描述：在数据库高可用性和容灾方面，MySQL复制可以用于实现主从切换和故障恢复。当主服务器出现故障时，可以迅速切换到从服务器继续提供服务，从而确保业务的连续性。
• 实现方式：通过配置主从复制和相应的故障切换机制（如MHA、Orchestrator等），可以在主服务器出现故障时自动或手动切换到从服务器。这样，即使主服务器出现故障，业务也可以继续运行而不会中断。

六、链式级联复制与分布式数据库

• 场景描述：在分布式数据库系统中，数据经常被分片到不同的服务器上。MySQL复制可以帮助将这些分片的数据整合到一个中央服务器上，以便于进行全局数据分析、报告或其他需要全局数据视图的操作。同时，链式级联复制（如A→B→C的复制形式）可以用于构建复杂的分布式数据库架构。
• 实现方式：通过配置链式级联复制，可以将数据从一个主服务器复制到多个从服务器，并再从这些从服务器中复制数据到更下一级的从服务器。这样，可以构建一个多层次的分布式数据库架构，实现数据的分布式存储和访问。