MySQL: Bulk import and logging the warnings to a file

Platform: Any UNIX*

If you are working on a small set of data or with fewer tables and if you notice that the DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE operation has generated few warnings then you can just execute the “SHOW WARNINGS” SQL command to display the exact warning messages generated during the last DML operation.
But “SHOW WARINGS” shows nothing if the last statement used a table and generated no messages.
SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session.
“SHOW WARINGS” can certainly help you in grabbing the warnings but what if you are loading a huge dump file or performing a batch operation and at the middle of load operation MySQL reports that the last DML completed with 500000 warnings? Certainly not sounds good if you are serious about your data.

Query OK, 3000000000 rows affected, 500000 warnings (xxxxx.xxx xxxx)
Records: 3000000000 Duplicates: 0 Warnings: 500000


So how do you grab those warnings?
You can use the --show-warnings parameter with the “mysql” command line utility which causes warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

/path/to/mysql --show-warnings –uUserName –pPassword DBNAME < /path/to/dump_file.sql >> /path/to/load_warnings.log 2>&1
The 2>&1 will write standard output and standard errors to a file (/path/to/load_warnings.log)

You may not see warnings
(i) If max_error_count is set to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.
(ii) if sql_notes session variable is set to 0 to cause Note-level warnings not to be recorded.


##On EE
ttp://www.experts-exchange.com/Database/MySQL/Q_26835461.html

MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
 
DELIMITER $$

DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(64);

DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
 FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';


DECLARE CONTINUE HANDLER FOR NOT FOUND


SET no_more_rows = TRUE;
SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP
    FETCH my_cur
      INTO my_table;

     IF no_more_rows THEN
         CLOSE my_cur;
         LEAVE the_loop;
     END IF;


     SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table);
      PREPARE s1 FROM @tmp_sql;
      EXECUTE s1;
     DEALLOCATE PREPARE s1;

     SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;

SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";


END$$
DELIMITER ;

Usage:

call CopySchema('dba','dba_dummy');

mysqlhotcopy

mysqlhotcopy is a Perl script that uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It is the fastest way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. It runs on Unix and NetWare.


The options can be viewed by executing the following command:
shell>mysqlhotcopy --help


Backup one/many database at once

shell>mysqlhotcopy [options] db_name1...db_nameN /path/to/backup_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:

shell>mysqlhotcopy [options] db_name./regex/

The regular expression for the table name can be negated by prefixing it with a tilde (“~”):


shell>mysqlhotcopy [options] db_name./~regex/

For complete info on mysqlhotcopy
http://dev.mysql.com/doc/refman/5.1/en/mysqlhotcopy.html

MySQL - Shell command to list installed MySQL packages

To know which MySQL packages have been installed on linux environment then the command is

shell>rpm -qa --queryformat "%{NAME}-%{VERSION}-%{RELEASE} (%{ARCH})\n" grep -i mysql