asked on

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

Zac Harris

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.

Chris Stanyon

ASKER

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.

ASKER CERTIFIED SOLUTION

Zac Harris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Stanyon

ASKER

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

SOLUTION

Seth Simmons

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Stanyon

ASKER

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 Harris

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

Chris Stanyon

ASKER

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 Harris

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.

Chris Stanyon

ASKER

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 Harris

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

Chris Stanyon

ASKER

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!

Chris Stanyon

ASKER

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 Harris

Your jail seems to have been really strict...like they say, crime doesn't pay...anybody?....nobody?...*drops mic* :)

Chris Stanyon

ASKER

:)

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.