Busindre

Extraer tablas individuales o base de datos a partir de un volcado sql previo con mysqldump es muy sencillo. El script mysqldumpsplitter permite generar un dump (volcado) de una tabla a partir de un fichero sql (volcado) realizado con mysqldump, el cual, por norma tiene todas las tablas de la base de datos seleccionada.

Sintaxis.

./mysqldumpsplitter.2.0.sh
Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME -- Extract all tables as a separate file from dump.
       sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME  -- Extract single table from dump.
       sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP  -- Extract tables from dump for specified regular expression.
       sh MyDumpSplitter.sh DUMP-FILE-NAME -d DATABASE-NAME  -- Extract complete database from dump.

Ejemplo de uso: genera un fichero BODYCONTENT.dump listo para restaurar con el contenido de la tabla BODYCONTENT.

./mysqldumpsplitter.2.0.sh confluence.dump BODYCONTENT

El fichero BODYCONTENT.dump sería equivalente al haber realizado un volcado exclusivo de dicha tabla.

mysqldump -u XXX -PXXX confluence BODYCONTENT > BODYCONTENT.dump

Código de mysqldumpsplitter.2.0.sh (codificación UNIX).

mysqldumpsplitter.2.0.sh

#!/bin/sh
# http://kedar.nitty-witty.com
# Ver 2 (added dump database & compression)
#SPLIT DUMP FILE INTO INDIVIDUAL TABLE DUMPS
# Text color variables
txtund=$(tput sgr 0 1)    # Underline
txtbld=$(tput bold)       # Bold
txtred=$(tput setaf 1)    # Red
txtgrn=$(tput setaf 2)    # Green
txtylw=$(tput setaf 3)    # Yellow
txtblu=$(tput setaf 4)    # Blue
txtpur=$(tput setaf 5)    # Purple
txtcyn=$(tput setaf 6)    # Cyan
txtwht=$(tput setaf 7)    # White
txtrst=$(tput sgr0)       # Text reset
 
TARGET_DIR="."
DUMP_FILE=$1
TABLE_COUNT=0
COMPRESSION=gzip
 
if [ $# = 0 ]; then
        echo "${txtbld}${txtred}Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME${txtrst} -- Extract all tables as a separate file from dump."
        echo "${txtbld}${txtred}       sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME ${txtrst} -- Extract single table from dump."
        echo "${txtbld}${txtred}       sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP ${txtrst} -- Extract tables from dump for specified regular expression."
        echo "${txtbld}${txtred}       sh MyDumpSplitter.sh DUMP-FILE-NAME -d DATABASE-NAME ${txtrst} -- Extract complete database from dump."
 
        exit;
elif [ $# = 1 ]; then
        #Loop for each tablename found in provided dumpfile
        for tablename in $(grep "Table structure for table " $1 | awk -F"\`" {'print $2'})
        do
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 | $COMPRESSION > $TARGET_DIR/$tablename.sql.gz
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 2  ]; then
        for tablename in $(grep -E "Table structure for table \`$2\`" $1| awk -F"\`" {'print $2'})
        do
                echo "Extracting $tablename..."
                #Extract table specific dump to tablename.sql
                sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 | $COMPRESSION > $TARGET_DIR/$tablename.sql.gz
                TABLE_COUNT=$((TABLE_COUNT+1))
        done;
elif [ $# = 3  ]; then
 
        if [ $2 = "-S" ]; then
                for tablename in $(grep -E "Table structure for table \`$3" $1| awk -F"\`" {'print $2'})
                do
                        echo "Extracting $tablename..."
                        #Extract table specific dump to tablename.sql
                        sed -n "/^-- Table structure for table \`$tablename\`/,/^-- Table structure for table/p" $1 | $COMPRESSION > $TARGET_DIR/$tablename.sql.gz
                        TABLE_COUNT=$((TABLE_COUNT+1))
                done;
        elif [ $2 = "-d" ]; then
                        echo "Extracting Database: $3...";
                        sed -n "/^-- Current Database: \`$3\`/,/^-- Current Database: /p" $1 | $COMPRESSION > $TARGET_DIR/$3.sql.gz
                        echo "${txtbld} Database $3  extracted from $DUMP_FILE at $TARGET_DIR${txtrst}"
                        exit;
        else
                echo "${txtbld}${txtred} Please provide proper parameters. ${txtrst}";
        fi
fi
 
#Summary
echo "${txtbld}$TABLE_COUNT Table extracted from $DUMP_FILE at $TARGET_DIR${txtrst}"

Si descargados de la web el script, este viene con codificación Windows, se puede utilizar la aplicacion dos2unix pare resolver el problema.

wget http://kedar.nitty-witty.com/blog/wp-content/uploads/2015/02/mysqldumpsplitter.2.0.sh.txt
Saving to: ‘mysqldumpsplitter.2.0.sh.txt’
 
mv mysqldumpsplitter.2.0.sh.txt mysqldumpsplitter.2.0.sh
chmod u+x mysqldumpsplitter.2.0.sh
./mysqldumpsplitter.2.0.sh
-bash: ./mysqldumpsplitter.2.0.sh: /bin/sh^M: bad interpreter: No such file or directory
 
dos2unix mysqldumpsplitter.2.0.sh
dos2unix: converting file mysqldumpsplitter.2.0.sh to Unix format ...
./mysqldumpsplitter.2.0.sh
Usage: sh MyDumpSplitter.sh DUMP-FILE-NAME -- Extract all tables as a separate file from dump.
       sh MyDumpSplitter.sh DUMP-FILE-NAME TABLE-NAME  -- Extract single table from dump.
       sh MyDumpSplitter.sh DUMP-FILE-NAME -S TABLE-NAME-REGEXP  -- Extract tables from dump for specified regular expression.
       sh MyDumpSplitter.sh DUMP-FILE-NAME -d DATABASE-NAME  -- Extract complete database from dump.

mysqldump -u XXX -p basededatos --ignore-table=basededatos.tabla1 --ignore-basededatos.tabla2