MySQL command line Shortcuts

\? = help 
\c = clear command
\C = switch to another charset (might be needed for processing multi-byte charsets)
\d = set delimiter (use something else after a statement instead of ;)
\g = send command to server
\G = send command to server
With \g and \G you can generate "vertical" output with each column value on a separate line. This makes large outputs far more readable !
\h = help
\p = print current command
\q = quit MySQL
\r = reconnect (or see which database you're in)
\R = change your mysql prompt (personalize your prompt, which can be funny)
\s = show status (which can be very informative)
\t = don't write to into outfile
\T = write into outfile
\u = use database
\w = don't show warnings after every statement
\W = show warnings after every statement
\# = rebuild completion hash
\. = execute an SQL scriptfile

MYSQL BINARY INSTALLATION IN LINUX

1.Check your operating system.Whether is it 32 or 64bit?
2.Go to root user.
3.Check the disk space.
4.Create useradd mysql and groupadd mysql.
5.If want to download from the website: go to http://dev.mysql.com/downloads/  > Click MySQL         community server >Select the platform >  Select the package you need as RPM or Binary
(http://dev.mysql.com/downloads/mysql/#downloads)  > Select download >  It goes to next page in that,right click on No,Thanks option > copy download link.
6.Now go to terminal and type,
   
        wget  --paste the copied link--

7.Now the MySQL download starts.
8.After (Binary)download.It will be in tar file.
9.Extract the tar file using :
         
        tar -xvzf mysql.tar.gz

10.If already you have MySQL in your system no need to download from net,check the MySQL path.
11.Then copy the extract file or already have MySQL in system to user directory.

        cp -r mysql    /usr/local/

12.After copy, go to /usr/local/mysql to change the owner and group name of mysql.

      chown -R mysql.mysql  /usr/local/mysql/

13.Now create a data directory.

      mkdir  /data/mysqldata/
      cd   /data/mysqldata/

14.Change the owner and group name of mysqldata.

     chown -R mysql.mysql   /data/mysqldata/

15. Now go to /usr/local/mysql/ directory to copy the supported files to my.cnf

     cd  /usr/local/mysql/
     cp support-files/my-small.cnf   /etc/my.cnf

16.Now install mysql using,

     ./scripts/mysql_install_db  --/defaults-file=/etc/my.cnf  --basedir=/usr/local/mysql/   --datadir=/datamysqldata/  -user=mysql

17.Installtion finished succesfully.Now give a command to start the mysql in safe way use.

     ./bin/mysqld_safe  --/defaults-file=/etc/my.cnf  --basedir=/usr/local/mysql/  --datadir=/data/mysqldata/   -user=mysql &

18.Then copy the mysql server support files to /etc/init.d/mysql.

   cp support-files/mysql.server   /etc/init.d/mysql

19. Edit the  /etc/init.d/mysql file to give the base and data directory file location.

     vi /etc/init.d/mysql

     basedir= /usr/local/mysql/
     datadir=/data/mysqldata/

20.Check the mysql status whether its running or not using and check the log error.

    /etc/init.d/mysql  status


To start and stop MySQL:
   
   /etc/init.d/mysql   start
   /etc/init.d/mysql   stop

LINUX COMMANDS FOR DBA's

1) Less :

Helps in viewing the content of files page by page.

ex)

# less country12_100812.sql

2) More :

It is also equivalent to less. It shows the percent of the file viewed so far.

ex)

# more country12_100812.sql

Linux commands

3) cat :

It shows the contents of the file as a whole. But not page by page. Useful for small files.

ex)

# cat anaconda-ks.cfg

4) ls :

List the files present in the current directory or the path.

ex)

# ls
alldatabase_030812.sql alldb100812.sql alldb.sql Country090812.sql
country12_100812.sql lost+found mysql_030812  world.sql

Linux commands

5) pwd :

It shows the path of your working directory .

ex) # pwd
/home/linux/Desktop

6) df

Displays the partitions present and the server . It also provide its disk usage and free
space available.

# df -h
Filesystem
/dev/sda7
tmpfs
/dev/sda2
/dev/sda6
/dev/sda9
/dev/sda12
/dev/sda8
/dev/sda10

Size Used Avail Use% Mounted on
3.9G 2.0G 1.9G 52% /
495M 420K 494M 1% /dev/shm
194M 55M 130M 30% /boot
6.2G 1.5G 4.5G 25% /data
2.0G 202M 1.7G 11% /logs
485M 11M 449M 3% /tmp
3.9G 3.3G 411M 90% /usr
2.0G 976M 893M 53% /var

7) du

Retrieve the disk usage of a folder or a file.

ex)
# du -sh
963M

8) ifconfig :

Displays the IP address assigned to the server and also its other network requistics.

ex) # ifconfig
eth1
Link encap:Ethernet HWaddr 00:1D:7D:80:C1:37
inet addr:10.10.10.10 Bcast:10.10.22.209 Mask:255.255.255.0
inet6 addr: fe80::21f:7dff:fe50:c137/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:0 errors:0 dropped:0 overruns:0 frame:0
TX packets:906 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:0 (0.0 b) TX bytes:47394 (46.2 KiB)
Interrupt:21 Base address:0xa000

9) Traceroute & tracepath :

They help in finding the network delay in reaching a server from remote host.

ex)
#tracepath google.com
#tracepath 10.10.10.10

10) Ping :

Examines whether the server is reachable or not . Helps in trouble shooting the packet
losses.

ex) ping 127.0.0.1

11) netstat :

Displays the active internet connections of the servers and the port opened for the
process.

12) crontab :

Scheduler for scheduling the automated jobs like backup and other maintenance scripts.

13) Free :

Displays the total ram present in the system and their utility.

# free -m
total
used
free shared buffers
Mem:
988
758
229
0
38
-/+ buffers/cache:
287
701
Swap:
1023
0
1023

14) Top :

It is similar to the Task manager in Windows . It displays all the process running
and their resource usage.

15) uptime :

Displays the total time after the server is booted, It also shows the load average.

ex)
# uptime
00:56:09 up 57 min, 2 users, load average: 0.00, 0.05, 0.03

16) ps :

It makes list of the current processes running on the system.

17) chmod :

Changes the permission of the files . Read , write and execute.

18) chown :

Changes the ownership of the file to some other user. We must have root permission to
change the ownership of files.

19) mkdir and rmdir:

Mkdir : Creates a directory (folder )

Rmdir : removes the directory (folder )

20) cd :

Change the active directory to required directory.

21) head :

Displays only the first 10 lines of the file as a default.

22) tail :

Displays the last 10 lines of the files as a default.

23) mv :

Move the contents from one directory to other. It is also helpful in renaming the files.

24) wc :

Displays the number of lines, word and character present in a file.

25)grep :

Search a word or a phrase in a file

ex)

#grep -i “mysql” grep_test.txt

26) find :

Search for a file with a pattern . It returns the file name.

27) locate:

Search for a file with a pattern. It returns file with a full path.

28) echo :

Prints the content given inside the quotes.

ex)

# echo "mysql is a database"
mysql is a database

29) gzip :

Compress the files with .gz as extension . Can be extracted using gunzip command.

Ex )
compress
# gzip alldb100812.sql
extract
# gunzip alldb100812.sql.gz

30 ) tar

Tape Archive file. It is a compression format used mostly in binary installation files.

It has extension of .tar

31) kill :

To terminate a running process by the pid (process id)

Ex) Kill 528412

32) who :

List the user logged into the server and their duration

33) whoami :

Displays the user name of current session.


34) sort :

Arranges the content of the files in the ascending or descending order.

35) Vi :

The editor command used to make changes in existing file . It is used widely in
scripting too as “Vim”

36) man :

Shows the manual page of a command . It gives the complete info about the
command.

37) useradd :

To add a linux user for any new process like mysql. Requires root access

38) groupadd:

To add a group in the linux. Requires root access.

39) passwd :

To change or assign password foe a user.

40 ) uname ;

It displays the system information like the OS ,kernel and processor.

# uname -a
Linux remotemysqldba.com 2.6.32-279.2.1.el6.x86_64 #1 SMP Fri Jul 20 01:55:29 UTC
2012 x86_64 x86_64 x86_64 GNU/Linux

41) scp :

Copy the files to a remote server.

ex)

scp mysql.sql root@10.101.150.21:

42) ssh:

It is to connect the remote shell .

ex)

ssh root@10.101.150.21

43 ) telnet :

It is also used to connect the remote host. But it used for checking port opening.

ex)

telnet 10.101.150.21 22

My SQL DBA SYLLABUS

1) Basic Linux commands.

2) Installing MySQL.

3) Starting and stopping mysql.

4) Various logs in mysql and uses.

5) MySQL client Programs.

6) Upgrading MySQL.

7) Mysqladmin commands.

8) Locking in MySQL


9) Storage engines in MySQL.

10) Innodb Configuration

11) MySQL Table maintenance.

12) Information Schema and Performance Schema.

13) MySQL backup and Recovery.

14) MySQL Replication and different replication Architecture.

15) MySQL Routines and Triggers

16) MySQL Resource allocation.


17) MySQL Architecture.

18) Query Cache Tuning.

19) Securing MySQL.

20) Optimizing queries.

21) Optimizing MySQL Variables effectively.

22) Hardware optimizaion.

23) LVM Snapshot.

24) Tools for high performance ( Percona toolkit, Mytop, Innotop).

25) Shell scripting for automation.

What is MySQL?

                      MySQL,   pronounced either "My S-Q-L" or "My Sequel," is an open source relational database management system. It is based on the structure query language (SQL), which is used for adding, removing, and modifying information in the database. Standard SQL commands, such as ADD, DROP, INSERT, and UPDATE can be used with MySQL.

                     MySQL can be used for a variety of applications, but is most commonly found on Web servers. A website that uses MySQL may include Web pages that access information from a database. These pages are often referred to as "dynamic," meaning the content of each page is generated from a database as the page loads. Websites that use dynamic Web pages are often referred to as database-driven websites.Many database-driven websites that use MySQL also use a Web scripting language like PHP to access information from the database. MySQL commands can be incorporated into the PHP code, allowing part or all of a Web page to be generated from database information. Because both MySQL and PHP are both open source (meaning they are free to download and use), the PHP/MySQL combination has become a popular choice for database-driven websites.