Extracting a single table from a huge mysqldump file

Someday you may find yourself in a situation where you want to restore a single table of your MySQL database. Hopefully this is in a lab situation or on a personal development box somewhere – but even if it is production you need not fear. There are plenty of articles about restoring your entire database, but not a lot about restoring a single table. What if your coding compadre’s mistakenly change the field for every row in a table on you, or maybe you want to alter all of the data in a table but want an easy way to ‘reset’ things so that you can tweak and try again? If you aren’t taking table level backups restoring that single table can be rather difficult. You’ll locate your MySQL dump, and realize that it is huge (even when zipped), and you don’t want to wait around to restore your entire DB in order to recover one measly table. So what to do? The file is too large to open in a text editor and do anything meaningful with it – it would take way too much time. This is where a little bit of ‘awk‘ magic can save your day. Using awk, you can extract a block of text from the MySQL dump that pertains to the table you want to work with.

First, you have to know where in your mysqldump output you want to begin your extraction, and where you want to end it. The key here is finding something unique at the beginning and ending of the block that won’t be found anywhere else.

A sample mysqldump contains something like the following:

--
-- Table structure for table `abc`
--
. . .
DROP TABLE IF EXISTS `abc`;
CREATE TABLE `abc` ( . . .
LOCK TABLES `abc` WRITE;
INSERT INTO `abc` VALUES (1,0,'2 . . .
UNLOCK TABLES;
. . .
--
-- Table structure for table `xyz`
--

As you can see, we have a line with the comment “Table structure for table `abc`”, then all of the dropping, creating, and inserting for the table, and then another comment for the next table. These two lines are perfect for grabbing all of the operations pertinent to our one table.

To extract the dump for a single table from an entire database dump, run the following from a command prompt:

$ awk ‘/Table structure for table .abc./,/Table structure for table .xyz./{print}’ mydumpfile.sql > /tmp/extracted_table_abc.sql

The awk command is very powerful – the above command searches through the dump file, and as soon as it matches a line containing the first search string (denoted by the first set of slashes), it prints that line and every subsequent line until it encounters a line containing the second search string (denoted by the second set of slashes). FYI, the periods surrounding the table names above are wildcard characters.

Now the extracted_table.sql file contains the SQL to restore your table! One final thing: There are usually various parameters at the top of your mysqldump file that you may need to set before restoring your table, depending on the complexity of your database (i.e. disabling foreign key checks.)

To restore your table, you’d run:

$ mysql -u user -ppwd mydb < /tmp/extracted_table_abc.sql

Alternate Method:

A second option is to restore your data into a temporary database (assuming you have the disk space), extract the table you’re interested in to it’s own dump file, and then restore that dump file to your original database.

Create a new database, name it something easy to distinguish from your production one, i.e. fakedb.

Restore your data to the fakedb with a command like this:

$ mysql -u user -ppassword fakedb <>

From fakedb, grab the data you want from your target table:

mysql> select * from targettable into outfile “/tmp/mytablebackup.bak”;

On the production db, where you have your undesired data, clear it all out with:

mysql> delete from baddatatable;

Import the good stuff back into it:

mysql> load data infile “/tmp/mytablebackup.bak” into table baddatatable;

Enjoy J

MySQL – Extractiing a Single Table from a mysqldump File

If you want to extract "xyz" table from "example.sql" dumpfile, the below statement can extract the desired table.

sed -n '/^CREATE TABLE `xyz`/,/^-- Table structure for table ` `/p' example.sql > xyz.sql 2>error &

Slow queries log, general log...

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 default values of long_query_time are 1 and 10, respectively.

* To enable full Log Query

log=/var/log/mysqldquery.log

The above will log all queries to the log file.

Selecting Queries to Optmize
• The slow query log
– Logs all queries that take longer than long_query_time
– Can also log all querie s that don’t use indexes with
--log-queries-not-using-indexes
– To log slow administatve commands use
--log-slow-admin-statements
– To analyze the contents of the slow log use
mysqldumpslow

Using AWK: For Pattern Matching and Processing

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 file testfile.

awk 'NR == 2, NR == 4' testfile - print the 2nd through 4th lines (default action is to print entire line)

awk '/chevy/' testfile - print only lines matching regular expression, same as grep 'chevy' testfile
awk '{print $3, $1}' testfile - print third and first field of all lines (default pattern matches all lines)

awk '/chevy/ {print $3, $1}' testfile - print third and first fiield of lines matching regular expression
awk '$3 == 65' testfile - print only lines with a third field value of 65
awk '$5 < = 3000' testfile - print only lines with a fifth field value that is less than or equal to 3000

awk '{print $1}' testfile - print first field of every record
awk '{print $3 $1}' testfile
awk '{print $3, $1}' testfile - inserts output field separator (variable OFS, default is space)

awk -F, '{print $2}' testfile - specifies that , is input field separator, default is space or tab
awk '$2 ~ /[0-9]/ {print $3, $1}' testfile - searches for reg-exp (a digit) only in the second field

awk '{printf "%-30s%20s\n", $3, $2}' testfile - print 3rd field left-justified in a 30 character field, 2nd field right-justified in a 20 character field, then skip to a new line (required with printf)


awk '$3 <= 23' testfile - prints lines where 3rd field has a value <= 23
awk '$3 <='$var1' {print $3}' testfile - $var1 is a shell variable, not an awk variable, e.g. first execute: var1=23

awk '$3<='$2' {$3++} {print $0}' testfile - if field 3 <= argument 2 then increment field 3, e.g. first execute: set xxx 23


awk '$3> 1 && $3 < 23' testfile - prints lines where 3rd field is in range 1 to 23

awk '$3 <> 4' testfile - prints lines where 3rd field is outside of range 2 to 4
awk '$3 < "4"' testfile - double quotes force string comparison
NF is an awk variable meaning # of fields in current record

awk '! (NF == 4)' testfile - lines without 4 fields
NR is an awk variable meaning # of current record
awk 'NR == 2,NR==7' testfile - range of records from record number 2 to 7

BEGIN is an awk pattern meaning "before first record processed"
awk 'BEGIN {OFS="~"} {print $1, $2}' testfile - print 1st and 2nd field of each record, separated by ~
END is an awk pattern meaning "after last record processed"

awk '{var+=$3} END {print var}' testfile - sum of 3rd fields in all records
awk '{var+=$3} END {print var/NR}' testfile - average of 3rd fields in all records - note that awk handles decimal arithmetic


awk '$5 > var {var=$5} END {print var}' testfile - maximum of 5th fields in all records
awk '$5 > var {var=$5} END {print var}' testfile - maximum of 5th fields in all records

sort -rk5 testfile | awk 'NR==1 {var=$5} var==$5 {print $0}' - print all records with maximum 5th field

Simple awk operations involving functions within the command line:


awk '/chevy/' testfile

# Match lines (records) that contain the keyword chevy note that chevy is a regular expression...

awk '{print $3, $1}' testfile

# Pattern not specified - therefore, all lines (records) for fields 3 and 1 are displayed

# Note that comma (,) between fields represents delimiter (ie. space)

awk '/chevy/ {print $3, $1}' testfile

# Similar to above, but for chevy

awk '/^h/' testfile


# Match testfile that begin with h

awk '$1 ~ /^h/' testfile ### useful ###

# Match with field #1 that begins with h

awk '$1 ~ /h/' testfile


# Match with field #1 any epression containing the letter h

awk '$2 ~ /^[tm]/ {print $3, $2, "$" $5}' testfile

# Match testfile that begin with t or m and display field 3 (year), field 2 (model name) and then $ followed by field 4 (price)

--------------------------------------------------------------------------------------------------

Complex awk operations involving functions within the command line:


awk ?/chevy/ {print $3, $1}? testfile

# prints 3rd & 1st fields of record containing chevy

awk ?$1 ~ /^c/ {print $2, $3}? testfile

# print 2nd & 3rd fields of record with 1st field beginning with c

awk ?NR==2 {print $1, $4}? testfile

# prints 1st & 4th fields of record for record #2

awk ?NR==2, NR==8 {print $2, $3}? testfile

# prints 2nd & 3rd fields of record for records 2 through 8

awk ?$3 >= 65 {print $3, $1}? testfile

# prints 3rd & 1st fields of record with 3rd field >= 65

awk ?$5 >= ?2000? && $5 < ?9000? {print $2, $3}? testfile

# prints 2nd & 3rd fields of record within range of 2000 to under 9000

Using mysqldumpslow for analyzing mysql slow queries

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.txt
It will put top ten slow query in file /tmp/top_ten_slow_query.txt
2. mysqldumpslow -s c -a -t 10 /var/lib/mysql/mysql-slow.log.1 > /tmp/top_ten_repeat_slow_query.txt
It will put top ten repeat slow query in file top_ten_repeat_slow_query.txt

Extract single db from dump file

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.

For extracting a particular table from a dump file, click here...

stop/start a process in linux temporarily from any session

We might have requirement that, particular job should stop for certain period of time and start again.

Most of us familiar are with KILL command, but here is another feature of KILL command , which saves your life :-

#kill -STOP 21234 (where 21234 is process id)

#kill -CONT 21234 (where 21234 is process id)

Check your memory in linux

We all are always worry about memory weather it is main memory, secondary, or other swap
These are few command which help u to debug your memory.

Simple stat.
#free
#du -h
#df -h

Some complex stat.

#cat /proc/meminfo
#dmesg | grep -n kernel

This command give you files have more than 100M usages.

#find / -size +100M -fprintf /root/big.txt '%-10s %p\n'

Changing the default port for SSh user

Default port for SSH user is 22. But we can change the default port to some another port.
eg. I want to run ssh service on port 99 instead of 22.

Make the following change.

open files
#emacs /etc/ssh/ssh_config
#emacs /etc/ssh/sshd_config

search for port ie is default port = 22

make port = 99

or

You can run on any port, But make sure other service not running on that port.

Recover Root Password of MYSQL

Step # 1 : Stop mysql service
# /etc/init.d/mysql stop

Step # 2: Start to MySQL server w/o password:
# mysqld_safe --skip-grant-tables &

Step # 3: Connect to mysql server using mysql client:
# mysql -u root

Step # 4: Setup new MySQL root user password
mysql> use mysql;
mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';
mysql> flush privileges;
mysql> quit

Step # 5: Stop MySQL Server:
# /etc/init.d/mysql stop

Step # 6: Start MySQL server and test it
# /etc/init.d/mysql start
# mysql -u root -p