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.