mysql 主從同步實驗細解

標籤：mysql master slave

mysql  主從同步實驗細解

一.實驗環境

實驗環境 192.168.9.108 為master

              192.168.9.109 為slave

資料庫版本：version              5.1.73

安裝方式：採用的yum 安裝 源為163的源

系統版本：centos 6.5 

1.查看系統版本

[[email protected] ~]# cat /etc/issue

CentOS release 6.5 (Final)

Kernel \r on an \m

二.實驗準備

1.安裝mysql服務

[[email protected] opt]# ansible -i hosts tomcatserver -m yum -a "name=mysql-server  state=present"

注意：這裡我之前裝過ansible ，這裡統一安裝的。

     name這裡需要寫mysql-server  

2.啟動mysql 服務 

[[email protected] opt]# ansible -i hosts tomcatserver -m service  -a ‘name=mysqld state=started‘

3.為MySQL資料庫建立密碼

mysql> set password for [email protected] = password(‘123456‘);

4.配置主要資料庫，建立需要同步的資料庫

[[email protected] opt]# mysql -uroot -p123456

mysql> create database HA;

mysql> use HA;

mysql> create table T1(id int,name varchar(20));

三.配置主伺服器

1.配置my.cnf檔案

vim /etc/my.cnf

[[email protected] ~]# cat /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql  #資料的存放點

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

user=mysql #使用者

log-bin=mysql-bin-master # 啟動二進位日誌

server-id =1 #本機資料庫ID標識

binlog-do-db=HA  #可以被從伺服器複製的庫。二進位需要同步的資料庫名稱。

binlog-ignore-db=mysql #不可以被從伺服器複製的庫

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

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

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

2.重啟資料庫

修改完資料庫要重啟

service mysqld restart

3.授權從使用者可以登陸

mysql> grant replication slave on *.* to sla[email protected] identified by "123456";

4.查看狀態資訊

mysql> show master status;

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

| File                    | Position | Binlog_Do_DB               | Binlog_Ignore_DB |

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

| mysql-bin-master.000001 |      106 | HA                  | mysql            |

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

1 row in set (0.00 sec)

查看二進位日誌（這個是在設定檔裡面配置過的）

[[email protected] ~]# ls /var/lib/mysql/

HA  ibdata1  ib_logfile0  ib_logfile1  mysql  mysql-bin-master.000001  mysql-bin-master.index  mysql.sock  test

mysql> show binlog events \G

*************************** 1. row ***************************

   Log_name: mysql-bin-master.000001

        Pos: 4

 Event_type: Format_desc

  Server_id: 1

End_log_pos: 106

       Info: Server ver: 5.1.73-log, Binlog ver: 4

1 row in set (0.00 sec)

5.到處資料庫到從的伺服器上保證同步的資料庫一致

[[email protected] ~]# mysqldump -uroot -p123456 HA > HA.sql

[[email protected] ~]# scp HA.sql  [email protected]:/root/

HA.sql

四.配置從伺服器

1.測試連結到主伺服器是否成功

[[email protected] ~]# mysql -uslave -p123456 -h 192.168.9.108

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

Your MySQL connection id is 11

Server version: 5.1.73-log Source distribution

mysql> show databases;

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

| Database           |

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

| information_schema |

| test               |

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

2 rows in set (0.00 sec)

這裡看不到HA 的資料庫，只有複製許可權

2.匯入資料庫，和主要資料庫伺服器保持一致

mysql> create database HA;

mysql> mysql -uroot -p123456 HA<HA.sql

3.修改從伺服器的設定檔

[[email protected] ~]# 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

server-id=2#從伺服器ID號，不要和主ID相同 ，如果設定多個從伺服器，每個從伺服器必須有一個唯一的server-id值，必須與主伺服器的以及其它從伺服器的不相同。可以認為server-id值類似於IP地址：這些ID值能唯一識別複製伺服器叢集中的每個伺服器實

master-host=192.168.9.108 #指定主伺服器IP 位址

master-user=slave #指定住伺服器上可以進行同步的使用者名稱稱

master-password=123456 # 指定密碼

master-port=3306

master-connect-retry=60 #斷點重新連結時間

[mysqld_safe]

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

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

4.重啟資料庫

service mysqld restart

注意：這裡要重啟 如果直接啟動從服務會報錯如下

mysql> start slave;

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解決方案：

mysql> slave stop;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> reset slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to master_host=‘192.168.1.63‘,master_user=‘slave‘,master_password=‘123456‘;

mysql> start slave; 啟動從

mysql> show slave status\G  查看狀態

*************************** 1. row ***************************

               Slave_IO_State: Waiting for master to send event

                  Master_Host: 192.168.9.108

                  Master_User: slave

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File: mysql-bin-master.000001

          Read_Master_Log_Pos: 106

               Relay_Log_File: mysqld-slave-relay-bin.000002

                Relay_Log_Pos: 258

        Relay_Master_Log_File: mysql-bin-master.000001

             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: 420

              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)

Slave_IO_Running ：一個負責與主機的io通訊

Slave_SQL_Running：負責自己的slave mysql進程

五.主伺服器上查看狀態

mysql> show processlist \G

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    10

Current database: HA

*************************** 1. row ***************************

     Id: 6

   User: root

   Host: localhost

     db: NULL

Command: Sleep

   Time: 1480

  State:

   Info: NULL

*************************** 2. row ***************************

     Id: 9

   User: slave

   Host: 192.168.9.109:33320

     db: NULL

Command: Binlog Dump

   Time: 51

  State: Has sent all binlog to slave; waiting for binlog to be updated

   Info: NULL

*************************** 3. row ***************************

     Id: 10

   User: root

   Host: localhost

     db: HA

Command: Query

   Time: 0

  State: NULL

   Info: show processlist

1.主要資料庫插入資料進行測試

mysql> insert into T1 values(1,‘天河‘);

2.從資料庫進行查看

mysql> select * from HA.T1;

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

| id   | name   |

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

|    1 | 天河    |

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

1 row in set (0.00 sec

六.排錯

如果遇到主從不同步，看一下主從bin-log的位置，然後再同步。

mysql> show master status;

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

| File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin-master.000001 |      200 | HA           | mysql            |

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

1 row in set (0.00 sec)

從伺服器執行MySQL命令下：

mysql> slave stop；             #先停止slave服務

mysql> change master to master_log_file=‘mysqllog.000004‘ ,master_log_pos=106;

#根據上面主伺服器的show master status的結果，進行從伺服器的位元據庫記錄迴歸，達到同步的效果

mysql>slave start;                      #啟動從伺服器同步服務

mysql> show slave status\G;          #用show slave status\G;看一下從伺服器的同步情況

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

如果都是yes，那代表已經在同步

重啟從伺服器，再查看狀態：

停止從伺服器slave stop;

開啟從伺服器slave start;

排錯思路：

1、二進位日誌沒有開啟

2、IPTABLES 沒有放開連接埠

3、對應的主機 IP地址寫錯了

SQL線程出錯

1、主從伺服器資料庫結構不統一

出錯後，資料少，可以手動解決建立插入，再更新slave狀態。

註：如果主上誤刪除了。那麼從上也就誤刪除了。  #因此主上要定期做mysqldump備份。

七.完成

          主從同步到這裡就完成了  這裡可以採用指令碼來進行監控，監控 slave的兩個yes（Slave_IO及Slave_SQL進程），如發現只有一個或零個yes，就表明主從有問題了，發簡訊警報吧。

[[email protected] ~]# cat mysql_master.sh#!/bin/bashport=`netstat -anl|grep 3306 |sed -n ‘1p‘ |awk ‘{print $4}‘|awk -F: ‘{ print $2}‘`array=($(mysql -uroot -p123456 -e "show slave status\G"|grep "Running" |awk ‘{print $2}‘))if [ "$port" == "3306" ]then  if [ "${array[0]}" == "Yes" ] || [ "${array[1]}" == "Yes" ]    then      echo "slave is OK"    else      echo "slave is error"  fifi

本文出自 “天真無邪” 部落格，請務必保留此出處http://innocence.blog.51cto.com/4313888/1963300

mysql 主從同步實驗細解