DBA成長之路---mysql主從同步，讀寫分離

標籤：原因分析   選項   資料庫服務   關閉selinux   stop   ddr   pos   pre   主從同步配置   

主從同步

什麼是主從同步： 讓其他的資料庫伺服器自動同步正在提供服務的資料庫伺服器上 的資料。

        1 添加授權使用者

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

        2啟用binlog日誌

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

        [mysqld]

        server_id=3

        log-bin=master11

        binlog_format="mixed"

        重啟資料庫服務

        [[email protected] ~]# systemctl restart mysqld

        4 查看日誌資訊

        mysql> show master status;

        master11.000001

        

        驗證主庫提供的授權使用者

        [[email protected] ~]# mysql -h 192.168.4.3 -u slaveuser -p123456

        修改設定檔

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

        [mysqld]

        server_id=4

        log-bin=db4

        [[email protected] ~]# systemctl restart mysqld

        

        使用本機器的資料管理員登入，指定主庫資訊

        show slave status;

        mysql> change master to master_host="192.168.4.3", master_user="slaveuser",master_password="123456",master_log_file="master11.000001",master_log_pos=154;

        mysql> show slave status\G;

                  Master_Log_File: master11.000001

                  Read_Master_Log_Pos: 154

                  Slave_IO_Running: No

                  Slave_SQL_Running: No

        mysql> start slave;

        mysql> show slave status\G;

                     Slave_IO_Running: Yes

                    Slave_SQL_Running: Yes

        slave 運行2個線程

        Slave_IO:複製master主機 binlog記錄檔裡的sql到原生relay-log檔案裡

        Slave_SQL:執行本機relay-log檔案裡的SQL語句，重現Master的資料操作

        

從資料庫目錄下會多的檔案

    master.info串連主伺服器資訊

    relay-log.info中繼日誌資訊

    主機名稱-relay-bin.000000 中繼記錄檔

    主機名稱-relay-bin.index中繼日誌索引

測試主從同步配置

    在主程式庫伺服器上添加訪問資料的使用者

    mysql> grant all on bbsdb.* to [email protected]"%" identified by '123456';

    mysql> show master status;

    | master11.000001 |      446 

    

    在用戶端使用授權使用者串連主庫，產生的資料在從庫也能查到

    [[email protected] ~]# mysql -h 192.168.4.3 -uyaya -p123456

    MySQL [(none)]> create database bbsdb;

    這時在

    [[email protected] ~]# 主伺服器

    [[email protected] ~]#從伺服器

    上都會產生相應的表

將從伺服器變被獨立伺服器，關閉同步

    mysql> stop slave;

    rm -rf master.info  relay-log.info

    rm -rf localhost-relay-bin*

故障分析及排除

    原因分析

    串連不上master資料庫伺服器

    解決辦法

    檢查物理串連ping 檢查授權使用者

    檢查是否有防火牆規則

    關閉SElinux

    或是binlog記錄檔指定錯誤

    

主從同步結構模式

    一主一從

    一主多從

    主從從

    主主結構（互為）

主從同步常用配置參數

        寫在vim /etc/my.cnf

        [mysqld]

        選項=值

        

主庫設定檔（對所有的從都有效）

不允許同步的庫binlog_ignore_db=庫名1，庫名2

只允許同步的庫binlog_do_db=庫名1，庫名2

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

        binlog_do_db=dba

        [[email protected] ~]# systemctl restart mysqld

        

從庫設定檔（只對本機有效）

        只同步的庫replicate_do_db=庫名1，庫名2

        不同步的庫replicate_ignore_db=庫名1，庫名2

        級聯複製log_slave_updates

資料讀寫分離   maxscale軟體  + 一主一從

什麼是讀寫分離： 把用戶端訪問資料時的查詢請求select 和寫請求insert 給不同的資料庫伺服器去處理

寫（主服務） 

讀 （從服務）

一。

裝包

[[email protected] ~]# rpm -ivh maxscale-2.1.2-1.rhel.7.x86_64.rpm 

修改設定檔：指定資料庫伺服器

[[email protected] ~]# vim /etc/maxcale.cnf

  9 [maxscale]

 10 threads=auto開啟線程 根據cpu

 18 [server1]#資料庫伺服器名

 19 type=server

 20 address=192.168.4.3#指定ip

 21 port=3306

 22 protocol=MySQLBackend

 23 

 24 [server2]#資料庫伺服器名

 25 type=server

 26 address=192.168.4.4#指定ip

 27 port=3306

 28 protocol=MySQLBackend

 36 [MySQL Monitor]

 37 type=monitor

 38 module=mysqlmon

 39 servers=server1,server2#監聽兩台 資料庫伺服器

 40 user=scalemon#授權使用者名

 41 passwd=123456#授權使用者密碼

 42 monitor_interval=10000#毫秒  每十秒監聽一次

 52 #[Read-Only Service]#Read-Only 全注釋

 53 #type=service

 54 #router=readconnroute

 55 #servers=server1

 56 #user=myuser

 57 #passwd=mypwd

 58 #router_options=slave

 63 [Read-Write Service]

 64 type=service

 65 router=readwritesplit

 66 servers=server1,server2#監聽兩台 資料庫伺服器

 67 user=maxscale#授權使用者名

 68 passwd=123456#授權使用者密碼

 69 max_slave_connections=100%

 75 [MaxAdmin Service]#定義軟體管理服務不用改

 76 type=service

 77 router=cli#使用方式  命令列

 85 #[Read-Only Listener]#Read-Only 全注釋

 86 #type=listener

 87 #service=Read-Only Service

 88 #protocol=MySQLClient

 89 #port=4008

 91 [Read-Write Listener]

 92 type=listener

 93 service=Read-Write Service#服務名

 94 protocol=MySQLClient

 95 port=4006#讀寫分離監聽連接埠號碼（預設）

 97 [MaxAdmin Listener]#管理服務監聽

 98 type=listener

 99 service=MaxAdmin Service

100 protocol=maxscaled

101 socket=default

102 port=4009#指定管理連接埠

根據設定檔設定，在資料庫上添加相應的授權使用者

scalemon123456監控資料庫服務狀態的使用者（資料庫服務狀態和主從同步狀態）

mysql> grant replication slave,replication client on *.* to [email protected]'%' identified by '123456';

mysql> grant select on mysql.* to [email protected]'%' identified by '123456';

maxscale123456檢查用戶端串連時使用的使用者名稱和密碼在監視的資料庫伺服器上是否存在

在代理主機測試

  mysql -h192.168.4.3 -uscalemon -p123456

  mysql -h192.168.4.3 -umaxscale -p123456

  mysql -h192.168.4.4 -umaxscale -p123456

  mysql -h192.168.4.4 -uscalemon -p123456

啟動服務

[[email protected] ~]# maxscale -f /etc/maxscale.cnf 

[[email protected] ~]# netstat -pantu | grep maxscale

tcp        0      0 192.168.4.5:42703       192.168.4.3:3306        ESTABLISHED 4877/maxscale       

tcp        0      0 192.168.4.5:44918       192.168.4.4:3306        ESTABLISHED 4877/maxscale       

tcp6       0      0 :::4009                 :::*                    LISTEN      4877/maxscale       

tcp6       0      0 :::4006                 :::*                    LISTEN      4877/maxscale   

二。

測試組態

在Proxy 伺服器本機訪問管理服務

[[email protected] ~]# maxadmin -uadmin -pmariadb -P4009

MaxScale> list servers

Servers.

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

Server                               | Address                       | Port         | Connections     | Status              

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

server1                              | 192.168.4.3                 |  3306     |           0                | Master, Running

server2                              | 192.168.4.4                 |  3306     |           0                | Slave, Running

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

在用戶端 訪問Proxy 伺服器 儲存資料或查詢資料

4-3

mysql> create database bbsdb;

Query OK, 1 row affected (0.00 sec)

mysql> create table bbsdb.t1(id int);

Query OK, 0 rows affected (0.26 sec)

MySQL [(none)]> show grants;

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

| Grants for [email protected]%                                                                           |

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

| GRANT USAGE ON *.* TO 'yaya'@'%'                                          |

| GRANT ALL PRIVILEGES ON `bbsdb`.* TO 'yaya'@'%'            |

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

2 rows in set (0.00 sec)

查看時顯示為從服務

MySQL [(none)]> select @@hostname;

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

| @@hostname |

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

| 4-4mysql          |

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

1 row in set (0.00 sec)

MySQL [(none)]> insert into bbsdb.t1 values(100);

添加後切換為主服務

MySQL [(none)]> select @@hostname;

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

| @@hostname |

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

| 4-3mysql          |

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

1 row in set (0.00 sec)

DBA成長之路---mysql主從同步，讀寫分離