Migrate mysql tables

3 min readMay 7, 2022

Shared with 3 cases

Sample database preparation

  1. Setup : 1.1 clone this repo : https://github.com/datacharmer/test_db 1.2 mysql < employees.sql
  2. Check database info
MariaDB [(none)]> use employees;
MariaDB [employees]> select count(1) from salaries;
| count(1) |
| 2844047 |

Lets pick salaries table since it has 2m rows, and migrate this sample salaries table into another database named ‘test’

Case1 : export table(s) and import into another database, same table structure .

Use mysqldump

export :

mysqldump -u root -p --databases employees --tables salaries > /tmp/salaries.sql

There are lot more options here . e.g. you can add ‘ — insert-ignore’ when need to .

import :

mysql -u root -p test < /tmp/salaries.sql

The export and import entire process only took < 10 seconds (1cpu, 2GB ram) .

Mysqldump is standard approach when backup/restore tables with same structure . it’s fast enough for millions of rows table, but if you have multiple tables with millions of rows, consider multiprocessing parrellel mysqldumpeach table to make process faster .

Case2 : what if need to insert into a new table with selected columns ?

Copy table using store proc

Create a new table salaries2 and add a new column

create table if not exists test.salaries2 like employees.salaries;
alter table test.salaries2 add column new_col int default 0 not null;

Now create a store proc to copy the data from old table to new table

drop procedure if exists proc_copy_salaries;
delimiter //
create procedure proc_copy_salaries()
declare i int default 10001;
declare num int default 10000;
declare maxId int default 0;
set maxId = (select emp_no from employees.salaries order by emp_no desc limit 1);
while i <= maxId do
start transaction;
insert ignore into test.salaries2(emp_no, salary, from_date, to_date) select * from employees.salaries
where emp_no between i and i+num…

A channel which focusing on developer growth and self improvement

Recommended from Medium


See more recommendations