rsync: Remote File Transfer

While we usually deal with huge data, we come across situations where we need to copy such data from one server/host to a remote host. 

One of the useful command is 'rsync', which is faster than the traditional scp command!

Below is the syntax with example :

[root@myhost file_path]# rsync -avrz /file_path/* user@12.12.12.12:/destination_path/ >> /log_path/rsync.log 2>> /log_path/rsync.err

where as, 

-a -> Its same as --archive, which means to say preserver everything as it is. No changes between source and destination

-v -> Its same as --verbose, which displays information about the current execution

-r -> Its same as --recursive, copy directory and sub-directories, recursively

-z -> Its same as --compress, which compress data while its being transferred

MySQL - Before Installation..

So, while you have a basic idea about MySQL, I would recommend the real-time trial and error than just reading out.

For that, download a latest stable community version of MySQL from the official website.

Though MySQL is platform independent, its best suited for Linux/Unix environment. 

So, better have Linux on VM and install MySQL.

(All my posts mostly will be based on the same combination.)

There are many ways to install MySQL on your Linux, based on its flavor. You may go for RPM installation or Binary installation  by downloading files, manually. 

Apart from that, you can get MySQL inbuilt in some of the Linux versions, if not, you can get it easily by updating the library(apt-get, yum installations)

Ubuntu:

sudo apt-get install mysql-server 

Non-Ubuntu: 

sudo yum install mysql-server

However, I prefer RPM way of installation. Just download MySQL stable Community edition RPM from official website. 

Make sure, you download both MySQL server and MySQL client RPMs.

MySQL Server - It is the actual MySQL software, which can be installed on any host.

MySQL Client - It is the client to connect to any MySQL server. Means, without this you can't access any MySQL server. While, this can be installed on any of the server through which you can access any MySQL server.

Hope that's not confusing..

MySQL - An Intro

I know, it might appear to you outdated if I start  telling about MySQL now, which is one of the world's best open source database. 

However, being a DBA, after making my hands dirty with it, I feel I must share my knowledge with the freshers who want to learn MySQL. Because, once I was struggling to learn it on my own.

So, here it is..

MySQL .. ?

MySQL is one of the widely used RDBMS, open source software. As you may aware, its now been owned and supported by Oracle. 

The latest and stable GA release is 5.6.15(as of 03 Dec 2013).


Why MySQL ..?

MySQL is basically very simple, lightweight DBMS. Having hands-on on MSSQL Server, I can assure you MySQL feels better. 

Physical structure, configurations and all other settings in MySQL is much simpler and pretty handy.
And mainly, its very 'flexy' with huge data.

If you ever want to develop an application which requires data to be stored in it, I would recommend MySQL, which also comes bundled version.

Apart from these, MySQL is being developed and supported by the most famous giant Oracle Corporation.

Hah, they must be understanding now, its not easy to sell which comes free ..!! :)


to be continued..

MySQL Replication: Exclude an SQL from replicating

If you're using MySQL Replication, in rare cases you may require to skip a particular SQL statement from replicating - which is to be executed on Master and should not be on the Slave. 

Though, its not a good practice!

However, here's how to do it. 

MySQL has a variable sql_log_bin, by default its value will be 'ON' or '1', when we enable replication. 

That means, it replicates all the SQL statements from Master to Slave.So, if we want to disable this for a particular SQL, do as below:

SET SESSION sql_log_bin=0; sql statement;SET SESSION sql_log_bin=1;Example:
SET SESSION sql_log_bin=0;

DELETE FROM tab1 WHERE col1='NULL';

SET SESSION sql_log_bin=1;

However, after MySQL 5.5.5 version, no need to use SESSION. By default its a SESSION variable and it won't affect other user's transactions. 

Note: Executing this on Master would cause data inconsistency. Don't try this unless you are sure about what you are doing!