Solved

How to backup specific tables in a mysql database through shell?

Posted on 2010-11-23
4
500 Views
Last Modified: 2012-05-10
Hello,

I have a very large mysql database and would like to fetch specific fields within tables and download them as a .csv file. More specifically:

I'd like to backup 3 separate user fields within one table out of the database (ex. email, username, ipaddress). I looked up some shell commands, and know some rudimentary stuff, but haven't been able to piece together a command to accomplish this.

Anyone know how?

By the way, I would like to download all of the data from each field.

Thanks a bunch!
0
Comment
Question by:ShootFromtheHip
[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
  • 2
  • 2
4 Comments
 
LVL 6

Expert Comment

by:brb6708
ID: 34198505

mysql -BrN -u«username» -p«password» «database» -e"select email, username, ipaddress from table1" > backupfile.csv

produces a csv file with tab as delimiter. If you need e.g. a semicolon as delimiter the command is like

mysql -BrN -u«username» -p«password» «database» -e"select email, username, ipaddress from table1" | tr '\t' ';' > backupfile.csv
0
 

Accepted Solution

by:
ShootFromtheHip earned 0 total points
ID: 34198926
Awesome! Exactly what I was looking for. Thanks a ton.

I presume the commands between the double quotes are SQL commands? If this is so, then I'd love to tweak the command a bit so that the data that is retrieved meets only certain criteria.

Here is the command that I would use to retrieve the data I'm talking about:

SELECT * FROM user WHERE options & 16;

By the way, "options" in the command above is a separate field.

What I'm trying to do is extract users from the DB who meet certain criteria (option & 16).

How would I incorporate this into the shell command above to yield appropriate results?

Thanks so much. You've already saved me TONS of headache and hassle. I love this place! Points to be awarded quite soon...
0
 
LVL 6

Expert Comment

by:brb6708
ID: 34200769
I don't understand "option & 16)" but you can use all kinds of sql statements between the double quotes even if you need many lines for the query. You can use variables ($1, $2, $variable...) in the statements.

Example:

variable_from=1000
variable_until=4999
mysql -BrN -u«username» -p«password» «database» -e"
select email, username, ipaddress from table1
where testvalue between $variable_from and $variable_until
" > backupfile.csv

So you're able to set up the scripts according to your needs.
0
 

Author Closing Comment

by:ShootFromtheHip
ID: 34255737
I think in my case I had to put the hostname in the shell command. Awesome answer
0

Featured Post

Webinar: MariaDB® Server 10.2: The Complete Guide

Join Percona’s Chief Evangelist, Colin Charles as he presents MariaDB Server 10.2: The Complete Guide on Tuesday, June 27, 2017 at 7:00 am PDT / 10:00 am EDT (UTC-7).

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

729 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