MySQL基礎day06_mysql伺服器的主從同步-MySQL 5.6

mysql伺服器的主從同步執行個體

環境：

server1     192.168.1.200

server2     192.168.1.110

環境部署：

1，首先保證伺服器在同一個網段，能夠互相連通

2，確定防火牆，selinux關閉

[root@server1  ~]# iptables -F          //清空防火牆清單

[root@server1  ~]# service iptables save           //儲存防火牆清單

[root@server1  ~]# service iptables stop            //關閉防火牆

iptables:  Flushing firewall rules:                         [  OK  ]

iptables:  Setting chains to policy ACCEPT: filter          [   OK  ]

iptables:  Unloading modules: c                             [  OK  ]

[root@server1  ~]# chkconfig iptables off          //設定防火牆自動關閉

[root@server1  ~]# getenforce   //檢視selinux的狀态（enforcing為開啟，permissive為允許，disabled為關閉）

Enforcing                      //若為開啟狀态，則需用setenforce 0 關閉

[root@server1  ~]# setenforce 0                //關閉selinux

[root@server1  ~]# vim /etc/sysconfig/selinux     //編輯selinux配置檔案，使其下次開機時為強制關閉狀态

[root@server1  ~]# grep -vE "^#|^$" /etc/sysconfig/selinux  

SELINUX=disabled

SELINUXTYPE=targeted

[root@server1  ~]#

3，確定能夠連通server2和server3

[root@server1  ~]# ping 192.168.1.110 -c 2

PING  192.168.1.110 (192.168.1.110) 56(84) bytes of data.

64 bytes  from 192.168.1.110: icmp_seq=1 ttl=64 time=0.708 ms

64 bytes  from 192.168.1.110: icmp_seq=2 ttl=64 time=0.394 ms

---  192.168.1.110 ping statistics ---

2 packets  transmitted, 2 received, 0% packet loss, time 1001ms

rtt  min/avg/max/mdev = 0.394/0.551/0.708/0.157 ms

[root@server1  ~]# ping 192.168.1.120 -c 2          //可以看到，能夠連通server2和server3

PING  192.168.1.120 (192.168.1.120) 56(84) bytes of data.

64 bytes  from 192.168.1.120: icmp_seq=1 ttl=64 time=2.66 ms

64 bytes  from 192.168.1.120: icmp_seq=2 ttl=64 time=0.379 ms

---  192.168.1.120 ping statistics ---

2 packets  transmitted, 2 received, 0% packet loss, time 1002ms

rtt  min/avg/max/mdev = 0.379/1.521/2.663/1.142 ms

注：server2和server3上也做同樣操作

第一步：安裝mysql資料庫相關軟體包，在所有伺服器上

[root@server1  ~]# cd /data/

[root@server1  data]# tar xvf  MySQL-5.6.15-1.el6.x86_64.rpm-bundle.tar

MySQL-shared-5.6.15-1.el6.x86_64.rpm

MySQL-devel-5.6.15-1.el6.x86_64.rpm

MySQL-embedded-5.6.15-1.el6.x86_64.rpm

MySQL-test-5.6.15-1.el6.x86_64.rpm

MySQL-server-5.6.15-1.el6.x86_64.rpm

MySQL-shared-compat-5.6.15-1.el6.x86_64.rpm

MySQL-client-5.6.15-1.el6.x86_64.rpm

[root@server1  data]# mkdir mysql

[root@server1  data]# mv MySQL-*.rpm ./mysql/

[root@server1  data]# cd mysql/

[root@server1  mysql]# rpm -Uvh MySQL-*              //安裝解壓出來的所有MySQL開頭的軟體包

注：server2，server3上執行同樣步驟

第二步：在server1上做授權

授權允許在192.168.1.110伺服器上使用者名userslave可以備份該資料庫：

grant replication slave on *.* to userslave@"192.168.1.110"identified by "123";

[root@server1  ~]# service mysql start

[root@server1  ~]# cat .mysql_secret           //mysql生成的随機密碼存放在~/.mysql_secret檔案内

# The random  password set for the root user at Thu Mar 20 19:31:40 2014 (local time): LiYvwtWk

[root@server1  ~]# mysql -pLiYvwtWk         //使用初始密碼登陸

mysql> set password for  "root"@"localhost"=password("tarena");   //修改密碼為tarena

mysql> grant replication slave on *.* to  userslave@"192.168.1.110" identified by "123";  //授權使用者

mysql>  quit

Bye

第三步：在server2上通過授權使用者userslave登入測試

[root@server2  ~]# ifconfig | head -2

eth0      Link encap:Ethernet  HWaddr 00:0C:29:CF:51:E3  

         inet addr:192.168.1.110  Bcast:192.168.1.255  Mask:255.255.255.0

[root@server2  ~]# mysql -h 192.168.1.200 -u userslave -p123

Warning:  Using a password on the command line interface can be insecure.

Welcome to  the MySQL monitor.  Commands end with ;  or \g.

Your MySQL  connection id is 3

Server  version: 5.6.15 MySQL Community Server (GPL)

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>

第四步：開啟server1上mysql伺服器的binlog日志并分别指定server1和server2上的server_id值

在server1的mysql配置檔案裡開啟log-bin ;

在server1和server2上分别指定server_id值;

重新開機mysql服務使其生效;

生效時可以看到mysql目錄下多出兩個檔案：server1-bin.000001和server1-bin.index

server1上的設定

[root@server1  ~]# grep -vE "^#|^$" /etc/my.cnf       //原始資料，沒有開啟binlog，也沒有設定server_id值

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[root@server1  ~]# vim /etc/my.cnf

[root@server1  ~]# grep -vE "^#|^$" /etc/my.cnf

log-bin        //添加字段bin-log啟動

server_id=200          //添加server_id字段，其值在伺服器中應唯一，是以最好指定為ip位址的網絡位

[root@server1  ~]# ll /var/lib/mysql/server1*

-rw-rw----.  1 mysql mysql 6 Mar 20 20:13 /var/lib/mysql/server1.example.com.pid

[root@server1  ~]# service mysql restart

Shutting  down MySQL..                                      [  OK  ]

Starting  MySQL.                                             [  OK  ]

[root@server1  ~]# ll /var/lib/mysql/server1*        //多出兩個以server1-bin開頭的檔案

-rw-rw----.  1 mysql mysql 120 Mar 20 20:14 /var/lib/mysql/server1-bin.000001

-rw-rw----.  1 mysql mysql  21 Mar 20 20:14  /var/lib/mysql/server1-bin.index

-rw-rw----.  1 mysql mysql   6 Mar 20 20:14  /var/lib/mysql/server1.example.com.pid

server2上的配置

[root@server2  ~]# vim /etc/my.cnf

[root@server2  ~]# head -3 /etc/my.cnf

server_id=110                    //指定server_id為110

[root@server2  ~]# service mysql restart

Shutting  down MySQL....                                    [  OK  ]

Starting  MySQL..                                            [  OK  ]

[root@server2  ~]#

第五步：server2登入本機的資料庫服務進行配置，使其成為server1的從伺服器

執行：start slave啟動slave程序

1，檢視主伺服器server1的偏移量

[root@server1  ~]# mysql -p tarena         //登入主伺服器檢視偏移量

mysql> show master status;                  //查詢主伺服器的偏移量

+--------------------+----------+--------------+------------------+-------------------+

| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

| server1-bin.000001 |      120 |              |                  |                   |

1 row in set  (0.00 sec)

mysql> quit

2，在server2伺服器進行從伺服器的配置

[root@server2  ~]# mysql -ptarena           //登入本機伺服器進行設定

mysql>  change master to

   ->  master_host="192.168.1.200",       //指定主伺服器ip位址

   -> master_user="userslave",          //指定授權登入使用者名

   -> master_password="123",           //指定授權登入使用者密碼

   ->  master_log_file="server1-bin.000001",      //指定server1的日志檔案

   -> master_log_pos=120;           //指定binlog日志的偏移量(pos)

Query OK, 0  rows affected, 2 warnings (0.07 sec)

[root@server2  ~]# ll

total 110636

-rw-rw---- 1  mysql mysql       56 Mar 20 14:18  auto.cnf

-rw-rw---- 1  mysql mysql 12582912 Mar 20 20:47 ibdata1

-rw-rw---- 1  mysql mysql 50331648 Mar 20 20:48 ib_logfile0

-rw-rw---- 1  mysql mysql 50331648 Mar 20 14:18 ib_logfile1

-rw-rw----  1 mysql mysql       93 Mar 20 21:00  master.info    //連接配接主伺服器server1的資訊

drwx--x--x 2  mysql mysql     4096 Mar 20 14:18 mysql

srwxrwxrwx 1  mysql mysql        0 Mar 20 20:47  mysql.sock

drwx------ 2  mysql mysql     4096 Mar 20 14:18  performance_schema

-rw-rw----  1 mysql mysql       59 Mar 20 21:00  relay-log.info     //中繼日志資訊

-rw-r--r-- 1  root  root       111 Mar 20 14:18 RPM_UPGRADE_HISTORY

-rw-r--r-- 1  mysql mysql      111 Mar 20 14:18  RPM_UPGRADE_MARKER-LAST

-rw-rw---- 1  mysql mysql        6 Mar 20 20:47  server2.example.com.pid

-rw-rw----  1 mysql mysql      120 Mar 20 21:00  server2-relay-bin.000001    //中繼binlog日志

-rw-rw----  1 mysql mysql       27 Mar 20 21:00  server2-relay-bin.index     //存放已有的中繼日志名

drwx------ 2  mysql mysql     4096 Mar 20 20:00 test

注：綠色顯示的四個檔案為start slave之後新添加的檔案

第六步：測試

在server1上登入mysql建立一個庫teadb

在teadb下建立一個表a

在server2登入mysql資料庫驗證是否同步

1，在server1上建立并檢視

[root@server1  ~]# mysql -ptarena

mysql>  create database teadb;

mysql>  create table teadb.a(id int(3));

mysql>  insert into teadb.a value(100),(100),(100);

mysql>  select * from teadb.a;

+------+

| id   |

|  100 |

3 rows in  set (0.00 sec)

2，在server2上查詢teadb.a的資訊

[root@server2  mysql]# mysql -ptarena

mysql> select * from teadb.a;          //登入後直接查詢，可以看到直接同步過來了

[root@server2  mysql]#

擴充：

1，在從伺服器上start slave 之後，可以在登入mysql服務之後使用show slave status\G來檢視Slave_IO_Running和Slave_SQL_Running兩個程序的狀态，都為Yes為成功！

2，IO和SQL程序的作用

IO程序的作用：

連接配接主資料庫伺服器；

從主的binlog日志裡拷貝sql語句放到本機的中繼binlog日志檔案裡。

SQL程序的作用：

執行本機中繼binlog日志檔案裡的sql語句，把資料寫進本機的資料庫裡。

注：由于SQL程序是同步的server1上的binlog日志檔案，是以如果server1沒有開啟binlog日志，server2将無法同步資料的寫入。

3，與從資料庫伺服器相關的4個配置檔案

master.info ：主伺服器(server1)的資訊，包含主機server1伺服器ip位址，授權用userslave以及密碼。IO程序根據該檔案的資訊區連接配接資料庫伺服器server1

relay-log.info  ：中繼日志資訊

server2-relay-bin.000001    ：中繼binlog日志

server2-relay-bin.index ：存放已有的中繼日志檔案名

4，若IO或SQL程序啟動不成功，可修改

使用change tomaster_xxx來修改IO程序的讀取資訊檔案master.info

注：在指定之前需停止slave：stop slave，修改之後，可以使用start slave開啟

若SQL程序無法啟動，則需檢視日志檔案，在沒有同步的時間都做了哪些修改，手動修改

注：配置不成功的幾個常見錯誤

1，防火牆或selinux沒有關閉

2，server1伺服器沒有開啟binlog日志

3，server1或server2沒有在配置檔案/etc/my.cnf檔案中添加server_id的值

4，server2的mysql資料庫中使用change to設定的值不于server1資料庫對應

本文轉自 murongqingqqq  51CTO部落格，原文連結:http://blog.51cto.com/murongqingqqq/1381922