Mysql

How to setup MySql master slave replication servers.

Ananda Raj PandeyLeave a comment

MySql server Master Slave  replication:

Setting  master MySql server for replication:

1) Open config file:

vi /etc/my.cnf

Add the server id. This is a number and must be unique among servers.

server-id = 1

Add the binary log file location, if location is not given, default will be used by MySql.

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

Save the file and restart MySql.

service mysqld restart

2) Create user for MySql replication:

On master, login into MySql and create user for replication server shell by :

mysql -u root -p
password: <password>
mysql > GRANT REPLICATION SALVE ON *.* TO  'username'@'%'  IDENTIFIED BY 'password';

Then apply changes by

FlUSH PRIVILEGES;

3) Create the backup of  current databases to replicate,

FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Copy file name and pid.

Do not close this window, if the window is closed or move away then MySql tables will be unlocked.

This is place from where slave will start replicating, so remmeber the filename and position.

4) Create the database backup:

cd /var/lib/mysql
tar -cvf /tmp/mysql-snapshot.tar *

Once the process is completed ,  go back to your original window and unlock the database by

UNLOCK TABLES;

Setting MySql slave server:

1) Copy backup database to slave

cd /var/lib/mysql
tar -xvf /tmp/mysql-snapshot.tar

2) Open config file and add following lines by:

vi /etc/my.cnf
server-id = 2
relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index
 relay-log-info-file = /var/lib/mysql/relay-bin.info

Restart the slave server by

$ service mysqld restart

3) Go to slave MySql shell and type:

mysql > CHANGE MASTER TO 
-> MASTER_HOST='IP_OF_MASTER_MYSQL',
-> MASTER_USER='username',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=  106;

Start slave MySql Server

We can check the status of replication by going to MySql shell in slave server and typing:

mysql > SHOW SLAVE STATUS\G;

*************************** 1. row ***************************
 Slave_IO_State: Waiting for master to send event
 Master_Host: 
 Master_User: replicate
 Master_Port: 3306
 Connect_Retry: 60
 Master_Log_File: mysqld-bin.000004
 Read_Master_Log_Pos: 144806
 Relay_Log_File: slave-relay.000004
 Relay_Log_Pos: 3430
 Relay_Master_Log_File: mysqld-bin.000004
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
 Skip_Counter: 0
 Exec_Master_Log_Pos: 144806
 Relay_Log_Space: 145249
 Until_Condition: None
 Until_Log_File:
 Until_Log_Pos: 0
 Master_SSL_Allowed: No
 Master_SSL_CA_File:
 Master_SSL_CA_Path:
 Master_SSL_Cert:
 Master_SSL_Cipher:
 Master_SSL_Key:
 Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 0
 Last_IO_Error:
 Last_SQL_Errno: 0
 Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified

Is MySQL query Caching really good for performance optimization ?

Ananda Raj Pandey1 Comment

MySQL query cache is one of the option on MySQL server optimization.  But question here is, is it really good to do MySQL query Caching?

To understand this we have to know what really happens during MySQL query caching.

Example:

Select * from table ;

Untitled-1

Above is the result of running a simple query on 2M rows, with query cache enabled. Here we see each time query is executed, Query cache is checked for existing result. If result not found, new cache is written to the query cache.  Which seems good but imaging the different scenario.

1. Each time there is modification in the table like update, edit on column e.t.c  query that is cached is invalid, so whole process of reading the query and writing to query has to perform again.

2. When query cache is written, its locks the cache of that query, so if one query is changing anything to the cache, no other query can not access it unless the lock is released, which results more response time. If there is higher frequency of updates on the query there will be more response time.

3. If you have millions of combination of query that can be generated in your database, caching query is not an option, as caching is only for few sec and holding millions of cached query to the memory, is not a possible options.

So, this summarized that query cache is not always good so when we use it ?

1. if we have same query is written in same piece of code very frequently,  then there is a less chance table is updated with in that milli- second, in that case we can use query caching.  Many framework related development of highly oops based development we are using same query many time in the code for example:

$result = $dataModel->find->id(‘5’);

// php code

if($dataModel->RelationalModel->status == 2)  {

// php code

}

In above example  dataModel is called twice, so same query will be executed in same code twice ( if object is not cached) , in that case query caching can be effective. The code is just a basic idea, its not a real code or working code of any kind.

So what we do?

1. If we have more read and less writes to the tables then query cache  can be efficient. Sites like forums, blogs, directory, news sites e.t.c has very less update frequency so query cache will be effective.

2. Limiting query cache size can be good option like few mega byte or few hundred mega byte relative to huge data size will limit the frequency of query caching.

3. if possible, disable query cache and test over all effect on performance can be good idea.

MySQL optimization depends on the database usage frequency and database data size, so testing different scenario with benchmark will be the best option. There is no hard written rule on optimization. But every time we optimize  MySQL server just enabling the query cache is not always the best option.