Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…

631 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