MySQL Basic Commands for DBA

MySQL Basic Commands for DBA

Linux command

      Check Disk Space usage:-

Command: - df –h

      Check memory usage:-

Command: - free –m

      Check OS Version:-

Command: - Cat /etc/redhat-release

Command: - uname –a                  ------------------ (For architectural info .i.e. 32 or 64 bit)

      Check MySQL Version:-

Command: - mysql –V

      MySQL Configuration file:-

/etc/my.cnf

      MySQL error Log file:-

/var/log/mysql.log

      MySQL data directory

/var/lib/mysql

MySQL Basic Understanding & Command

MySQL, the most popular Open Source SQL database management system, is developed, distributed, and supported by Oracle Corporation.  The MySQL Web site (http://www.mysql.com/) provides the latest information about MySQL software.

Refer below official link for more detail on below topics.
http://dev.mysql.com/doc/refman/5.5/en/what-is-mysql.html

      MySQL databases are relational

      MySQL software is Open Source

      The MySQL Database Server is very fast, reliable, scalable, and easy to use

      MySQL Server works in client/server or embedded systems

      A large amount of contributed MySQL software is available

Basic MySQL Commands:-

      To check the databases:-

        mysql>show databases;

        +--------------------+

        | Database           |

        +--------------------+

        | information_schema |

        | mysql              |

        | performance_schema |------- (From 5.5)

        | test               |

        +--------------------+

      To Use a particular database:-

        mysql> use xyz;(“xyz” database name)

      Create a database:-

        mysql> create database abcd; (“abcdb” database name)

      To check the available tables inside the database:-

  mysql> show tables;---

it will show you all the tables of the particular database.

      Create MySQL User:- By giving all privileges by allowing from localhost

mysql> grant all on *.* to 'username'@'localhost' identified 
by ‘pass’;

mysql> Flush privileges;

MySQL Master-Slave Replication Setup

MySQL Master-Slave Replication

Replication enables data from one MySQL database server (the master) to be replicated to one or more MySQL database servers (the slaves). Replication is asynchronous - slaves need not be connected permanently to receive updates from the master. This means that updates can occur over long-distance connections and even over temporary or intermittent connections such as a dial-up service. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Benefits:-

1. Robustness is increased with a master/slave setup. In the event of problems with the master, you can switch to the slave as a backup.
    
2. Better response time for clients can be achieved by splitting the load for processing client queries between the master and slave servers. SELECT queries may be sent to the slave to reduce the query processing load of the master. Statements that modify data should still be sent to the master so that the master and slave do not get out of synchrony. This load-balancing strategy is effective if no updating queries dominate, but that is the normal case.
   
3.  Another benefit of using replication is that you can perform database backups using a slave server without disturbing the master. The master continues to process updates while the backup is being made.

Pre-Requisite:-

• In Master conf file:

         [mysqld]
         log-bin=mysql-bin
         server-id=1

• In Slave conf file:

         [mysqld]
         log-bin=mysql-bin
         server-id=2

         Post above changes need to restart the MySQL service on both server.

• Grant access rights for replication on Master:

         Grant replication slave on *.* to 'repliation_user'@'slave_ip' identified by ‘password’;
         Flush Privileges;

Steps to Perform:-

• Take a Full backup of master as follows:

       

          shell   > mysqldump –root –p –A --events --routines --triggers --single-transaction --master-data > All_DB_Backup_date.sql

• Transfer the file from Master to Slave

• On Slave: Restore the above full taken Backup.

          Shell > mysql –uroot –p < All_DB_Backup_date.sql (backup file name)

• Post completion of restoration Initiate Sync of Replication:

          Mysql > stop slave;

          Mysql >  

CHANGE MASTER TO MASTER_HOST='x.x.x.x’,  MASTER_USER='repliation_user', MASTER_PASSWORD='repli_password',  MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxxxxxxxx;

Note: for above master_log_file and master_log_pos, check mysqldump file with less command.

          Mysql > start slave;

          Mysql > show slave status \G;

• Check below 3 parameters of the above command out put:-

          Slave_IO_Running: Yes                  ------------ > (Should be Yes)

          Slave_SQL_Running: Yes              ------------- > (Should be Yes)

          Seconds_Behind_Master: 0            ---------- > (ideally it should be 0, however this is  subjective.  This parameter indicates the lag in seconds behind master)