Solved

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

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

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE 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 35

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 27

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

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.​
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
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.

685 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