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

Table of Contents

1. mysqldump-tables.sh
2. mysqldump-dbfilter.sh
3. dump-cdmportal-dbs.sh
   1. restore-dataportal-dumps.sh
   2. bulk_rename_tables.sql

---

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:

#!/bin/sh
BACKUPDIR="/backups/db";
DBFILTER="(information_schema|dev|tmp)";
MYSQLDUMP="$(which mysqldump) --opt";
DIRNAME=`date +%Y-%m`;
 
MYSQL=$(which mysql);
TAR=$(which tar);
GZIP=$(which gzip);
NICE=$(which nice);
EGREP=$(which egrep);
 
mkdir -p "$BACKUPDIR/$DIRNAME";
 
DBS=$($MYSQL -Bse "show databases");
for db in $DBS
do
    if !(echo $db | $EGREP $DBFILTER > /dev/null);
    then
        FILENAME="$db-`date +%Y_%m_%d_%H_%M_%S`.sql.gz";
$NICE -n 20 $MYSQLDUMP $db | $NICE -n 20 $GZIP -c > "$BACKUPDIR/$DIRNAME/$FILENAME";
    fi
done

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` ;