Home / server / Questions / 514030
Accepted
cgasp
Asked: 2013-06-07 23:35:13 +0800 CST

How to regain access to myql server and dump database?

  • 772

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 ?

linux

2 Answers

  1. Best Answer

    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
    • 3

  2. The database is obviously not named db.

    Why don't you just dump all databases?

    mysqldump --all-databases ......
    • 2