中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

高可用Mysql安裝配置方法和步驟

發布時間：2020-04-15 17:03:12 來源：億速云 閱讀：298 作者：三月 欄目：建站服務器

億速云的負載均衡（Server Load Balancing）是對多臺云服務器（計算集群）進行流量分發的網絡服務設備。它可以通過流量分發，快速提高應用系統對外的服務能力；隱藏實際服務端口，增強內部系統的安全性；通過消除服務單點故障，提升應用系統的可靠性。今天億速云就給大家介紹下

 高可用Mysql安裝配置方法和步驟

1     高可用Mysql安裝配置

1.1  實驗環境

云服務器角色

云服務器IP

VIP

192.168.8.200

Mycat1

192.168.8.22

Mycat2

192.168.8.23

Mysql1(Master1)

192.168.8.24

Mysql2(Master2)

192.168.8.25

Mysql3(Slave)

192.168.8.26

 

1.2  Mysql安裝配置

(192.168.8.24，192.168.8.25和192.168.8.26)三臺服務器操作

1.2.1     Mysql安裝

到官網下載mysql的yum源

https://www.mysql.com/

安裝yum源rpm軟件

rpm-ivh mysql57-community-release-el7-11.noarch.rpm

 

yum安裝mysql

yuminstall mysql-community-server

1.2.2     Mysql初始化

啟動mysql

systemctlstart mysqld

獲取root密碼

cat/var/log/mysqld.log | grep "temporary password"

2017-07-06T03:23:46.053467Z1 [Note] A temporary password is generated for root@localhost: iGUl/j*_r1*z

修改root密碼

mysql–uroot –p

SETPASSWORD=PASSWORD('newpassword');

1.3  Mysql主主配置

(192.168.8.24和192.168.8.25)兩臺服務器操作

1.3.1     Mysql配置

192.168.8.24配置：

vi/etc/my.cnf

增加如下內容：

[mysqld]

symbolic-links=0

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

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

 

server_id  = 1

log-bin=mysql-bing

log_bin_trust_function_creators=TRUE

log-slave-updates  

sync_binlog=1  

auto_increment_offset=1  

auto_increment_increment=2  

slave-skip-errors=all

lower_case_table_names=1

character_set_server=utf8

skip-name-resolve

 

[mysql]

default-character-set=utf8

[mysqld_safe]

default-character-set=utf8

[mysql.server]

default-character-set=utf8

[client]

default-character-set=utf8

 

192.168.8.25配置：

vi/etc/my.cnf

增加如下內容：

symbolic-links=0

 

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

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

 

server_id  = 2

log-bin=mysql-bing

log_bin_trust_function_creators=TRUE

log-slave-updates

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

slave-skip-errors=all

lower_case_table_names=1

character_set_server  = utf8

skip-name-resolve

 

[mysql]

default-character-set=utf8

[mysqld_safe]

default-character-set=utf8

[mysql.server]

default-character-set  = utf8

[client]

default-character-set  = utf8

 

服務器1（192.168.8.24）和服務器2（192.168.8.25）重啟服務：

systemctlrestart mysqld

 

1.3.2     Sql配置

服務器1（192.168.8.24）操作：

mysql–u root –p

mysql>GRANT REPLICATION SLAVE ON *.* to 'repluser'@'192.168.8.25' identified by'123456';

mysql>flush privileges;

mysql>flush tables with read lock;            #防止進入新的數據

#查看bin文件和position，之后需要用到

mysql>SHOW MASTER STATUS;

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

|File             | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      106 |              |                  |

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

 

服務器2（192.168.8.25）操作：

mysql–u root –p

mysql>GRANT REPLICATION SLAVE ON *.* to 'repluser'@'192.168.8.24' identified by'123456';

mysql>flush privileges;

mysql>flush tables with read lock;            #防止進入新的數據

#查看bin文件和position，之后需要用到

mysql>SHOW MASTER STATUS;

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

|File             | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      350 |              |                  |

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

 

服務器1（192.168.8.24）操作：

mysql>CHANGE MASTER TO MASTER_HOST='192.168.8.25',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=350,MASTER_PORT=3306;

 

服務器2（192.168.8.25）操作：

mysql>CHANGE MASTER TOMASTER_HOST='192.168.8.24',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=106,MASTER_PORT=3306;

 

服務器1（192.168.8.24）和服務器2（192.168.8.25）分別操作：

mysql> unlock tables;

mysql> start slave;

mysql> show slave status\G;

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

主要關注以下 2 個參數：

...

...

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

...

...

 

1.3.3     測試

服務器1（192.168.8.24）新增加庫：

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

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

 

mysql>create database tom;

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

|tom                |

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

 

服務器2（192.168.8.25）查看自動新增加庫：

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

|tom                |

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

 

服務器2（192.168.8.25）將新增加庫刪除：

mysql>drop database tom;

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

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

 

服務器1（192.168.8.24）將自動刪除：

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

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

至此說明雙方同步成功

1.4  Mysql主從配置

(192.168.8.24和192.168.8.26)兩臺服務器操作

1.4.1     Mysql配置

192.168.8.24配置在上節已經完成。略過

192.168.8.26配置：

vi/etc/my.cnf

增加如下內容：

[mysqld]

symbolic-links=0

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

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

 

server_id  = 3

log-bin=mysql-bing

log_bin_trust_function_creators=TRUE

log-slave-updates

sync_binlog=1

auto_increment_offset=1

auto_increment_increment=2

slave-skip-errors=all

lower_case_table_names=1

character_set_server  = utf8

skip-name-resolve

 

[mysql]

default-character-set=utf8

[mysqld_safe]

default-character-set=utf8

[mysql.server]

default-character-set  = utf8

[client]

default-character-set  = utf8

 

重啟服務

systemctlrestart mysqld

 

1.4.2     Sql配置

服務器1（192.168.8.24）操作：

mysql–u root –p

mysql>GRANT REPLICATION SLAVE ON *.* to 'repluser'@'192.168.8.26' identified by'123456';

mysql>flush privileges;

mysql>flush tables with read lock;            #防止進入新的數據

#查看bin文件和position，之后需要用到

mysql>SHOW MASTER STATUS;

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

|File             | Position |Binlog_Do_DB | Binlog_Ignore_DB |

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

| mysql-bin.000004 |      106 |              |                  |

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

 

服務器2（192.168.8.26）操作：

mysql>CHANGE MASTER TO MASTER_HOST='192.168.8.24',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=106,MASTER_PORT=3306;

 

服務器1（192.168.8.24）操作：

mysql>unlock tables;

服務器2（192.168.8.26）操作：

mysql>start slave;

mysql>show slave status\G;

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

主要關注以下 2 個參數：

...

...

Slave_IO_Running:Yes

Slave_SQL_Running:Yes

...

...

 

1.4.3     測試

服務器1（192.168.8.24）新增加庫：

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

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

 

mysql>create database tom;

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

|tom                |

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

 

服務器2（192.168.8.26）查看自動新增加庫：

mysql>show databases;

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

|Database           |

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

|information_schema |

|mysql              |

|test               |

|tom                |

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

 

 

1.5  Mycat安裝配置

(192.168.8.22和192.168.8.23)兩臺服務器操作

1.5.1     Mycat安裝

安裝java

yuminstall java-1.8.0-openjdk

下載mycat：

http://dl.mycat.io/

 

解壓安裝包

tarzxvf Mycat-server-1.7.0-DEV-20170416134921-linux.tar.gz

mvMycat  /usr/local/mycat

 

配置環境變量

vi/etc/profile,在系統環境變量文件中增加 MYCAT_HOME=/usr/local/mycat

 

1.5.2     Mycat配置

cd/usr/local/mycat/conf

viserver.xml

修改如下配置

      <user name="root">

                <property  name="password">Test@123</property>

                <property  name="schemas">tom,test（有多個數據庫可以添加多個邏輯庫）

</property>

 

                <!-- 表級 DML 權限設置 -->

                <!--

                <privileges  check="false">

                        <schema  name="TESTDB" dml="0110" >

                                <table  name="tb01" dml="0000"></table>

                                <table name="tb02"  dml="1111"></table>

                        </schema>

                </privileges>

                 -->

        </user>

 

        <user name="user">

                <property  name="password">user</property>

                <property  name="schemas">tom,test</property>

                <property  name="readOnly">true</property>

        </user>

 

vischema.xml

只保留以下配置讀寫分離，其他刪除

<?xml  version="1.0"?>

<!DOCTYPE  mycat:schema SYSTEM "schema.dtd">

<mycat:schema  xmlns:mycat="http://io.mycat/">

 

        <schema name="tom"  checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn1">

        </schema>

        <schema name="test"  checkSQLschema="false" sqlMaxLimit="100"  dataNode="dn2">

        </schema>

        <dataNode name="dn1"  dataHost=" localhost1" database="tom" />

        <dataNode name="dn2"  dataHost=" localhost1" database="test" />

        <dataHost name="  localhost1" maxCon="10000" minCon="10"  balance="1"

                           writeType="0" dbType="mysql"  dbDriver="native" switchType="1"  slaveThreshold="100">

                <heartbeat>select  user()</heartbeat>

                <writeHost host="hostM1"  url="192.168.8.24:3306" user="root"

                                   password="Test@123">

                           <readHost  host="hostS2" url="192.168.8.26:3306"  user="root" password="Test@123" />

                </writeHost>

                <writeHost host="hostS1"  url="192.168.8.25:3306" user="root"

                                   password="Test@123"  />

        </dataHost>

</mycat:schema>

啟動mycat

/usr/local/mycat/bin/mycatstart

 

1.6  keepalived安裝配置

1.6.1         實驗環境

服務器角色

服務器IP

VIP1

192.168.8.200

LVS1

192.168.8.10

LVS2

192.168.8.11

 

1.6.2     安裝keepalived

yuminstall -y keepalived ipvsadm

1.6.3     配置keepalived

vim/etc/keepalived/keepalived.conf

LVS-DR-Master上，其配置如下（192.168.8.10操作）：

!  Configuration File for keepalived

 

global_defs  {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from  Alexandre.Cassen@firewall.loc

   smtp_server 192.168.200.1

   smtp_connect_timeout 30

   router_id LVS_DEVEL

}

 

vrrp_instance  VI_1 {

    state BACKUP

    interface ens160

    virtual_router_id 51

    priority 100

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 123456

    }

    virtual_ipaddress {

        192.168.8.100

        192.168.8.200

    }

}

 

virtual_server  192.168.8.100 80 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.12 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 80

        }

    }

 

    real_server 192.168.8.13 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 80

        }

    }

}

 

virtual_server  192.168.8.100 21 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.12 21 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 21

        }

    }

 

    real_server 192.168.8.13 21 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 21

        }

}

}

 

virtual_server 192.168.8.200 8066 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.22  8066 {

        weight 1

        TCP_CHECK {   

            connect_timeout  3

            nb_get_retry 3

             delay_before_retry 3

            connect_port 8066

        }

    }

 

    real_server 192.168.8.23  8066 {

        weight 1

        TCP_CHECK {

            connect_timeout  3

            nb_get_retry 3

             delay_before_retry 3

            connect_port  8066

        }

    }

}

LVS-DR-Backup上，其配置如下（192.168.8.11操作）：

!  Configuration File for keepalived

 

global_defs  {

   notification_email {

     acassen@firewall.loc

     failover@firewall.loc

     sysadmin@firewall.loc

   }

   notification_email_from  Alexandre.Cassen@firewall.loc

   smtp_server 192.168.200.1

   smtp_connect_timeout 30

   router_id LVS_DEVEL

}

 

vrrp_instance  VI_1 {

    state BACKUP

    interface ens160

    virtual_router_id 51

    priority 90

    advert_int 1

    authentication {

        auth_type PASS

        auth_pass 123456

    }

    virtual_ipaddress {

        192.168.8.100

        192.168.8.200

    }

}

 

virtual_server  192.168.8.100 80 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.12 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 80

        }

    }

 

    real_server 192.168.8.13 80 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 80

        }

    }

}

 

virtual_server  192.168.8.100 21 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.12 21 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 21

        }

    }

 

    real_server 192.168.8.13 21 {

        weight 1

        TCP_CHECK {

            connect_timeout 3

            nb_get_retry 3

            delay_before_retry 3

            connect_port 21

        }

}

}

 

virtual_server 192.168.8.200 8066 {

    delay_loop 6

    lb_algo rr

    lb_kind DR

    nat_mask 255.255.255.0

    persistence_timeout 50

    protocol TCP

 

    real_server 192.168.8.22  8066 {

        weight 1

        TCP_CHECK {   

            connect_timeout  3

            nb_get_retry 3

            delay_before_retry  3

            connect_port  8066

        }

    }

 

    real_server 192.168.8.23  8066 {

        weight 1

        TCP_CHECK {

            connect_timeout  3

            nb_get_retry 3

             delay_before_retry 3

            connect_port  8066

        }

    }

}

 

1.6.4         虛擬IP腳本

mycat服務器(192.168.8.22和192.168.8.23)操作

chmod+x /etc/rc.d/init.d/functions

vi/usr/local/bin/realserver.sh

#!/bin/bash

#description:  Config realserver

 

VIP=192.168.8.200

 

/etc/rc.d/init.d/functions

 

case  "$1" in

start)

       /sbin/ifconfig lo:0 $VIP netmask  255.255.255.255 broadcast $VIP

       /sbin/route add -host $VIP dev lo:0

       echo "1"  >/proc/sys/net/ipv4/conf/lo/arp_ignore

       echo "2"  >/proc/sys/net/ipv4/conf/lo/arp_announce

       echo "1"  >/proc/sys/net/ipv4/conf/all/arp_ignore

       echo "2"  >/proc/sys/net/ipv4/conf/all/arp_announce

       sysctl -p >/dev/null 2>&1

       echo "RealServer Start OK"

       ;;

stop)

       /sbin/ifconfig lo:0 down

       /sbin/route del $VIP >/dev/null  2>&1

       echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore

       echo "0"  >/proc/sys/net/ipv4/conf/lo/arp_announce

       echo "0"  >/proc/sys/net/ipv4/conf/all/arp_ignore

       echo "0"  >/proc/sys/net/ipv4/conf/all/arp_announce

       echo "RealServer Stoped"

       ;;

*)

       echo "Usage: $0  {start|stop}"

       exit 1

esac

 

exit  0

啟動腳本

/usr/local/bin/realserver.shstart

1.6.5         啟動keepalived

LVS-DR-Master（192.168.8.22）和LVS-DR-Backup（192.168.8.13）分別操作：

/etc/init.d/keepalivedstart

通過ipvsadm -L命令查看VIP是否能成功映射到后端服務。如果失敗了，可通過/var/log/messages日志定位keepalived啟動失敗的原因。

IPVirtual Server version 1.2.1 (size=4096)

ProtLocalAddress:Port Scheduler Flags

  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn

TCP  123.com:8066 rr

  -> 192.168.8.22:8066           Route   1     0          0        

  -> 192.168.8.23:8066           Route   1     0          0         

如果大家還有什么地方需要了解的可以在億速云官網找我們的專業技術工程師的，億速云技術工程師在行業內擁有十幾年的經驗了，所以會比小編回答的更加詳細專業。億速云官網鏈接www.5655pk.com


向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
南安市| 莒南县| 宁武县| 彝良县| 拉萨市| 霍山县| 香港| 岳西县| 遵化市| 体育| 英山县| 玛多县| 石林| 汾阳市| 临清市| 瓮安县| 贵定县| 嵊州市| 鄂托克旗| 淳安县| 黑龙江省| 泸溪县| 祁阳县| 天台县| 板桥市| 新津县| 阿城市| 商南县| 庄河市| 衡阳市| 建平县| 盈江县| 晋州市| 长沙县| 彝良县| 体育| 竹山县| 玉屏| 安吉县| 西林县| 萨嘎县|