Restoring MySQL Databases

[thedust2010]

A database appears to have disappeared from our web server. We have a cron job that runs daily (a default command in Plesk):

/usr/local/psa/bin/mysqldump.sh >/dev/null 2>&1

My question is how to do we recover MySQL data that is backed up? Where is a good resource for this? I've searched around with little to no luck thus far...

 

[DjFarout]

Option 1) Download Mysql Administrator (http://dev.mysql.com/downloads/administrator/1.1.html) and use it to restore your database.. Once you install it transfer the sql file from your server and use the restore function on the Mysql Admin program

Option 2) use "mysqlimport" via SSH/command line
">mysqlimport [options] db_name textfile1 [textfile2 ...] "
For more info of mysqlimport goto (http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html)


I think Option 1 is a little easier and gives you a nice GUI

I would also take a backup of the current database before you do this! just in case!!


Have Fun!

 

[thedust2010]

I am trying to use the MySQL Administrator, but I'm not able to log in with my root account. Do I need a MySQL admin account? I don't believe I have these details... where might I be able to find them or reset the password?

 

[DjFarout]

There is no root account.. plesk changes it to admin and use the PSA admin password!

If that doesnt work, do this to reset the admin password

1. Take down the mysqld server by sending a kill (not kill-9) to the mysqld server. The pid is stored in a `.pid' file, which is normally in the MySQL database directory:
shell> kill `cat /mysql-data-directory/hostname.pid`
You must be either the Unix root user or the same user mysqld runs as to do this.

2. Restart mysqld with the --skip-grant-tables option.

3. Set a new password with the mysqladmin password command:
shell> mysqladmin -u root -p'mynewpassword'

4. Now you can either stop mysqld and restart it normally, or just load the privilege tables with:
shell> mysqladmin -h hostname flush-privileges

5. After this, you should be able to connect using the new password.

Alternatively, you can set the new password using the mysql client:

1. Take down and restart mysqld with the --skip-grant-tables option as described above.

2. Connect to the mysqld server with:
shell> mysql -u root mysql

3. Issue the following commands in the mysql client:
mysql> UPDATE user SET Password=PASSWORD('mynewpassword') WHERE User='admin';
mysql> FLUSH PRIVILEGES;

4. After this, you should be able to connect using the new password.

5. You can now stop mysqld and restart it normally.


Have fun!!