MySQL run script and log to dynamic file name

[AndrisP]

Is the method in MySQL to set in SQL script dynamic log file name?
e.g in Oracle PL/SQL

set define on
column sdate new_value sdate
select to_char(sysdate,'YYYY.MM.DD_HH.MI') sdate from dual;
spool 'logs/install_&sdate..log';

e.g. in PG/SQL

\o ./logs/install_`date +"%Y-%m-%d_%H%M"`.log

but I cant find similar in MySQL

\T ??????

[rproffitt]

This is a rabbit hole. How deep you want to go is up to you. For me I found log rotation to suffice and here's the link I used to refresh my memory on this subject.

But there are many rabbit holes here. That is, MySQL has more than one log. I think it's best to find and use a log rotation script and use that to rotate/maintain/control log growth.

Read https://dev.mysql.com/doc/refman/5.7/en/log-file-maintenance.html for examples.

[AndrisP]

That's not what I'm looking for - its for install script

[cereal]

Hi Andris,

you may want to use SELECT ... INTO OUTFILE 'file_name_here' and perhaps use CONCAT() to create the query, if you do:

set @file = concat('/tmp/file_', UNIX_TIMESTAMP(), '.log');
select 'hello' into outfile @file;

It will not work, so you have to do:

set @file := concat("/tmp/file_", UNIX_TIMESTAMP(), ".log");
set @query := concat("select 'hello' into outfile '", @file, "'");
prepare stmt from @query;
execute stmt;
deallocate prepare stmt;

As suggested here:

• https://stackoverflow.com/a/13549427
• https://dev.mysql.com/doc/refman/5.7/en/select-into.html

But it's mandatory that the destination file does not exists, otherwise the query will fail, this is done to avoid overwriting files with random content. The alternative, if you want to append results, for example, is to use mysqldump or something like this:

mysql -uVAR -pVAR -e "SELECT 'hello';" > /tmp/random.log

To execute, if using PHP, from exec().

// Edit
But looking at previous answers, now I'm not sure is this you where searching for.

[AndrisP]

@cereal - this do not workd inside SQL script file. I want to set command in script file

tee ./dinamic_file_name_here.log

or similar command

\T ./dinamic_file_name_here.log

but file name can not set dynamically to run script from command prompt e.g.

\T ./dinamic_file_name_here.log
\. ./make_tables.sql
\. ./install_routines.sql
\. ./install_triggers.sql
\. ./insert_start_setup.sql

[rproffitt]

More to the point:

Log control at runtime. The system variables associated with log tables and files enable runtime control over logging:

The global log_output system variable indicates the current logging destination. It can be modified at runtime to change the destination. 

From https://dev.mysql.com/doc/refman/5.7/en/log-destinations.html

However my bet is you'll have to do the flush command for MySQL to start using the new log file.

[AndrisP]

Not so beautiful but I found another solution - add --tee to make connection

mysql -u <username> -p -D <database> --tee ./install_$(date +"%Y-%m-%d_%H-%M").log

MySQL does not support dynamic filename in SQL script