• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

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

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
ShootFromtheHip
Asked:
ShootFromtheHip
  • 2
  • 2
1 Solution
 
brb6708Commented:

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
 
ShootFromtheHipAuthor Commented:
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
 
brb6708Commented:
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
 
ShootFromtheHipAuthor Commented:
I think in my case I had to put the hostname in the shell command. Awesome answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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