Linux SSH database transferring between two databases MySQL

Question

This is the situation: I have server with linux, and there is two databases in it. I need to make a cron job to transfer some data from db1 to db2. The thing is i need this: db1->table1->column1 + db1->table1->column2 + db1->table2->column1 TRANSFER TO db2->table1(create table) with these three fields. Can anyone help me to form a proper cron job command? I know how to transfer tables between databases, but this is far more complicated.

Your question is not accurate enough: apart the cronjob, you must first define which programming language you'll use, write and test your transfer script. Then it's pretty easy to transform it as a cronjob. — hornetbzz
– hornetbzz, Commented Dec 9, 2011 at 10:25
Sorry, i want to use pure mysql. I mean i want to write linux command wich i will you as cron job to transfer mentioned data. I managed to write this by myself: CREATE TABLE testas.test SELECT pgs_id, pg_id_t, kodas, pavad, if(del_date IS NOT NULL, 1, if(rod_int<>1, 2, 0)) FROM 9001_komeksimas.pg_seima INNER JOIN 9001_komeksimas.pg_zodynas ON 9001_komeksimas.pg_seima.pgs_id=9001_komeksimas.pg_zodynas.pg_id; But now i need a linux command to use it as cron job :) — Minutis
– Minutis, Commented Dec 9, 2011 at 10:44

Community · Accepted Answer · 2017-05-23 10:08:23Z

2

Partial duplicate of this.

In command line there are (at least) two ways to achieve this :

mysql -u[user] -p[pass] -e "[mysql commands]"

or :

mysql -u[user] -p[pass] <<QUERY_INPUT   
[mysql commands]   
QUERY_INPUT

Put your command "CREATE TABLE testas.test ..." instead of [mysql commands],
Save this into a bash script "myscript" :

#!/bin/bash
user=$1
pwd=$2
mysql -u"$user" -p"$pwd" <<QUERY_INPUT   
[mysql commands]   
QUERY_INPUT

Make it executable by the crontab user

chmod +x myscript

Then it depends on the distro you're using for the cronjob. For example in Debian's-like distro, you don't need to do anything else than putting your script into the right directory. For example, il you wish the script to be run daily, move the file into /etc/cron.daily.
Just take care in this case not to forget to call the cronjob with the 2 datas user and pwd :-)

edited May 23, 2017 at 10:08

CommunityBot

11 silver badge

answered Dec 9, 2011 at 10:59

hornetbzz

9,4056 gold badges41 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

Linux SSH database transferring between two databases MySQL

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