mysql database copy script using bash

Question

I want to create a script that copies all tables in a database to another database.

Obviously I found it faster than dump & restore with mysqldump.

The script below works fine, but the tables are copied one by one in order. (only one process id)

In order to operate faster, i want to make all tables copied at the same time.

Help.

#!/bin/bash
HOSTNAME='127.0.0.1'
QUERY1="select concat('create table ', ' $1','.',table_name , ' like ' , '$2','.',table_name,';') as '' from information_schema.tables where table_schema = '$2'"
QUERY2="select concat('insert into ', ' $1','.',table_name , ' select * from ' , '$2','.',table_name, ';') as '' from information_schema.tables where table_schema = '$2'"
Q1=`mysql -uaccount -ppass -h$HOSTNAME -e "$QUERY1"`
Q11=`mysql -uaccount -ppass -h$HOSTNAME -e "$QUERY2"`

C1=`mysql -uaccount-ppass -h$HOSTNAME -e "CREATE DATABASE $1"`

Q2=`mysql --default-character-set=utf8 -v -t -uaccount-ppass -h$HOSTNAME -e "$Q1"`
Q22=`mysql --default-character-set=utf8 -v -t -uaccount-ppass -h$HOSTNAME -e "$Q11"`

sh test.sh newdb_01 olddb_01

you can copy a database directly into another machine see dev.mysql.com/doc/refman/8.0/en/copying-databases.html — nbk
– nbk, Commented Mar 18, 2020 at 22:50
thank you. But what I have to work on is a very big database. I tried it with mysqldump like what you gave but it takes a very long time. At least the above script is faster than mysqldump. But I want to do it faster. — user1819769
– user1819769, Commented Mar 18, 2020 at 22:56
bash can multithreading stackoverflow.com/questions/3004811/… but i doubt than you can increase this for many tables — nbk
– nbk, Commented Mar 18, 2020 at 23:18

bsrawat · Accepted Answer · 2020-03-19 09:46:21Z

3

Have you tried parallel ( https://www.gnu.org/software/parallel/ )

The approach I suggest is to get the list of table names first and create the DB.

Then pass the names of the tables to parallel with your script. Some examples here -> https://www.freecodecamp.org/news/how-to-supercharge-your-bash-workflows-with-gnu-parallel-53aab0aea141/

In your case, this should work like:-

cat table_name_list | parallel -j10 test.sh args...

Hope this helps. Feel free to explore parallel for your case.

answered Mar 19, 2020 at 9:46

bsrawat

1015 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql database copy script using bash

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related