Find and dump a mysql database

Matsaki

Registered
I have a server (in another country) and I don't know Unix/Linux so a friend was setting it up for me. Now my friend have disappeared and I did not even have the root passwd to the server.

The hosting company have now helped me to reset the passwd and I want to migrate all data and move to a web hosting.

I found out that the database is in ./var/lib/mysql/mydatabase So how do I dump it and copy it to my home directory so I can download "mydatabase" via my FTP client?


Maybe a difficult question but I hope somebody can help.

The OS on the server is Debian.

Thanks!
 
Last edited:
The simplest way:

Code:
mysqldump --opt --all-databases > dump.sql

This will produce a file called "dump.sql" containing ALL databases in the mysql server into the current working directory into a file called "dump.sql" that you can do several things with:

1) copy it to your own server
2) gzip/tarball it (as it may be quite large) and copy it
3) create a remote connection to your home mysql server and import it over the 'net

I would recommend number 2, using the "gzip -t" option to ensure the gzip operation was smooth before deleting the original .sql dump file.

You can then import that .sql file into another mysql database server by doing this on that server:

Code:
mysql -uusername -p < dump.sql

...where "username" is the mysql username you wish to use to do the import.

There are other switches you can use to simply dump a single database instead of all databases on the mysql server.

You may want to do some reading up on this type of thing first, as there are a TON of options you can use and a bunch of things you need to be aware of... the first of which is, "do you also need to move from one mysql server to another the user accounts for the old server?"

If so, read on:

http://www.psoft.net/HSdocumentation/sysadmin/moving_mysql_accounts.html

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
 
Thank you. And excuse me for my bad knowledge in this matter. That's why I'm migrating to a webhotell where there is a cPanel so I don't have to use any programming skills to setup databases, email accounts etc.

1. When I get to the catalog where I see my database "mydatabase" can I just gzip -t mydatabase and the make a cp to my home directory so I can download it from my FTP?

2. If my database "mydatabase" account had several databases how do I then chose the one I want to download? I want it as a .sql file as I know how to import and export this format to the new server/webhotell.

3. Also even though I am logged in as root in the server and I try using
Code:
mysqldump --opt --all-databases > dump.sql
I get the error:

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect
And I don't know the root password for MySQL.
 
Last edited:
1) No, you can't just gzip the files on the mySQL server and then copy them to your own mySQL server -- due to permissions, etc. You must use mysqldump for safety and completeness.

2) I'm not sure I understand... a "database" cannot have another "database" inside it -- you have a mySQL server, and you have one or more databases, each with zero or more tables within each database. If you just want to dump a single database, you can use:

Code:
mysqldump --opt --databases [databasename] > dump.sql

...where "[databasename]" is the name of the database you want to dump.

3) Your UNIX user account and your mysql user account are two completely different things. Just because you're logged in as "root" on the UNIX/Linux server does not mean that's the same "root" account for mySQL. These are two separate sets of credentials.

With that being said, you don't have to log into the mySQL database with the root account (the mySQL root account, not the UNIX/Linux root account)... you can be logged in as any user that has "read" access to the database you want to dump.

If you know the mySQL user that you want to use to dump the database, use this command with the proper username and password:

Code:
mysqldump --opt --databases [databasename] -u [username] -p [password] > dump.sql

...where "[username]" and "[password]" are the mySQL user account username and password you wish to use.
 
Very strange. The setup of the server is to be as secure as possible. So when I should connect to my database I had to do that through a SSL tunnel. When I connected to my database I used a password of corse. But as my site which uses the database is on the same server and are still working, it's using another password.

Non of them is accepted when I try to login to MySQL database logged in to the server?
 
Strange.

So, you ssh into your server there at your webhost, then you connect to mysql using a username and password of some sort.

What happens if you ssh into your webhost, then try to launch the mysql client like so:

Code:
mysql -u[username] -p[password]
...where "[username]" and "[password]" are your mysql username and password?
 
Now I managed to log in to the database. But can i change so I don't have to log in to the database via SSL, but doing the "normal way"?

Now I have to first log in to the server from the terminal with
ssh -L 3306:127.0.0.1:3306 mydatabase@myserver
And then from my MySQL local client (Navicat) using Hostname: 127.0.0.1 and Port: 3306

Thank you so much for the help so far :)
 
I managed to dump the database
> dump.sq
l but that only dumped the structure and not structure and data as I want.

Problem solved, but still hoping for a answer about how to connect remotely via port 3306 as above.
 
Last edited:
Back
Top