The Cache: Technology Expert's Forum
 
*
Welcome, Guest. Please login or register. September 16, 2019, 01:41:15 PM

Login with username, password and session length


Pages: [1] 2
  Print  
Author Topic: mysql command line question  (Read 6235 times)
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« on: April 02, 2009, 10:15:39 AM »

Code:
mysql -u [USERNAME] -p[PASSWORD]  [DATABASE_NAME] -h [HOST]

gets me in but

mysql -u [USERNAME] -p[PASSWORD]  [DATABASE_NAME] -h [HOST] < [INPUT FILE]

returns a:

Code:
ERROR 1044 (42000) at line 12: Access denied for user '[USERNAME]'@'%' to database '[DATABASE]'
(uiserver):u41722287:~/somedir/someotherdir >

what is this %
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #1 on: April 02, 2009, 10:20:52 AM »

% is a SQL wildcard. It's saying (your username) at (anydatabase) is invalid.

So you can log in and get to a SQL prompt? My suggestion, although a bit longer than VS's is a little more interactive. His is "do it all from the shell" and mine is more "get into the database and execute there."

My advice would be to get into the mysql command line and say use (mydatabase) and then perhaps "describe tables" and see what's there. If you can do this sort of simple SQL command then you are at least talking to your database. Then you can use the source command to execute your big SQL file.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #2 on: April 05, 2009, 10:16:08 AM »

GRANT ALL ON %(or [DATABASE]) TO [USERNAME]@% USING PASSWORD [PASSWORD]
Logged

hai
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #3 on: April 05, 2009, 11:44:57 AM »

% is a SQL wildcard. It's saying (your username) at (anydatabase) is invalid.

% is Actually host in that error not database
% is all but localhost

right?
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #4 on: April 05, 2009, 09:48:40 PM »

You're right nuts ... it's saying that the user/pw for (database) rules do not exist for *that inbound IP*.

I think the wildcard in that instance is saying it's not available anywhere. If he's command line, then he's local host. If he's getting the error, then something's amiss.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #5 on: April 06, 2009, 08:08:54 AM »

thank you... the next problem i am having is trying to figure out how to pipe the results of the:



Code:
mysql -u [USERNAME] -p[PASSWORD] [DATABASE_NAME] -h [HOSTNAME] < [INPUT FILE]

out to a file.


i tried to
Code:
mysql -u [USERNAME] -p[PASSWORD] [DATABASE_NAME] [HOSTNAME] < [INPUT FILE] | results.txt

but it didnt work.. could this be piped to a file through a shell script?

the source command worked but the  dump file had a couple of errors but i couldnt read them and puttys buffers for the sewer.

« Last Edit: April 06, 2009, 08:11:37 AM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #6 on: April 06, 2009, 08:16:31 AM »

GRANT ALL ON %(or [DATABASE]) TO [USERNAME]@% USING PASSWORD [PASSWORD]

does the % have to be in single quotes?


i got this error:


mysql> GRANT ALL ON % TO [username]@% USING PASSWORD [thepassword]
    -> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% TO [username]@% USING PASSWORD [thepassword]' at line 1

« Last Edit: April 06, 2009, 08:22:07 AM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
vsloathe
vim ftw!
Global Moderator
Lifer
*****
Offline Offline

Posts: 1669



View Profile
« Reply #7 on: April 06, 2009, 08:46:30 AM »

Yeah, it does need to be in quotes I think.

Pipe writes to stdout/stdin (for piping data between processes). If you want files written or read, you need to use > and <.

Code:
mysql -u [USERNAME] -p[PASSWORD] [DATABASE_NAME] [HOSTNAME] < [INPUT FILE] > results.txt
Logged

hai
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #8 on: April 06, 2009, 09:00:28 AM »

TRIED IT in quotes and it didnt work either...  i can log in with the credentials im supplying but this < isnt working... how do i throw this into a batch file and execute it... sorry i know i could prolly google this stuff but i dont even know what words to use to fish through all the billions of results you seo bastards spam out there... hahaha

Code:
ERROR 1044 (42000) at line 12: Access denied for user 'dbo222227665'@'%' to database 'db232223622'

so maybe if i get the syntax right for the grant all on percent working ill be able to use the < > commands with access?  Wont i also need this access to run a shell script?

essentially cant i just vim a file like ms dos .bat and then execute it with ./filename.sh or something.... im a little rusty.
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #9 on: April 06, 2009, 09:31:46 AM »

just to be clear that notation syntax is not getting in the way...
and there are a few things that might be causing issues.

foo =database. could also use a * for all.
*=tables but could be a specific table   foo.tblfoo
bar = username already existing in mysql
password = duh....

the diff is the star and the "identified by" instead of "using password".

GRANT ALL ON foo.* TO bar@'%' IDENTIFIED BY 'PASSWORD'


note that * is a wildcard outside of SQL syntax. in otherwords, its to match anything outside of the content of fields.
% on the otherhand is a wildcard to match anything INSIDE a field.
Grant is adding entries to users tables, and putting a % as the host allowed. This is a little backwards because normally you would use % to match whats inside the field, but.... this field is actually used backwards in essence.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #10 on: April 06, 2009, 09:42:49 AM »

SO THIS IS done from the mysql command line client, correct?

and should the password be in single quotes? how about the username?
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
perkiset
Olde World Hacker
Administrator
Lifer
*****
Offline Offline

Posts: 10096



View Profile
« Reply #11 on: April 06, 2009, 09:43:43 AM »

Why aren't you just connecting in PHP to do this again? Seems like it'd be a helluvalot easier.
Logged

It is now believed, that after having lived in one compound with 3 wives and never leaving the house for 5 years, Bin Laden called the U.S. Navy Seals himself.
nutballs
Administrator
Lifer
*****
Offline Offline

Posts: 5627


Back in my day we had 9 planets


View Profile
« Reply #12 on: April 06, 2009, 09:56:11 AM »

SO THIS IS done from the mysql command line client, correct?

and should the password be in single quotes? how about the username?

yes from within the MYSQL prompt. though it can be done via any avenue what will allow you to execute SQL statements as root.
pass in quotes, user not, unless it has any wierd characters.
Logged

I could eat a bowl of Alphabet Soup and shit a better argument than that.
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #13 on: April 06, 2009, 10:11:29 AM »

Why aren't you just connecting in PHP to do this again? Seems like it'd be a helluvalot easier.

Now you know *I* cant  make anything easier? only harder.  hahahaha

how would it be done in php then?  I JUST WANT TO IMPORT A TEXT FILE AND kNOW WHAT HAPPENED TO IT BEFORE IT SCROLLS OFF THE PAGE.... @!!!! LOL


Code:
GRANT ALL ON db222227665.* TO dbo222227665@'%' IDENTIFIED BY '[password]';

im getting:

Code:
ERROR 1044 (42000): Access denied for user 'dbo2222227665'@'%' to database 'db222227665'

so this means that i DONT have root access then correct?
and i would obviously need root access to do it as any user with root access
from php right?

« Last Edit: April 06, 2009, 10:15:51 AM by jammaster82 » Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
jammaster82
Lifer
*****
Offline Offline

Posts: 666


Thats craigs list for ya


View Profile
« Reply #14 on: April 06, 2009, 10:17:48 AM »

just want to bbe sure i can do it with only one thing in the way (me) and not
my lack of php knowledge... i cant even issue an EMPTY DATABASE command
so this makes me think i dont have root access...
Logged

The watched pot, never boils... But if you walk away from it , the soup burns.  What gives?
Pages: [1] 2
  Print  
 
Jump to:  

Perkiset's Place Home   Best of The Cache   phpMyIDE: MySQL Stored Procedures, Functions & Triggers
Politics @ Perkiset's   Pinkhat's Perspective   
cache
mart
coder
programmers
ajax
php
javascript
Powered by MySQL Powered by PHP Powered by SMF 1.1.2 | SMF © 2006-2007, Simple Machines LLC
Seo4Smf v0.2 © Webmaster's Talks


Valid XHTML 1.0! Valid CSS!