Virtual Box Host-Guest Machine connectivity Error

Virtual Box networking issue scenario:

• Both Host and Guest can access Internet
• Host can ping Guest but Guest is not able to ping Host

The host is running Windows 10 64-bit and the guest is running CentOS 6.4 64bit.

Solution:

1. Setup the virtualbox to use 2 adapters:
• The first adapter is set to Bridged Adapter (that will give you the internet connection).

• The second adapter is set to host only.

1. Start the virtual machine and assign a static IP for the second adapter in CentOS instance.
2. Now, to make the connection available both ways (accessing the windows host from the CentOS guest) there's still one more step to be performed. Windows will automatically add the virtualbox host-only network to the list of public networks and that cannot be changed. This entails that the firewall will prevent proper access.
3. To overcome this and not make any security breaches in your setup:
   • go to the windows firewall section, in control panel,
   • click on advanced settings. In the page that pops up,
   • click on inbound rules (left column), then on new rule (right column). Chose custom rule, set the rule to allow all programs, and any protocol. Click next, select allow the connection, next, check all profiles, next, give it a name and save.

CentOS 6+  Text mode Installation Step By Step

1) Open Virtual Box and click on New to create new Virtual Machine.

2) Below window will open:

Provide Machine name, Type and Version that you want to install. I am using above details. Click Next.

3) Provide RAM as per your need. I am using 1024MB of RAM for this machine. Click Next. 

4) Check create a virtual hard disk now and click on create. 

5) Select VDI option and click Next.  

6) Select Dynamically allocated option and click Next. 

7) Provide file location and storage size for your machine. Click on create.

8) New machine created.

9) Provide the centos iso file location using below:

Click on Settings à Storage à Controller IDE .

I am using centos 6.4 iso file. Click on OK.

10) Click on Start to start the machine. Below Window will open.

11) Click anywhere on the screen , hit TAB and enter the command

Vmlinuz initrd=initrd.img text expert

Wait for the installation to complete

12) Click anywhere on the screen and hit Tab to SKIP then hit Enter

13) Click anywhere on the screen and hit Tab to OK then hit Enter

14) Choose language as English and click OK

 15) Choose US keyboard and click OK

16) Hit TAB and choose Re-initialize all

17) Choose asia/Kolkata as TimeZone then click OK

18) Choose simple password to remember

19) Choose USE ANYWAY

20)Choose Use Entire drive then Click OK

  

21) Choose Write changes to disk

22) Wait for installation gets completed. Click Reboot.

23) Login as root

 

Enjoy The working on Linux.

MySQL GTID Concepts

A global transaction identifier (GTID) is a unique identifier created and associated with each transaction committed on the server of origin (master). This identifier is unique not only to the server on which it originated, but is unique across all servers in a given replication setup. There is a 1-to-1 mapping between all transactions and all GTIDs.

A GTID is represented as a pair of coordinates, separated by a colon character (:), as shown here:

GTID = source_id:transaction_id

The source_id identifies the originating server. Normally, the server's server_uuid is used for this purpose. The transaction_id is a sequence number determined by the order in which the transaction was committed on this server; for example, the first transaction to be committed has 1 as itstransaction_id, and the tenth transaction to be committed on the same originating server is assigned a transaction_id of 10. It is not possible for a transaction to have 0 as a sequence number in a GTID. For example, the twenty-third transaction to be committed originally on the server with the UUID3E11FA47-71CA-11E1-9E33-C80AA9429562 has this GTID:

3E11FA47-71CA-11E1-9E33-C80AA9429562:23

This format is used to represent GTIDs in the output of statements such as SHOW SLAVE STATUS as well as in the binary log. They can also be seen when viewing the log file with mysqlbinlog --base64-output=DECODE-ROWS or in the output from SHOW BINLOG EVENTS.

As written in the output of statements such as SHOW MASTER STATUS or SHOW SLAVE STATUS, a sequence of GTIDs originating from the same server may be collapsed into a single expression, as shown here.

3E11FA47-71CA-11E1-9E33-C80AA9429562:1-5

The example just shown represents the first through fifth transactions originating on the MySQL Server whose server_uuid is 3E11FA47-71CA-11E1-9E33-C80AA9429562.

In MySQL 5.6.6 and later, this format is also used to supply the argument required by the START SLAVE options SQL_BEFORE_GTIDS and SQL_AFTER_GTIDS.

GTIDs are always preserved between master and slave. This means that you can always determine the source for any transaction applied on any slave by examining its binary log. In addition, once a transaction with a given GTID is committed on a given server, any subsequent transaction having the same GTID is ignored by that server. Thus, a transaction committed on the master can be applied no more than once on the slave, which helps to guarantee consistency.

When GTIDs are in use, the slave has no need for any nonlocal data, such as the name of a file on the master and a position within that file. All necessary information for synchronizing with the master is obtained directly from the replication data stream. From the perspective of the database administrator or developer, GTIDs entirely take the place of the file-offset pairs previously required to determine points for starting, stopping, or resuming the flow of data between master and slave. This means that, when you are using GTIDs for replication, you do not need (or want) to include MASTER_LOG_FILE or MASTER_LOG_POS options in the CHANGE MASTER TO statement used to direct a slave to replicate from a given master; in place of these options, it is necessary only to enable the MASTER_AUTO_POSITION option introduced in MySQL 5.6.5.

The generation and lifecycle of a GTID consists of the following steps:

1. A transaction is executed and committed on the master.
   This transaction is assigned a GTID using the master's UUID and the smallest nonzero transaction sequence number not yet used on this server; the GTID is written to the master's binary log (immediately preceding the transaction itself in the log).
2. After the binary log data is transmitted to the slave and stored in the slave's relay log, the slave reads the GTID and sets the value of its gtid_next system variable as this GTID. This tells the slave that the next transaction must be logged using this GTID.
   The slave sets gtid_next in a session context.
3. The slave checks to make sure that this GTID has not already been used to log a transaction in its own binary log. If and only if this GTID has not been used, the slave then writes the GTID and applies the transaction (and writes the transaction to its binary log). By reading and checking the transaction's GTID first, before processing the transaction itself, the slave guarantees not only that no previous transaction having this GTID has been applied on the slave, but also that no other session has already read this GTID but has not yet committed the associated transaction. In other words, multiple clients are not permitted to apply the same transaction concurrently.
4. Because gtid_next is not empty, the slave does not attempt to generate a GTID for this transaction but instead writes the GTID stored in this variable—that is, the GTID obtained from the master—immediately preceding the transaction in its binary log.

SUMMARY:

What is GTID ? 
GTID is a global transaction identifier which consists of two parts separated by a column: 
{source_id:transaction_id} 
                source_id: server's UUID. 
                transaction_id: sequence number. 
Ex: b9b4712a-df64-11e3-b391-60672090eb04:3

GTID Benefits 

● Setting up MySQL replication is so simple now! 

● Consistency is guaranteed between master and slaves. 

● Simple to determine inconsistency 

● Fail-over process become much easier. 

● Automatic fail-over script is not a pain now.

GTID Important Variables 

● gtid-mode: ON|OFF 

● enforce-gtid-consistency: prevent executing the non-transactionally safe statements. 

● gtid-purged: transactions have been purged from the binary logs. 

● gtid-executed: transactions already executed on the server. 

● gtid-next: GTID for the next transaction.

Setting Up Replication Using GTIDs

This section describes a process for configuring and starting GTID-based replication in MySQL 5.6. This is a “cold start” procedure that assumes either that you are starting the replication master for the first time,

The key steps in this startup process for the simplest possible GTID replication topology—consisting of one master and one slave:

Step 1: If replication is already running, synchronize both servers by making them read-only.

Synchronize the servers.  Make the servers read-only. To do this, enable the read_only system variable by executing the following statement on both servers:

mysql> SET @@global.read_only = ON;

Then, allow the slave to catch up with the master. It is extremely important that you make sure the slave has processed all updates before continuing.

Step 2: Stop both servers.  Stop each server using mysqladmin as shown here, where username is the user name for a MySQL user having sufficient privileges to shut down the server:

shell> mysqladmin -uusername -p shutdown

Then supply this user's password at the prompt.

Step 3: Restart both servers with GTIDs enabled.  To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging enabled, and with statements that are unsafe for GTID-based replication disabled. In addition, you should prevent unwanted or accidental updates from being performed on either server by starting both in read-only mode. This means that both servers must be started with (at least) the options shown in the following invocation of mysqld_safe:

shell> mysqld_safe --gtid_mode=ON --log-bin --log-slave-updates --enforce-gtid-consistency &

Note: On master my.cnf

We can add below parameters in my.cnf in place of above step,

 [mysqld] 

server-id = 1 

log-bin = mysql-bin 

binlog_format = ROW 

gtid_mode = on 

enforce_gtid_consistency 

log_slave_updates.

In addition, you should start the slave with the --skip-slave-start option along with the other server options specified in the example just shown.

Note: On Slave

Add the following variables to my.cnf:

[mysqld] 

server_id = 2 

log_bin = mysql-bin 

binlog_format = ROW 

skip_slave_start 

gtid_mode = on 

enforce_gtid_consistency 

log_slave_updates

Restart MySQL to apply the configuration changes on both the server: 

shell> service mysql restart

Step 4 : Create a MySQL user on Master to be used by the slave:

MySQL> GRANT REPLICATION SLAVE ON *.* TO 'slave_user_name'@'slave_ip' IDENTIFIED BY 's3cret';

Step 5: Direct the slave to use the master.  Tell the slave to use the master as the replication data source, and to use GTID-based auto-positioning rather than file-based positioning. Execute a CHANGE MASTER TO statement on the slave, using the MASTER_AUTO_POSITION option to tell the slave that transactions will be identified by GTIDs, and then start the slave.

You may also need to supply appropriate values for the master's host name and port number as well as the user name and password for a replication user account which can be used by the slave to connect to the master; 

mysql> CHANGE MASTER TO
     >     MASTER_HOST = master_host,
     >     MASTER_PORT = master_port,
     >     MASTER_USER = slave_user,
     >     MASTER_PASSWORD = slave_user_password,
     >     MASTER_AUTO_POSITION = 1;

Neither the MASTER_LOG_FILE option nor the MASTER_LOG_POS option may be used with MASTER_AUTO_POSITION set equal to 1. Attempting to do so causes the CHANGE MASTER TO statement to fail with an error.

Assuming that the CHANGE MASTER TO statement has succeeded, you can then start the slave, like this:

mysql> START SLAVE;

Step 6: Disable read-only mode.  Allow the master to begin accepting updates once again by running the following statement:

mysql> SET @@global.read_only = OFF;

GTID-based replication should now be running, and you can begin (or resume) activity on the master as before. 

Most of the steps that follow require the use of the MySQL root account or another MySQL user account that has the SUPER privilege. mysqladmin shutdown requires either the SUPER privilege or the SHUTDOWN privilege.

Checking the replication !!

NewSlave > show slave status\G
         [..]
         Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
         [...]
         Retrieved_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:5
         Executed_Gtid_Set: c777888a-b6df-11e2-a604-080027635ef5:1-5