Types of buffers in mysql with examples

Table cache :

When users request row from tables, the rows to be given to client are stored in table cache.

Grant table buffer :

it stores the privileges of user created tables and user privileges.

Key buffer :

it contains index information of tables.

Query Cache :

it contains the execution plans of the previously executed statements.

buffer is divided into pages that can potentially hold multiple rows.

When we kill a connection thread then it will automatically create a new connection with a new thread id to the server with same user credentials.

How Many types of logs are there in mysql

Error log :

it records the problems encountered in starting ,running and stopping of MySQL

log-errors = /var/log/mysqld.log 

General query log :

Records established connections and statements received from clients.

log = /var/mysql.general.log

Binary logs :

records statements that change data

log-bin = /var/log/mysql-log 

To see the contents of binary log files use mysqlbinlog utility.

mysqlbinlog -p mysql-log.00012 >file.txt

Slow query logs :

records queries that take more than a minute to execute.

 log-slow-queries = /var/log/mysqld-slow.log 

Data Types in mysql with examples

MySQL Data Types

Data Types:
is a restriction where we can place it on top of column

Integer(int):
it will accept only numerics

Char(n):
it will accept alphanumaric and special characters

Varchar(n):
it will accept alphanumaric and special characters

Difference between char and varchar?
Char: 

1.Fixed length memory storage

2.CHAR takes up 1 byte per character

3.Use Char when the data entries in a column are expected to be the same size

5.Ex:

Declare test Char(100);

test="Test" -

Then "test" occupies 100 bytes first four bytes with values and rest with blank data.

VarChar: 

1.Variable length memory storage(Changeable)

2.VARCHAR takes up 1 byte per character, + 2 bytes to hold length information

3.varchar when the data entries in a column are expected to vary considerably in size.

4.Ex:

Declare test VarChar(100);

test="Test" -

Then "test" occupies only 4+2=6 bytes. first four bytes for value and other two bytes for variable length information.

Date:
it will accept only dates

Datetime:
it will accept dates along with time

Money:
it will accepts only money values

Boolean:
it will accepts only boolean values(i.e true/false)

Image:
it will accepts images(photos)

Float:
it will accept decimal values

Text:
will accepts ‘n’ number of characters

MySQL Architecture and Concepts

MySQL Architecture and Concepts:-

MySQL Architecture

Connection Manager :
The Connection Manager listens for incoming connection from clients and dispatches the requests to the Thread Manager

Connection Thread :
The Connection Thread is the heart of the client processing which establishes connection.

Thread Manager :
The Thread Manager is responsible for keeping track of threads and for making sure a thread is allocated to handle the connection from a client.

User Authentication Module :
The user Authentication module authenticates the connection user and initializes the structures and variables containing the information on his level of privileges.

Command Dispatcher :
The Command Dispatcher is the responsible for directing requests to the lower-level modules that will know how to reslove them.

Parser :
The Parser is responsible for parsing queries and generating a parse tree.

Query Cache Module :
The Query Cache Module caches query results and tries to short-circuit the execution of queries by delivering the cached result whenever possible.

Optimizer :
The Optimizer is responsible for creating the best strategy to answer the query and executing it to deliver the result to the client.

Table Modification Modules :
This collection of modules is responsible for operations such as creating ,deleting ,renaming,dropping,updating or inserting into a table.

Table Maintenance Module :
the Table Maintenance Module is responsible for table maintenance operations such as check,repair,back up,restore,optimize and analyze. This module comes into picture when a table is corrupted.

Status Reporting Module :
The Status Reporting module is responsible for answering queries about server configuration settings,performance tracking varibales,table structure information,replication progress,condition of the tables cache and other things.

Replication Master Module :
The Replication Master Module is responsible for the replication functionality on the master. then most common operation for this module is to deliver continuous feed of the replication log events to the slave upon request.

Replication Slave Module:
The Replication Slave Module is responsible for the replication functionality of the slave. the role of the slave is to retrieve updates from the master and apply them on the slave.

Table Manager :
The Table Manager is responsible for creating ,reading and modifying the table definition files(.frm extension),maintaining a cache of the table descriptor called table cache and managing table-level locks.

How To Set Up MySQL Master-Master Replication

MySQL Master-Master Replication configuration setup

=> Setup requirement :-

We will have two mysql Servers, named db1 and db2 to setup the master to master Replication.Both servers have two IP addresses (one public, one private).We will configure the replication to be done over the private IP interface so that we don’t incur any bandwidth charges.

Note: Commands listed below are to be run as a privileged (root, sudo group) user.

Step 1: Installing Mysql

First we need to install MySQL on both the DB Servers. As always, prior to installing any packages, we need to make sure that our package list is up to date and our locale/language settings are configured properly.

Update the package Database          

# yum update

Now, you can run the following commands to install MySQL

# yum install mysql-server mysql-client libmysqlclient15-dev

=> Configuring replication:-
Once the mysql-server package has been installed successfully, we can start configuring each of the MySQL nodes in order to enable replication between them.
We need to create the database that will be replicated as well as the replication username and password to be used with it. You can use the commands outlined below to set them up.

First on DB1, login to the mysql console (using mysql root password setup during MySQL installation).

# mysql -u root -p

       Enter password:

       mysql>

Now let’s create the replication user, which will be used to synchronize the changes.
mysql> grant replication slave on *.* to slaveuser@'[private IP of DB2]' identified by '[password]';
          mysql> flush privileges;
          mysql> exit

Do the same for DB2
mysql> grant replication slave on *.* to slaveuser@'[private IP of DB1]’ identified by ‘[password]’;
mysql> flush privileges;
mysql> exit

Now in DB1, edit /etc/mysql/my.cnf and insert/update or uncomment following entries

bind-address = [Ip address of DB1]

server-id = 1

log-bin = /var/log/mysql/var/bin.log

log-slave-updates

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

auto_increment_increment = 10

auto_increment_offset = 1

master-host = [private IP address of DB2]

master-user = [replication username]

master-password = [replication password]

replicate-do-db = <database name to be replicated>




=> Repeat the steps on the DB2 server:

bind-address = [Ip address of DB2]

server-id = 2

log-bin = /var/log/mysql/bin.log

log-slave-updates

log-bin-index = /var/log/mysql/log-bin.index

log-error = /var/log/mysql/error.log

relay-log = /var/log/mysql/relay.log

relay-log-info-file = /var/log/mysql/relay-log.info

relay-log-index = /var/log/mysql/relay-log.index

auto_increment_increment = 10

auto_increment_offset = 2

master-host = [private IP address of DB1]

master-user = [replication username]

master-password = [replication user password]

replicate-do-db = [database name to be replicated]



Now, restart both databases. If the service restart on either server fails, then please check the /var/log/mysql/error.log file for any errors. Update the configuration and check for any typos, etc.



=> Testing the scenarios:-

To test the Replication setup,we can create the database specified in the configuration file.



mysql> create database [your-db-name];

             mysql> use [your-db-name]

             mysql> create table foo (id int not null, username varchar(30) not null);

             mysql> insert into foo values (1, 'bar');

An additional test is to stop the MySQL service on DB2, making database changes on the DB1 server and then restarting the MySQL service on DB2. The DB2 MySQL service should sync up all the new changes automatically.

You should also consider changing the default binary log rotation values (expire_logs_days and max_binlog_size ) in the /etc/mysql/my.cnf file, as by default all the binary logs will be kept for 10 days. If you have high transaction count on your database application then it can cause significant hard disk space usage in logs. So, we recommend changing those values to match your server backup policies. For example, if you have daily backups setup of your MySQL node then it makes no sense to keep 10 days worth of binary logs.

Data Manipulation Language (DML) Statements - MySQL Data Manipulation and Query Statements

DML COMMANDS:  (Data Manipulation Language) statements are statements to change data values in database tables.  

There are 3 primary DML statements:
INSERT –  Inserting new rows into database tables.
UPDATE – Updating existing rows in database tables.
DELETE – Deleting existing rows from database tables.

INSERT: If a column is defined with a default value in a table, you can use the key word DEFAULT in the INSERT statement to take the default value for that column.

To insert data into MySQL table, you would need to use SQL INSERT INTO command.

To insert string data types, it is required to keep all the values into double or single quote.
For example:- “value

UPDATE: If you want to update some values in one row or multiple rows in a table, you can use the UPDATE statement. The tutorial script below shows a good example:

Where existing data in a MySQL table needs to be modified. You can do so by using SQL UPDATE This will modify any field value of any MySQL table.

DELETE: If you want to delete an existing row from a table, you can use the DELETE statement with a WHERE clause to identify that row. Here is good sample of DELETE statements:

• If WHERE clause is not specified, then all the records will be deleted from the given MySQL table.
• You can specify any condition using WHERE clause.
• You can delete records in a single table at a time.
• The WHERE clause is very useful when you want to delete selected rows in a table.

If you want to delete a record from any MySQL table, then you can use SQL command DELETE FROM

How to Replace MySQL with MariaDB

Simple Steps Migration From MySQL To MariaDB On Linux

Step #1 : Add the MariaDB Repository

#yum -y update
# vim /etc/yum.repos.d/MariaDB55.repo

Insert below code in that file:
# MariaDB 5.5 CentOS repository list – created 2014-10-03 15:57 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://server1.kproxy.com/servlet/redirect.srv/slxv/sknlueob/sdmr/p1/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Then exit and save the file with the command :wq .

Note: Take the Backup all the Databases for Feature Reference

Step #2: Remove the Existing MySQL Installation

# service mysqld stop
# yum -y remove mysql-server mysql


Step #3: Install MariaDB

# yum -y install mysql-server mysql
# service mysql start
# mysql_upgrade
# mysql

You will see below message:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.39-MariaDB MariaDB Server

Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

MariaDB [(none)]>