MySQL & Shell commands
A new client has requested shell access so they can manage there database via command line...below are quotes from the client, looking for any help with resolving there issue...
We gave him jailed access at first...
[QUOTE]Hello, I understand the concern. The reason I ask is because the bulk of my MySQL management is done through Shell. I update my databases by uploading CSV text files, then via Shell, empty the old tables and populate them with the new data. I know this can also be one via PHPAdmin, but that doesn't work because my files are pretty large andI cannot upload them via an online form. I have to upload them using FTP, then using Shell extract them to MySQL. I wrote a simply .sh script that does all this for me. I may be able to do this with a cron job as well... I think.
I do this once a week do update my stores data tables, so this would have to permanent.
If this is a problem, I'll see if I can figure out another way to do it not using Shell.
I do this with my current host, so I'm not sure of an alternate way of doing it. I also need to unzip files via Shell commands, but I see CPanel has an extract function in file manager that may work[/QUOTE]
[QUOTE]Hi again, now that I'm able to access via Shell, I cannot seem to tap into my MySQL databases. When I try to access MySQL to populate my database tables it says:
"Can't connect to local MySQL server through socket '/disk3/mysqldata/mysql.sock'"
I'm assuming this is restricted on the server side. If that is the case, my work-around was just to run the MySQL commands through PHPMyAdmin. The problem I seem to be having is I'm not sure what the absolute path is to my files. I'm trying to upload a CSV file to my server via FTP, then run the MySQL command:
"LOAD DATA INFILE "/home/redrumme/test.txt" REPLACE INTO TABLE cscart_products FIELDS TERMINATED BY '|';"
To populate the data from this text file to the table specified. The problem is, everytime I try to run that through MySQL is gives me the following error:
#1045 - Access denied for user 'redrumme'@'localhost' (using password: YES)
Is my path above to the text file incorrect for the server I am on?[/QUOTE]
[QUOTE]I added my IP address to the list in Cpanel for Remote MySQL access. No luck. It still will not allow me to access MySQL over Shell. I get the following error:
Error 2002 (HYOO): Can't connect to local MySQL server through socket '/disk3/mysqldata/mysql.sock'
I also double-check my Shell commands and I am including the entire name of the database and username. I'm using the following to try and access it:
mysql -uredrumme_newcart -ppassword_omitted redrumme_eStore[/QUOTE]
We then gave them bash shell access.
[QUOTE]I was able to access my MySQL databases now through Shell, but it doesnt seem to like the "LOAD DATA INFILE" command. It says "Access denied for user 'redrumme_newcart'@'localhost'"
This is the same error I was getting in PHPMyAdmin when I tried to run LOAD DATA INFILE. I tried to figure out what the problem is on the Internet and it appears that I have to have FILE permissions in the MySQL users table to allow me to use the LOAD DATA INFILE command.You probably know more about this than I do though.[/QUOTE]
[QUOTE]Reading about this, I guess there is an alternate "LOAD DATA LOCAL INFILE" that can be used and no permissions need to be granted but when I run that it says "The used command is not allowed with this MySQL version"[/QUOTE]
I got your page and direct messages ....
Yes, I'll help you with this issue. I'm busy with another client at the moment but I'll get back to you shortly.
This was taken care of...
[QUOTE=D-Litz;574881]A new client has requested shell access so they can manage there database via command line...below are quotes from the client, looking for any help with resolving there issue...
We then gave them bash shell access.[/QUOTE]
[url=http://dev.mysql.com/doc/refman/5.1/en/load-data.html]MySQL :: MySQL 5.1 Reference Manual :: 12.2.6 LOAD DATA INFILE Syntax[/url]
Since it was resolved, it might be helpful for others to know how it was resolved for future reference if you still have details on what was done anyway in your situation. Glad you got it fixed :)
[QUOTE=cpaneldanielle;576753]Since it was resolved, it might be helpful for others to know how it was resolved for future reference if you still have details on what was done anyway in your situation. Glad you got it fixed :)[/QUOTE]
It was not resolved...
cPanel techs can not locate or fix the problem...it appears its only for one account so far...so we are going to leave it alone unless we receive other complaints.
Ah, since you'd stated it was "taken care of" in your prior post, that made it appear everything was working. I'm sorry to hear the issue couldn't be pinpointed for that account.
[QUOTE=D-Litz;576761]It was not resolved...
cPanel techs can not locate or fix the problem...it appears its only for one account so far...so we are going to leave it alone unless we receive other complaints.[/QUOTE]
Well, I didn't follow up for the exact same reason as the last comment as it appeared you resolved the problem!
I'd be happy to take a look since I usually find things Cpanel misses! (No offense meant to them LOL)
Like I told you previously though, it is most likely a problem with the user's data than with a problem with the system but I can determine that for certain upon looking at it.
This was resolved by following this.
[QUOTE]Ok, an update. The user had changed their main account password, however, the .my.cnf file located within the users directory, had not updated the password. To correct the issue, I moved the existing .my.cnf file, to .my.cnf.backup, thus forcing cPanel to recreated this file with the correct password.
mv /home/user/.my.cnf /home/user/my.cnf.backup
Then, have the user log into cPanel again, or, if you know the users password, you can log in with it. Once completed, you should be able to download sql backups[/QUOTE]
Which was posted here