Actions
Maintenance Scripts¶
This page is a means to exchange knowledge and hints about MySQL
- Table of contents
- Maintenance Scripts
Dump & restore all databases¶
- Dump all: https://dev.e-taxonomy.eu/svn/trunk/server-scripts/mysql/dump-all-databases.sh
- Restore all: https://dev.e-taxonomy.eu/svn/trunk/server-scripts/mysql/restore-all-databases.sh
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¶
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 about 2 years ago · 26 revisions