Mysql QA1

1. How to log-in to mysql server?
mysql -h<hostname> -u<username> -p [ for the password]

   2. How do you start MySQL on Linux? 
/etc/init.d/mysql start

   3. Explain the difference between mysql and mysqli interfaces in PHP? 
mysql is the object-oriented version of mysql library functions.

   4. What’s the default port for MySQL Server?
 3306

   5. What does tee command do in MySQL? 
tee followed by a filename turns on MySQL logging to a specified file. It can be stopped by command notee.

   6. Can you save your connection settings to a conf file?
Yes, and name it ~/.my.conf. You might want to change the permissions on the file to 600, so that it’s not readable by others.

   7. How do you change a password for an existing user via mysqladmin?
mysqladmin -u root -p password "newpassword"

   8. Use mysqldump to create a copy of the database?
mysqldump -h mysqlhost -u username -p mydatabasename > dbdump.sql

   9. Have you ever used MySQL Administrator and MySQL Query Browser? 
Describe the tasks you accomplished with these tools.

  10. What are some good ideas regarding user security in MySQL? 
There is no user without a password. There is no user without a user name. There is no user whose Host column contains % (which here indicates that the user can log in from anywhere in the network or the Internet). There are as few users as possible (in the ideal case only root) who have unrestricted access.

  11. Explain the difference between MyISAM Static and MyISAM Dynamic.
 In MyISAM static all the fields have fixed width. The Dynamic MyISAM table would include fields such as TEXT, BLOB, etc. to accommodate the data types with various lengths. MyISAM Static would be easier to restore in case of corruption, since even though you might lose some data, you know exactly where to look for the beginning of the next record.

  12. What does myisamchk do?
It compressed the MyISAM tables, which reduces their disk usage.

  13. Explain advantages of InnoDB over MyISAM? 
Row-level locking, transactions, foreign key constraints and crash recovery.

  14. Explain advantages of MyISAM over InnoDB?
Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity.

  15. What are HEAP tables in MySQL?
HEAP tables are in-memory. They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables. You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.
  
  16. How do you control the max size of a HEAP table?
MySQL config variable max_heap_table_size.
  
  17. What are CSV tables?
Those are the special tables, data for which is saved into comma-separated values files. They cannot be indexed.
  
  18. Explain federated tables.
Introduced in MySQL 5.0, federated tables allow access to the tables located on other databases on other servers.
  
  19. What is SERIAL data type in MySQL?
BIGINT NOT NULL PRIMARY KEY AUTO_INCREMENT
  
  20. What happens when the column is set to AUTO INCREMENT and you reach the maximum value for that table?
It stops incrementing. It does not overflow to 0 to prevent data losses, but further inserts are going to produce an error, since the key has been used already.
  
  21. Explain the difference between BOOL, TINYINT and BIT.
Prior to MySQL 5.0.3: those are all synonyms. After MySQL 5.0.3: BIT data type can store 8 bytes of data and should be used for binary data.
  
  22. Explain the difference between FLOAT, DOUBLE and REAL.
FLOATs store floating point numbers with 8 place accuracy and take up 4 bytes. DOUBLEs store floating point numbers with 16 place accuracy and take up 8 bytes. REAL is a synonym of FLOAT for now.
  
  23. If you specify the data type as DECIMAL (5,2), what’s the range of values that can go in this table?
999.99 to -99.99. Note that with the negative number the minus sign is considered one of the digits.
  
  24. What happens if a table has one column defined as TIMESTAMP?
That field gets the current timestamp whenever the row gets altered.
  
  25. But what if you really want to store the timestamp data, such as the publication date of the article? Create two columns of type TIMESTAMP and use the second one for your real data.
  
  26. Explain data type TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
The column exhibits the same behavior as a single timestamp column in a table with no other timestamp columns.
  
  27. What does TIMESTAMP ON UPDATE CURRENT_TIMESTAMP data type do?
On initialization places a zero in that column, on future updates puts the current value of the timestamp in.
  
  28. Explain TIMESTAMP DEFAULT ‘2006:09:02 17:38:44′ ON UPDATE CURRENT_TIMESTAMP.
A default value is used on initialization, a current timestamp is inserted on update of the row.
  
  29. If I created a column with data type VARCHAR(3), what would I expect to see in MySQL table? CHAR(3), since MySQL automatically adjusted the data type.

yum

Using yum:

yum install package...

yum remove package...

yum update [package...]

Searching packages/files:

yum search searchterm

yum list (all | available | extra | installed | recent | updates )

yum info packagename

Searching files:

yum whatprovides filename

Configuring Additional Repositories:

Create a file in /etc/yum.repos.d for your repository

Required Information:

[repo-name]

name=A nice description

baseurl=http://yourserver.com/path/to/repo

enabled=1

gpgcheck=1

Creating a private repository:

1. crate a directory to hold your packages

2. Make this directory available by *http/ftp*

3. install the *createrepo* RPM

4. Run * createrepo -v /package/directory *

5. This will create a * repodata subdirectory and the needs support files 

About Me

About Me:

Following the links where you can find few of my details and site information:

My LinkedIn profile: http://in.linkedin.com/pub/amit-mund/3/884/b9

Site: https://sites.google.com/site/amitmund/

Meetup: http://www.meetup.com/lspe-in/

How to setup ssh environment with a password bashed key pairs

1. Background:

The purpose of this document is to overcome the issue that we face at our AWS environment. Its true that amazon not keeping our private key, when we have create a new instance. But we are not very strict about those aws keys, most of the team inside the company use the key and keep the same in lots of the place, and this can cause of a security hole.

Example:

Let say foo.com is an online company and the qa environment people is not keeping track of where the keys are keeping. They are not very sure about the keys. Let say if any of the qa team person kept the key at document root and later some how that key got into hand of some cracker, then he/she can log-in to the QA environment and that's compromise our environment.

2. What we can do?

Its true that without the key no one can able to log-in. But we don't even want to share those aws keys to everyone.

So, create your own ssh-key pairs, with a pass-phase.

1. ssh-keygen [enter]

2. select your preferred key type [rsa / dsa] or can go for the default one. [ enter ] 

3. in pass-phase enter a password. 

After that you can update the public key of that key pairs to qa server's authorized_keys and share the QA team. Now onwards when ever they want to login to the qa servers, they can use the same key from a blessed host. [ You can create a secure host, from where every-one login to company servers]. And you can protect the blessed hosts in the same way.

3. Question(s)?

How do I perform the automation:

You can do the following for that:

1. Log in to bless host [ with your personal keys ].

2. use "ssh-agent bash" [ I am using bash, you can use any of your shell ]

3. ssh-add [ at this time it will ask for the password, provide the password. ]

Later you can log-in to the qa server from the bless hosts without typing the password again and again.

You might also like to go through "screen" command.

4. Further reading:

 

Please go through further documentation on the following command for more details:

ssh-keygen

ssh-agent

ssh-add

screen

Mysql Notes1

1. mysql errcode:24

 mysqldump: Couldn't execute 'show fields from `tablename`': Out of resources when opening file './databasename/tablename#P#p125.MYD' (Errcode: 24) (23)

For above kind of problem, you might need to check the ulimit value of system and mysql user.

- login as mysql user: [ sudo -u mysql bash ]
- ulimit -a [ check for the ulimit values ]
- update the /etc/security/limits.conf

If you find the ulimit is less for the mysql user, you can update the same.

# sudo lsof -p <pid_of_mysql> | wc -l  
The above command will let you know how many file is getting used by the mysql process.

mysql> show variables like 'open%';


2. To delete unwanted mysql database partition:

for i in {n1..n2}; do mysql -uroot --pPassword -e "use database_name; alter table tablename_x drop partition p$i;" ;done

In the above base script, n1 and n2 are starting of unwanted mysql database partition number and end of unwanted mysql databse partition number.


To recheck there should not have unwanted mysql table partition:
show create table table_name_x \G

web_accelerator

I am trying understand some open web accelerator, and as of now going through the data from http://en.wikipedia.org/wiki/Web_accelerator  and found following is the order: if I have to chose a open web accelerator:

1. Varnish: http://en.wikipedia.org/wiki/Varnish_(software)

2. Nginx: http://en.wikipedia.org/wiki/Nginx

3. Squid: http://en.wikipedia.org/wiki/Squid_(software)
4. trafficServer: http://trafficserver.apache.org/  [ ATS: Apache Traffic Server ]

Technologies

NOTE:


www.apache.org have so many new things to learn. Follow the new stuffs over there and you can learn a lots, that is new to the IT.


Few more new technologies:
- Apache Mesos -> http://mesos.apache.org/
  Making it easy to build resource-efficient distributed systems

- Spark -> http://spark-project.org/ :

Spark is an open source cluster computing system that aims to make data analytics fast — both fast to run and fast to write.Spark is also the engine behind Shark, a fully Apache Hive-compatible data warehousing system that can run 100x faster than Hive.To run programs faster, Spark provides primitives for in-memory cluster computing

http://spark-project.org/examples/

Few top Sites:
https://amplab.cs.berkeley.edu/projects/
http://www.apache.org

Spark, 
Shark, 
GraphX, 
ZooKeeper
https://github.com/amplab/shark/wiki
http://spark-project.org/docs/latest/
https://github.com/amplab/shark/wiki
https://amplab.cs.berkeley.edu/projects/
http://www.scala-lang.org/







•High quality technical documentation, runbooks, diagrams.
•PCI-DSS compliance and implementation.
•Platforms - CentOS/RHEL, Debian, Solaris and FreeBSD.
•Shell scripting, Automation, UNIX/GNU tools.
•Deep understanding of critical networking fundamentals, tools, protocols.
•Virtualization - VMWare Vsphere, ESX/ESXi, Xen.
•Security - Bluecoat, Checkpoint, ASA/PIX, SonicWall.
•Hardware - Dell, HP, Sun, Cisco, IBM, EMC, Netapp, Hitachi
•MTA/SMTP - Postfix/Sendmail, Exchange, Zimbra
•Enterprise Monitoring - Nagios, SolarWinds, Cacti, BMC.
•ITIL - problem/incident management, change management, continual service improvement.

Specialties: Enterprise Systems Administration, Infrastructure Design/Architecture, Automation, HA/DR, Linux, UNIX, Scripting, Bash, Perl, Python, Apache, MySQL, SAN/NAS, Virtualization, LDAP, Virtualization, SMTP, Postfix, VMWare, Xen, Enterprise Server Hardware (HP, Dell, Sun, Cisco, IBM), Enterprise Monitoring (Nagios, BMC Patrol, SolarWinds Orion, Zenoss, Cacti), PCI-DSS, Bluecoat, Netscaler/F5, Active Directory, Exchange, DLP, iptables, MSSQL



*) Technology:

Zabbix
glusterfs
monit
bacula : -> open source network backup.
powerdns

nagios
puppet
Cacti
OpenTSDB
ganglia
Flume
Hadoop
logstash
graphite

Technology I need to learn:
GFS, BigTable, MapReduce, Chubby and large-scale 'cloud computing' clusters.
Languages: Python, Ruby, PHP, Perl, Javascript, Shell
SQL/Database : MySQL, SQLite, Cassandra
Distributed Caches: Redis, Memcache
Distributed Processing: Hadoop + Pig + ZooKeeper + Mahout
Cloud Platforms: Amazon Web Services, Google App Engine, Microsoft Azure
Protocols: XMPP, Jingle, ICE, RTSP, SMTP, POP, IMAP
Installers: NSIS
Code Repository Tools: Git, SVN, CVS
Collaboration: JIRA, Confluence
Build Management: Ant, Maven, MsBuild, Nant, Glu, Hudsan
OS: Linux (Redhat, CentOS), Ubuntu, freebsd
Monitoring: Nagios, Cactii, Ganglia, nagiosgraph, RRDTools, Ntop
CI: Teamcity, Clover, Hudson
Config Management: Puppet
Infrastructure: ServerIron Load Balancers,
File Systems: Ext3, NTFS, XFS, GFS
Mail Servers: Qmail, Postfix, Sendmail
App/Web Servers: Tomcat, Resin, IIS, PHP Accelerator, Jetty, apache http
Mailing List: Exmlm, Mailman, Sympa
Jabber Servers: eJabberd, Tigase, Openfire
VOIP Servers: Asterisk
DNS Servers: BIND, Power DNS,
Proxy servers: Squid, ISA, Perdition, NGinx, Varnish, Perlbal
Few more: tcp/ip, http, load balancers, web servers, memcache,
DB Replication: Slony, MSSQL Replication
FTP Servers: Proftpd, VSftpd
Virtualization: Xen, VmWare
Patch Management: WSUS, Yum, up2date
Apache, memcached, Squid, MySQL, NFS, DHCP, NTP, SSH, DNS, and SNMP
Advanced knowledge of Linux, TCP/IP and web services
A strong background in internet service deployment, provisioning, IP networking, service infrastructure, or software deployments.
Bootstrap, other, awk, sed, tc, cfengine, openNMS, MRTC, OpenVPN, HAProxy.
Security and venerability tools, CISSP, CEH tools.
Storage. 
Dbugging tools, gd, firefox web tools: greasemonkey and other imp web imp tools/plugins.

Queue system: [ Message Broker ]
. Gearman
. RabbitMQ
. ZeroMQ
. Amazon SQS
. WebSphere  MQ [ MQSeries ]
. Advanced Message Queuing Protocol [ AMQP ]
. http://en.wikipedia.org/wiki/Gearman
. Redis
- web analytics softwares:  [ Analog, AWStats,CrawlTrack, Open Web Analytics, Piwik, W3Perl, Webalizer, syslog-ng ]
- awstat [ sudo apt-get install awstat ] at browser: file:///usr/share/doc/awstats/html/index.html
- webalizer [ web log analysis software ]
- nodejs
- mongodb

Development
Languages: Scala, Python, Ruby, Java, C#, VB.net, PHP, VC++, C++, Perl, XUL, Javascript, C, Shell
Web Technologies: HTML 5, CSS, Dojo, jQuery, YUI, Flash, Silverlight
Frameworks & Libraries: Hibernate, Hibernate Shards, Spring, Apache MINA, Project Grizzly, log4j, XAPool, Poolman
RDBMS: Postgres, MySQL, Microsft SQL Server, Firebird, SQLite
NoSql Stores: Redis, Cassandra, Voldemort, Berkeley DB
Distributed Caches: Redis, Memcache
Distributed Queues: Kestrel, RabbitMQ
Distributed Processing: Hadoop + Pig + ZooKeeper + Mahout
Cloud Platforms: Amazon Web Services, Google App Engine, Microsoft Azure
Protocols: XMPP, Jingle, ICE, RTSP, SMTP, POP, IMAP
Scripting: Perl, Python, Ruby
Unit Testing: JUnit, NUnit, MbUnit
Stress Testing: Jmeter, Tsung, Iozone, Iometer, Bonnie, Bonnie++
Functional Testing: Watir, Selenium
Installers: NSIS
Code Repository Tools: Git, SVN, CVS
Collaboration: JIRA, Confluence
Build Management: Ant, Maven, MsBuild, Nant
CI: Teamcity, Clover, Hudson
IDEs: Aptana, Komodo, Eclipse, IntelliJ, Visual Studio, EMacs!
System Administration
OS: Linux (Redhat, CentOS), Windows
Monitoring: Nagios, Cactii, Ganglia
Config Management: Puppet
Infrastructure: ServerIron Load Balancers, Cisco ASA Firewall, FC/iSCSI SANs (Comet, Dell)
Scripting: Bash, Perl, Expect, Python, PHP, VBS, Powershell
File Systems: Ext3, NTFS, XFS, GFS
Other: DRBD, Heartbeat, ldirectord, RIS, LVS
Servers
App/Web Servers: Tomcat, Resin, IIS, PHP Accelerator, Jetty
Mail Servers: Qmail, Postfix, Sendmail
Mailing List: Exmlm, Mailman, Sympa
Antivirus / Antispam: clamd, Razor, Kaspersky server, Pyzor, Policyd, RBL/DNSBL
Jabber Servers: eJabberd, Tigase, Openfire
VOIP Servers: Asterisk
DNS Servers: BIND, Power DNS, DLZ, Microsoft DNS
Proxy servers: Squid, ISA, Perdition, NGinx, Varnish, Perlbal
DB Replication: Slony, MSSQL Replication
FTP Servers: Proftpd, VSftpd
Virtualization: Xen, VmWare
Patch Management: WSUS, Yum, up2date
UI
UI Prototyping: Balsamiq, Axure
Design: Photoshop, Flash, Coreldraw
Web: ECMAscript (actionscript/javascript), RSS, XML, HTML (4.01), XHTML, CSS1.0 & CSS2.1


Few companies are looking for the following skills for there "Site Reliability Engineer" (SRE) :

real-time metrics
deployment
Apache Http
Nginx
mysql
node.js
solr
Ruby
perl
python
java
php
Lucene
Scala
system level thinking ability
SOLR or Lucene
Scala
Performance Tuning
chef
puppet
unix
Scrum
FreeBSD
Ubuntu
Redhat
Hadoop
Riak
ZooKeeper
RabbitMQ
Haproxy
AWS
OpenStack
Cloud Foundry
Heroku
Linux Kernel Internals
Filesystems
High availability
High performance
High security
Salt
SVN, GIT
PostgreSQL
Cassandra
Mysql
tcpdump
device drivers
FreeBSD
dtrace
ktrace
load balancing
LAMP stack
column stores
erlang
haskell
scala
scheme
tcp/ip
http
security
storage
memcache
code-review
Sharing and Guding others
capistrano
mesos
scribe
capacity planing
CDN
memcached
squid
nfs
dhcp
ntp
ssh
dns
snmp
Varnish
Redis
Nagios/Icinga
OpenTSDB

Artifactory Repository