2

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.

  • By default, mysqldump will dump databases in alphabetically order (character collation order for OS with case sensistivity). What is the name of the database that is corrupt ??? – RolandoMySQLDBA Jul 23 '15 at 16:38
  • The name of the database is moments_production_wp – adjc98 Jul 23 '15 at 17:38
3

You will have to mysqldump around moments_production_wp

I have old posts to bypass databases and tables

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.

  • Thanks Rolando, I tried to dump just the trouble database and it brought the server and mysql down. The first time it made it to 87MB for the dump. The second the dump made it to 164MB which tells me the database might not be corrupted. – adjc98 Jul 23 '15 at 19:36
  • After restarting the mysql server could not be restarted and I ended up doing a force restart. Mysql status said something about a lock file. ` >root@moments [~]# service mysql restart ERROR! MySQL server PID file could not be found! Starting MySQL....... ERROR! The server quit without updating PID file (/var/lib/mysql/moments.serveroflight.com.pid). root@moments [~]# service mysql status ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists` – adjc98 Jul 23 '15 at 19:37
  • Is it crashing on the same table ? – RolandoMySQLDBA Jul 23 '15 at 19:37
  • 1
    You will have to rm -f /var/lock/subsys/mysql. Then, start mysql – RolandoMySQLDBA Jul 23 '15 at 19:38
  • I am sorry I wrote the wrong thing. It is not crashing on the same table from what I can tell. – adjc98 Jul 23 '15 at 19:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.