How to get one complete column in mysql?

Question

I have a script that will fetch Last_Error in mysql slave in a particular interval of time and write that to a file.

To fetch Last_Error, currently I am using below command in the script.

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | grep -v "Warning: Using a password" |grep "Last_Error"

The challenge I am facing is that grep "Last_Error" will only show that particular line and not complete error. For example, one of my Last_Error look like below::

Last_Error: Error 'TRIGGER command denied to user 'user'@'xx.xx.xx.xx' for table 'table_name'' on query. Default database: 'Db_name'. Query: 'UPDATE 
                    table 
                 SET 
                    user_status=0 
                 WHERE 
                    contractid ='id.000.000''

and one another look like ::

Last_Error: Error 'Duplicate entry '117583' for key 'PRIMARY'' on query. Default database: 'labs'. Query: 'insert into User (a,b,c,d) values ("Other","",now(),"")

So basically I can't use A4 kind of switch with grep. Is it possible to get one complete column in mysql or any other work around?

score 1 · Answer 1 · answered Apr 21 '17 at 19:56

If you remove all newlines and whitespace from the output before grepping, it should work for you.

Use

mysql -u root -pxxxxxxxxx -e "show slave status\G;" 2>&1 | tr -d '\n\r \t' | grep -v "Warning: Using a password" | grep "Last_Error"

where the tr command removes the newline, carriage return, space, and tab characters from the input.

How to get one complete column in mysql?

1 Answers1