Database Administration tips

10 DB Administration tips:
------------------------------------
1> Define backup/recovery strategy - Take backups at regular intervals - Test the recovery time - Manage how much downtime a system can afford (in case of disaster)
2> Optimize the DB parameters based upon system configuration.
3> Define the high availability mode - keeping the system in replication on some other HW machine. Replicated DB should be in some other zone.
4> Always keep some of queries pretty handy:
   a) Size of a table/DB
   b) How much processes are running in DB
   c) Kill single/all processes in DB
   d) Change the parameter value globally/session wide.
   e) To check the value of any DB level parameter
   f) Create user/update the user password
   g) If the replication is working fine
5> Auto commit at DB level should be off - let transaction handle it.
6> DB Monitoring/Alert mechanism should be in place. (Eg. nagios/cacti and some sort of shell/python scripts which take DB health snapshot at regular intervals)
7> Upgrade your databases timely. Don't lag too behind. (MySQL 5.6 or MySQL 5.7)
8> Logging tables should be partitioned on date basis. For partitioned tables, some cleanup mechanism should be defined.
9> If required, keep older data in some warehouse. And try to get rid of it from transactional database.
10> Try to maintain data and indexes into memory. (Database size should be less than innodb_buffer_pool_size)