Project

General

Profile

Actions

Maintenance Scripts

This page is a means to exchange knowledge and hints about MySQL


more scripts can be found in

Dump & restore all databases

Locally these can be found opt/server-scripts/mysql (but svn pull is needed)

Dump all Users and Grants

restore this dump with:

mysql -uroot -p < user_privileges_final.sql

Note: make sure that the script does not contain double \ (replace by single \ otherwise)

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

mysql-rename-database

see https://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name

This command is available in server-scripts/mysql

#!/bin/bash

#
# IMPORTANT before using this script you need to create the new database!
#


if [[ -z $4 ]]
then
  echo "Usage: USER PASSWORD DBNAME NEW_DBNAME"
  exit -1
fi

USER=$1
PWD=$2
DBNAME=$3
NEW_DBNAME=$4

for table in `mysql -u$USER -p$PWD -s -N -e "use $DBNAME;show tables from $DBNAME;"`; do mysql -u$USER -p$PWD -s -N -e "use $DBNAME;rename table $DBNAME.$table to $NEW_DBNAME.$table;"; done;

Updated by Andreas Müller almost 2 years ago · 26 revisions