4

How to log the error and warning while restoring the SQL dump on MySQL ?

Following are the steps we were doing.

mysql> CREATE DATABASE dbname;

mysql> USE dbname;

mysql> SOURCE dbdumpname.sql
Chaminda Bandara
  • 547
  • 6
  • 17
Aha
  • 389
  • 3
  • 6
  • 18

2 Answers2

6

To redirect MySQL errors and warnings to a log file, restore the SQL dump from bash not from a MySQL prompt.

mysql -u root -p db_name < dumpfilename.sql > /var/tmp/mysqldump.log 2>&1

If you need to supresss foreign key checks, create a shell script named mysql-import.sh and put the following contents in it:

#!/bin/bash

mysql -u root -p'password' -h hostname db_name << EOF

CREATE DATABASE dbname;
USE dbname;
SET foreign_key_checks=0;
SOURCE dbdumpname.sql;

EOF

Then chmod +x mysql-import.sh to make the script executable and run the script as,

./mysql-import.sh > /var/log/mysqldump.log 2>&1

This script will run the code that you were running in MySQL, but with the ability to redirect output to a log file, since it can be invoked from the shell.

Evan Donovan
  • 123
  • 5
Sachin Divekar
  • 2,445
  • 2
  • 20
  • 23
  • Ok How can I set the foreignkey_checks=0 with the restore command. – Aha Dec 02 '11 at 09:47
  • @Arun I am not getting your question. – Sachin Divekar Dec 02 '11 at 10:12
  • While restoring the dump there are possibility of exception due to foreignkey_checks. So we are doing the same as follows.mysql> CREATE DATABASE dbname; mysql> USE dbname; mysql > set foreign_key_checks=0; mysql> SOURCE dbdumpname.sql; How can I do this with mysql -u root -p db_name < dumpfilename.sql > /var/tmp/mysqldump.log 2>&1 – Aha Dec 05 '11 at 05:05
  • @Arun I have updated the answer. Check if it is working. – Sachin Divekar Dec 05 '11 at 06:09
3

The mysql client offers a useful --tee <tofile> option. Either enable it at startup, or interactively in the prompt.

mysql> tee file.log
Logging to file 'file.log'
mysql> \W
Show warnings enabled.
mysql> use database;
mysql> source dump.sql;

Note the description in the docs

All the data displayed on the screen is appended into a given file. [..] Tee functionality works only in interactive mode.

wbob
  • 176
  • 4