Solved

How to copy a table from one database to another using mysql cli

Posted on 2014-04-14
5
1,166 Views
Last Modified: 2014-04-15
Hi Team,

  I need to have a cron job that copies a table from one mysql table to another, so I am setting up my bash script.  Lines 1and 2 are working, but for the succeeding lines, I have tried variations and permutations of the apostrophe, reverse apostrophe, variable, etc, but I keep getting syntax error.  I think it's because the command line wants me to invoke only the database name called dbmaster, but in the -e parameter, I am actually referencing TWO databases, dbmaster and dbmaster-test.  Maybe the command doesn't allow this?


DBASE=dbmaster-test
mysql -u root -pmypass dbmaster -e "TRUNCATE dbmaster.Cert_Inventory_Master_List;"
###mysql -u root -pmypass dbmaster -e  "INSERT INTO  dbmaster.Cert_Inventory_Master_List SELECT * FROM  'dbmaster-test'.'Cert_Inventory_Master_List';"
###mysql -u root -pmypass dbmaster -e  "INSERT INTO  dbmaster.Cert_Inventory_Master_List SELECT * FROM  dbmaster-test.Cert_Inventory_Master_List;"
###mysql -u root -pmypass dbmaster -e  "INSERT INTO  dbmaster.Cert_Inventory_Master_List SELECT * FROM  ${DBASE}.Cert_Inventory_Master_List;"
###mysql -u root -pmypass dbmaster -e  "INSERT INTO  dbmaster.Cert_Inventory_Master_List SELECT * FROM  `dbmaster-test`.`Cert_Inventory_Master_List`;"

Open in new window


* Thanks in advance for any advice.
0
Comment
Question by:rleyba828
5 Comments
 

Author Comment

by:rleyba828
ID: 39998847
...also, this one works perfectly fine from phpmyadmin
TRUNCATE dbmaster.Cert_Inventory_Master_List;
INSERT INTO  dbmaster.Cert_Inventory_Master_List SELECT * FROM  `dbmaster-test`.`Cert_Inventory_Master_List`;

Open in new window

0
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 100 total points
ID: 39998867
Can you try using a 'here' document, like this:
mysql -u root -pmypass <<SQL
connect dbmaster
INSERT INTO dbmaster.Cert_Inventory_Master_List SELECT * FROM  'dbmaster-test'.'Cert_Inventory_Master_List';
INSERT INTO dbmaster.Cert_Inventory_Master_List SELECT * FROM  dbmaster-test.Cert_Inventory_Master_List;
INSERT INTO dbmaster.Cert_Inventory_Master_List SELECT * FROM  ${DBASE}.Cert_Inventory_Master_List;
INSERT INTO dbmaster.Cert_Inventory_Master_List SELECT * FROM  `dbmaster-test`.`Cert_Inventory_Master_List`;
exit
SQL

Open in new window

0
 
LVL 34

Assisted Solution

by:Dan Craciun
Dan Craciun earned 100 total points
ID: 39999165
I think it's easier to dump the tables from the test db and then import them into the production (?) db.

Something like this (untested):
mysqldump -u root -pmypass dbmaster-test Cert_Inventory_Master_List > dump.sql
mysql -u root -pmypass -A -dbmaster < dump.sql

Open in new window

HTH,
Dan
0
 
LVL 26

Accepted Solution

by:
skullnobrains earned 300 total points
ID: 39999532
you are supposed to use either nothing or backticks. but backticks in double-quotes get interpreted by the shell

this should work

mysql -u root -pmypass dbmaster -e  'INSERT INTO  `dbmaster`.`Cert_Inventory_Master_List` SELECT * FROM  `dbmaster-test`.`Cert_Inventory_Master_List` '

Open in new window


you also should quote the name of the db when you set the corresponding variable

... and post errors

note that the heredoc as suggested above will also make things somehow simpler but you should not use backticks in that case or escape them or use <<'SQL'
0
 

Author Comment

by:rleyba828
ID: 40001450
Hi Team,
   Thanks for all the suggestions above.  It is the solution of skullnobrains that worked best for me.  It is simply the syntax of doublequotes, single quotes, and backticks that really causes me so much confusion.

* I believe the solution of Gerwin and Dan would have worked as well.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question