0

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 ??????

Edited by AndrisP

3
Contributors
6
Replies
36
Views
1 Month
Discussion Span
Last Post by AndrisP
0

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.

1

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

Votes + Comments
On many MySQL installs, the logrotate is included. If not, pick a rabbit hole and choose what drink you want. (Yes, Alice in Wonderland references.)
0

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:

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.

Edited by cereal

0

@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

Edited by AndrisP

0

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.

0

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

Edited by AndrisP

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.