Day 24 Replication Setup

Today we will discuss how setup a replication:

  1. Master – Slave Replication:


  • 2 (Linux) System (MasterServer and SlaveServer)
  • MySQL Installed
  • Connection to both servers are accessible.

On Master Server:

  • Edit my.cnf with following information:


Log-Bin =MySQL_Binlog


  • Create a replication login which has access to REPLICATION SLAVE

mysql> CREATE USER ‘repl’@’%.%’ IDENTIFIED BY ‘mypass’;

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%.%’;


  • Make sure before backup gather data for replication. Like bin-log file name and position.



| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |


| mysql_Binlog.006 | 100       | mysql       |                  |



This information is important from this information only we can setup the replication correctly(making both master and slave in sync).


  • Copy the data from master to slave and restore it to slave

mysqldump –all-databases –master-data > Master_Dump.bkp

It is important to include –master-data option which automatically appends the CHANGE MASTER TO statement required on the slave to start the replication.

We have to restart the mysql to effect my.cnf (bin-log enable) and other settings.

sudo /etc/init.d/mysql restart

On Slave Server:

  • Make system aware that this is slave, by making server-id =2 in my.cnf.




Restore the database

mysql –uroot –p < Master_Dump.bkp

Now we are ready to setup the replication configuration and provide the relation between master an stave. Here we have to provide information which we captured from show master status


   ->     MASTER_HOST=’ MasterServer,

   ->     MASTER_USER=’repl’,

   ->     MASTER_PASSWORD=’mypass’,

   ->     MASTER_LOG_FILE= ‘mysql_Binlog.006’,

   ->     MASTER_LOG_POS=100;


Once we run change master to command replication is setup between master and slave.

Now to start the replication we have to start slave



That it we have replication running.

To see the status of replication

mysql> SHOW SLAVE Status\G;


It will show all the information, some of the important information would be:

Slave_IO_State: Waiting for master to send event

Means replication is running good and no lag

Slave_IO_Running: Yes   Slave_SQL_Running: Yes

Means slave MySQL is running and connections is also good – replication is active.

Replicate_Do_DB: DBReplicate_Ignore_DB:Replicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:

This Variables/options will filter or provide information about database/table specific replication.


Shows error occurred lastly.

Seconds_Behind_Master: 0

Another very important variable to validate replication is up to date

Read_Master_Log_Pos: xxx  Relay_Log_File: xxx.xx  Relay_Log_Pos: xx  Relay_Master_Log_File: xxx.xx


Provide information about Slave logging, how slave is working on the logs received from Master.


For setup master – master replication:

You have to enable bin-log at slave as well (second master).

And server_id=1 for that server.

Follow the same show master status parameters create repl user

And change master to configuration with master status parameters.



Posted in Disaster Recovery, High Avaliability, Internal, MySQL, Others, Replication, What I learned today | Tagged , , | Leave a comment

Day 23 Replication

Replication means making copy of the objects and moving transaction from primary server to secondary/standby/Slave server. There are two type of Replication

Master-Master Replication

Master-Slave Replication

Like other RDBMS MySQL works replication on Transactions. The pre requisite of replication is to enable the log-bin (bin log) transactions on the server. In MySQL when you enable the Bin log it will logged all the transactions from all the schemas/Databases, so we can setup a replication on all the databases. To enable replication for a specific database we have to make filter/specify the database or exclude the database for replication (we can even exclude/include the table/objects for replication).

Master-Master replication: in this type of replication both the server are accepting connections and changes can be performed on either of the server will replicate/effect to other node. Means on both the nodes you can make the changes.

For this Replication it could be possible that the conflict on the transactions/object may slow down or sometime it could be possible the replication may failed due to not managing the transactions (simultaneously)

Master-Slave Replication: In this type of replication master will be read-write and slave will be read only. So transactions will be moved from master to slave but slave will be worked as a reporting or taking a backup purpose to make master more efficient OLAP operations. This is most useful and commonly used replication.

Master should user with REPLICATION SLAVE permission on master and that user slave can use to connect to Master.

mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘password’;

mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;


Finally once we backup the master and restore on to slave we need to configured the replication with log-bin position from where replication will start and Slave will start reading transactions from master.



Using this command we can get the Log-bin file name and the position of the log-bin to retrieve the transaction.

To enable or setup the replication


->     MASTER_HOST=’master_host_name‘,

->     MASTER_USER=’replication_user_name‘,

->     MASTER_PASSWORD=’replication_password‘,

->     MASTER_LOG_FILE=’recorded_log_file_name‘,

->     MASTER_LOG_POS=recorded_log_position;


*Replication cannot use UNIX socket files. You must be able to connect to the master MySQL server Host using TCP/IP

Start and stop slave/Replication can be done using following command:


mysql> START Stop;

There is other way of configuration replication using GUID


Posted in MySQL, Others, Replication, What I learned today | Tagged , | Leave a comment

Day 22 Other Storage Engines

So far we have discussed InnoDB, MyIASM and NDB Storage engine which are most standard/common and important storage engine MySQL has, there are some other Storage engine which are specific to the business requirement and used only for their special purpose. These Storage engine may require specific configuration as well and may not be for general purpose but we can integrate these storage engine tables with standard/common storage engine to make efficient use of our system, that is the specialty of MySQL Storage engines…

  • Memory: As the name implies, this storage engine stores data all in memory, it is also know Heap Engine. This provides great performance on data but less durable.


  • CSV: This storage engine stores the text data (Comma separated Data), it is used mostly to import and export the data from CSV format. In this format tables are not indexed.


  • Archive: This storage engine is used for large data to get archival for better performance. Data will be un-indexed and used to store the historical data generally not being used but we have to keep it for reference.


  • Blackhole: It does not store data, similar to the Unix /dev/null device. This is used for replication configurations where DML statements are sent to slave servers, but the master server does not keep its own copy of the data.


  • Merge: It is logically group of identical MyISAM tables and reference them as one object. recommended for VLDB like OLAP


  • Federated: Offers the ability to link separate MySQL servers to create one logical database from many physical servers. Good for distributed or data mart environments.


  • Example: This engine serves as an example in the MySQL source code that illustrates how to begin writing new storage engines.



Posted in MySQL, Others, Performance Tuning | Tagged , | Leave a comment

Day 21 NDB Storage Engine (Cluster)

MySQL also support High Availability, NDB Storage engine provides high availability- shared-nothing system, NDB Cluster integrates the standard MySQL server with an in-memory clustered storage engine called NDB (which stands for “Network DataBase”

NDB Cluster required 3 Nodes required complete the setup:

  • Management node(mgmd):

This node is used to manage the cluster, using this node we can configured, start and stop the cluster, using this node only we can run the backup- ndb_mgmd.

As the name suggest, this node contains the actual data. There should be more than 1 data node required for data redundancy (replica). Default set to 2- these replca’s will contain the same information and if any one node goes down data will be available on other node.

Eg. If we have 4 data node with replica of 2, then their will be 2 set of 2 replica’s and each contains part of data(set). ndbd (data node daemon), ndbmtd (multi-threaded)

NDB Cluster tables are normally stored completely in memory rather than on disk (this is why we refer to NDB Cluster as an in-memory database). The data will be flushed from memory to data nodes periodically using LCP and GCP.

Local Checkpoint (LCP):

This is checkpoint to data node, it save the data from memory to disk occurs every few minutes. It depends upon the amount of data stored by the node, the level of cluster activity, and other factors.

Global Checkpoint (GCP):

GCP occurs every few seconds, when transactions for all nodes are synchronized and the redo-log is flushed to disk.

This node contains MySQLD mysql services, using this node Cluster data will be accessed. This is also called as API node.

Each data node or SQL node requires a my.cnf file as follows:







The management node needs a config.ini file, this is important and contains all the information about NDB Cluster (SQL Node, Data Node and management node) For our representative setup, the config.ini file should read as follows:

[ndbd default]

NoOfReplicas=2   # replicas

DataMemory=80M   # memory allocate – data storage

IndexMemory=18M   # memory allocate – index storage



HostName=HostNm # MGM node

DataDir=/var/lib/mysql-cluster                               #MGM node log files location















The management node should be started first, followed by the data nodes, and then finally by any SQL nodes:

It support sonly READ COMMITTED transaction isolation level



Posted in Disaster Recovery, High Avaliability, Isolation Level, MySQL, Others | Tagged , , , | Leave a comment

Day 20 Innodb Storage engine

InnoDB is the major Storage engine and is default Storage engine after MySQL 5.5 version. As standard RDBMS requires ACID properties and to make the Enterprise level of the system requires Transaction management. Before this MySQL was known to be part of LAMP and mostly used by Web Developers and small systems. During this period (2010) MySQL was part of SUN and they were progressing on Enterprise support quite well. (This could be one of the reason might Oracle acquired Sun Microsystem – my guessJ.

InnoDB is a complete RDBMS and support all the feature which all the other major RDBMS system supports. When discuss MySQL it will be mostly about InnoDB. So in general MySQL is InnoDB.

When you create the table with InnoDB storage engine there will be 4 files created, 1 .FRM file at database directory, 2 Log files .ibd Files and 1 Data file ibdData. We can change the setting innodb_file_per_table=1 to create 1 data file per table (similar to myIASM storage engine).

As stated, it fulfilled all the properties as other RDBMS includes:

  1. Support ACID Transaction property
  2. Support Recovery and point in time Recovery.
  3. High standard Troubleshooting utilities (information_Schema, Performance_Schema, Sys Databases).
  4. Stable Consistency system with enterprise support and most Bug-free.
  5. Highly Query optimizers/tuning mechanism.
  6. Highly used Storage Engine



Posted in MySQL, Others | Tagged , , | Leave a comment

Day 19 MyIASM Storage engine

MyIASM storage engine is available from the early stage of MySQL this engine is developed from IASM language and it is used for Read intensive operations. As described in yesterday this Engine was default till MySQL 5.5 and is widely used, and can able to store huge amount of data as there will be no transactions only heavy reading the data.

When we use MyIASM engine for table it creates 3 physical files for that table:

.FRM: Format File – stores Metadata information about the table/object

.MYD:Data File – Stores actual data from the objects

.MYI: Index File – stores index data /information.

MyIASM has following utilities:

mysqlcheck/myisamchk – for checking consistency,  it can recover the corruptions

compressMyISAM / myisampack for compression for faster data retrieval and optimum space utilization.

  • MySQL Table can have max rows =(232)2
  • Max Indexes per table = 64
  • Max columns per index =16
  • Index can be on BLOB and TEXT columns.
  • Indexed columns can contains NULL.

Storage formats:

Fixed /Static – having fixed row length or column has fixed size.

Dynamic – Variable row length, variable types column (dynamics)

Compressed – compressed by myisampack utility, data will be read-only (only DDL allowed)

When you use CREATE TABLE or ALTER TABLE for a table that has

Using ROW_FORMAT option while creating a table we can specify type of storage format.

To un-compressed the data- myisamchk –unpack.

Consider following points before choosing MyIASM:

  1. Locking is table level.
  2. For Big extensive read Database
  3. Good for TEXT and BLOB data type.
  4. Perform better on Fixed format for faster reads(could take more disk space)
  5. Performance is great on index as the statistics are accurate.
  6. Corruption handles well
  7. No Referential integrity (foreign key)


Posted in General, History, Internal, MySQL, Others | Tagged , , , , | Leave a comment

Day 18 Storage engine

MySQL is special compare to all the other RDBMs System because of its special architecture, the STORAGE Engine. MySQL has many storage engine as per the developer/application requirements. With this good part is that MySQL can work multiple storage engine in one instance/databases without any issues (btw it would be little complicated to mix many Storage engine in one DB).

As stated in earlier blogs MySQL was designed for developers so it used to maintain the storage part as per developer’s requirement. MyISAM is the default storage engine before MySQL 5.5.5 from there it will be INNODB.

Following are some of the MySQL Storage engines:

  • MyISAM
  • InnoDB
  • NDB
  • CSV
  • Memory
  • Archive
  • BlackHole
  • Merge
  • Federated
  • Example

To see what all storage engine is configured and can be used in the system.


Some of the comparison on Storage engine is as follows:

Feature MyISAM Memory InnoDB Archive NDB
Storage limits 256TB RAM 64TB None 384EB
Transactions No No Yes No Yes
Locking granularity Table Table Row Row Row
B-tree indexes Yes Yes Yes No No
T-tree indexes No No No No Yes
Hash indexes No Yes No[b] No Yes
Full-text search indexes Yes No Yes[c] No No
Clustered indexes No No Yes No No
Data caches No N/A Yes No Yes
Index caches Yes N/A Yes No Yes
Compressed data Yes[d] No Yes[e] Yes No
Encrypted data[f] Yes Yes Yes Yes Yes
Replication support[g] Yes Yes Yes Yes Yes
Foreign key support No No Yes No Yes[h]


You could observed that INNODB and NDB are mostly identical on functionality. MyIASM and Memory are having common features.

Generally we can call InnoDB and NDB are RDBMS for OLTP(On Line Transactional Processing) system Transactions and MyIASM is for OLAP (On Line Analytical Processing) used for BI(Business Intelligence) –Analysis.

MySQL Has three Major Storage engines (MyIASM(oldest), INNODB and NDB(Cluster).

Depending upon the storage engine the data will be stored and database physical system will vary So it is very important to have information about which Storage Engine we are using and we should use and the limitation of these storage engine to understand the system explore and use MySQL more efficiently.



Posted in Internal, MySQL, Others, What I learned today | Tagged , , , , | Leave a comment

Day 17 Backup and Recovery

There are there types of Backup:

  • Full Backup
  • Incremental Backup
  • Bin-Log Backup

Full Backup: is a complete backup of the database

You can use following some of the options in the MySQLDump command

–all-databases             Dump all tables in all databases

–databases                  Interpret all name arguments as database names

–flush-logs                  Flush MySQL server log files before starting dump

–no-create-info           Do not write CREATE TABLE statements that re-create each dumped table

–no-data                      Do not dump table contents

–single-transaction      Issue a BEGIN SQL statement before dumping data from server

Database Copy

shell> mysqldump Test_DB > mybkp.sql

shell> mysqladmin create Test_DB_copy

shell> mysql Test_DB_copy < mybkp.sql

Do not use –databases on the mysqldump command that causes USE db1 to be included in the script, which overrides the effect of naming Test_DB on the mysql command line.

shell> mysql < mybkp.sql

  • or use a source command from MySQL :

mysql> source mybkp.sql

Database Copy to different Server:

On Source server

shell> mysqldump –databases Test_DB > Test_DB_bkp.sql

Copy the .sql file from source server to destination server.

On Destination Server:

shell> mysql < Test_DB_bkp.sql

Backup only metadata

Include –no-data in mysqldump will copy only metadata – database structure.

shell> mysqldump –no-data Test_DB > Test_DB_metadata_Bkp.sql

shell> mysqldump –no-create-info Test_DB > Test_DB_Data_bkp.sql



Posted in Backup and Restore, Disaster Recovery, MySQL, Others | Tagged , , , | Leave a comment

Day 16 Security

MySQL user format it as ‘User_name’@’hostname’

To create the user account use following syntax:

  • CREATE USER ‘User_name’@’Host_name’ IDENTIFIED BY <Pwd>;


We can specify same name user for different hosts. Hostname can be specify either of this:

‘local_host’ –> for the mysql server local user

‘ip address’ /’host_name -> for specific host or ip.

‘’ -> specific to network

‘’ – > specific fqdn network

Password can be saved at my.cnf on [client] group



Restrict the my.cnf file to be access by others using chmod 400 or 600

chmod 600 .my.cnf

Using Environmental variable – Not Recommanded

Environment variable: MYSQL_PWD


This will create a user but that user is not having any access to mysql system, to grant an access to system we have to GRANT access to that USER as

  • GRANT <Privilege> ON <db.object> TO ‘User_name’@’Host_name’ WITH GRANT OPTION;



Here DB is the db name of the instance and object is tables we can specify * for all objects.

Following are list of privileges available to grant an access:

Server administration:
































Routine and File    





Multiple Privilege can be granted with common separated.

Grant SELECT, UPDATE, INSERT, DELETE  ON DB.*  TO ‘User_name’@’Host_name’;

To see current users granted permission:



To Show information about what all access user has granted.

  • SHOW GRANTS FOR ‘User_name’@’Host_name’;

To show information about user an account, use SHOW CREATE USER:

  • SHOW CREATE USER ‘User_name’@’Host_name’;



To revoke the granted access from user:


  • REVOKE priv_type ON [object_type] priv_level FROM user


To revoke all privileges




To Drop the User

  • DROP USER ‘User_name’@’Host_name’;



To Change the password for the user before 5.7.6:

  • SET PASSWORD FOR ‘User_name’@’Host_name’ = <Pwd>;



  • GRANT  USAGE ON *.* TO ‘User_name’@’Host_name’ IDENTIFIED BY <pwd>;


For locking and unlocking user accounts using the ACCOUNT LOCK and ACCOUNT UNLOCK clauses for the CREATE USER and ALTER USER statements:

Alter user were used only for Password Expire options. But from 5.7.6 on ALTER USER is more options included.

Mysql version 5.7.6 on this has changed with ALTER USER:



auth_option: < Identified by “XXX”>

tls_option: <SSL/xx>




tls_option : are the encryption options we can use for user password

resource_option: we can restrict user to use the limited resources from the system.

Password options: for password expire policy.

Lock option: account can be locked or unlocked.




  • ALTER USER ‘user_name’@’localhost’ ACCOUNT LOCK/UNLOCK;



As you know we can perform the task with other ways as well like to change the password expire using my.cnf.



Using global variable

SET GLOBAL default_password_lifetime = 180;


The default password_expired value is ‘N’ -0

We can provide resource options and other options at the time of creation of the user as well.

  • CREATE USER username IDENTIFIED BY ‘xxx’ 


  • WITH {resource_option:}

Some important points to be noted:

MySQL internally stores user information at mysql.user table(MySQL System Database). So this database should be highly protected.

Tables: userdbtables_privcolumns_priv, and procs_priv contains all user related information. DBA/Developer can directly work on this tables to perform the user access related activity. Beware this may have major impact if you unware of table structures.

Privilege Effects:

When Mysql starts, it retrieves information from system tables into memory. The in-memory tables become effective for access control at that point.

On changes of GRANTREVOKESET PASSWORD, or RENAME USER, the server notices these changes and loads the grant tables into memory again immediately.

For other DML access level information DBA has to explicitly reload the memory with flush-privileges operations by either of this command:


mysqladmin flush-privileges

mysqladmin reload command




Posted in MySQL, Others, Security, What I learned today | Tagged , | Leave a comment

Day 15 Server Logs

There are 6 types of logs available for MySQL environment, Logs are important information for DBA to troubleshoot or understand the system. MySQL track the information in those logs.

Following are the list of logs:

Error Log (– start/stop/errors)

Query Log (trace)

Bin Log ( Transaction/Replication)

Slow Log (Slow queries)

Relay log (Replication)

DDL log (metadata log)


By default when MySQL Starts except Error Log all other logs are disabled, we have to enable those.

Error Log:

Error log are Basic log which provides information about Mysql Activity during start and during stop of MySQL Processes. It also capture errors while MySQL is running.


Query Log:

Query log provides detail statement level information about all the SQL Queries runs on the MySQL server, it will be as is information about queries which is executed on Mysql. As it tracks all the activity and captured the size of the file grows heavily and if this log enabled for long and heavily active system, it will occupy all your system space quickly. So be aware before enabling this log.

This log can be enabled in my.cnf file on [mysqld] group




Bin Log:

This log is very important and captures transaction activity on the system. Any DML operations performed on this system is stored in this log in Binary format. Binary format is safe and consumes less space in compare to query log also as it tracks only transactions.

This log is also mandatory to enable replication on the system, Replication works on Bin log transactions, Binlog transferred the transactions from master to slave and those transactions is tracked in Relay log.

This log is also enabled in my.cnf file on [mysqld] group



We can also set the format we would like:

SET GLOBAL binlog_format = {Statement/Row/Mixed}


Prior to MySQL 5.7.7, statement-based logging format was the default. In MySQL 5.7.7 and later, row-based logging format is the default.


Relay log:

This log is only be used for replication and as stated on Bin log discussion, Relay log is keeping track of transactions applied at slaves.


Slow Query Log:

This is very important log for performance point of view, the events will be captured in this log on either one of the situations:

When Query duration is more than 10 seconds( long_query_time seconds)

When query is not having indexes or no indexes being used on the table (log_queries_not_using_indexes)

Other conditions where Query can be tuned


DDL Log:

This log maintains the DDL operations of the system. This DDL is useful for Recovery purpose, during system crash or server down without proper shutdown mysql at the middle of incomplete DDL Operations this logs captured the information and helps the system to recovery it at the stable state. This log stored the data in Binary format only. Generally this log file is empty or does not exist if system starts /shutdown normally. As stated this is used only for recovery time.

The location of this log would be at data directory and name would be ddl_log.log




Posted in Error Log, MySQL, Others, Performance Tuning, Transaction Log, Troubleshooting | Tagged , , | Leave a comment