transaction - restaurar solo una tabla mysql

Saltar ciertas tablas con mysqldump

Question

¿Hay alguna forma de restringir ciertas tablas desde el comando mysqldump?

Por ejemplo, usaría la siguiente sintaxis para volcar solo table1 y table2:

mysqldump -u username -p database table1 table2 > database.sql

Pero, ¿hay una forma similar de volcar todas las tablas excepto table1 y table2? No he encontrado nada en la documentación de mysqldump, ¿así que la fuerza bruta (especificando todos los nombres de las tablas) es la única manera de hacerlo?

Answer 1

Volcar todas las bases de datos con todas las tablas pero omitir ciertas tablas

en github: https://github.com/rubo77/mysql-backup.sh/blob/master/mysql-backup.sh

#!/bin/bash
# mysql-backup.sh

if [ -z "$1" ] ; then
  echo
  echo "ERROR: root password Parameter missing."
  exit
fi
DB_host=localhost
MYSQL_USER=root
MYSQL_PASS=$1
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
#MYSQL_CONN=""

BACKUP_DIR=/backup/mysql/

mkdir $BACKUP_DIR -p

MYSQLPATH=/var/lib/mysql/

IGNORE="database1.table1, database1.table2, database2.table1,"

# strpos $1 $2 [$3]
# strpos haystack needle [optional offset of an input string]
strpos()
{
    local str=${1}
    local offset=${3}
    if [ -n "${offset}" ]; then
        str=`substr "${str}" ${offset}`
    else
        offset=0
    fi
    str=${str/${2}*/}
    if [ "${#str}" -eq "${#1}" ]; then
        return 0
    fi
    echo $((${#str}+${offset}))
}

cd $MYSQLPATH
for i in */; do
    if [ $i != ''performance_schema/'' ] ; then 
    DB=`basename "$i"` 
    #echo "backup $DB->$BACKUP_DIR$DB.sql.lzo"
    mysqlcheck "$DB" $MYSQL_CONN --silent --auto-repair >/tmp/tmp_grep_mysql-backup
    grep -E -B1 "note|warning|support|auto_increment|required|locks" /tmp/tmp_grep_mysql-backup>/tmp/tmp_grep_mysql-backup_not
    grep -v "$(cat /tmp/tmp_grep_mysql-backup_not)" /tmp/tmp_grep_mysql-backup

    tbl_count=0
    for t in $(mysql -NBA -h $DB_host $MYSQL_CONN -D $DB -e ''show tables'') 
    do
      found=$(strpos "$IGNORE" "$DB"."$t,")
      if [ "$found" == "" ] ; then 
        echo "DUMPING TABLE: $DB.$t"
        mysqldump -h $DB_host $MYSQL_CONN $DB $t --events --skip-lock-tables | lzop -3 -f -o $BACKUP_DIR/$DB.$t.sql.lzo
        tbl_count=$(( tbl_count + 1 ))
      fi
    done
    echo "$tbl_count tables dumped from database ''$DB'' into dir=$BACKUP_DIR"
    fi
done

Con un poco de ayuda de https://.com/a/17016410/1069083

Utiliza lzop, que es mucho más rápido, consulte: http://pokecraft.first-world.info/wiki/Quick_Benchmark:_Gzip_vs_Bzip2_vs_LZMA_vs_XZ_vs_LZ4_vs_LZO

Answer 2

Me gusta la solución de Rubo77, no la había visto antes de modificar la de Paul. Éste realizará una copia de seguridad de una sola base de datos, excluyendo las tablas que no desee. Luego lo comprimirá y eliminará cualquier archivo con más de 8 días de antigüedad. Probablemente usaré 2 versiones de esto que hacen una completa (una tabla de registros menos) una vez al día, y otra que simplemente realiza una copia de seguridad de las tablas más importantes que cambian más cada hora usando un par de trabajos cron.

#!/bin/sh
PASSWORD=XXXX
HOST=127.0.0.1
USER=root
DATABASE=MyFavoriteDB

now="$(date +''%d_%m_%Y_%H_%M'')"
filename="${DATABASE}_db_backup_$now"
backupfolder="/opt/backups/mysql"
DB_FILE="$backupfolder/$filename"
logfile="$backupfolder/"backup_log_"$(date +''%Y_%m'')".txt

EXCLUDED_TABLES=(
logs
)
IGNORED_TABLES_STRING=''''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure started at $(date +''%d-%m-%Y %H:%M:%S'')" >> "$logfile"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE}  > ${DB_FILE} 
echo "Dump structure finished at $(date +''%d-%m-%Y %H:%M:%S'')" >> "$logfile"
echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}
gzip ${DB_FILE}

find "$backupfolder" -name ${DATABASE}_db_backup_* -mtime +8 -exec rm {} /;
echo "old files deleted" >> "$logfile"
echo "operation finished at $(date +''%d-%m-%Y %H:%M:%S'')" >> "$logfile"
echo "*****************" >> "$logfile"
exit 0

Answer 3

Otro ejemplo para ignorar tablas múltiples

/usr/bin/mysqldump -uUSER -pPASS --ignore-table={db_test.test1,db_test.test3} db_test> db_test.sql

usando --ignore-table y crea una matriz de tablas, con sintaxis como database.table

--ignore-table={db_test.table1,db_test.table3,db_test.table4}

Enlaces con información que te ayudará.

comprimir la salida mysqldump

• mysqldump y gzip
• mysqldump a un tar.gz

Nota: probado en el servidor ubuntu con mysql Ver 14.14 Distrib 5.5.55

Importar base de datos

 mysql -uUSER  -pPASS db_test < db_test.sql

Answer 4

Para excluir algunos datos de la tabla, pero no la estructura de la tabla. Así es como lo hago:

Volcar la estructura de la base de datos de todas las tablas, sin ningún dato:

mysqldump -u user -p --no-data database > database_structure.sql

Luego, voltee la base de datos con datos, excepto las tablas excluidas, y no voltee la estructura:

mysqldump -u user -p --no-create-info /
    --ignore-table=database.table1 /
    --ignore-table=database.table2 database > database_data.sql

Luego, para cargarlo en una nueva base de datos:

mysql -u user -p newdatabase < database_structure.sql
mysql -u user -p newdatabase < database_data.sql

Answer 5

Puedes usar la opción --ignore-table . Para que pudieras hacer

mysqldump -u USERNAME -pPASSWORD --ignore-table=database.table1 > database.sql

No hay espacios en blanco después de -p (esto no es un error tipográfico).

Si desea ignorar varias tablas, puede usar un script simple como este

#!/bin/bash
PASSWORD=XXXXXX
HOST=XXXXXX
USER=XXXXXX
DATABASE=databasename
DB_FILE=dump.sql
EXCLUDED_TABLES=(
table1
table2
table3
table4
tableN   
)

IGNORED_TABLES_STRING=''''
for TABLE in "${EXCLUDED_TABLES[@]}"
do :
   IGNORED_TABLES_STRING+=" --ignore-table=${DATABASE}.${TABLE}"
done

echo "Dump structure"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} --single-transaction --no-data --routines ${DATABASE} > ${DB_FILE}

echo "Dump content"
mysqldump --host=${HOST} --user=${USER} --password=${PASSWORD} ${DATABASE} --no-create-info --skip-triggers ${IGNORED_TABLES_STRING} >> ${DB_FILE}

Answer 6

Sobre la base de la respuesta de @ Brian-Fisher y respondiendo a los comentarios de algunas de las personas en este post, tengo un montón de tablas enormes (e innecesarias) en mi base de datos, así que quise omitir sus contenidos al copiar, pero mantengo la estructura :

mysqldump -h <host> -u <username> -p <schema> --no-data > db-structure.sql
mysqldump -h <host> -u <username> -p <schema> --no-create-info --ignore-table=schema.table1 --ignore-table=schema.table2 > db-data.sql

Los dos archivos resultantes son estructuralmente sólidos, pero los datos volcados ahora son ~ 500MB en lugar de 9GB, mucho mejor para mí. Ahora puedo importar estos dos archivos a otra base de datos para realizar pruebas sin tener que preocuparme por manipular 9 GB de datos o quedarse sin espacio en disco.

Answer 7

para múltiples bases de datos:

mysqldump -u user -p --ignore-table=db1.tbl1 --ignore-table=db2.tbl1 --databases db1 db2 ..