Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Confused maybe about MySQL Dump & PHP
Top Forums Web Development Confused maybe about MySQL Dump & PHP Post 302321867 by jzacsh on Tuesday 2nd of June 2009 09:29:16 AM
Old 06-02-2009
Thanks for the reply!!

Quote:
Originally Posted by otheus
If he'll put it in a cronjob, you cannot rightly assume what the PWD (present working directory) will be, so that the line:
Code:
sql_bDir="./sql_backups/" #LOCATION FOR SQL BACKUPS TO LAND

Doesn't make sense. You need a full path, or maybe something prefixed with $HOME.
good point, I realize that though and I only had ./ in place as something temporary as I do manual tests (easier to type out for testing)

Quote:
Originally Posted by otheus
Second, the mysqldump line should be:
Code:
mysqldump -u NAME -p PASS  DBASENAME

Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe
Code:
mysqldump -u NAME -p PASS  | gzip -c > $sql_bName.gz

Thank you, I had failed to read what the "opt" option does (was going on an online article's suggestion to use it that said opt does compression)
This is what i found:
Quote:
o --opt

This option is shorthand; it is the same as specifying
--add-drop-table --add-locks --create-options --disable-keys
--extended-insert --lock-tables --quick --set-charset. It should
give you a fast dump operation and produce a dump file that can be
reloaded into a MySQL server quickly.

The --opt option is enabled by default. Use --skip-opt to disable
it. See the discussion at the beginning of this section for
information about selectively enabling or disabling certain of the
options affected by --opt.
If I don't know what most of those ^ do (as I'm not familiar with MYSQL) would you suggest I just stick with the simpler code you laid out? or will the above options of "opt" not hurt?

Quote:
Originally Posted by otheus
Finally, you might gain some performance by buffering the output through a program called "dd". This allows mysqldump to do its work for a longer time before switching over to do the compression.
Code:
mysqldump -u NAME -p PASS DBASENAME  | dd bs=1M | gzip -c > $sql_bName.gz

This is really cool, thanks!
Quote:
Originally Posted by otheus
For my dump, I make sure the dump is done in a "single-transaction" to ensure that the dump represents a recoverable state. (ie, transactions don't take place during the dump). I also send the output to bzip2 for higher compression.
Code:
mysqldump -u NAME -p PASS --single-transaction DBASENAME | dd bs=1M | bzip2 -c >dumpfile

There are more than 8,100 items in our shopping cart. after reading the man I see it is suggested to use "quick" in conjunction with "single-transaction" for large tables. What is a "large" table? Is 8,000+ items considered a large table? If I use "quick" will it defeat the purpose of running the bs=1M option with dd?

Thanks for all your help!!
 

9 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Linux/Unix/PHP/MySQL & servers?

Hello everyone! First I would like to say that I am very glad that I found this forum, and by some of the posts I have viewed, I see that I can learn a lot from you all! Secondly, I know next to nothing about Linux/Unix (gotta learn sometime right?) and need some assistance. I am a... (5 Replies)
Discussion started by: kolton
5 Replies

2. UNIX for Dummies Questions & Answers

PHP & Apache & MySQL install how-to ?

how do i install php & mysql with apache on suse linux ??? apache was installed and configured when i installed linux. all its files are in different folders. e.g http files in usr/local/httpd/htdocs/ and its configs are in etc/httpd/ so how do i install php and get it to work with apache and... (4 Replies)
Discussion started by: perleo
4 Replies

3. Shell Programming and Scripting

PHP & MySQL: sort desending and print

How do i get php to sort the times in a table and print order desending. eg: in the table i have: 02:60.00 02:70.00 02:20.00 02:50.00 in that order in a table how do i get php & mysql to print... (1 Reply)
Discussion started by: perleo
1 Replies

4. Shell Programming and Scripting

restore mysql dump file in many remote servers?

Hi all, I want to restore DB file in many mysql servers, i already using script for sending the dumpfile in all servers, but it's just annoying if i have to restore the dumpfile in all servers, i want just execute 1 script, and will restore in all remote mysql servers. I make script but not... (2 Replies)
Discussion started by: blesets
2 Replies

5. Shell Programming and Scripting

how to break mysql dump sql file

Hi folks I have mysql dump which having insert queries, i want to break that file when 10 complete "INSERTS" lines so extract that line and store in 1.sql and 2.sql and for next 10 insert lines. pls guide me how can i do that. Regards, Bash (2 Replies)
Discussion started by: learnbash
2 Replies

6. Web Development

[php] webpage with login & mysql-db

Hi all, What I was looking for before was a multi-user password manager, web-based! The offer of free or cheap tools of this kind is very pover. Or they are too complex (or too expensive) I'm not a web programmer but I now decided to set up a (php) website with login and based on this login... (1 Reply)
Discussion started by: thibautp
1 Replies

7. Shell Programming and Scripting

PHP read large string & split in multidimensional arrays & assign fieldnames & write into MYSQL

Hi, I hope the title does not scare people to look into this thread but it describes roughly what I'm trying to do. I need a solution in PHP. I'm a programming beginner, so it might be that the approach to solve this, might be easier to solve with an other approach of someone else, so if you... (0 Replies)
Discussion started by: lowmaster
0 Replies

8. Shell Programming and Scripting

Can't add variable to file name in mySQL dump script

I have a script (below) which works ok, but I have tried to modify it as I want to keep the older files for a restore if needed. I have tried adding a date suffix to the newly created files (second lump of code), but it doesn't seem to work. I get the error: $SOURCEDIR/p1db_$DATEVAR.sql:... (3 Replies)
Discussion started by: ojobson
3 Replies

9. Shell Programming and Scripting

Create mysql database with bash script - confused

Hi, i have the following: db="create database xxx;GRANT ALL PRIVILEGES ON xxx.* TO user@localhost IDENTIFIED BY 'password';FLUSH PRIVILEGES;quit;" mysql -u root -p$mysql_pass -e "$db" I don't understand why this is failing, it works fine when run from cmd but when is run in a bash script,... (1 Reply)
Discussion started by: ktm
1 Replies
All times are GMT -4. The time now is 06:39 PM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy