We help IT Professionals succeed at work.

Error trying to run mysqldump

997 Views
Last Modified: 2015-08-12
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
Comment
Watch Question

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.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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.
Systems Administrator
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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
Seth SimmonsLead Systems Administrator
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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)
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.
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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!
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
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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!
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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.
Zac HarrisSystems Administrator

Commented:
Your jail seems to have been really strict...like they say, crime doesn't pay...anybody?....nobody?...*drops mic* :)
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Author

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.