Mysql – How to get one complete column in thesql

linuxMySQLstring-representation

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?

Best Answer

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.

Related Question