Useful Linux Commands

Command		Description
•	man -t ascii | ps2pdf - > ascii.pdf	make a pdf of a manual page
	which command	Show full path name of command
	time command	See how long a command takes
•	time cat	Start stopwatch. Ctrl-d to stop. See also sw
dir navigation
•	cd -	Go to previous directory
•	cd	Go to $HOME directory
	(cd dir && command)	Go to dir, execute command and return to current dir
•	pushd .	Put current dir on stack so you can popd back to it
file searching
•	alias l='ls -l --color=auto'	quick dir listing
•	ls -lrt	List files by date. See also newest and find_mm_yyyy
•	ls /usr/bin | pr -T9 -W$COLUMNS	Print in 9 columns to width of terminal
	find -name '*.[ch]' | xargs grep -E 'expr'	Search 'expr' in this dir and below. See also findrepo
	find -type f -print0 | xargs -r0 grep -F 'example'	Search all regular files for 'example' in this dir and below
	find -maxdepth 1 -type f | xargs grep -F 'example'	Search all regular files for 'example' in this dir
	find -maxdepth 1 -type d | while read dir; do echo $dir; echo cmd2; done	Process each item with multiple commands (in while loop)
•	find -type f ! -perm -444	Find files not readable by all (useful for web site)
•	find -type d ! -perm -111	Find dirs not accessible by all (useful for web site)
•	locate -r 'file[^/]*\.txt'	Search cached index for names. This re is like glob *file*.txt
•	look reference	Quickly search (sorted) dictionary for prefix
•	grep --color reference /usr/share/dict/words	Highlight occurances of regular expression in dictionary
archives and compression
	gpg -c file		Encrypt file
	gpg file.gpg		Decrypt file
	tar -c dir/ | bzip2 > dir.tar.bz2		Make compressed archive of dir/
	bzip2 -dc dir.tar.bz2 | tar -x		Extract archive (use gzip instead of bzip2 for tar.gz files)
	tar -c dir/ | gzip | gpg -c | ssh user@remote 'dd of=dir.tar.gz.gpg'		Make encrypted archive of dir/ on remote machine
	find dir/ -name '*.txt' | tar -c --files-from=- | bzip2 > dir_txt.tar.bz2		Make archive of subset of dir/ and below
	find dir/ -name '*.txt' | xargs cp -a --target-directory=dir_txt/ --parents		Make copy of subset of dir/ and below
	( tar -c /dir/to/copy ) | ( cd /where/to/ && tar -x -p )		Copy (with permissions) copy/ dir to /where/to/ dir
	( cd /dir/to/copy && tar -c . ) | ( cd /where/to/ && tar -x -p )		Copy (with permissions) contents of copy/ dir to /where/to/
	( tar -c /dir/to/copy ) | ssh -C user@remote 'cd /where/to/ && tar -x -p'		Copy (with permissions) copy/ dir to remote:/where/to/ dir
	dd bs=1M if=/dev/sda | gzip | ssh user@remote 'dd of=sda.gz'		Backup harddisk to remote machine
rsync (Network efficient file copier: Use the --dry-run option for testing)
	rsync -P rsync://rsync.server.com/path/to/file file		Only get diffs. Do multiple times for troublesome downloads
	rsync --bwlimit=1000 fromfile tofile		Locally copy with rate limit. It's like nice for I/O
	rsync -az -e ssh --delete ~/public_html/ remote.com:'~/public_html'		Mirror web site (using compression and encryption)
	rsync -auz -e ssh remote:/dir/ . && rsync -auz -e ssh . remote:/dir/		Synchronize current directory with remote one
ssh (Secure SHell)
	ssh $USER@$HOST command		Run command on $HOST as $USER (default command=shell)
•	ssh -f -Y $USER@$HOSTNAME xeyes		Run GUI command on $HOSTNAME as $USER
	scp -p -r $USER@$HOST: file dir/		Copy with permissions to $USER's home directory on $HOST
	scp -c arcfour $USER@$LANHOST: bigfile		Use faster crypto for local LAN. This might saturate GigE
	ssh -g -L 8080:localhost:80 root@$HOST		Forward connections to $HOSTNAME:8080 out to $HOST:80
	ssh -R 1434:imap:143 root@$HOST		Forward connections from $HOST:1434 in to imap:143
	ssh-copy-id $USER@$HOST		Install public key for $USER@$HOST for password-less log in
wget (multi purpose download tool)
•	(cd dir/ && wget -nd -pHEKk http://www.pixelbeat.org/cmdline.html)		Store local browsable version of a page to the current dir
	wget -c http://www.example.com/large.file		Continue downloading a partially downloaded file
	wget -r -nd -np -l1 -A '*.jpg' http://www.example.com/dir/		Download a set of files to the current directory
	wget ftp://remote/file[1-9].iso/		FTP supports globbing directly
•	wget -q -O- http://www.pixelbeat.org/timeline.html | grep 'a href' | head		Process output directly
	echo 'wget url' | at 01:00		Download url at 1AM to current dir
	wget --limit-rate=20k url		Do a low priority download (limit to 20KB/s in this case)
	wget -nv --spider --force-html -i bookmarks.html		Check links in a file
	wget --mirror http://www.example.com/		Efficiently update a local copy of a site (handy from cron)
networking
	ethtool eth0		Show status of ethernet interface eth0
	ethtool --change eth0 autoneg off speed 100 duplex full		Manually set ethernet interface speed
	iwconfig eth1		Show status of wireless interface eth1
	iwconfig eth1 rate 1Mb/s fixed		Manually set wireless interface speed
•	iwlist scan		List wireless networks in range
•	ip link show		List network interfaces
	ip link set dev eth0 name wan		Rename interface eth0 to wan
	ip link set dev eth0 up		Bring interface eth0 up (or down)
•	ip addr show		List addresses for interfaces
	ip addr add 1.2.3.4/24 brd + dev eth0		Add (or del) ip and mask (255.255.255.0)
•	ip route show		List routing table
	ip route add default via 1.2.3.254		Set default gateway to 1.2.3.254
•	host pixelbeat.org		Lookup DNS ip address for name or vice versa
•	hostname -i		Lookup local ip address (equivalent to host `hostname`)
•	whois pixelbeat.org		Lookup whois info for hostname or ip address
•	netstat -tupl		List internet services on a system
•	netstat -tup		List active connections to/from system
text manipulation (Note sed uses stdin and stdout. Newer versions support inplace editing with the -i option)
	sed 's/string1/string2/g'		Replace string1 with string2
	sed 's/\(.*\)1/\12/g'		Modify anystring1 to anystring2
	sed '/ *#/d; /^ *$/d'		Remove comments and blank lines
	sed ':a; /\\$/N; s/\\\n//; ta'		Concatenate lines with trailing \
	sed 's/[ \t]*$//'		Remove trailing spaces from lines
	sed 's/\([`"$\]\)/\\\1/g'		Escape shell metacharacters active within double quotes
•	seq 10 | sed "s/^/      /; s/ *\(.\{7,\}\)/\1/"		Right align numbers
	sed -n '1000{p;q}'		Print 1000th line
	sed -n '10,20p;20q'		Print lines 10 to 20
	sed -n 's/.*

What’s New in MySQL 5.5 and 5.6

Hi All 

 

I checked the presentation of  Data charmer and Sheeri. It's awasome.

 

 What’s New in MySQL 5.5 and 5.6 Replication

1.    MySQL  5.5 supports  Semi Synchronous replication

2.    MySQL  5.6 supports delayed replication

3.    Server UUID ( New: Added  to ensure  Global transaction ID )

4.    Crash-Safe Slave

5.    Multi-threaded slave (Most Awaited features)

6.    Global transaction identifiers

 MySQL 5.5 – Semi synchronous replication

It increases reliability of slaves by making sure that the changes of master are committed on at least one of the slaves before the write query is returned.

To use it, you will require to install the plug-in rpl_semi_sync_master on the master and rpl_semi_sync_slave on the slave. Then you can configure the master/slave to use the plug-in. You need to restart both the master and slave to activate the plug-in and configurations.

Once you restart you will see variables like rpl_semi_sync and you can see whether it’s enabled, what the timeout, trace level, etc are.

Rpl_semi_sync_master_no_trx (how many transactions didn’t go to the slaves) and Rpl_semi_sync_master_yes_trx (was it successfully sent) are status variables or counters on the master.

 MySQL 5.6 – Delayed replication

It is part of the MySQL 5.6 that ships with MySQL, No need to install plug-ins. You need to execute below command after stopping slave.

CHANGE MASTER TO MASTER_DELAY=#seconds;

Show slave status shows you two parameters in output SQL_Delay and SQL_Remaining_Delay.  Where SQL_Delay show total defined delay and SQL_remaining_delay shows the remaining delay.

UUID – Each master has a UUID in addition to the server_id. This is important for global transaction IDs, which makes sure that even if you change server_id’s the global transaction ID is still associated with the same server.  So you can finally identify the each transaction on a server.

More crash-safe replication: Replication in MySQL 5.6 is crash-safe.   It will make sure a statement is going to execute more than one time.

Execute below command
SHOW VARIABLES LIKE ‘%info%’ ;

Apart from these variables one more variables use is called slave_parallel_workers.

These variables help ensure that if the slave crashes, the relay log and master.info files are kept in sync and don’t try to retrieve or apply a statement more than once.

The crash-safe features are only for innodb, but MySQL ships the following tables with MyISAM engines.

1.    slave_master_info

2.    slave_relay_log_info

3.    slave_worker_info

So when you start working with it, change engine of these tables to innodb using command

 ALTER TABLE …ENGINE=INNODB

When you do SHOW SLAVE STATUS you’ll see Master_info_file: mysql.slave_master_info

When you will check these tables , you will   get a lot of information. Table slave_master_info is most, but not all, of the same info in SHOW SLAVE STATUS.

Table slave_worker_info has 1 line for each worker you have set.

Multi-threaded slave aka parallel replication

1.    It requires MySQL 5.6 in both master and slave (can’t do 5.5 master, 5.6 slave) to actually do parallel replication.

2.    Parallel replication with a MySQL 5.5 master will slow down replication – so you can do parallel replication but it’s 3x slower (right now).

3.    Data gets parallelized by the schema – so if you have 2 queries in the same schema, they can’t be done in parallel, but 2 queries in 2 different schemas, they can be done in parallel. If you only have one schema, don’t bother using parallel replication.

To use it configure the global dynamic parameter:

SET GLOBAL slave_parallel_workers=10; (or 3, or whatever, default is 0).

 How do you choose the # of parallel workers to choose? MySQL is smart enough to figure out how to use the parallel workers around the schemas you have. In tests, parallel replication was 3-4x faster than regular replication.

Global transaction ID – If a master fails, all the data from the master cannot be accessed. So you have to figure out which slave is the most advanced one (in terms of time ), promote that slave to the master, and figure out which transactions the other slaves were missing. But it’s hard to know in the relay log, because it can be a different file/position in each slave. So the global transaction id uses a unique number, so you don’t have to worry about the relay log filename/position. To use the global transaction id, all machines in the cluster (master and slaves) should have following parameters in their configuration files.

log-bin
log-slave-updates
gtid-mode=ON
disable-gtd-unsafe-statemnets –tricky

Why tricky? This feature does not work on non-transactional tables, like MyISAM. If you try to remove the anonymous user from the mysql.user table and you have this set, it’s not safe so it doesn’t work and you get a master error. GRANT, REVOKE, DROP USER will work, but DELETE, INSERT, etc don’t work.

CREATE TABLE…SELECT does not work with this feature, so don’t turn it on if you use that.

After changing the variables in the mysql config file, restart the servers to pick up the changes.

You then see the

 @@SESSION.GTID_NEXT=’44556A96-8417-11E1-9589-2BD5ACDD51FD:1′
After the : is the transaction ID number (1 in this case), everything before it is the server’s UUID. Using this information you can more easily find which transactions in the binary log you need.

There are 2 more lines in SHOW SLAVE STATUS\G:
Retrieved_Gtid_Set:1-100
Executed_Gtid_Set:1-100

The show slave output will be like below now.

MySQL Cluster 7.2 & Future Webcasts: Check out this MySQL Cluster 7.2 DemoFrom the Cluster 7.2 Demo if you have ... http://t.co/fJjaSyB9 -- planetmysql (@planetmysql)

MariaDB 5.3 is now GA(Comptitor of MySQL)

MariaDB 5.3 is now GA

This is another, in-case-you-missed-it, but it is definitely worth mentioning again:
MariaDB 5.3 is now GA
So, if you’re using MariaDB 5.1 or 5.2, I’d definitely recommend upgrading to the new MariaDB 5.3.
Also, if you’re a Windows user using MySQL 5.1, I’d highly consider making the switch to MariaDB 5.3 due to all of the Windows-specific improvements in it.
MariaDB 5.3 Enhancements:

• Subquery optimizations
• Semi-join subquery optimizations
• Non-semi-join optimizations
• Subquery Cache
• Subquery Optimizations Map
• Optimizations for derived tables and views
• Disk access optimization
• Join optimizations
• Index Merge improvements
• Optimizer control
• NoSQL-style interfaces
• Group commit for the binary log
• Replication and binary logging enhancements
• Microsecond support for NOW() and timestamp, time, and datetime columns
• Windows performance improvements
• And a number of other general improvements

MariaDB 5.3 Windows-Specific Performance improvements include:

• Backported Windows performance patches from MySQL 5.5
• Asynchronous IO in XtraDB is redesigned and is now faster, due to the use of IO completion ports
• Additional durability option for XtraDB : innodb_flush_method can now be O_DSYNC, like on Unixes. (The effect of using this option is that the log file is opened with FILE_FLAG_WRITETHROUGH, and FlushFileBuffers() is not done. This may improve speed in write-heavy scenarios.)
• A new Windows MSI installer
• Includes a GUI-tool, HeidiSQL

All of the above, and more are documented in further detail here:
http://kb.askmonty.org/en/what-is-mariadb-53
You can download it here:
http://downloads.askmonty.org/mariadb/5.3/
(And just for reference, if running MariaDB you should probably keep your eye on the new MariaDB 5.5 too (5.5.20 was alpha, 5.5.21 is now beta), benchmarks here).

Why You Need a Part-Time Remote MySQL Expert

Copied :)

 

 Why You Need a Part-Time Remote MySQL Expert

When I thought to start to write this post, I was thinking about all the logical reasons why you would consider hiring a part-time remote MySQL database administrator or MySQL expert.
It then occurred to me that most of the readers, who are reading this post now, already know or can imagine the benefits of hiring a DBA to take care of their system.

So what I want to offer is a case based on the emotional benefits and value a part-time DBA can offer you and your business.

1) Peace of Mind

A DBA can offer you peace of mind that one of your core business components is being taken care of. It is like maintaining your house gas boiler or car every now to keep it running in good working order and preventing it from blowing up. Except that in this example, if it does break, you can't use the entire house or car until they are fixed.
What is important to remember here is that there is no immediate justification for spending money on maintenance. There is even a sense of loss when you do spend the money without seeing any value from it. You are left in a mental tug-of-war between the possibility of downtime and loss of profits to the immediate hit to your cash flow.

What usually helps here is to calculate the cost of downtime with the estimated time and effort to recover from it divided by the chance that it will happen.

In the event that your main database servers breaks down:

• How much money are you losing from sales?
• How much money are you losing from possible future sales? (For example, sales people and marketeers cannot send people to the website or loss of reputation)
• How much money will it cost to get people (even at the dead of night) to resolve the problem? (people multiplied by hours or overtime hours)
• How much disruption will it cause later on? (For example, sales people need to speak with clients to assure them everything is OK for two days instead of finding new clients)

Divided by - the chance that your database servers will break down.

• Are we expecting any seasonal bursts of traffic and will the database servers handle it?
• Will the hardware that the database server sits on hold up in the next 6 months?
• How many single-points-of-failure are there in the environment where the database servers are?
• How many people have DROP TABLE access to your servers and how often to people run "heavy" reporting queries on them?
• How high is your developers skill level when it comes to writing efficient queries that prevent high database usage? (When releasing a new product or feature)

Once you answered all (or some) of those questions and jotted down some numbers, you will then have a figure of how much money you should spend to prevent downtime. This is a very basic risk management formula for managers and will allow you to get some perspective.
The mental tug-of-war can take a break as you are now allocating resources for something you planned a head of time.

2) Preventing loss of confidence

More often then not, I see a lot of small companies have people maintain their databases who do not have a lot of expertise to do it. Typically, if you have some developers, you ask one of them to do it. If you have a system administrator then that usually falls under that responsibility.

What happens then is that those people (usually, but not always) not fully understand their databases and just "keep them running". If something comes up that is a bit out of the ordinary then things start to get stressful.

I've even seen the database becomes the "don't touch it or it will break" system in the company.
At that point, you lose confidence in your system and prefer to not have to deal with it as much as possible.


3) Energy, Focus and Flow

When you have people with database expertise on hand, you can solve database related problems very quickly. Problems that were once difficult and complicated suddenly become straight forward.
For example, instead of having meetings discussing how to implement a very data intensive operation in the best way, you now have someone that can provide a quick answer or at least a direction to help implement it based on best practice and experience.
Your team can become more cross-functional as the expertise gets shared and solutions come more quickly.
You feel more in a "flow" - that things are moving in the right direction. You can focus on things which you would rather focus on you feel more energetic because of it.


4) Trust, Knowledge and Speed

Having an expert in your business and part of your team is very comforting. The more this expert is involved your business, the more this expert knows, understands and appreciate how your business runs and solves theirs problems. There is a cost benefit of having someone you trust and is knowledgeable about your business when you compare it to:

1. Finding a new expert (Possible additional fees for system or agent that found the expert).
2. Evaluating the person is in fact, an expert in their field.
3. Explaining what the business problem is
4. Double-checking if their solution does work or fully solves your problem.
5. If you are not happy with them, go to step 1.
6. If there was a misunderstanding in communication, go to step 3
7. Implement solution

Put a price on each step and compare it to writing an email to your expert saying "we have a problem with that thing we spoke about a while back. Can you please take a look at it?"

In fact, I would go one step further. If your expert is part of your team and they "own" and are responsible for your database system, there is a good chance that they would have come to you after finding the problem themselves and with a possible solution.

I never fully appreciated outsourcing a lot of core parts of a business to one-time development shops. I believe there is valuable business-knowledge that gets lost and that the additional overhead to manage everything becomes very high.

Conclusion

I truly hope that this post may benefit people by giving them peace of mind, confidence and a clear direction that will help them with their business and make it more enjoyable.

Check My Migration Presentation

http://vimeo.com/6678128