MySql Replication Configuration Tutorial

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.

There are two core types of replication format, Statement Based Replication (SBR), which replicates entire SQL statements, and Row Based Replication (RBR), which replicates only the changed rows. You may also use a third variety, Mixed Based Replication (MBR).

Replication Configuration
Replication between servers in MySQL is based on the binary logging mechanism. The MySQL instance operating as the master (the source of the database changes) writes updates and changes as “events” to the binary log.

The information in the binary log is stored in different logging formats according to the database changes being recorded. Slaves are configured to read the binary log from the master and to execute the events in the binary log on the slave's local database.

Both the master and each slave must be configured with a unique ID (using the server-id option). In addition, each slave must be configured with information about the master host name, log file name, and position within that file. These details can be controlled from within a MySQL session
using the CHANGE MASTER TO statement on the slave.

Steps to Set Up Replication:


Mysql Master Slave Replication Configuration


1) On the master, you must enable binary logging and configure a unique server ID. This might require a server restart.
[mysqld]
port=3306
server_id=1//default must be 1 fro master
log-bin=mysql-bin //bin log file
bind-address = 127.0.0.1 //local ip address
binlog-do-db='sample' //database to be replicated 
binlog_format='mixed'

we can also give the expiry days for binary log for more details visit the given link:-
Mysql Binary Logs

2) On each slave that you want to connect to the master, you must configure a unique server ID. This might require a server restart.
[mysqld]
server_id=2 //it must be unique and different than master's server id
port=3307 // port no must be different than master
bind-address = 127.0.0.1 //local ip address

3) You want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication.

mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';


4) Before creating a data snapshot or starting the replication process, you should record the position of the binary log on the master. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events.

mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
+------------------+----------+--------------+------------------+

5) If you already have data on your master and you want to use it to synchronize your slave, you will need to create a data snapshot. You can create a snapshot using mysqldump.

shell> mysqldump --all-databases --lock-all-tables >dbdump.db


After the dump is over unlock the tables it is very much important otherwise the data would not be entered into databases.

mysql> UNLOCK TABLES;

6) When are creating the replication between new master and new slave,You will need to configure the slave with settings for connecting to the master, such as the host name, login credentials, and binary log file name and position.

There are two methods for this,

1) by using CHANGE MASTER TO command.
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name',
-> MASTER_PORT = port_num,
-> MASTER_USER='replication_user_name',
-> MASTER_PASSWORD='replication_password',
-> MASTER_LOG_FILE='recorded_log_file_name',
-> MASTER_LOG_POS=recorded_log_position;
Before using change to master stop the slave using STOP SLAVE

2)by setting the variables in my.ini file of slave by adding host_name, port, user etc.
also replicate-do-db=exampledb// database to be replicated must be set

7) When you are setting up replication with existing data, you need to import the existing data in following ways:-

1)If you used mysqldump:
a. Start the slave, using the --skip-slave-start option so that replication
does not start.

b. Import the dump file:
shell> mysql < fulldb.dump

on windows use following:-
c:\mysql\bin\mysql -u root dbname < dumpfilename

2) If you created a snapshot using the raw data files:
a. Extract the data files into your slave data directory. For example:
shell> tar xvf dbdump.tar


The logging format also can be switched at runtime. To specify the format globally for all clients, set the global value of the binlog_format system variable:

mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';

Mysql  Master Slave Replication Configuration:

MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync.


1. Configure The Master with following Steps:-


1) Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows resp).

2) We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf or my.ini under [mysqld] section:

[mysqld]
port=3306 
server-id=1 //default is 1 for master
log-bin = /var/log/mysql/mysql-bin.log //Binary log file name and path to 
store 
binlog-do-db=exampledb // database name to replicate
expire_logs_days = 7 // bin_log must expire after 7 days
binlog_format=row // it may be row or statement or mixed .

3) Then we restart MySQL.

4) Then we log into the MySQL database as root and create a user with
replication privileges:

mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

5) To get the Binary logging Co-ordinates use following:-
mysql> FLUSH TABLES WITH READ LOCK;
mysql > SHOW MASTER STATUS;
+------------------+----------+--------------+---------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+---------------------------+
| mysql-bin.006 | 183 |exampledb | |
+------------------+----------+--------------+---------------------------+

6) To use the existing data, Dump the data using following command:-
mysqldump -u root -p<password> --opt exampledb > exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)

7) After the dump is over unlock the tables it is very much important
otherwise the data would not be entered into databases.

mysql> UNLOCK TABLES;


2. Configure The Slave with following Steps:-

1) On the slave we first have to create the database exampledb:

CREATE DATABASE exampledb;
use following:-
default-collation=latin1_general_ci 
default-character-set=latin1

2) Import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace
<password> with the real password for the MySQL user root! Important: There
is no space between -p and <password>!)

3) we add the following lines to /etc/mysql/my.cnf of Slave under [mysqld]
section:

[mysqld]
server-id=2
master-host= '192.168.0.100'
master-user= 'slave_user'
master-password= 'secret'
master-connect-retry= 60
replicate-do-db= 'exampledb'


4) Then we restart MySQL.

5) We change the master to bin_log position which is noted during show
master status.
SLAVE STOP;
CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', 
MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', 
MASTER_LOG_POS=183;

6) START SLAVE;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.

Mysql Binary Logs Tutorial | Mysql Binary Log Backups:

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows).The binary log has two important purposes:

1) For replication, the binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

2) Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.The binary log is not used for statements such as SELECT or SHOW that do not modify data.The format of the events recorded in the binary log is dependent on the binary logging format. Three format types are supported, row-based logging, statement-based logging and mixed-base logging.

To enable the binary log, start the server with the --log-bin[=base_name] option. If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.we have to uncommnet the following filed from my.ini or my.cnf file to enable the binary log-

# binary logging is required for replication
log-bin=mysql-bin // base name is mysql-bin

The server creates a new file in the series each time it starts or flushes the logs. The server also creates a new binary log file automatically after the current log's size reaches max_binlog_size(which is 1gb).Binary log size can be seen using following statement:
SHOW VARIABLES LIKE 'max_binlog_size'

A replication slave server by default does not write to its own binary log any data modifications that are received from the replication master. To log these modifications, start the slave with the --log-slave-updates option in addition to the --log-bin option.

You can delete all binary log files with the RESET MASTER statement, or a subset of them with PURGE BINARY LOGS. You can also set the expire_logs_days system variable to expire binary log files automatically after a given number of days.

RESET Syntax
RESET reset_option;
reset_option can be any of the following:
1)MASTER .
2)SLAVE.

PURGE BINARY LOGS Syntax
PURGE { BINARY | MASTER } LOGS { TO 'log_name' | BEFORE datetime_expr }


Examples:
PURGE BINARY LOGS TO 'mysql-bin.010';
PURGE BINARY LOGS BEFORE '2012-03-22 22:46:26';


Expire_logs_days Syntax


We can set the variable expire_logs_days in following way:-
1) Mysql> SET GLOBAL expire_logs_days = 6;
Query OK, 0 rows affected (0.00 sec)

Mysql> show variables like 'expire_%';
+------------------+--------+
| Variable_name| Value |
+------------------+--------+
| expire_logs_days | 7 |
+------------------+--------+
1 row in set (0.00 sec)

2) Also we can cghange in Masters my.cnf or my.ini file like this:-

#expire bin logs
expire_logs_days = 7


You can display the contents of binary log files with the mysqlbinlog utility. So to read and display the contents of the binary log file named binlog.000001, use this command:

mysqlbinlog binlog.000001

The binary log files and its data are likely to be very huge, thus making it almost impossible to read anything on screen. However, you can pipe the output of mysqlbinlog into a file which can be open up for later browsing in text editor, by using the following command:


mysqlbinlog binlog.000001 > filename.txt

References:-
1)http://dev.mysql.com/doc/refman/5.0/en/binary-log.html


2)http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

Extract zip, tar.gz, tar.bz2

In this tutorial, I wll explain how to extract zip or tar.gz or tar.bz2 using command line.


How to Extract zip file:
unzip file_name.zip

How to Extract tar.gz file:
tar -xzf file_name.tar.gz
or
tar -xvzf file_name.tar.gz

How to Extract tar.bz2 file:
tar -xjf file_name.tar.bz2
or
tar -xvjf file_name.tar.bz2

Backup Outlook 2007 account settings

How to export my account setting in Outlook 2007?

1. Open Run Application

2. Type `regedit`

3. Locate HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles\Outlook

4.Right click on the key `outlook` and select `Export` Option.

5.Save the .reg file

6. You can install the file to restore your outlook  account settings .You will need to enter your password as Password is not stored in the .reg file.

7.Done.

php array length for loop

In this Article, I will explain How to get the size of array and then use the for loop to display all the data into the array.

sizeof :Get the actual length of array in PHP.

Syntax
sizeof(array)
count(array)

Example: Find array length for loop

<?php
$arr1 = array('A','B','C','D','E');
$size= sizeof($arr1); //Get php size of an array


echo "size of an array is:$size<br>";


for($i=0;$i<$size;$i++){
echo $arr1[$i]."<br>";
}
?>


Output:
size of an array is:5
A
B
C
D
E

Array size in PHP

In this Article, I will explain How to get the size of array in php using `sizeof` php function.In PHP sizeof() is just a alias of count() function.
sizeof :Get the actual length of array in PHP.

Syntax
sizeof(array)

Example:
<?php
$arr1 = array('A=>0','B=>1');
echo sizeof($arr1); 
?>


Output:
2

PHP array length size count

In this Article, I will explain How to get the length of array in php using `count` php function.In PHP sizeof() is just a alias of count() function.
count :Get the actual length of array in PHP.

Syntax
count(array)

Example:array size in php

<?php
$arr1 = array('A=>0','B=>1');
echo count($arr1); 
?>

Output:
2

PHP key array | Fetch a key from an array in PHP

The key() function is used to fetch a key from an array.

Syntax
key(array)

Example:


<?php
$arr1 = array('A=>0','B=>1');
echo key($arr1); 
?>


Output:
A,B

Count php array element

count : Count all elements in an array.

Syntax
count(array)

Example:
<?php
$arr1 = array('A','B');
echo count($arr1); 
?>


Output:
2

explode php-string to array in php

explode: converts a string into an array.

Syntax
explode(separator,string)

Example:string to array in php

<?php
$str = "A,B,C,D";
print_r (explode(",",$str));
?>


OUTPUT:
Array
(
[0] => A
[1] => B
[2] => C
[3] => D
)

PHP array Functions

array()
array_change_key_case()
array_chunk()
array_combine()
array_count_values()
array_diff()
array_diff_assoc()
array_diff_key()
array_diff_uassoc()
array_diff_ukey()
array_fill()
array_filter()
array_flip()
array_intersect()
array_intersect_assoc()
array_intersect_key()
array_intersect_uassoc()
array_intersect_ukey()
array_key_exists()
array_keys()
array_map()
array_merge()
array_merge_recursive()
array_multisort()
array_pad()
array_pop()
array_product()
array_push()
array_rand()
array_reduce()
array_reverse()
array_search()
array_shift()
array_slice()
array_splice()
array_sum()
array_udiff()
array_udiff_assoc()
array_udiff_uassoc()
array_uintersect()
array_uintersect_assoc()
array_uintersect_uassoc()
array_unique()
array_unshift()
array_values()
array_walk()
array_walk_recursive()
arsort()
asort()
compact()
count()
current()
each()
end()
extract()
in_array()
key()
krsort()
ksort()
list()
natcasesort()
natsort()
next()
pos()
prev()
range()
reset()
rsort()
shuffle()
sizeof()
sort()
uasort()
uksort()
usort()

in_array :php find in array

PHP in_array is used to check if a value exists in an array


Syntax:
bool in_array($var,$arr)

Returns TRUE if $var exists in an array, FALSE otherwise.

Example:php find in array
<?php
$arr1 = array('A','B');
if(in_array('A',$arr1))
echo '`A` exists in an array' 
else 
echo 'Not Found';
?>


Output :
Array.

is_array : PHP array function

PHP is_array is used to find whether a variable is an array or not.

Syntax:
bool is_array($arr)

Returns TRUE if $arr is an array, FALSE otherwise.

Example: php is array


<?php
$arr1 = array('A','B');


if(is_array($arr1))
echo 'Array' 
else 
echo 'Not an Array';
?>

Output :
Array.

SVN obstructed


How to repair obstructed directories.


Solution:
1.Remove all obstructed files from your System.copy them to a Backup Folder.
2.Remove all .svn directories.
3.SVN cleanup on the parent directory.
4 Then replace it with the backed up files.
5.Now do the svn update
6.Done

Apache Server conflict with IIS

If Apache Server conflict with IIS then just make the access ports different

Example:
Apache port No:80
IIS Port:81

Change the port of your Apache:

1.Open httpd.conf file.
2.Change Port number(By default it's 80)
3.Save file
4.Restart Apache Server
5.Done

Change the port of your IIS:

1. Go to Control Panel -> Administrative Tools ->Internet Information
Services

2. Expand Local Computer
    + Web Sites
    + Default SMTP Virtual Server

3. Expand Web Sites
    + Default Web Site

4. Right-click Default Web Site =>choose Properties=>choose the Web Site
tab.

5. Change TCP Port number.

6. Apply

7.Done

.PHPS file extension

The PHPS file type is primarily associated with 'PHP Source' by The PHP Group. Generally, PHP files will get interpreted by the Web server and PHP executable, and you will never see the code behind the PHP file.

If you make the file extension .PHPS, a properly-configured server will output a color-formated version of the source instead of the HTML that would normally be generated. Not all servers are so configured.

Detailed information for file extension PHPS:
Primary association: PHP Source
Company: The PHP Group
File classification: Source Code
Mime type: text/html, application/x-httpd-php-source,
application/x-httpd-php3-source

How to Copy paste in Turbo C

Shortcut Keys for Turbo C:


1.For Cut: "shift + delete"
2.For Copy: "ctr + insert"
3.For Paste: "shift + insert"

CTRL + KB - point to the start of a line to copy.
CTRL + KK - point to the end of the line to copy.
CTRL + KC - whole line of text to copy (CTRL + KB+CTRL + KK)
CTRL + KV - Paste

Does JQuery work with JavaScript turned OFF / disabled

Since Jquery is a Javascript library , disabling Javascript automatically disables Jquery..

<script type="text/javascript">
function alertBox() {
    alert("Simple Javascript");
}


$(document).ready(function(){
    $("#div").click(function(){
    alert("Jquery Javascript");
    });
});
</script>

After disabling JavaScript, Jquery script will still work unless the page is reloaded.

System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use.

Error:
Restore Failed For Server 'MyServer' (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: Exclusive access could not be obtained 
because the database is in use. (Microsoft.SqlServer.Smo)

Solution:


1.execute SP_WHO

EXEC sp_who

2.Execute KILL 53 /Kill 116

3.Restore from another database.

Right click on database which you want to restore

4.Done.