-1

I've got ~ 30 sites worth of databases data on a volume on my server in the following directory /mnt/volume0/var/lib/mysql

The directory contains a 6.2G ibdata1 file and directories containing .frm files for each of the websites.

I'm trying to get dumps of the databases so I can move them to another server, however accessing mysql only shows the default databases (as datadir in /etc/mysql/mysql.conf.d/mysqld.cnf is set to /var/lib/mysql)

I've tried changing the datadir in /etc/mysql/mysql.conf.d/mysqld.cnf to /mnt/volume0/var/lib/mysql but mysql fails to start.

It was complaining about "failed to set datadir" which I fixed by running chown mysql:mysql mysql/ -R on /mnt/volume0/var/lib/mysql but now it's complaining about Failed password for root from xxx.xx.xxx.xx port 19192 ssh2

Any help is greatly appriciated!

Thanks

garyh
  • 2,782
  • 1
  • 26
  • 28
Sammy
  • 1
  • 1
  • The best way is to make a mysql dump. But if you arent able to start your old installation, then its important that you have the same MySQL version in your new installation. – A. Blub Jun 29 '17 at 10:33

2 Answers2

0

You can try to reset your password

mysqld_safe --skip-grant-tables

if you have here an error, then you have a bigger problem

mysql --user=root mysql
update user set Password=PASSWORD('your new password') where user='root';
flush privileges;
exit;
A. Blub
  • 792
  • 1
  • 5
  • 10
  • Before seeing your answer, I tried uninstalling and reinstalling MySQL. This has now allowed me to changed datadir (as detailed here: https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04) to the mysql directory on the volume (/mnt/volume0/var/lib/mysql) but mysql still isn't picking up the databases. Any ideas? Thanks – Sammy Jun 29 '17 at 10:57
  • did you wrote [mysqld] in a line before your datadir setting? – A. Blub Jun 29 '17 at 11:44
0

I ended up solving this issue by replacing the contents of /var/lib/mysql/ on my local machine with the contents of /mnt/volume0/var/lib/mysql/ (from the server).

I then encountered an error whilst trying to start MySQL Table 'performance_schema.session_variables' doesn't exist which I solved by upgrading MySQL on my local machine and restarting: https://stackoverflow.com/a/20262826/8231049

Now I'm able to dump the databases from my local machine :)

Edit: Can't accept my answer before waiting 2 days. Will do then.

Sammy
  • 1
  • 1