2

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

user1819769
  • 85
  • 7
  • you can copy a database directly into another machine see https://dev.mysql.com/doc/refman/8.0/en/copying-databases.html – nbk Mar 18 '20 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 Mar 18 '20 at 22:56
  • bash can multithreading https://stackoverflow.com/questions/3004811/how-do-you-run-multiple-programs-in-parallel-from-a-bash-script but i doubt than you can increase this for many tables – nbk Mar 18 '20 at 23:18
  • You need to send process in background using `&` – Digvijay S Mar 19 '20 at 02:01

1 Answers1

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.

bsrawat
  • 101
  • 5