This page is a means to exchange kowlede and hint about MySQL

• Table of contents
• Maintenance Scripts
  • mysqldump-tables.sh
  • mysqldump-dbfilter.sh
  • dump-cdmportal-dbs.sh
    • restore-dataportal-dumps.sh
    • bulk_rename_tables.sql

see also MySqlFaq for further MySql related help

Maintenance Scripts¶

mysqldump-tables.sh¶

Dump only a subset of the tables of a database:

#!/bin/sh

#####################################################
#                EDIT THESE SETTING                 #

BACKUPDIR="db_export";

DATABASE="drupal_dataportal"

# the pattern to search for. eg: "my_site_%"
TABLEPATTERN="cdm_cichorieae_node_%";

####################################################


MYSQLDUMP="$(which mysqldump)";
DATEPREFIX="$(date +%Y-%m-%d_%H-%M-%S)";
FILENAME_FPART="$(echo $TABLEPATTERN | sed 's/%//g')";
FILENAME="${DATEPREFIX}_${DATABASE}.${FILENAME_FPART}.sql.gz";

MYSQL=$(which mysql);
#TAR=$(which tar);
GZIP=$(which gzip);
#NICE=$(which nice);
#EGREP=$(which egrep);

echo "will dump all tables matching $TABLEPATTERN from $DATABASE into $FILENAME";
read -p "mysql user:" MYSQL_USR;
read -s -p "mysql password:" MYSQL_PWD;

mkdir -p "$BACKUPDIR";
#echo $MYSQL -u $MYSQL_USR --password="$MYSQL_PWD" -Bse "show tables from $DATABASE LIKE '$TABLEPATTERN'";

TNS=$($MYSQL -u $MYSQL_USR --password="$MYSQL_PWD" -Bse "show tables from $DATABASE LIKE '$TABLEPATTERN'");
echo "\ntables:$TNS"

$MYSQLDUMP -u $MYSQL_USR --password="$MYSQL_PWD" --single-transaction $DATABASE $TNS | $GZIP -c > "$BACKUPDIR/$FILENAME";

download mysqldump-tables.sh

mysqldump-dbfilter.sh¶

Dump a filtered set of all databases:

date +%Y-%m

download mysqldump-dbfilter.sh

dump-cdmportal-dbs.sh¶

#!/bin/bash

###########################################
MYSQL_USER=admin
DATE_STR=$(date +%Y-%m-%d-%H%M)
DBSET=a
###########################################

echo "Starting to dump the $DBSET set of dataportal databases";
echo "password for admin@mysql ";
read -s pwd

echo "cdm_edit_cichorieae_$DBSET ..";
mysqldump -u admin --password=$pwd cdm_edit_cichorieae_$DBSET > $DATE_STR-cdm_edit_cichorieae_$DBSET.sql
echo "cdm_edit_palmae_$DBSET ..";
mysqldump -u admin --password=$pwd cdm_edit_palmae_$DBSET > $DATE_STR-cdm_edit_palmae_$DBSET.sql
echo "cdm_edit_diptera_$DBSET ..";
mysqldump -u admin --password=$pwd cdm_edit_diptera_$DBSET > $DATE_STR-cdm_edit_diptera_$DBSET.sql
echo "creating tar.gz archive ...";
tar -czf $DATE_STR-cdm_edit_portals_$DBSET.tar.gz $DATE_STR-cdm_edit_cichorieae_$DBSET.sql $DATE_STR-cdm_edit_palmae_$DBSET.sql $DATE_STR-cdm_edit_diptera_$DBSET.sql

rm $DATE_STR-cdm_edit_*.sql

echo "done!";
done

restore-dataportal-dumps.sh¶

restore data portal dumps:

#!/bin/bash
###########################################
DATE_STR=2009-10-22-1308    # should match the form of: date +%Y-%m-%d
DBSET=a
###########################################

echo "Starting to restore the $DBSET with the dumps in $DATE_STR-cdm_edit_portals_$DBSET.tar.gz";
read -p "MySQL User:" MYSQL_USER
read -s -p "MySQL Password:"  pwd
tar -xzf $DATE_STR-cdm_edit_portals_$DBSET.tar.gz

echo "restoring cdm_edit_cichorieae_$DBSET ..";
echo "DROP DATABASE cdm_edit_cichorieae_$DBSET; CREATE DATABASE cdm_edit_cichorieae_$DBSET;" | mysql -u $MYSQL_USER --password=$pwd
mysql -u $MYSQL_USER --password=$pwd cdm_edit_cichorieae_$DBSET < $DATE_STR-cdm_edit_cichorieae_$DBSET.sql

echo "restoring cdm_edit_palmae_$DBSET ..";
echo "DROP DATABASE cdm_edit_palmae_$DBSET; CREATE DATABASE cdm_edit_palmae_$DBSET;" | mysql -u $MYSQL_USER --password=$pwd
mysql -u $MYSQL_USER --password=$pwd cdm_edit_palmae_$DBSET < $DATE_STR-cdm_edit_palmae_$DBSET.sql

echo "restoring cdm_edit_diptera_$DBSET ..";
echo "DROP DATABASE cdm_edit_diptera_$DBSET; CREATE DATABASE cdm_edit_diptera_$DBSET;" | mysql -u $MYSQL_USER --password=$pwd
mysql -u $MYSQL_USER --password=$pwd cdm_edit_diptera_$DBSET < $DATE_STR-cdm_edit_diptera_$DBSET.sql


echo "deleting sql dumps ...";
rm $DATE_STR-cdm_edit_*.sql

echo "done!";

download restore-dataportal-dumps.sh

bulk_rename_tables.sql¶

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `bulk_rename_tables` (findstring varchar(255), replacement varchar(255))
BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE tableName, newTableName varchar(255);
  DECLARE cur1 CURSOR FOR SHOW TABLES;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur1;

  REPEAT
    FETCH cur1 INTO tableName;
    IF NOT done THEN

         SET newTableName = REPLACE (tableName, findstring, replacement);

         set @alterTableSql = concat('ALTER TABLE ', tableName, ' RENAME TO ' , newTableName , ';');
         prepare alterTable from @alterTableSql; 
         execute alterTable; 
         deallocate prepare alterTable; 

       -- SELECT concat(tableName , ' -> ', newTableName) as message;

    END IF;
  UNTIL done END REPEAT;

  CLOSE cur1;

END//
DELIMITER ;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


/* ======================================================================================= */


CALL bulk_rename_tables('cdm_flore_afrique_centrale_', 'flore_afrique_centrale_');

DROP PROCEDURE `bulk_rename_tables` ;

root