MySQL master slave replication in CentOS 6

[root@localhost ~]# yum install mysql mysql-server mysql-devel -y

[root@localhost ~]# hostname -I
192.168.183.135
[root@localhost ~]# /etc/init.d/mysqld status
mysqld is stopped
[root@localhost ~]# service mysqld status
mysqld is stopped

[root@localhost ~]# service mysqld start
Initializing MySQL database: Installing MySQL system tables…
OK
Filling help tables…
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h localhost.localdomain password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[ OK ]
Starting mysqld: [ OK ]
[root@localhost ~]# netstat -tlpn |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 16610/mysqld

[root@localhost ~]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
———————————————–
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
bind-address=192.168.183.135
server-id=1
binlog-ignore-db = “mysql”
binlog-format = mixed
log-bin=mysql-bin
datadir=/var/lib/mysql
innodb_flush_log_at_trx_commit=1
sync_binlog=1
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[root@localhost ~]# service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@localhost ~]# netstat -tlpn |grep 3306
tcp 0 0 192.168.183.135:3306 0.0.0.0:* LISTEN 16824/mysqld

[root@localhost ~]# /usr/bin/mysqladmin -u root password ‘new-password’

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> GRANT REPLICATION SLAVE ON *.* TO replication@192.168.183.136 IDENTIFIED BY ‘password’;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)
mysql> create database rafi;
Query OK, 1 row affected (0.02 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| rafi |
| test |
+——————–+
4 rows in set (0.00 sec)

mysql> use rafi;
Database changed
mysql> CREATE TABLE example (name VARCHAR(20), owner VARCHAR(20),species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
Query OK, 0 rows affected (0.01 sec)

mysql> describe example;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name | varchar(20) | YES | | NULL | |
| owner | varchar(20) | YES | | NULL | |
| species | varchar(20) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| birth | date | YES | | NULL | |
| death | date | YES | | NULL | |
+———+————-+——+—–+———+——-+
6 rows in set (0.01 sec)
mysql> INSERT INTO example VALUES (‘Puffball’,’Diane’,’hamster’,’f’,’1999-03-30′,NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from example;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+———-+——-+———+——+————+——-+
1 row in set (0.00 sec)
mysql> \q
Bye

[root@localhost ~]# mysqldump –skip-lock-tables –single-transaction –hex-blob –flush-logs –master-data=2 -u root -p rafi > rafi-backup.sql
Enter password:
[root@localhost ~]# ls -lh rafi-backup.sql
-rw-r–r– 1 root root 2.2K Apr 12 04:39 rafi-backup.sql

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 106 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

[root@localhost ~]# head -100 rafi-backup.sql | grep “MASTER_LOG_POS”
— CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=106;

[root@localhost ~]# scp rafi-backup.sql root@192.168.183.136:/root
The authenticity of host ‘192.168.183.136 (192.168.183.136)’ can’t be established.
RSA key fingerprint is ef:21:fd:a5:17:ee:db:9c:92:96:76:bd:4b:fa:48:14.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.183.136’ (RSA) to the list of known hosts.
root@192.168.183.136’s password:
rafi-backup.sql 100% 2179 2.1KB/s 00:00

############ Slave Side ###########################

[root@localhost ~]# yum install mysql mysql-server mysql-devel -y

[root@localhost ~]# hostname -I
192.168.183.136

[root@localhost ~]# service mysqld status
mysqld is stopped
[root@localhost ~]# service mysqld start
Initializing MySQL database: Installing MySQL system tables…
OK
Filling help tables…
OK

To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h localhost.localdomain password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &

You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl

Please report any problems with the /usr/bin/mysqlbug script!

[ OK ]
Starting mysqld: [ OK ]

[root@localhost ~]# /usr/bin/mysqladmin -u root password ‘new-password’

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| test |
+——————–+
3 rows in set (0.00 sec)

mysql> exit

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=50
binlog-format=mixed
log_bin=mysql-bin
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@localhost ~]# /etc/init.d/mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ]
[root@localhost ~]# netstat -tlpn |grep 3306
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 43726/mysqld

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> create database rafi;
Query OK, 1 row affected (0.00 sec)

mysql> exit
Bye

[root@localhost ~]# ls -lh rafi-backup.sql
-rw-r–r– 1 root root 2.2K Apr 12 04:43 rafi-backup.sql

[root@localhost ~]# mysql -u root -p rafi < rafi-backup.sql
Enter password:
[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>
mysql> SHOW SLAVE STATUS \G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> CHANGE MASTER TO MASTER_HOST=’192.168.183.135′,MASTER_USER=’replication’,MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000003′, MASTER_LOG_POS=106;
Query OK, 0 rows affected (0.02 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.183.135
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 106
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 251
Relay_Master_Log_File: mysql-bin.000003
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: 106
Relay_Log_Space: 406
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

################# Master side ############

 

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> show master status;
+——————+———-+————–+——————+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————+———-+————–+——————+
| mysql-bin.000003 | 106 | | mysql |
+——————+———-+————–+——————+
1 row in set (0.00 sec)

mysql> use rafi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from example;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+———-+——-+———+——+————+——-+
1 row in set (0.00 sec)

mysql> INSERT INTO example VALUES (‘jack’,’jones’,’human’,’m’,’1991-06-24′,NULL);
Query OK, 1 row affected (0.00 sec)

mysql> select * from example;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| jack | jones | human | m | 1991-06-24 | NULL |
+———-+——-+———+——+————+——-+
2 rows in set (0.00 sec)

mysql>
mysql> show processlist;
+—-+————-+———————–+——+————-+——+—————————————————————-+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———————–+——+————-+——+—————————————————————-+——————+
| 13 | replication | 192.168.183.136:36770 | NULL | Binlog Dump | 304 | Has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 14 | root | localhost | rafi | Query | 0 | NULL | show processlist |
+—-+————-+———————–+——+————-+——+—————————————————————-+——————+
2 rows in set (0.00 sec)

###################### Slave Side ##########################

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| rafi |
| test |
+——————–+
4 rows in set (0.00 sec)

mysql> use rafi;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+—————-+
| Tables_in_rafi |
+—————-+
| example |
+—————-+
1 row in set (0.00 sec)

mysql> select * from example;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
+———-+——-+———+——+————+——-+
1 row in set (0.00 sec)
mysql> select * from example;
+———-+——-+———+——+————+——-+
| name | owner | species | sex | birth | death |
+———-+——-+———+——+————+——-+
| Puffball | Diane | hamster | f | 1999-03-30 | NULL |
| jack | jones | human | m | 1991-06-24 | NULL |
+———-+——-+———+——+————+——-+
2 rows in set (0.00 sec)
mysql> SHOW SLAVE STATUS \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.183.135
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 242
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 387
Relay_Master_Log_File: mysql-bin.000003
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: 242
Relay_Log_Space: 542
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)

mysql> show processlist;
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| Id | User | Host | db | Command | Time | State | Info |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
| 4 | root | localhost | rafi | Query | 0 | NULL | show processlist |
| 5 | system user | | NULL | Connect | 313 | Waiting for master to send event | NULL |
| 6 | system user | | NULL | Connect | 143 | Has read all relay log; waiting for the slave I/O thread to update it | NULL |
+—-+————-+———–+——+———+——+———————————————————————–+——————+
3 rows in set (0.00 sec)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s