Error trying to run mysqldump

Hi Experts,

I have a VPS that we use as our web server, running CentOS 6 / Plesk and I'm trying to setup a script to backup our database. I know I need to use the mysqldump command and set up a cronjob, but I'm struggling to even get the mysqldump running from a command line.

If I SSH into the server as the web user, and try the mysqldump command, I simply get the following error:

/bin/mysqldump: No such file or directory

I can see that mysqldump is not in the users /bin folder so that error makes sense. However, if I echo out my $PATH I get the following:

/usr/local/bin:/bin:/usr/bin

mysqldump is in the /usr/bin folder, so not sure why it doesn't look there:

-rwxr-xr-x. 1 root root 98272 Feb 12  2014 mysqldump

If I try to run /usr/bin/mysqldump, I still get No Such File or Directory. That doesn't make sense to me - it clearly does exist!

I've also logged in as root and copied (hardlinked) mysqldump to the users /bin folder, but then the user just gets:

mysqldump: error while loading shared libraries: libmysqlclient.so.16: cannot open shared object file: No such file or directory

I can't even get over the first hurdle in backing up my DBs so any pointers from the Experts would be great. I know so little about Linux admin so I'm hoping for a simple solution (aren't we all!) and explanation.

Many thanks

Chris
LVL 47
Chris StanyonWebDevAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zac HarrisSystems Administrator Commented:
I use cpanel for my MySQL installs so the folder structure for you may vary some... try this:

Put the following code in the file db-backup.sh:
#/bin/bash
/usr/bin/mysqldump -–user=Database_User -–password=***** 
-–databases Database_Name | gzip > 
/home/cPanel-User/Database_Name-`date +%Y%m%d%H%M`.sql.gz

Open in new window


Where ‘Database_User’ is the database user, ‘Database_Name’ is the database name and ‘*****’ is the password of database user
+%Y%m%d%H%M is the time stamp,

The backup file will be created under directory “/home/cPanel-User/”.

Now, you need to add cron job. You can do it with the help of your control panel.

0 0 * * * /home/cPanel-User/db-backup.sh

Open in new window


Make sure you have permissions to run this script and you can test it by going to shell, and just run the script manually. If it runs successfully, you have your Database dump at the specified directory.

The above cron job will executes every day at 00:00 hour and creates backup file for your MySQL Database.
0
Chris StanyonWebDevAuthor Commented:
Thanks Zac, but I think you've missed the whole point of my question. I know how to use mysqldump and I know how to create a cronjob. The problem I'm having is with actually getting mysqldump to run at all, or even for my command line to recognise that it exists.
0
Zac HarrisSystems Administrator Commented:
Sorry about that...

The dynamic linker (ld.so) does not know it's supposed to search for these shared libs in that directory.

You have several options to resolve this:
- Add /usr/local/mysql/lib/mysql to your LD_LIBRARY_PATH environment variable
or
- copy the shared mysqlclient.so files into /usr/local/lib and run "ldconfig -v"
or
- Add the path /usr/local/mysql/lib/mysql to /etc/ld.so.conf

You can use "ldd /usr/local/mysql/bin/mysql" to verify that all shared libs are actually found.

It feels like Monday today so hopefully I didn't miss again :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chris StanyonWebDevAuthor Commented:
Cheers Zac - for me it feels like Friday - already been a long week :)

I'll have a go at your suggestions and get back to you. Probably going to be tomorrow now.

Been doing a bit of research and starting to figure out why I'm getting the problems - the web user is chroot'ed and none of the mysqldump stuff is available in the chroot jail.

Sounds like your suggestions could sort that out.

Be in touch
0
Seth SimmonsSr. Systems AdministratorCommented:
much easier to simply add the path in the crontab; no need to be messing with LD configs or copying libraries

by default cron doesn't use any paths so defining it there would resolve it
0
Chris StanyonWebDevAuthor Commented:
OK. Making some progress, but hit another snag.

I've managed to get mysqldump to run by copying mysqldump to the users /bin folder and the necessary libraries to the users lib64 folder - found out which ones to copy using ldd /usr/bin/mysqldump whilst logged in as root.

mysqldump will now run but with the following error:

mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect

Going to Google that now and see if I can make any more progress, but any insight you guys could give would be great.

@Seth - thanks for the advice. The user can only set up a cronjob to run as themselves so setting the path didn't work - I'm guessing it's still trying to run it from a chroot jail and as far as that's concerned any path outside of the jail just doesn't exist. When setting the path for the cronjob to /usr/bin/mysqldump it still gives me a No Such File or Directory error
0
Zac HarrisSystems Administrator Commented:
That's why I suggested what I did haha chroot jail is very limited.

Try adding this to your my.cnf file to address the new error:

[client]
socket=/home/mysql/mysql.sock
0
Chris StanyonWebDevAuthor Commented:
Yeah - just learning all about the chroot jail and it's limitations :(

Adding the socket entry to ~/.my.cnf didn't work. There is no /home/mysql/mysql.sock file in the jail.

I have managed to get it to work by changing the host entry for mysqldump to the local IP address (127.0.0.1), instead of localhost.

Now I Just need to see if I can manage to get mysqldump running from within a PHP script (not very hopeful at the moment)
0
Zac HarrisSystems Administrator Commented:
I'm very surprised that since you're on a VPS that you are so limited. Sounds like your hosting company needs to give you more permissions.

While you may not be the only one on the physical server hosting the virtuals (you most certainly aren't actually) you should be the only one on the virtual server. At least that's what you're paying for. Maybe you should contact them and ask for higher privileges.
0
Chris StanyonWebDevAuthor Commented:
I do have root access to the VPS so I can run mysqldump any time I like. The problem is that when a virtual host was set up for a domain and website, that user was automatically put into a chroot jail. It's this user that needs to run a script to backup their own databases, so I'm trying to allow them to do that.

I don't want them having to log in as root to do it!
0
Zac HarrisSystems Administrator Commented:
Hmmm. Is the user completely chrooted? If not You might try making a symlink to mysqldump in the users chroot/bin folder.

I'll keep digging around on my server in the mean time to see if I can come up with additional thoughts
0
Chris StanyonWebDevAuthor Commented:
Thanks Zac,

Yeah - I believe the webuser is completely chrooted.

Bit of a pain to be honest but I guess it's the most secure way of doing things!
0
Chris StanyonWebDevAuthor Commented:
Right guys,

After a few pointers from you both, and a lot of reading, I've finally made enough progress to sign off on this one, and learn a lot in the process :)

Basically, I copied mysqldump into the chroot jail /bin folder and created a bash script to backup all the necessary DBs. I then added that script to cronjob.

Because of the nature of the jail I had to get creative in the solution, but it's now working the way I want it to.

Thanks for the pointers.
0
Zac HarrisSystems Administrator Commented:
Your jail seems to have been really strict...like they say, crime doesn't pay...anybody?....nobody?...*drops mic* :)
0
Chris StanyonWebDevAuthor Commented:
:)

Yeah, it does seem that way. I don't really know enough to start changing the setup too much. I don't want to do something stupid that will jeopardise the server, but as I learn more about Admin, I'll probably make some changes.

Thanks for stepping in.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.