[RESOLVED] MySQL Backups

[tams]

Hi everyone,

I want to backup my database so that I don't lose any information but when I try doing it through mysqldump, it doesn't work. It can't find mysqld.sock and I was unable to locate it too.

So when I do the following in terminal, it tells me that mysqld.sock is missing.

/usr/local/mysql/bin# mysqldump --user=username --password=secret DbInfo >/usr/local/mysql/backup/DbBackup
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) when trying to connect

I looked up the error in google but didn't get any results for the exact same problem as mine.

Now I have no idea how to get the .sock file. Is there a way to create this file in the var/run directory. Also is there any automatic ways of backup and restore so that the database will be backed up automatically everyday instead of me going through mysqldump?

I have mysql version 5.0.41. And it is installed on my local machine.

Thank you in advance.

[mpb001]

/var/run/mysqld/mysqld.sock is the default location. You can try a find / -name mysqld.sock or a locate mysqld.sock to find it as it could be somewhere else.

You can then connect with -S for socket location. You can cron job the mysqldump in a shell file. The .sock part is short for socket which is an alternate way of connecting which allows more security ( uncomment skip-networking in the my.cnf).

If you have php on the machine and you can connect without a socket declaration then the socket path will also be in the php.ini under mysql.default_socket =.

I assume you are working on Linux.

[tams]

I am using ubuntu 7.04.

I did a search for the mysqld.sock file and nothing was found. I also manually went through the filesystem folders and still nothing.

I don't believe the file was somehow deleted because I installed php and mysql myself about 2 months ago. I think it was just not there to begin with.

And the mysql_default_socket is also empty in php.ini so it uses the built in MySql defaults.

I downloaded a program that makes automatic backups but when I try to restore it, I still need mysql.sock

I tried mysqld and it tells me that it is not installed.

/usr/local/mysql/libexec# mysqld --user=usern --pass=password --host=localhost MInfo < /usr/local/mysql/backup/daily/MInfo/MInfo_2007-08-21_15h43m.Tuesday.sql
The program 'mysqld' is currently not installed. You can install it by typing:
apt-get install mysql-server-5.0

That is weird since mysql is installed.

[mpb001]

The .sock file is created when the service starts as it's a socket, not a normal file.

If you do a

netstat -a | more

You will get a list of all sockets and ports listening. At the top you should see *:mysql which will be mapped to 3306 as default and at the bottom are the list of all the listening sockets. If neither is there then the service is not running/listening.

There should also be the /etc/init.d/mysqld script file which stops and starts the service.

[tams]

netstat -a | more did show the :mysql

tcp 0 0 *:mysql : LISTEN
unix 2 [ ACC ] STREAM LISTENING 20366 /tmp/mysql.sock

A search for mysqld shpwed that the filename is only in two places
/usr/local/mysql/libexec/mysqld
/usr/local/mysql-5.0.41/sql/mysqld

Also there is no mysqld script file at etc/init.d

[mpb001]

In you my.cnf you can set the socket path with the socket directive if it is not there.

under [mysqld]
socket=/var/run/mysqld/mysqld.sock

also you can try connecting with the -P=3306 for the port directive instead. Though when using the port you have to have usernames set to the 127.0.0.1 host on Linux as the network element is treated seperatley from the socket(localhost) directive.

Services are started by the scripts/symlinks in /etc/rc.d/rc(current runlevel).d folder. S for start, K for kill. Higher the number later they load/die. That should tell you where the service is started from as well.

[tams]

The path to mysqld is the same in my.cnf as you suggested.
Also there is no rc.d folder in etc. There is rc0.d, rc1.d, rc2.d, rc3.d, rc4.d, rc5.d, rc6.d and rcS.d

the mysql.sock is in the tmp folder.
So how canI specify where to look for this folder when I do the following in terminal.

mysql --user=username --pass=password --host=dbserver database < /path/file.sql

[mpb001]

Well if it doesn't let you move it by changing the value in my.cnf to the default location of /var/run/mysqld/mysqld.sock then you can connect by

mysql --user=username --pass=password --host=dbserver -S /tmp/mysql.sock database < /path/file.sql

if the socket is located at /tmp/mysql.sock.

You can also try setting the client to
automatically try it in the my.cnf.

[client]
socket=/tmp/mysql.sock

Though sometimes these things do not pay attention for some reason. If the [client] part does not work set the mysql.default_socket =/tmp/mysql.sock in the php.ini so at least when connecting from that you do not have to fudge about.

My email didn't show the quote bit so I missed you found the socket earlier.

[tams]

Thank you, that worked and I was able to restore.😃

[mpb001]

Glad to be of help 🙂