Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We retake the management of our server and now we need to make some reverse engineering. We gave to an external company the development of our new Website and they need a dump of database from old site.

The Webserver is on Debian with Apache and MySQL running. I locate the server running on localhost port 3306. The server is on production.

Tried a "longshot" mysqldump :

web:/home/user# mysqldump -P 3306 -h localhost -u root -p db
Enter password: 
-- MySQL dump 10.11
--
-- Host: localhost    Database: db
-- ------------------------------------------------------
-- Server version       5.0.32-Debian_7etch12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
mysqldump: Got error: 1049: Unknown database 'db' when selecting the database

I have the root access to the machine. Here my questions:

How can I retrieve the information about the existing databases ?
How can I retrieve access to it (user/pass) ?
How can I make a proper dump ?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

If you don't have access to mysql anymore you can reset the mysql root password in a ssh session (as root) by first stopping the mysqld socket process by killing the .pid file. Don't use kill -9.

kill `cat /mysql-data-directory/host_name.pid`

Now create a text file with the following Mysql query. Each on a new line. Save it to something like /home/me/mysql-init

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

Now start mysql with the following command:

mysqld_safe --init-file=/home/me/mysql-init &

You just regained access to mysql with the root user and a new root password. Don't forget to delete the mysql-init file.

With this login information you can show all databases on the server. First login in mysql with the new login information:

mysql --user=root --password=your-password

Now view all databases:

mysql> show databases;

And now you can dump any database you want into a file by using:

mysqldump -h localhost -u root -p databasename > dumpfile.sql

Or just all databases:

mysqldump -h localhost -u root -p --all-databases > all_dbs.sql
share|improve this answer
    
bingo, mysqld_safe is your best friend in this situation –  David Houde Jun 7 '13 at 10:47

The database is obviously not named db.

Why don't you just dump all databases?

mysqldump --all-databases ......
share|improve this answer
    
web:/home/gps# mysqldump --all-databases mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect web:/home/gps# mysqldump -P 3306 -h localhost -u root --all-databases mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect When I try to dump all –  cgasp Jun 7 '13 at 7:40
    
You need to use all of the rest of the options. –  Michael Hampton Jun 7 '13 at 7:41
    
web:/home/gps# mysqldump -P 3306 -h localhost -u root --all-databases mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when trying to connect Seems I don't have the right to connect –  cgasp Jun 7 '13 at 7:44
1  
You keep forgetting your -p option. Why? Have you not read the documentation? Do you not have a system administrator? –  Michael Hampton Jun 7 '13 at 7:44
    
First, we don't have the password of database. Second, unfortunately we don't have hardcore skill of sysadmin due we are a network engineers company. –  cgasp Jun 7 '13 at 7:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.