Migrate mysql tables

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()
    begin
        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…