Mysql shutting down during a mysqldump of a specific database

Question

I have a server which is running Centos 6.6 with Cpanel. I have been attempting to do mysqldump backups on individual databases. All of the databases are under 5MB except for one.

The database that is around 250MB causes a mysql shutdown in the middle of running a mysqldump. The other databases work fine during a mysqldump.

My question is could this be due to a corrupted database? What should I do to start troubleshooting this?

Is there another way to backup this database other than mysqldump. I just need one copy so I can move the troubled database to another server and remove it from this one.

Here is my.cnf

[mysqld]
table_open_cache=100K
max_connections=500
thread_cache_size=4
query_cache_size=8M
query_cache_limit=1M
query_cache_type=1
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=10000
max_allowed_packet=268435456
innodb_buffer_pool_size=123731968

Let me know if you need more information.

Answer 1

You will have to mysqldump around moments_production_wp

I have old posts to bypass databases and tables

• Dec 16, 2011 : How do you mysqldump specific table(s)?
• Feb 20, 2013 : Any option for mysqldump to ignore databases for backup?

Using the same concepts, here is how to mysqldump all except moments_production_wp

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DATABASES_TO_EXCLUDE="moments_production_wp"
EXCLUSION_LIST="'information_schema','mysql','performance_schema'"
for DB in `echo "${DATABASES_TO_EXCLUDE}"`
do
    EXCLUSION_LIST="${EXCLUSION_LIST},'${DB}'"
done
SQLSTMT="SELECT schema_name FROM information_schema.schemata"
SQLSTMT="${SQLSTMT} WHERE schema_name NOT IN (${EXCLUSION_LIST})"
MYSQLDUMP_DATABASES="--databases"
for DB in `mysql -ANe"${SQLSTMT}"`
do
    MYSQLDUMP_DATABASES="${MYSQLDUMP_DATABASES} ${DB}"
done
MYSQLDUMP_OPTIONS="--routines --triggers --single-transaction"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${MYSQLDUMP_DATABASES} > GoodDatabases.sql

After dumping all the other databases, mysqldump the one database

mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} moments_production_wp > OneDatabase.sql

It will mysqldump tables in alphabetical order. It should break near the bad table.

You can then mysqldump that database one table at a time

Let's say you know tables tb1 and tb2 makes mysqld crash

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
DBTODUMP=moments_production_wp
SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema='${DBTODUMP}'"
SQL="${SQL} AND table_name NOT IN ('tb1','tb2')"
TBLIST=`mysql ${MYSQL_CONN} -AN -e"${SQL}"`
MYSQLDUMP_OPTIONS="--hex-blob --max-allowed-packet=1073741824"
for TB in `echo "${TBLIST}"`
do
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DBTODUMP} ${TB} > ${DBTODUMP}-${TB}.sql
done

This should help narrow the beam as to which table is causing the issue.

I do have one further thing to share. I have a 4-year-old post on the silent killer of mysqldumps : MySQL server has gone away obstructing import of large dumps. You will need to set your max_allowed_packet to 1G in my.cnf and restart mysqld. If you cannot change the max_allowed_packet at this time, you should mysqldump one row at a time using --skip-extended-insert to throttle the amount of data passing into your current MySQL Packet.