Solved

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

Posted on 2014-04-14
5
1,148 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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.
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.

757 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now