Hello. the purpose of my efforts right now are to get a larger script of mine (which the admin told me he'd put into cron for me) to properly back-up my MySQL database. To test out the sql back-up part (before getting the whole script into cron, and having it not work) I wanted to test it. So here's what I did:
PHP file to do my testing (on the live server)
Script being called by the PHP
just for testing, both these files were set to 777 on the server. The output from the PHP file looks just as it should, but then I find the ./sql_backups/ directory empty. I tried the username, password and database of the last line of that script in both capital letters and lower case (read somewhere that capitals works better.. anyways). I'm not experienced with php/mysql.. I found the syntax for that mysqldump line in an article.
If he'll put it in a cronjob, you cannot rightly assume what the PWD (present working directory) will be, so that the line:
Doesn't make sense. You need a full path, or maybe something prefixed with $HOME.
Second, the mysqldump line should be:
Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe
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.
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.
If he'll put it in a cronjob, you cannot rightly assume what the PWD (present working directory) will be, so that the line:
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:
Finally, you should understand that the output is not automatically compressed. Do that separately, via a pipe
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--optoptionisenabledbydefault.Use--skip-opttodisable 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.
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.
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?
You don't need to use --opt since it's enabled by default. 8000 items is not a lot. The "quick" option will not defeat the purpose of using bs=1M.
Thanks so much for your help. I'm trying to test this out on my personal live website's wordpress database first. so I uploaded a test.php file to run the shell script, like so:
test.php
the shell script its set to run is:
then I visit the above ^ test.php via browser and get this output:
When I download the file its only 4KB, and upon unarchiving the file, I find a Zero KB file, which using "cat" via terminal shows that the file is indeed blank.
1. change sql_bDir to $PWD/sql_pickup/ so we can see where at actually outputs.
2. for testing, turn off compression and skip the dd command... output directly to the file.
1. change sql_bDir to $PWD/sql_pickup/ so we can see where at actually outputs.
2. for testing, turn off compression and skip the dd command... output directly to the file.
okay, done. didn't seem to make a difference (the file still comes out 0 B, just uncompressed)
test.php output:
source of dbackup.sh
actually... just in posting that ^ I realized I haven't checked:
echo $SHELL
...it returned
/usr/local/cpanel/bin/noshell
i'm guessing that means I can't run things properly? (I'm not sure, I obviously had a shell to output a file with and everything... I just wasn't successful in outputting a GOOD file)
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)
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)
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)
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)
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)
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)
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)
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)
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)