It's Friday (a MySQL Question)
in [General]
|
Prev: [ENCODING QUEUE] - anyone think of a good way to setup aqueuing system so everyone gets their turn
Next: login to protected directory by php
From: tedd on 13 Aug 2010 17:48 Hi gang: Normally if I want to dump a MySQL database, I read the database via a PHP script (i.e., list tables and fetch rows) and save the results as a text file -- after which I download the file -- it's not a big deal. However while I was doing my daily read of the MySQL Manual, namely: http://dev.mysql.com/doc/refman/5.0/en/select.html I came across this statement: SELECT * FROM table_reference INTO OUTFILE 'file_name' It looked to be bit simpler/shorter than my code, so I tried it. But it reports: Access denied for user 'me'@'localhost' (using password: YES). I suspect that the "access being denied" is because MySQL doesn't have permission to create the output file. The MySQL manual reports: 1) that a file cannot be present; 2) AND MySQL must have file privileges to create the file -- but I don't know how to set that up. So, has anyone got this to work? If so, how did you do it? If at all possible, please provide code (MySQL/PHP) and not command-line statements. Cheers, tedd -- ------- http://sperling.com/
From: Ashley Sheridan on 13 Aug 2010 17:59 On Fri, 2010-08-13 at 17:48 -0400, tedd wrote: > Hi gang: > > Normally if I want to dump a MySQL database, I read the database via > a PHP script (i.e., list tables and fetch rows) and save the results > as a text file -- after which I download the file -- it's not a big > deal. > > However while I was doing my daily read of the MySQL Manual, namely: > > http://dev.mysql.com/doc/refman/5.0/en/select.html > > I came across this statement: > > SELECT * FROM table_reference INTO OUTFILE 'file_name' > > It looked to be bit simpler/shorter than my code, so I tried it. But > it reports: > > Access denied for user 'me'@'localhost' (using password: YES). > > I suspect that the "access being denied" is because MySQL doesn't > have permission to create the output file. The MySQL manual reports: > 1) that a file cannot be present; 2) AND MySQL must have file > privileges to create the file -- but I don't know how to set that up. > > So, has anyone got this to work? If so, how did you do it? If at all > possible, please provide code (MySQL/PHP) and not command-line > statements. > > Cheers, > > tedd > > -- > ------- > http://sperling.com/ > I've only ever done something like this via the command line. Having said that, could you maybe pass a command line string to exec(). Something like (untested): echo 'password' | mysql -u root -p < query I believe that is the right sort of thing, but I've never quite done it all as a single statement like this before, I've always tended to type in things on a line-by-line basis. Thanks, Ash http://www.ashleysheridan.co.uk
From: "Daniel P. Brown" on 13 Aug 2010 18:11 On Fri, Aug 13, 2010 at 17:48, tedd <tedd(a)sperling.com> wrote: > > SELECT * FROM table_reference INTO OUTFILE 'file_name' > > It looked to be bit simpler/shorter than my code, so I tried it. But it > reports: > > Access denied for user 'me'@'localhost' (using password: YES). > > I suspect that the "access being denied" is because MySQL doesn't have > permission to create the output file. The MySQL manual reports: 1) that a > file cannot be present; 2) AND MySQL must have file privileges to create the > file -- but I don't know how to set that up. No, the 'access denied' message means that either the username or password is incorrect, or that the given user doesn't have permission to access the given database on the given host. Easiest method, from the command line on the server from which you want to dump the database: mysqldump -u user -p database_name > outfile.sql (Where `user` is the username, `database_name` is the database to dump, and `outfile.sql` is the name of the file to which you will write. You will be prompted for the password.) -- </Daniel P. Brown> UNADVERTISED DEDICATED SERVER SPECIALS SAME-DAY SETUP Just ask me what we're offering today! daniel.brown(a)parasane.net || danbrown(a)php.net http://www.parasane.net/ || http://www.pilotpig.net/
From: "Daevid Vincent" on 13 Aug 2010 18:14 > -----Original Message----- > From: Ashley Sheridan [mailto:ash(a)ashleysheridan.co.uk] > Sent: Friday, August 13, 2010 3:00 PM > To: tedd > Cc: php-general(a)lists.php.net > Subject: Re: [PHP] It's Friday (a MySQL Question) > > On Fri, 2010-08-13 at 17:48 -0400, tedd wrote: > > > Hi gang: > > > > Normally if I want to dump a MySQL database, I read the > database via > > a PHP script (i.e., list tables and fetch rows) and save > the results > > as a text file -- after which I download the file -- it's not a big > > deal. > > > > However while I was doing my daily read of the MySQL Manual, namely: > > > > http://dev.mysql.com/doc/refman/5.0/en/select.html > > > > I came across this statement: > > > > SELECT * FROM table_reference INTO OUTFILE 'file_name' > > > > It looked to be bit simpler/shorter than my code, so I > tried it. But > > it reports: > > > > Access denied for user 'me'@'localhost' (using password: YES). > > > > I suspect that the "access being denied" is because MySQL doesn't > > have permission to create the output file. The MySQL manual > reports: > > 1) that a file cannot be present; 2) AND MySQL must have file > > privileges to create the file -- but I don't know how to > set that up. http://dev.mysql.com/doc/refman/5.0/en/grant.html http://dev.mysql.com/doc/refman/5.0/en/privileges-provided.html#priv_file GRANT SELECT, FILE ON mydb.table_reference TO 'me'@'localhost'; > I've only ever done something like this via the command line. Having > said that, could you maybe pass a command line string to exec(). > Something like (untested): > > echo 'password' | mysql -u root -p < query You know you can pass that on the command line right and avoid this pipe business? mysql -uroot -ppassword < query Or mysql --user=root --password=password < query > I believe that is the right sort of thing, but I've never > quite done it > all as a single statement like this before, I've always tended to type > in things on a line-by-line basis.
From: "Daniel P. Brown" on 13 Aug 2010 18:14
On Fri, Aug 13, 2010 at 17:59, Ashley Sheridan <ash(a)ashleysheridan.co.uk> wrote: > > echo 'password' | mysql -u root -p < query If you're going to do the password in plain text from the command line like that (which is a bad idea), you don't need to pipe an echo. Just type: mysql -u root -ppassword < query As long as there's no space between the -p flag and your password, you're good to go. Still, not only is it a horrible idea, it's far worse if it's your MySQL root. Far, far, FAR worse if it's the same as your system root. You belong in jail --- no, *hell* --- if the MySQL and system root passwords are the same, and you have remote root login enabled with password authentication. -- </Daniel P. Brown> UNADVERTISED DEDICATED SERVER SPECIALS SAME-DAY SETUP Just ask me what we're offering today! daniel.brown(a)parasane.net || danbrown(a)php.net http://www.parasane.net/ || http://www.pilotpig.net/ |