Mysql binlog path from mysql console

Question

Is there a way to know the bin log files path from mysql console as we can know whether its ON or OFF by using

Select * information_schema.GLOBAL_VARIABLES
where variable_name like '%log_bin%'. 

Swenson

Answer 1

Use this to show on/off:

SHOW VARIABLES LIKE 'log_bin'

Also:

SHOW GLOBAL VARIABLES LIKE '%bin%'

Or

SHOW SESSION VARIABLES LIKE ...

More Information: (notice that some of these values and results changed from 5.5 to 5.6!)

http://dev.mysql.com/doc/refman/5.5/en/show-master-status.html

mysql > SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73       | test         | manual,mysql     |
+---------------+----------+--------------+------------------+

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+

Angeli

No,Iam asking is there a way to know where the binlog.0000xx is present from console instead of my.cnf like mysql> SELECT * from information_schema.GLOBAL_VARIABLES where variable_name like 'slow_query%'; +---------------------+--------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +---------------------+--------------------------------------+ | SLOW_QUERY_LOG | ON | | SLOW_QUERY_LOG_FILE | /usr/local/mysql/data/mysql_slow.log | +---------------------+--------------------------------------+ – Swenson

It does not look like there is an absolute path. But you can parse mysql config with PHP or bash, or just hardcode to /var/log or something – Angeli

Answer 2

From the docs regarding log-bin"

Setting this option causes the log_bin system variable to be set 
to ON (or 1), and not to the base name. This is a known issue; 
see Bug #19614 for more information.

There is a workaround using mysqld instead in the Bug 19614 which I modified a bit. If you are scripting, you can use from the mysql client (which I found a bit tedious to do, see the next workaround):

mysql >\! dirname $(mysqld --help --verbose 2> /dev/null | egrep "^log-bin " | grep -o "\/.*")

Looks like there is a patch submitted by Mark Callaghan, and it was never committed. There is a function in WP5465 (which is the work in progress for this patch), however it didn't work properly for me, as the location of the logs can be different across setups.

Inland