Mysql – Users complain that system runs slow when thesqldump is in progress

Try this in my.cnf file,

[mysqld] innodb_buffer_pool_size = 80M

And you need to restart the Workbench as well as the Sql service.

It would make more sense in my opinion to do a backup by table instead of by database. Here is a script I use to make backups of databases that contain MyISAM and InnoDB tables.

#!/bin/bash
USER="some_user"
PASSWD="some_password"
STAMP=$(date +"%d-%m-%y-%H:%M:%S")
HOST="some_host"
db="some_database"
FILESTAMP=$(date +"%d%m%y")
FILE="/some_directory/some_file-$STAMP.sql.gz"
Port=3306

TABLE_DUMP=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT table_name FROM information_schema.tables where table_schema = '$db' and engine is not null order by table_type, table_name;")
for table_name in $TABLE_DUMP
do      
    table_engine=$(mysql -u $USER -P$PASSWD -h $HOST -Bse "SELECT engine FROM information_schema.tables where table_schema = '$db' and table_name = '$table_name';")
    case "$table_engine" in
        InnoDB ) 
            echo "$table_name InnoDB\n" 
            mysqldump --skip-opt --disable-keys --create-options --single-transaction --quick --extended-insert --add-drop-table -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
        ;;
        MyISAM) 
            echo "$table_name MyISAM\n" 
            mysqldump --opt -P $Port -u $USER -P$PASSWD -h $HOST $db $table_name | gzip -9 >> $FILE
    esac

    #register all table names
    skiptables="$skiptables --ignore-table=$db.$table_name" 
done

# View
mysqldump -P $Port -u $USER -P$PASSWD -h $HOST $db $skiptables | gzip -9 >> $FILE