2

How to log the error and warning while restoring the sql dump on MySql ? Following are the steps we are doing.

mysql> CREATE DATABASE dbname;

mysql> USE dbname;

mysql> SOURCE dbdumpname.sql

4

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.

4
  • Ok How can I set the foreignkey_checks=0 with the restore command. – Aha Dec 2 '11 at 9:47
  • @Arun I am not getting your question. – Sachin Divekar Dec 2 '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 5 '11 at 5:05
  • @Arun I have updated the answer. Check if it is working. – Sachin Divekar Dec 5 '11 at 6:09
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.