mysql主從複製環境搭建

標籤：mysql   主從複製、   

準備安裝包
從ORACLE官方網站下載最新版本的MYSQL用戶端和伺服器安裝包，MYSQL目前採用整體打包下載，沒單獨的安裝包下載，直接下載

V45446-01整體壓縮包，裡麵包含最新版本MYSQL5.6.19的用戶端和伺服器安裝包。

MySQL-client-advanced-5.6.19-1.el6.x86_64.rpm（V45446-01）
MySQL-server-advanced-5.6.19-1.el6.x86_64.rpm（V45446-01）

在redhat安裝，還要準備MYSQL 關依賴包：
perl-5.10.1-136.el6.x86_64.rpm
libaio-0.3.107-10.el6.x86_64.rpm
mysql-lib5-5.1.71-1.el6.x86_64.rpm

一、mysql安裝

1、安裝系統、配置ip、配置/etc/hosts 檔案
/etc/hosts 檔案
192.168.1.21 replm
192.168.1.22 replsl

配置ip
[[email protected] mysql]# cat /etc/sysconfig/network-scripts/ifcfg-eth0
DEVICE=eth0
HWADDR=08:00:27:a7:0e:e2
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=yes
BOOTPROTO=static
IPADDR=192.168.1.21
NETMASK=255.255.255.0
GATEWAY=192.168.1.1
IPV6INIT=no
USERCTL=no

2、上傳rpm 安裝包
3、關閉防火牆 （需要關閉，否則同步可能不成功，我遇到此問題，後面詳細介紹遇到的問題）
service iptables stop     --停止防火牆
chkconfig iptables off   --配置系統重啟不啟動防火牆

4、配置yum源

mount  /dev/cdrom  /mnt/    --掛載光碟機

 vi /etc/yum.repos.d/mysql.repo

[mysql]
name=mysql
baseurl=file:///mnt
gpgcheck=0
enable=1

5、刪除系統內建的 mysql-libs* 包，否則安裝MySQL-server-advanced-5.6.19-1.el6.x86_64.rpm 報錯，刪除命令為：
yum -y remove mysql-libs-*

6、安裝rpm包，命令為 rpm
rpm -ivh MySQL-devel-advanced-5.6.19-1.el6.x86_64.rpm
rpm -ivh MySQL-client-advanced-5.6.19-1.el6.x86_64.rpm
rpm -ivh MySQL-server-advanced-5.6.19-1.el6.x86_64.rpm

7、啟動mysql

service mysql start
設定開啟自啟動
chkconfig mysql on

8、登陸MYSQL，並設定root使用者密碼

Mysql安裝後，會將預設密碼放在/root/.mysql_secret檔案中，第一次登陸時，請採用改密碼登陸：
cat /root/.mysql_secret
# The random password set for the root user at Thu Jul 10 16:23:52 2014 (local time): GeEUu0kRfQKatLY0

使用預設密碼登陸mysql：
mysql -uroot -p GeEUu0kRfQKatLY0

設定新的root密碼，假設設定為111111

mysql> SET PASSWORD = PASSWORD(‘111111‘);

設定root使用者登陸許可權，使使用者可以使用用戶端遠程登陸：

mysql> use mysql;
mysql> GRANT ALL PRIVILEGES ON *.* TO [email protected]"%" IDENTIFIED BY "111111";
mysql> update user set password = password(‘111111‘) where user=‘root‘;

重新整理許可權,許可權修改才會生效：
mysql>flush privileges;

注意：遠程登陸並驗證root使用者訪問沒問題，再開始配置主從複製。

二、 MYSQL主從配置

1、先關閉服務：
 service mysql stop

修改mysql設定檔，主從複製要求台機器server-id 不能相同，從庫必須開啟relay-log：

1）修改master 設定檔
 vi /etc/my.cnf
server-id = 111
log-bin=/var/lib/mysql/binlog/mysql-bin.log     
relay-log=/var/lib/mysql/relaylog/mysql-relay.log

建立日誌路徑：
mkdir /var/lib/mysql/binlog
mkdir /var/lib/mysql/relaylog

修改路徑許可權：
chown mysql:mysql /var/lib/mysql/binlog -R
chown mysql:mysql /var/lib/mysql/relaylog -R

2）修改從slave 設定檔
vi /etc/my.cnf
加入以下內容：
server-id = 222
log-bin=/var/lib/mysql/binlog/mysql-bin.log
relay-log=/var/lib/mysql/relaylog/mysql-relay.log

建立日誌路徑：
mkdir /var/lib/mysql/binlog
mkdir /var/lib/mysql/relaylog

修改路徑許可權：
chown mysql:mysql /var/lib/mysql/binlog -R
chown mysql:mysql /var/lib/mysql/relaylog -R

2、master 和salve 啟動mysql執行個體

service mysql start

3、登陸master 和slave 設定專用的複製帳號repl，主從都執行

設定主從帳號，起名為repl，設定其存取範圍為192.168.1網段內，密碼123456：

mysql > grant replication slave,replication client on *.* to ‘repl‘@‘192.168.1.%‘ identified by ‘111111‘;
重新整理許可權：
mysql > flush privileges;

查看主庫當前位置（主庫執行）
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3、從庫開始同步，同步位置為主庫當前位置（從庫執行）：
mysql>change master to master_host=‘192.168.100.6‘,master_user=‘repl‘,
master_password=‘123456‘,master_log_file=‘mysql-bin.000003‘,master_log_pos=235;

啟動slave
mysql> start slave;

檢查從庫是否複製正常（Slave_IO_Running和Slave_SQL_Running都Yes，必須都為yes ，其他狀態都不複製）

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 120
               Relay_Log_File: replsl-relay-bin.000002
                Relay_Log_Pos: 283
        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: 120
              Relay_Log_Space: 457
              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:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 111
                  Master_UUID: 7b4e0e11-05dc-11e5-b3be-080027a70ee2
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.00 sec)

配置完成

三、測試

1、master端上建立資料庫和表、並插入資料

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

mysql> use zxmdb;
Database changed
mysql> create table t1 (id int,name char(10));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into t1 values (1,‘zxm‘);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (1,‘zxm‘);
Query OK, 1 row affected (0.02 sec)

mysql> insert into t1 values (1,‘zxm‘);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 values (2,‘king ‘);
Query OK, 1 row affected (0.04 sec)

2、slave端驗證

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| zxmdb              |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zxmdb
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 t1;
+------+------+
| id   | name |
+------+------+
|    1 | zxm  |
|    1 | zxm  |
|    1 | zxm  |
|    2 | king |
+------+------+
4 rows in set (0.00 sec)

四、總結

遇到問題
1、安裝過程中報錯，無法安裝 MySQL-server-advanced-5.6.19-1.el6.x86_64.rpm ，原因是redhat 內建安裝了 mysql-libs-

5.1.66-2.el6_3.x86_64，已經存在某些檔案，導致無法安裝。
解決方案： 刪除 mysql-libs-5.1.66-2.el6_3.x86_64
yum -y remove mysql-libs-*
然後安裝成功

2、slave端執行show slave status 輸出     Slave_IO_Running: Connecting ，導致不能複製資料；
 Last_IO_Error: error connecting to master ‘[email protected]:3306‘ - retry-time: 60  retries: 36
分析原因：slave端無法串連到master端
1）repl 使用者權限不對
2）連接埠被佔用
3）防火牆阻擋
 
1）測試連接，果然報錯
 mysql -u repl -p111111 -h 192.168.1.21
Warning: Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can‘t connect to MySQL server on ‘192.168.1.21‘ (113)

2）關閉防火牆測試連接，正常串連，然後重啟slave ，複製正常

1. my.cnf 設定檔中 skip-networking 、bindaddress  被配置

skip-networking 導致所有TCP/IP連接埠沒有被監聽,其他用戶端都無法用網路連接到本mysql伺服器
bindaddress這個參數是mysql伺服器只回應配置ip地址的請求串連

 

mysql主從複製環境搭建