My SQL Administration
Hello Friends ,In this topic we will discuss about administrator task and few basic concept , how Resolve the basic problems in My Sql .
First You should know about variables or parameter that are used by the MySql Application because as load will be increase in database ,then you should know which parameter is going to be change for smooth performance of mysql ..
Q : how to check variable list in Mysql ?
Ans : Mysqladmin variables or show variables command in msysql cli . can be used to get the variable list of msql .
For example Few Variable List :
have_innodb for checking inndob enable or not
have_isam for indicates whether ISAM tables are available .
key_buffer_size : sets the size of buffer used for indexes shared among thread .
log : indicate whether logging is enable for all queries or not .
Q : What are support config file used in MySql for defining setting of msyql .
Ans : my-small.cnf ,my-medium.cnf , my-large.cnf , my-heavy.cnf .
the concept of creating four config files in early version of msyql is different configuration of hardware for using these file .
- my-small.cnf : mostly used in a case where primary memory is less than 64MB RAM .
- my-medium. : for upto 128MB-256MB RAM.
- my-large.cnf : For more than 512MB RAM to 1GB
- my-huge.cnf : with 1GB or more .
Q: What would be your first step in case , mysql performance is degrading .
Ans : check for slow query log and error log in your msyql data directory .or where ever you have given path for same .
Q : how to check current status of server .?
Ans : show status or show extended-status can be used for checking status of server .
Q: if you are finding key_read_request and key_reads are high value ? what will be do ?
Ans : this means user accessing table index is high , if there are valid then increase key_buffer_size .
Q: can we run multiple msyql server in single server .?
Ans : yes , but for that installation of server should be in diffrent directory file and socekt and port address should be different . so for connecting to msyql server , need to assign port and socket reference for connection .
Q : how to manage security in database .
Ans :
1: apply patches as soon possible after release of new patches for mysql .
2: always run the server as non priviledged user , in case some one hack it ,he will do harm only till user has priviledge .
3: change the port of msyql from default port 3306 .
4: always data sent to mysql user .
5: check for frequently error log .
6: keep your password much strong by using alpah numeric and special chracter .
Q: How to database backup. ?
Ans :
msyqlhotcopy , mysqldump , backup table .
mysqlhotcopy is used to backup the data that is live by copying result into different directory . it is actually a perl programme ,so perl and its binaries should be installed into server.
msyqlhotcopy actually uses cp commands ,so it can not used in windows .
mysqlhotcopy works in three steps :
1: lock the table or specified table .
2: flush any open table to close tables.
3: perform actuall copying the file .
Basic syntax of msyqlhotcopy :
mysqlhotcopy databasename.tablename directroy_name --allowed --keepold .
–allowold: This options renames the old backup to {datbase-name}_old before taking a new backup.
–keepold: This option instructs the mysqlhotcopy to keep the old backup (i.e the renamed _old) after the backup is completed.
Mysqldump is used to take the backup of database including DDL and DML statement .
syntax :
mysqldump -u -p database1,database2 -R -e --triggers --single- transaction > C:\FileName.sql .In case of if you want to take backup of all database use -A in place of databases ,- R is used to include routines backup in while doing backup -e for all events , --triggers for triggers , --single-transaction without locking the table or without interrupting any connection(R/W).
In case if Mysql dump command is not working , please check installation path of bin is included
in system Variable of Environment of Windows .
Backup Table : syntax backup table to directory .
backup table is used for MyIsame table makes the copy of .frm and .myd files .
after backup of table ,don't forget to use flush table command .
Q : how to restore database in MySQl ?
Ans :
1: if database is taken bakcup with mysqlhotcopy syntax ,then same can be used for restoring database . or simpley copy the backup file into proper directory can restore the database.
Syntax : mysqlhotcopy -ppasword --allowold --keepold bacupfile datbasename
2: if database is taken backup with the help of mysqldump then directory by loggin in mysql CLI you can restore
syntax : mysql -hlocalhost -uroot -p < /directory/*.sql
3: If backup is table by using backup table then use command restore table tablename .
Syntax : Restore Table , from
Q : what is Perfromance tuning ?
Ans :Performance tuning means you should know about variable /parameter and their values of server configuration and other files .
there are four common log files :
binary log : store all updates and changes in data .used for update ,insert data track .
Error log : records error encountered by the server . in case if server is not started we have to look at error log.err
query log : store all connection and queries . by default it is stored as.log
slow query log : store the query who is taking more time than define in long_query_time or not using indexes with long-long-format option . by default it is stored as-slow.log .
Q : how can reset you log file .?
Ans : log file can be rotated by function flush-logs . syntax used for msyqladming flush-logs.
other flush function can be used with msyqladmin command .
flush-hosts : clean the DNS cache of MySQl . can assist if a host get blocked due to errors .
flush-logs : clear and cycle mysql log file
flush-privileges : clear privileges
flush-status : clear server counter found with status and extended-stauts function .
flush-tables : closes all opent tables . it has three variation first is flush tables will clear all tables , second is flussh table1 ,table2
And third is flush table with read lock(tablname) ,flush table with read lock statment close the open table including read lock on table ,so for opening table ,you have to unlock table . flush-thread : closes all open threads .
First You should know about variables or parameter that are used by the MySql Application because as load will be increase in database ,then you should know which parameter is going to be change for smooth performance of mysql ..
Q : how to check variable list in Mysql ?
Ans : Mysqladmin variables or show variables command in msysql cli . can be used to get the variable list of msql .
For example Few Variable List :
have_innodb for checking inndob enable or not
have_isam for indicates whether ISAM tables are available .
key_buffer_size : sets the size of buffer used for indexes shared among thread .
log : indicate whether logging is enable for all queries or not .
Q : What are support config file used in MySql for defining setting of msyql .
Ans : my-small.cnf ,my-medium.cnf , my-large.cnf , my-heavy.cnf .
the concept of creating four config files in early version of msyql is different configuration of hardware for using these file .
- my-small.cnf : mostly used in a case where primary memory is less than 64MB RAM .
- my-medium. : for upto 128MB-256MB RAM.
- my-large.cnf : For more than 512MB RAM to 1GB
- my-huge.cnf : with 1GB or more .
Q: What would be your first step in case , mysql performance is degrading .
Ans : check for slow query log and error log in your msyql data directory .or where ever you have given path for same .
Q : how to check current status of server .?
Ans : show status or show extended-status can be used for checking status of server .
Q: if you are finding key_read_request and key_reads are high value ? what will be do ?
Ans : this means user accessing table index is high , if there are valid then increase key_buffer_size .
Q: can we run multiple msyql server in single server .?
Ans : yes , but for that installation of server should be in diffrent directory file and socekt and port address should be different . so for connecting to msyql server , need to assign port and socket reference for connection .
Q : how to manage security in database .
Ans :
1: apply patches as soon possible after release of new patches for mysql .
2: always run the server as non priviledged user , in case some one hack it ,he will do harm only till user has priviledge .
3: change the port of msyql from default port 3306 .
4: always data sent to mysql user .
5: check for frequently error log .
6: keep your password much strong by using alpah numeric and special chracter .
Q: How to database backup. ?
Ans :
msyqlhotcopy , mysqldump , backup table .
mysqlhotcopy is used to backup the data that is live by copying result into different directory . it is actually a perl programme ,so perl and its binaries should be installed into server.
msyqlhotcopy actually uses cp commands ,so it can not used in windows .
mysqlhotcopy works in three steps :
1: lock the table or specified table .
2: flush any open table to close tables.
3: perform actuall copying the file .
Basic syntax of msyqlhotcopy :
mysqlhotcopy databasename.tablename directroy_name --allowed --keepold .
–allowold: This options renames the old backup to {datbase-name}_old before taking a new backup.
–keepold: This option instructs the mysqlhotcopy to keep the old backup (i.e the renamed _old) after the backup is completed.
Mysqldump is used to take the backup of database including DDL and DML statement .
syntax :
mysqldump -u -p database1,database2 -R -e --triggers --single- transaction > C:\FileName.sql .In case of if you want to take backup of all database use -A in place of databases ,- R is used to include routines backup in while doing backup -e for all events , --triggers for triggers , --single-transaction without locking the table or without interrupting any connection(R/W).
In case if Mysql dump command is not working , please check installation path of bin is included
in system Variable of Environment of Windows .
Backup Table : syntax backup table
backup table is used for MyIsame table makes the copy of .frm and .myd files .
after backup of table ,don't forget to use flush table command .
Q : how to restore database in MySQl ?
Ans :
1: if database is taken bakcup with mysqlhotcopy syntax ,then same can be used for restoring database . or simpley copy the backup file into proper directory can restore the database.
Syntax : mysqlhotcopy -ppasword --allowold --keepold bacupfile datbasename
2: if database is taken backup with the help of mysqldump then directory by loggin in mysql CLI you can restore
syntax : mysql -hlocalhost -uroot -p < /directory/*.sql
3: If backup is table by using backup table then use command restore table tablename .
Syntax : Restore Table
Q : what is Perfromance tuning ?
Ans :Performance tuning means you should know about variable /parameter and their values of server configuration and other files .
there are four common log files :
binary log : store all updates and changes in data .used for update ,insert data track .
Error log : records error encountered by the server . in case if server is not started we have to look at error log
query log : store all connection and queries . by default it is stored as
slow query log : store the query who is taking more time than define in long_query_time or not using indexes with long-long-format option . by default it is stored as
Q : how can reset you log file .?
Ans : log file can be rotated by function flush-logs . syntax used for msyqladming flush-logs.
other flush function can be used with msyqladmin command .
flush-hosts : clean the DNS cache of MySQl . can assist if a host get blocked due to errors .
flush-logs : clear and cycle mysql log file
flush-privileges : clear privileges
flush-status : clear server counter found with status and extended-stauts function .
flush-tables : closes all opent tables . it has three variation first is flush tables will clear all tables , second is flussh table1 ,table2
And third is flush table with read lock(tablname) ,flush table with read lock statment close the open table including read lock on table ,so for opening table ,you have to unlock table . flush-thread : closes all open threads .
Comments
Post a Comment