Mysql unresponsive after DB Import


Here’s your scenario. You’ve installed mysql on your local machine. Everything was working fine. Then you decided to download a few remote mysql database dumps and update your local databases. But as soon as you import them into your local machine your mysql dies. You might get an error that says that mysql has gone away, or maybe a message that says your access is denied.

Access denied for user 'root'@'localhost' (using password: YES)

so how do you fix it? In my case I delete all of mysql and reinstall. Everything looks good, add a few dbs, import some dbs – all is working fine, then I import another db and then BAM! “Access denied for user ‘root’@’localhost’ (using password: YES)” – WHAT THE . . .?!?!?!

I’ve had this issue for months. I’d work for awhile then do an update and then mysql would just take a crap. But why?

The Solution

The dump I was using included 3 databases that were overwriting my local db; Mysql, Performance_schema, and Information_schema. It turns out the –all-databases script I was running on my remotes was including those 3 databases as well as all of my site dbs. Whenever I imported them to my local machine (all the databases) my local mysql, performance_schema and information_schema were being overwritten which meant that things like mysql users were being overwritten as well. I’m pretty positive this was my problem all along.

If you are making your dumps using msyqldump –all-databases then you might want to jump down to the Solution part

Other hypothesis

Mysql version

I had thought it might be some other culprit – something else besides the import, but none of them panned out. One issue I thought it might be was my local mysql version. The remote systems all were running mysql 5.5 and my local was using mysql 5.6. While this discrepancy might have some unintended behaviors I don’t think it would be enough to bring mysql to a grinding halt.

Mysql instances

Another common issue is having multiple versions of mysql running on your local environment. I work on a mac and IOS ships with mysql and apache. I had also been setting my local environments up with homebrew and one of the times mysql died corresponded with an update being sent by Apple for High Sierra. While having multiple running instances can create connections issues it wasn’t an issue that persisted. You can see how many processes are running of mysql with this command in terminal

ps -ax | grep mysqld

You can kill any of those processes running using the id and the kill command

kill -9 {id}

kill -9 123 // would kill process 123

I ended up removing the homebrew instances and installed MAMP. Everything would work temporarily then I’d do an update and then the local mysql would die. So while you could be having issues with multiple installs, it isn’t a persistent problem

Here is an article on fixing broken mysql installs – it is for maverick but it works on sierra and high sierra – link

Fixing the DB dump


The quick answer is to export each db you want to import individually. If you’d prefer a more automated replacement for mysqldump –all-databases then you could create a script using the method outlined here. The solution involves querying the mysql and grabbing all of the databases and outputting their names unless it is mysql, performance_schema or information_schema

mysqldump --databases `mysql --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/AllDbDump.sql

You might need to include your user and password in the command. That would look like this

mysqldump -uroot -ppassword --databases `mysql -uroot -ppassword  --skip-column-names -e "SELECT GROUP_CONCAT(schema_name SEPARATOR ' ') FROM information_schema.schemata WHERE schema_name NOT IN ('mysql','performance_schema','information_schema');" >` >/mysql/backup/AllDbDump.sql

You would replace root with your user

 -uuser

would be for user ‘user’ and

 -p1234 

is for password ‘1234’. Make sure there isn’t a space. Also make sure that you include the user and password for the subquery portion.

You may also like...

Leave a Reply

Your email address will not be published.