mysqldump: command not found ??
Geoff
I am using PuTTY to connect to a server with mysql on it.
I can get to the mysql prompt once connected to the server by typing
mysql and giving the password.
If instead I type mysqldump I get an error message,
mysqldump: command not found
How do I find out where mysqldump is?
Is it possible to backup the database whilst at the mysql prompt?
Any help appreciated.
Cheers
Geoff
Gordon Burditt
What kind of server? Linux? BSD Unix? Windows? Solaris?
>I can get to the mysql prompt once connected to the server by typing
>mysql and giving the password.
>
>If instead I type mysqldump I get an error message,
>
>mysqldump: command not found
>
>How do I find out where mysqldump is?
You need to give mysqldump arguments (it's not very interactive, although
it will ask for a password), but first deal with finding
the program.
If you have a program that can locate files by filename, such as
"locate" on BSD UNIX (and probably Linux), look for a filename of
the form "mysqldump*" (probably just "mysqldump" on UNIX, or
"mysqldump.exe" on Windows).
On my system (FreeBSD), "mysqldump" is in the same directory as
"mysql", which is probably not the case on your server as it would
have been found. It is possible that mysqldump is not installed
on the server.
>Is it possible to backup the database whilst at the mysql prompt?
Yes, somewhat painfully. You can do something like "select * from
table;" to get a copy of the table, in a format which is very
difficult to put back, or something to extract the data in a better
format, like CSV or tab-separated columns. Or you could issue a
complicated query which cobbles together SQL insert statements out
of data in the table (this is largely what mysqldump does; have you
ever tried reading the output of mysqldump? It's a bunch of SQL
statements. I know you can't find mysqldump on that server, but
you might have used it elsewhere.)
D Finnigan
>
> How do I find out where mysqldump is?
>
whereis mysqldump
--
Mac GUI Vault - A source for retro Apple II and Macintosh
computing.
http://macgui.com/vault/
Geoff
Finnigan) wrote:
>Geoff wrote:
>>
>> How do I find out where mysqldump is?
>>
>
>whereis mysqldump
I get
whereis: command not found
When I logon I see SunOS 5.9 and then I select Unix Shell from the
list of options.
Should I be able to use whereis?
Cheers
Geoff
Geoff
Burditt) wrote:
>>I am using PuTTY to connect to a server with mysql on it.
>
>What kind of server? Linux? BSD Unix? Windows? Solaris?
>
>>I can get to the mysql prompt once connected to the server by typing
>>mysql and giving the password.
>>
>>If instead I type mysqldump I get an error message,
>>
>>mysqldump: command not found
>>
>>How do I find out where mysqldump is?
>
>You need to give mysqldump arguments (it's not very interactive, although
>it will ask for a password), but first deal with finding
>the program.
>
>If you have a program that can locate files by filename, such as
>"locate" on BSD UNIX (and probably Linux), look for a filename of
>the form "mysqldump*" (probably just "mysqldump" on UNIX, or
>"mysqldump.exe" on Windows).
Gordon
When I logon I see SunOS 5.9 and then I select Unix shell from the
list of options.
I have little experience of using unix - so what exactly would be the
command line to use say locate or find?
Does
find -name mysqldump -type f
search the current and lower directories or is something missing?
>
>On my system (FreeBSD), "mysqldump" is in the same directory as
>"mysql", which is probably not the case on your server as it would
>have been found. It is possible that mysqldump is not installed
>on the server.
>
>
>>Is it possible to backup the database whilst at the mysql prompt?
>
>Yes, somewhat painfully. You can do something like "select * from
>table;" to get a copy of the table, in a format which is very
>difficult to put back, or something to extract the data in a better
>format, like CSV or tab-separated columns. Or you could issue a
>complicated query which cobbles together SQL insert statements out
>of data in the table (this is largely what mysqldump does; have you
>ever tried reading the output of mysqldump? It's a bunch of SQL
>statements. I know you can't find mysqldump on that server, but
>you might have used it elsewhere.)
Thanks for the above. I can export a table using phpmyadmin on another
server and so have seen the type of structure.
I can save a table on the SunOS server as an excel type of file using
a php file. Could I use that to replace the table should the table get
lost/corrupted/dropped?
Cheers
Geoff
Luuk
> On Thu, 29 Apr 2010 13:12:19 -0500, gordon...@burditt.org (Gordon
> Burditt) wrote:
>
>>> I am using PuTTY to connect to a server with mysql on it.
>>
>> What kind of server? Linux? BSD Unix? Windows? Solaris?
>>
>>> I can get to the mysql prompt once connected to the server by typing
>>> mysql and giving the password.
>>>
>>> If instead I type mysqldump I get an error message,
>>>
>>> mysqldump: command not found
>>>
>>> How do I find out where mysqldump is?
>>
>> You need to give mysqldump arguments (it's not very interactive, although
>> it will ask for a password), but first deal with finding
>> the program.
>>
>> If you have a program that can locate files by filename, such as
>> "locate" on BSD UNIX (and probably Linux), look for a filename of
>> the form "mysqldump*" (probably just "mysqldump" on UNIX, or
>> "mysqldump.exe" on Windows).
>
> Gordon
>
> When I logon I see SunOS 5.9 and then I select Unix shell from the
> list of options.
>
> I have little experience of using unix - so what exactly would be the
> command line to use say locate or find?
>
> Does
>
> find -name mysqldump -type f
>
> search the current and lower directories or is something missing?
>
correct...
# find / -xdev -name mysqldump -type f
/usr/bin/mysqldump
the '-xdev' makes find not looking on mounted drives (like my external
USB-drive)
--
Luuk
Geoff
I think I have a problem! When using
find / -xdev etc
I get permission denied for each directory!
Cheers
Geoff
Gordon Burditt
>>>
>>>mysqldump: command not found
>>>
>>>How do I find out where mysqldump is?
>>
>>You need to give mysqldump arguments (it's not very interactive, although
>>it will ask for a password), but first deal with finding
>>the program.
>>
>>If you have a program that can locate files by filename, such as
>>"locate" on BSD UNIX (and probably Linux), look for a filename of
>>the form "mysqldump*" (probably just "mysqldump" on UNIX, or
>>"mysqldump.exe" on Windows).
>
>Gordon
>
>When I logon I see SunOS 5.9 and then I select Unix shell from the
>list of options.
>
>I have little experience of using unix - so what exactly would be the
>command line to use say locate or find?
*IF* you have locate, which I don't see as a standard SunOS manual page,
locate mysqldump
will list all public files containing the string "mysqldump" in the
file name. If it can't find the command locate, just give up on
locate. You may get references to the manual page, program, or the
porn in someone's home directory named to disguise its contents in
addition to or instead of the actual program. Locate will usually
give irrelevant crap (but which does match your search pattern) in
addition to what you're really looking for.
'locate' doesn't list files not accessable by some fairly unprivileged
user, so if it doesn't list anything, that doesn't mean it's not there,
but if it is there, you may not have permission to use it.
>Does
>find -name mysqldump -type f
>search the current and lower directories or is something missing?
find / -name mysqldump -a -type f
will search the whole system. You may get error messages about
directories you can't access. You may substitute another path (or
more than one in a single command) for /, but /bin and /usr/bin are
places you should be searching. Use a path of "." for the current
directory (and stuff below it).
>>>Is it possible to backup the database whilst at the mysql prompt?
>>
>>Yes, somewhat painfully. You can do something like "select * from
>>table;" to get a copy of the table, in a format which is very
>>difficult to put back, or something to extract the data in a better
>>format, like CSV or tab-separated columns. Or you could issue a
>>complicated query which cobbles together SQL insert statements out
>>of data in the table (this is largely what mysqldump does; have you
>>ever tried reading the output of mysqldump? It's a bunch of SQL
>>statements. I know you can't find mysqldump on that server, but
>>you might have used it elsewhere.)
>
>Thanks for the above. I can export a table using phpmyadmin on another
>server and so have seen the type of structure.
>
>I can save a table on the SunOS server as an excel type of file using
>a php file. Could I use that to replace the table should the table get
>lost/corrupted/dropped?
My general approach to excel files and non-Microsoft software is
"you don't have a license from Microsoft to do that", for just about
any value of 'that'. If it's not coming from Excel or going TO Excel,
involving Excel in the process is likely to be more trouble than it's
worth.
mysqlimport doesn't take excel files as input. Neither does anything
that comes with MySQL, as far as I know. If you've got some program,
like Excel itself, that will export excel files as CSV or tab-delimited
files or SQL commands, *THAT*s the format to save stuff in. Then
feed CSV or tab-delimited files to "mysqlimport", or SQL commands
to the "mysql" command-line tool.
If you have got an application that takes excel files and will load
them into MySQL, fine, use it. I strongly recommend testing it
first, including restoring the table. Also be sure that such a
process will work for tables of the size you really want to work
with. PHP often limits process size to something like 8 meg, and
web servers often limit file upload size, so your save procedure
and/or your restore procedure may break when you get to a table
with a million rows, but work fine with a toy test case with a
small amount of data.
Lennart Jonsson
[...]
>
> Should I be able to use whereis?
It's been many years since I used Solaris, but if I remember correctly
there where a slocate command that worked prox like locate on Linux.
/Lennart
Gordon Burditt
>>/usr/bin/mysqldump
>>
>>the '-xdev' makes find not looking on mounted drives (like my external
>>USB-drive)
I advise against using -xdev unless you are sure that /usr/bin and
other popular places to install programs are on the root drive.
It may exclude a lot more than USB drives.
>I think I have a problem! When using
>
>find / -xdev etc
>
>I get permission denied for each directory!
If you really get permission denied for *EVERY* directory, including
your home directory and /tmp or /var/tmp, ask your admin to clean
up the mess. Your account is badly installed. If you get permission
denied for lots of directories but it comes up with a result anyway,
use the result. It won't do find any good to tell you where mysqldump
is if it's in a directory you can't access.
Perhaps you don't have permission to use mysqldump for a reason.
Ask your admin.
Gordon Burditt
>> list of options.
>>
>> Should I be able to use whereis?
>>
>> Cheers
>>
>> Geoff
>
>
>which mysqldump
Since the shell couldn't find mysqldump, it probably isn't in the
$PATH, so which is going to be useless here.
>I get
>/usr/local/mysql-5.1.40-osx10.5-powerpc/bin/mysqldump
>on my MacOS X system. If you don't see anything (e.g. "which foo" will
>return without typing anything but the shell prompt), then your shell's
>PATH isn't setup to access the mysql binaries.
One of the reasons for trying to find mysqldump is to figure out
what directory needs to be added to $PATH. Interestingly, he claims
that the command-line tool 'mysql' *does* work for him. 'mysql'
and 'mysqldump' are often installed in the same directory. It looks
less like a simple $PATH problem.
>I'd guess there's a
>reason for that, like the system administrator doesn't want regular
>users accessing these tools.
Or perhaps it's simply not installed. I'd say the command-line tool
'mysql' is more dangerous than mysqldump in the hands of inexperienced
users. For that matter, a shell is probably more dangerous than either,
and he has that.
>You _really_ need to contact the sysadmin
>and ask them for help. They're the ones that setup the system and
>created your account. They should be able to answer questions that we
>can only guess at the answers. That's why there's been so much back and
>forth on this topic.
>It's also clear that you need detailed hand-holding to do very basic
>things on a shell's command line interface.
>
>Let's go back to the very beginning. You posted a question on problems
>getting access to mysqldump on a Solaris system.
>What, specifically are you trying to do with mysqldump?
The OP stated he wants to back up a MySQL table (or whole database)
in a form that can be easily restored using the tools he has access
to (which includes the command-line tool 'mysql'). (And we'll
assume he does not have admin privileges, and file-based backup
strategies aren't really that good for MySQL anyway.)
>Why do you think you need to use it?
The OP wants to be able to back up his MySQL database. Other
methods, such as doing SELECT * FROM table and saving the output,
yield a format that is much more awkward to try to put back, should
the need arise. mysqldump was specifically created for this task,
and handles a number of special cases like special characters in
data fields equal to the field delimiter chosen that home-grown
strategies do not.
>What task are you trying to accomplish?
Backing up a MySQL database (and being able to restore it, if
necessary). This seems like a perfectly reasonable objective.
I will mention here also that although there are a number of GUI
interfaces to MySQL, many of them have the fatal flaw that they
work great for toy databases but when you go to production (read:
large! ) databases, you run into file upload limits, PHP memory
limits, PHP CPU execution limits, and other problems that administrators
are unlikely to be willing to raise. Or they just try to load the
whole table into memory and swap the machine to death.
Geoff
Burditt) wrote:
Gordon and others
Many thanks for all the suggestions.
No doubt the best thing is to go to the sys admin and this I will do
but have found that doing a full search for mysqldump
find / -name mysqldump -a -type f
found no reference to it.
I then tried
find / -name mysql -a -type f
as I know that the mysql command does work and this found mysql in
usr/sol2 and usr/sol2/bin
mysqldump was not in either directory.
Cheers
Geoff
Bodo
if you have a client (Mac, Windows, Linux) MySQLWorkbench might solve
your problem.
See: http://dev.mysql.com/downloads/workbench/
There you have a bunch of excellent tools to operate a MySQL database,
kind regards,
Bodo
PS: If there are other users on the server, they will kill you if they
find out you are treating it with "find / ...." ;-)
Peter H. Coffin
> Hi,
>
> if you have a client (Mac, Windows, Linux) MySQLWorkbench might solve
> your problem.
>
> See: http://dev.mysql.com/downloads/workbench/
>
> There you have a bunch of excellent tools to operate a MySQL database,
Hmm... Well, let's just say that you don't want to try dumping a
non-trivial database over a network socket... 4 GB coming over even LAN
speeds takes a while. (And no, I'm not saying 4GB is giant, so don't start
with any size wars. It is, however big enough that one does have to plan
for moving it.)
[..]
> PS: If there are other users on the server, they will kill you if they
> find out you are treating it with "find / ...." ;-)
Theoretically, it'll only happen once, right? Stick a "nice" in front of
it and don't worry about it.
--
Progress (n.): The process through which Usenet has evolved from
smart people in front of dumb terminals to dumb people in front
of smart terminals.
-- o...@burnout.demon.co.uk
Bodo
> Hmm... Well, let's just say that you don't want to try dumping a
> non-trivial database over a network socket... 4 GB coming over even LAN
> speeds takes a while. (And no, I'm not saying 4GB is giant, so don't start
> with any size wars. It is, however big enough that one does have to plan
> for moving it.)
>
> [..]
> > PS: If there are other users on the server, they will kill you if they
> > find out you are treating it with "find / ...." ;-)
>
> Theoretically, it'll only happen once, right? Stick a "nice" in front of
> it and don't worry about it.
Hi,
dumping 4 GB over a network socket....
You are right. May be other users trying to use same network might kill
you ;-)
No chance to use nice with workbench... ;-)
Hmmm. Maybe i should try my database with 90 GB as a worst case test ;-)
BTW: I use some sort of online compression with i was used to use with
Oracle.
I use mabackup from the GUI Tools to be able to restore single tables
with gui tools.
mysqldump failed here.
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/mysql-gui-tools-5.0/lib
mknod /tmp/fifo p
gzip < /tmp/fifo > backup.sql.gz&
/opt/mysql-gui-tools-5.0/mabackup -o/tmp/fifo -c server profilename
rm /tmp/fifo
a disadvantage of this method is: You have to adjust your profile if you
create/drop tables,
kind regards,
Bodo
Bodo
> Hmmm. Maybe i should try my database with 90 GB as a worst case test ;-)
Hi again,
couldn't resist and did a backup with MySQLWorkbench 5.2.19 BETA
Options used: compress, single transaction
Used engine on server: INNODB
883 Tables
490000000 rows
90 GB data without indexes
resulting backup file on client 122 GB
LAN saturation was 3 MB/sec
Server:
SLE-10-x86_64, MySQL Community 64 5.0.51a, FSI Primergy RX300S3, 16 GB
RAM, 2 * Xeon 5150 2.66 GHz
FibreCAT SX40 SAS, 8 * 3Gb/s 146 GB 15k HDD drives RAID 5
Server continued running with 100-300 QPS production
Client:
Apple iMac24, 2,16 gHz Core2 Duo, 2,5 GB RAM, 1,5 TB external HDD via
Firewire 800
backup time: 2:15 this is ... dammned fast
My compliments to the mysql developers!
backup via mabackup on the server takes whole night.
Conclusion: 4 GB should be done in less than an hour even on normal
hardware.
kind regards,
Bodo