$ /usr/local/bin/mysqldump -a --add-drop-table --skip-lock-tables $DB_NAME `/bin/cat tables.lst` | /usr/bin/bzip2 -c >dump-$PFX.dump.bz2

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `gals_kw2_inno`': The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay (1104)

MariaDB [dfrot]> show variables like '%join%';

+-------------------------+----------+

| Variable_name           | Value    |

+-------------------------+----------+

| join_buffer_size        | 33554432 |

| join_buffer_space_limit | 2097152  |

| join_cache_level        | 2        |

| max_join_size           | 16777216 |

| sql_max_join_size       | 16777216 |

+-------------------------+----------+

MariaDB [dfrot]> show variables like '%big%';

+-----------------+-------+

| Variable_name   | Value |

+-----------------+-------+

| big_tables      | OFF   |

| sql_big_selects | OFF   |

| sql_big_tables  | OFF   |

+-----------------+-------+

MariaDB [dfrot]> show table status like 'gals_kw2_inno';

+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| Name          | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |

+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| gals_kw2_inno | InnoDB |      10 | Compact    | 93515627 |             40 |   3778691072 |               0 |   1797046272 |   5242880 |           NULL | 2013-08-21 14:42:57 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |

+---------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

MariaDB [dfrot]> select count(gid) from gals_kw2_inno;

+------------+

| count(gid) |

+------------+

|   80122933 |

+------------+

1 row in set (38.10 sec)

# The MySQL server

[mysqld]

#innodb_force_recovery = 6

user=mysql

bind-address=127.0.0.1

port            = 3306

socket = /tmp/mysql.sock

#skip-locking

myisam-recover

#safe_show_database

skip-name-resolve

innodb_file_per_table=1

myisam_sort_buffer_size = 512M

max_allowed_packet = 16M

table_cache = 2000

thread_cache = 10

query_cache_size= 256M

query_cache_type= 1

query_cache_limit = 256M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

# flush all tables every day

flush_time = 86400

interactive_timeout = 600

max_connections = 200

max_connect_errors = 1000

max_user_connections = 1000

wait_timeout = 300

#innodb_force_recovery=1

back_log = 120

max_tmp_tables=1500

default_storage_engine=myisam

innodb_buffer_pool_size= 6144M

key_buffer_size = 4096M

sort_buffer_size = 4M

read_buffer_size = 2M

max_heap_table_size = 6144M

tmp_table_size = 6144M

join_buffer_size = 32M

aria-sort-buffer-size=512M

thread-handling=pool-of-threads

thread-pool-size=16

innodb_log_file_size=768M

innodb_log_buffer_size=8M

innodb_flush_log_at_trx_commit=2

innodb_flush_method = O_DIRECT

##innodb_force_recovery=1

innodb_read_io_threads=64

innodb_write_io_threads=64

innodb_io_capacity=20000 #(set this to your device's IOPs)

#long_query_time=1

#log_slow_queries=/home/logs/mysql_slow.log

#log_queries_not_using_indexes=1

tmpdir = /var/tmp_md

#tmpdir = /home/tmp

table_open_cache = 2048

binlog_cache_size = 1M

read_rnd_buffer_size = 16M

thread_cache_size = 16

innodb_additional_mem_pool_size = 16M

#innodb_read_io_threads=2

#innodb_write_io_threads=2

innodb_lock_wait_timeout = 120

[mysqldump]

max_allowed_packet = 16M

[myisamchk]

key_buffer_size=512M

sort_buffer_size=512M

read_buffer_size=1M

write_buffer_size=1M