MySQL, Where Art Thou?
I am using MySQL for various things - my wiki is running against it, my local Sonar installation uses it and often I run Hibernate unit tests against it. I remember installing MySQL via macports, but since then I have not touched it. Well, until a couple of days ago, where I got strange error messages from my JDBC driver when trying to connect to a specific schema in my database. I still could connect to MySQL using the mysql command line though. I wanted to know more. I needed to find the mysqld configuration and error logs. But where?
I haven't looked at the MySQL installation for months and I was not sure where macport installed it. I remembered that the MySQL configuration file is called my.cnf or something *.cnf. I tried a quick:
Both commands gave a uge amounts of hits and I was still not sure where to look at. There must be a better way to find out which configuration the database is using and where the data is stored. I started some digging. Turns out if MySQL is still running, you can simplly issue a SQL statement to get most information you need. The key is the show command. You might already know show databases and show tables, but do you know:
This command will tell you almost all you need to know, amongst others:
Now I was almost there. I had found the data directories and the log file. A quick look in the log revealed something along these lines:
Digging deeper I was able to restore now my database, but I still did not have a direct way to find out which configuration was used. What if the database would not have been running? Turns out you can also simply run:
Somewhere hidden in the output you will find:
BTW, if you are looking for a MySQL client for Mac - there is Sequel Pro. Not perfect, but good enough for most day to day tasks.
So long,
Hardy
I haven't looked at the MySQL installation for months and I was not sure where macport installed it. I remembered that the MySQL configuration file is called my.cnf or something *.cnf. I tried a quick:
> locate my.cnf
> locate *.cnf
Both commands gave a uge amounts of hits and I was still not sure where to look at. There must be a better way to find out which configuration the database is using and where the data is stored. I started some digging. Turns out if MySQL is still running, you can simplly issue a SQL statement to get most information you need. The key is the show command. You might already know show databases and show tables, but do you know:
show variables;This command will tell you almost all you need to know, amongst others:
log_error /opt/local/var/db/mysql5/Sarmakand.local.err
pid_file /opt/local/var/db/mysql5/Sarmakand.local.pid
datadir /opt/local/var/db/mysql5/
Now I was almost there. I had found the data directories and the log file. A quick look in the log revealed something along these lines:
InnoDB: Cannot find table foo from the internal data dictionary
InnoDB: of InnoDB though the .frm file for the table exists. Maybe you
InnoDB: have deleted and recreated InnoDB data files but have forgotten
InnoDB: to delete the corresponding .frm files of InnoDB tables?
Digging deeper I was able to restore now my database, but I still did not have a direct way to find out which configuration was used. What if the database would not have been running? Turns out you can also simply run:
> mysql -helpSomewhere hidden in the output you will find:
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /opt/local/etc/mysql5/my.cnf ~/.my.cnf
BTW, if you are looking for a MySQL client for Mac - there is Sequel Pro. Not perfect, but good enough for most day to day tasks.
So long,
Hardy