Solved

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

Posted on 2014-04-14
5
1,195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It’s 2016. Password authentication should be dead — or at least close to dying. But, unfortunately, it has not traversed Quagga stage yet. Using password authentication is like laundering hotel guest linens with a washboard — it’s Passé.
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
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.

705 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