MySQL backup – mysqldump
shell> mysqldump [options] db_name [tbl_name …] > db_name.sql
shell> mysqldump [options] –databases db_name … > multi_db.sql
shell> mysqldump [options] –all-databases > all_dbs.sql

Importing MySQL Table
To import the table run the following command from the command line:
shell> mysql -D dbname < tableName.sql

Check database space
SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;

MySQL Uptime
a) mysql> SHOW GLOBAL STATUS;
b) # mysqladmin version | grep -i uptime

innodb_open_files
mysql> show global variables like “innodb_open_files”\G

Binary Logs
>> Enable:
> /etc/my.cnf
log-bin = /var/lib/mysql/bin-log

Enable the slow query log
slow-query-log = 1

Log queries that take longer than 2 seconds
long-query-time = 2

Set ‘max_connections’:
>> On the fly (GLOBAL variable. we can increase it on the fly without restarting mysqld service)
[Check] select @@global.max_connections;
[Set] set @@global.max_connections=300;
[Re-Check] select @@global.max_connections;
(or mysql> set global max_connections=250;)

>> CHANGE on /etc/my.cnf
max_connections = 50
max-connections = 50

set @@global.max_connections=default;

Set the query_cache_size to 16MB, query_cache_type to 1 and query_cache_limit to 1MB

mysql> set global query_cache_size=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_limit=1*1024*1024;
Query OK, 0 rows affected (0.00 sec)

Check variables
select @@global.max_connections;OR
show variables;show variables like ‘%max%’;

Disable InnoDB
[mysqld]
skip-innodb
default-storage-engine = myisam

Check if Query Cache is enabled:
SHOW VARIABLES LIKE ‘have_query_cache’;

Check Query Cache statistics:
show status like ‘Qcache%’;

MySQL’s maximum memory usage is dangerously high
>> (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size) x max_connections
=> change max_connections

wait_timeout (global variable)
mysql> show processlist;
If there are too many queries, it might be a bug in the code (for example no “close connections”). In this case, it would be safer to setup a wait_timeout low, maybe 180 (seconds -> 3mins) to make sure the sleeping connections will get dropped at that time.