Busindre

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== Extraer tablas / base de datos de volcados (.sql / .dump / ...) realizados con mysqldump =====

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**.
<code bash>./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.</code>

**Ejemplo de uso**: genera un fichero BODYCONTENT.dump listo para restaurar con el contenido de la tabla BODYCONTENT.
<code bash>./mysqldumpsplitter.2.0.sh confluence.dump BODYCONTENT</code>

El fichero BODYCONTENT.dump sería equivalente al haber realizado un volcado exclusivo de dicha tabla.
<code bash>mysqldump -u XXX -PXXX confluence BODYCONTENT > BODYCONTENT.dump</code>

**Código de mysqldumpsplitter.2.0.sh** (codificación UNIX).
<code bash 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}"
</code>

Si descargados de la web el script, este viene con codificación Windows, se puede utilizar la aplicacion dos2unix pare resolver el problema.
<code bash>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.
</code>

===== Evitar volcar determinadas tablas al hacer un dump con mysqldump =====
<code bash>mysqldump -u XXX -p basededatos --ignore-table=basededatos.tabla1 --ignore-basededatos.tabla2</code>