A broken VIEW was caused by the view’s definer being non-existent. In this particular system, backups are done by replicating all the machines (production, development, test, etc) to one server and doing cold physical backups off that server, which currently has 12 instances running.
So in order to find on what machine the user might still be defined, I went to the backup server. All the data directories are in one path, ie:
instance 1 has a datadir of /data/mysql/instance1 instance 2 has a datadir of /data/mysql/instance2
Now, the unix tool
strings can be used against many types of files. In particular, though, you can use
strings on the mysql/user.MYD file to see the username, host, and password hash. (note that
strings only shows strings longer than 3 characters, so if your host or username is 3 characters or less, it will not show up in the output of
strings. You can change this with the
-n option to
$ cd /data/mysql/ $ strings -f */mysql/user.MYD | grep username instance5/mysql/user.MYD: username*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX instance7/mysql/user.MYD: username*XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX $
While writing this tidbit up, I realized I could have easily run
grep and gotten the same results:
$ grep username */mysql/user.MYD Binary file instance5/mysql/user.MYD matches Binary file instance7/mysql/user.MYD matches
So do not underestimate the power of basic tools such as strings and grep. They can really help you! (I often use
strings mysql/user.MYD to see if a particular mysql user has been set up, especially when I cannot seem to login. This way I can know whether or not I am typing an incorrect password, or if the user just does not exist at all.)
Interested in working with Sheeri? Schedule a tech call.