Solved

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

Posted on 2010-11-23
4
457 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
  • 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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 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

15 Experts available now in Live!

Get 1:1 Help Now