[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2010-11-23
4
Medium Priority
?
516 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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Exchange database can often fail to mount thereby halting the work of all users connected to it. Finding out why database isn’t mounting is crucial and getting the server back online. Stellar Phoenix Mailbox Exchange Recovery is a champion product t…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

612 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