Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1237
  • Last Modified:

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

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
rleyba828
Asked:
rleyba828
3 Solutions
 
rleyba828Author Commented:
...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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
Dan CraciunIT ConsultantCommented:
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
 
skullnobrainsCommented:
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
 
rleyba828Author Commented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now