Admin Linux ... MySQL DBA

Verify database Objects: 1. Verify table. a. SHOW TABLE STATUS LIKE '%user%'\G b. SHOW TABLE STATUS from forum LIKE '%user%' \G 2. It will show you all tables have MyISAM engine. a. SELECT TABLE_SCHEMA, table_name, table_type, engine From information_schema.tables where engine='MyISAM'; 3. To know rows per table, you can use a query like this: a. SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'forum'; 4. Verify you procedure / function. a. SHOW procedure/FUNCTION STATUS LIKE ´hello´G b. show create procedure/function hello\G 5. Routines a. select routine_name from information_schema.routines; // list of all the routines in the system b. select routine_name, routine_schema, routine_type from information_schema.routines; // This lists all of the routines in the system. With additional Information like the database the routines belongs too and also distinct between the different routines

Few MySQL Tips: a. calculate databases size : -> It will show database size SELECT s.schema_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") as Data_size, CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") as Index_size,COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name not in("mysql","information_schema","test") GROUP BY s.schema_name order by Data_size DESC; -> It will show database size along with table size. SELECT s.schema_name,t.table_name, CONCAT(IFNULL(ROUND(SUM(t.data_length)/1024/1024,2),0.00),"Mb") data_size,CONCAT(IFNULL(ROUND(SUM(t.index_length)/1024/1024,2),0.00),"Mb") index_size, t.ENGINE ENGINE, t.table_rows TABLE_ROWS,t.row_format TABLE_ROW_FORMAT,date(t.update_time) FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema

Some time we have requirement that, particular job should stop for certain period of time and start again. Most of us familiar with KILL command, but here is another feature of KILL command , which saves your life :- #kill -STOP 10067 ( where 10067 is process id ) #kill -CONT 10067 ( where 10067 is process id ) have a fun :)

using sed to extract single database from the dumpfile: ========================================= sed -n '/^-- Current Database: `dbname`/,/^-- Current Database: `/p' dumpfile > dbname.sql 2>error eg: sed -n '/^-- Current Database: `blogs`/,/^-- Current Database: `/p' dump.sql > blogs.sql 2>error we can also use "awk" for the same. extract single table from the dumpfile:-

nohup command if added in front of any command will continue running the command or process even if you shut down your terminal or close your session to machine nohup command-name & Where, * command-name : is name of shell script or command name. You can pass argument to command or a shell script. * & : nohup does not automatically put the command it runs in the background; you must do that explicitly, by ending the command line with an & symbol. examples: # nohup mysql -q -uUSER1 -pPASS1 < dump.sql > dump.log 2> error.log & source: http://www.idevelopment.info/data/Unix/General_UNIX/GENERAL_RunningUNIXCommandsImmunetoHangups_nohup.shtml http://www.cyberciti.biz/tips/nohup-execute-commands-after-you-exit-from-a-shell-prompt.html

Recently,I created tables using auto increment number as the Primary Key. As name say, the number will increase every time I insert new record into the table. At some point I wanted to delete the whole dummy data I inserted. ALTER TABLE tablename AUTO_INCREMENT = value; eg. If I had a items table and I created a few new items and then deleted them, to set the auto increment value back to ‘100′ i would simply: ALTER TABLE items AUTO_INCREMENT = 100;

Rotating MySQL Log Files on Linux. A. Check logrotate must on daily cron #less /etc/cron.daily/logrotate B. Check on logrotate.d there must be mysql #less /etc/logrotate.d/mysql Its default Content is: # by setting the variable "err-log" # in the [safe_mysqld] section as follows: # # [safe_mysqld] # err-log=/var/lib/mysql/mysqld.log # # If the root user has a password you have to create a # /root/.my.cnf configuration file with the following # content: # # [mysqladmin] # password = # user= root # # where " " is the password. # # ATTENTION: This /root/.my.cnf should be readable ONLY # for root ! /var/lib/mysql/mysqld.log { # create 600 mysql mysql notifempty daily rotate 3 missingok compress postrotate # just if mysqld is really running if test -x /usr/bin/mysqladmin && \ /usr/bin/mysqladmin ping &>/dev/null then /usr/bin/mysqladmin flush-logs fi

First enable slow query logging, then generate a slow query and finally look at the slow query log. mysqldumpslow This program parses and summarizes a 'slow query log'. -v verbose -d debug -s=WORD what to sort by (t, at, l, al, r, ar etc) -r reverse the sort order (largest last instead of first) -t=NUMBER just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n=NUMBER abstract numbers with at least n digits within names -g=WORD grep: only consider stmts that include this string -h=WORD hostname of db server for *-slow.log filename (can be wildcard) -i=WORD name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time eg. 1. mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_slow_query.tx

awk 'pattern {action}' filename Reads one line at a time from file, checks for pattern match, performs action if pattern matched pattern. NR is a special awk variable meaning the line number of the current record can use a line number, to select a specific line, by comparing it to NR (for example: NR == 2) can specify a range of line numbers (for example: NR == 2, NR == 4) can specify a regular expression, to select all lines that match $n are special awk variables, meaning the value of the nth field (field delimiter is space or tab) $0 is the entire record can use field values, by comparing to $n (for example: $3 == 65) every line is selected if no pattern is specified Instructions print - print line(s) that match the pattern, or print fields within matching lines print is default if no action is specified there are many, many instruction, including just about all C statements with similar syntax other instructions will be covered in future courses examples, using the fi

Yesterday I have found that there is no space left on server of MySQL master and on Slave. Once I debugged I have come to know that there is GB's of bin-log files on Master and relay-log on Slave. Its due to I have forget to add a expire_logs_days Variable in my.cnf during the configuration of replication server. # expire_logs_days = 7 It will purged binary logs older than 7 days.The old logs will be purged during the next bin-log switch. Or, You can also delete bin-log manually using command : PURGE BINARY LOGS TO 'mysql-bin.010'; PURGE BINARY LOGS BEFORE '2008-04-02 22:46:26';

MySQL has built-in functionality that allows you to log SQL queries to a file , You can enable the full SQL queries logs to a file or only slow running queries log. It is easy for us to troubleshoot/ debug the sql statement if SQL queries log enable , The slow query log is used to find queries that take a long time to execute and are therefore candidates for optimization. To enable you just need to add some lines to your my.cnf file, and restart. Add the following: * To enable slow Query Log only log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 After enabling slow query, mysqld writes a statement to the slow query log file and it consists of all SQL statements that took more than long_query_time seconds to execute. The time to acquire the initial table locks is not counted as execution time. mysqld only log after SQL statements has been executed and after all locks have been released, so log order might be different from execution order. The minimum and defau

1. The client sends the SQL statement to the server. -> The protocol is halfduplex, which means that at any given time the MySQL server can be either sending or receiving messages, but not both. It also means there is no way to cut a message short. The client sends a query to the server as a single packet of data. This is why the max_packet_size configuration variable is important if you have large queries. Once the client sends the query, it doesn’t have the ball anymore; it can only wait for results. The response from the server usually consists of many packets of data.When the server responds, the client has to receive the entire result set. It cannot simply fetch a few rows and then ask the server not to bother sending the rest. If the client needs only the first few rows that are returned, it either has to wait for all of the server’s packets to arrive and then discard the ones it doesn’t need, or disconnect ungracefully. Neither is a good idea, which is why appropriate LIMIT

The Unix top command is designed to help users determine which processes are running and which applications are using more memory or processing power than they should be. The top command is very easy to use but you should know the things in details. The output of to is : top output: top - 22:09:08 up 14 min, 1 user, load average: 0.21, 0.23, 0.30 Tasks: 81 total, 1 running, 80 sleeping, 0 stopped, 0 zombie Cpu(s): 9.5%us, 31.2%sy, 0.0%ni, 27.0%id, 7.6%wa, 1.0%hi, 23.7%si, 0.0%st Mem: 255592k total, 167568k used, 88024k free, 25068k buffers Swap: 524280k total, 0k used, 524280k free, 85724k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 3166 apache 15 0 29444 6112 1524 S 6.6 2.4 0:00.79 httpd 3161 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.79 httpd 3164 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.75 httpd 3169 apache 15 0 29444 6112 1524 S 5.9 2.4 0:00.74 httpd 3163 apache 15

Block a particular #service iptables start #iptables -I INPUT -s 10.1.24.4 -j DROP This command will simply drop any packet coming from the address 10.1.24.4 To list the chains: #iptables -L -n To make persist : #service iptables status #iptables-save (copy output) #emacs /etc/sysconfig/iptables (paste output) #service iptables restart make sure iptables service start on default.

Today I have configured NX server. Now, Next task is to create users and really its very time consuming and boring task. Usually you use useradd command to create a new user or update default new user information from command line. So i have explore Linux and searched on Google , I have found few scripts to do this. But later I have found one good and easy solution. Here is that, Update and create new users in bulk. newusers command reads a file of user name and clear-text password pairs and uses this information to update a group of existing users or to create new users. Each line is in the same format as the standard password file. This command is intended to be used in a large system environment where many accounts are updated at a single time (batch mode). Since username and passwords are stored in clear text format make sure only root can read/write the file. Use chmod command: # touch /root/bulk-user-add.txt # chmod 0600 /root/bulk-user-add.txt Create a user list as follows. Op

Boot time parameters you should know about the Linux kernel. The Linux kernel accepts boot time parameters as it starts to boot system. This is used to inform kernel about various hardware parameter. The kernel command line syntax name=value1,value2,value3… Where, * name : Keyword name, for example, init, ro, boot etc Common Boot time parameters init This sets the initial command to be executed by the kernel. Default is to use /sbin/init, which is the parent of all processes. To boot system without password pass /bin/bash or /bin/sh as argument to init init=/bin/bash single The most common argument that is passed to the init process is the word 'single' which instructs init to boot the computer in single user mode, and not launch all the usual daemons root=/dev/device This argument tells the kernel what device (hard disk, floppy disk) to be used as the root filesystem while booting. For example following boot parameter use /dev/sda1 as the root file system: root=/dev/sda1 I

MySQL’s built-in replication capability is the foundation for building large, high performance applications on top of MySQL. Replication lets you configure one or more servers as slaves, or replicas, of another server. 1. Setting up Replication: Three threads are involved in Replication: One on the master and two on the slave. • The I/O thread on the slave connects to the master and requests the binary update log. The Binary log dump thread on the master sends the binary update log to the slave on request. • Once on the slave, the I/O thread reads the data sent by the master and copies it to the relay log in the data directory. • The third thread, also on the slave, is the SQL Thread, which read and executes the queries from the relay log to bring the slave in alignment with the master. 2. Replication with example: Version: Both master and slave should be the same version. Otherwise replication will be improper. Network Configuration Settings Master Server IP Address: 10.5.1.10 Slave S