msyql mgr單主模式 + keepalived + mycat MySQL MGR單主模式部署 創建MySQL存儲位置及日誌位置
1 2 sudo mkdir -p /data/mysql && sudo mkdir -p /data/logs/mysql sudo chown -R mysql:mysql /data/mysql && sudo chown -R mysql:mysql /data/logs/mysql
MySql5.7安装(三臺服務器進行同樣操作) 1 2 3 4 5 6 7 ##下载所需安装包 cd /opt sudo wget http://mirrors.ustc.edu.cn/mysql-ftp/Downloads/MySQL-5.7/mysql-server_5.7.34-1ubuntu18.04_amd64.deb-bundle.tar sudo tar -xvf mysql-server_5.7.34-1ubuntu18.04_amd64.deb-bundle.tar #解压安装包 sudo wget http://archive.ubuntu.com/ubuntu/pool/main/m/mecab/libmecab2_0.996-10build1_amd64.deb #下载依赖包
tdm-uat-db1上操作
在安装包所在的目录下执行（安装顺序不可更改）
1、安装mysql-common_5.7.34-1ubuntu18.04_amd64.deb 1 2 3 sudo apt install libtinfo5 -y sudo dpkg -i mysql-common_5.7.34-1ubuntu18.04_amd64.deb
1 sudo dpkg -i mysql-community-client_5.7.34-1ubuntu18.04_amd64.deb
3、安装mysql-client_5.7.34-1ubuntu18.04_amd64.deb 1 sudo dpkg -i mysql-client_5.7.34-1ubuntu18.04_amd64.deb
安装过程中会提示缺少依赖包libmecab2：libmecab2：
1 sudo dpkg -i libmecab2_0.996-10build1_amd64.deb
然后安装mysql-community-server_5.7.34-1ubuntu18.04_amd64.deb
1 sudo dpkg -i mysql-community-server_5.7.34-1ubuntu18.04_amd64.deb
安装过程中会要求在粉色的大页面上输入root密码，重複输入两次即可。
5、修改配置文件 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf # db1 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /data/mysql log-error = /data/logs/mysql/error.log sql_mode = user = mysql bind-address = 0.0.0.0 port = 3306 binlog_checksum = NONE log-bin = mysql-bin #hostname来设置id server-id = 1 #binlog-ignore-db = mysql,information_schema,performance_schema auto-increment-offset = 9 #auto-increment-increment = 2 #slave_parallel_type = 'logical_clock' #slave_parallel_workers = 8 #relay_log = relay-bin gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW master_info_repository = TABLE relay_log_info_repository = TABLE log-slave-updates = ON #组复制设置 #server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction_write_set_extraction=XXHASH64 #告知插件加入或创建组命名，UUID loose-group_replication_group_name="05153196-c2ab-43ae-b355-b9832eacf0b2" #server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 loose-group_replication_start_on_boot=off #告诉插件使用IP地址，端口24901用于接收组中其他成员转入连接 loose-group_replication_local_address="192.168.23.81:24901" #启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 loose-group_replication_group_seeds="192.168.23.81:24901,192.168.23.82:24901,192.168.23.83:24901" loose-group_replication_bootstrap_group=off init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 604800 wait_timeout = 604800 explicit_defaults_for_timestamp = true
修改MySQL存儲位置後需要修改/etc/apparmor.d/usr.sbin.mysqld
1 2 3 4 5 6 7 8 9 10 11 sudo vim /etc/apparmor.d/usr.sbin.mysqld # Allow data dir access /data/mysql/ r, /data/mysql/** rwk, # Allow log file access /data/logs/mysql/ r, /data/logs/mysql/** rwk, # 重啟apparmor sudo systemctl restart apparmor
复制原有的数据到新存储位置
1 2 3 4 sudo systemctl stop mysql sudo mv /var/lib/mysql /data/mysql sudo chown -R mysql:mysql /data/mysql && sudo chown -R mysql:mysql /data/logs/mysql sudo systemctl start mysql && sudo systemctl enable mysql
以上操作在DB2和DB3上面重复操作，配置文件中的server-id根據hostname來修改，如DB2中修改為server-id=2,DB3中修改為server-id=3, loose-group_replication_local_address 设置为自身服务器地址**;**修改MySQL配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 # db2 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /data/mysql log-error = /data/logs/mysql/error.log sql_mode = user = mysql bind-address = 0.0.0.0 port = 3306 binlog_checksum = NONE log-bin = mysql-bin server-id = 2 #binlog-ignore-db = mysql,information_schema,performance_schema auto-increment-offset = 9 #auto-increment-increment = 2 #slave_parallel_type = 'logical_clock' #slave_parallel_workers = 8 #relay_log = relay-bin gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW master_info_repository = TABLE relay_log_info_repository = TABLE log-slave-updates = ON #组复制设置 #server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction_write_set_extraction=XXHASH64 #告知插件加入或创建组命名，UUID loose-group_replication_group_name="05153196-c2ab-43ae-b355-b9832eacf0b2" #server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 loose-group_replication_start_on_boot=off #告诉插件使用IP地址，端口24901用于接收组中其他成员转入连接 loose-group_replication_local_address="192.168.23.82:24901" #启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 loose-group_replication_group_seeds="192.168.23.81:24901,192.168.23.82:24901,192.168.23.83:24901" loose-group_replication_bootstrap_group=off init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 604800 wait_timeout = 604800 explicit_defaults_for_timestamp = true
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 # db3 [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /data/mysql log-error = /data/logs/mysql/error.log sql_mode = user = mysql bind-address = 0.0.0.0 port = 3306 binlog_checksum = NONE log-bin = mysql-bin server-id = 3 #binlog-ignore-db = mysql,information_schema,performance_schema auto-increment-offset = 9 #auto-increment-increment = 2 #slave_parallel_type = 'logical_clock' #slave_parallel_workers = 8 #relay_log = relay-bin gtid_mode = ON enforce_gtid_consistency = ON binlog_format = ROW master_info_repository = TABLE relay_log_info_repository = TABLE log-slave-updates = ON #组复制设置 #server必须为每个事务收集写集合，并使用XXHASH64哈希算法将其编码为散列 transaction_write_set_extraction=XXHASH64 #告知插件加入或创建组命名，UUID loose-group_replication_group_name="05153196-c2ab-43ae-b355-b9832eacf0b2" #server启动时不自启组复制,为了避免每次启动自动引导具有相同名称的第二个组,所以设置为OFF。 loose-group_replication_start_on_boot=off #告诉插件使用IP地址，端口24901用于接收组中其他成员转入连接 loose-group_replication_local_address="192.168.23.83:24901" #启动组server，种子server，加入组应该连接这些的ip和端口；其他server要加入组得由组成员同意 loose-group_replication_group_seeds="192.168.23.81:24901,192.168.23.82:24901,192.168.23.83:24901" loose-group_replication_bootstrap_group=off init-connect = 'SET NAMES utf8mb4' character-set-server = utf8mb4 # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 default_storage_engine = InnoDB innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 64M innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 604800 wait_timeout = 604800 explicit_defaults_for_timestamp = true
db01設置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 # 登錄db01 mysql配置 mysql -u root -p mysql> set sql_log_bin=0; # 創建複製用戶 mysql> CREATE USER replication@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; mysql> FLUSH PRIVILEGES; mysql> CHANGE MASTER TO MASTER_USER='replication',MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'; mysql> install plugin group_replication soname 'group_replication.so'; # group_replication_bootstrap_group参数仅在第一台启动的MGR实例上开启，其他实例不要操作 mysql> set global group_replication_bootstrap_group=ON; mysql> START GROUP_REPLICATION; mysql> set global group_replication_bootstrap_group=OFF; msyql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+------------------+-------------+--------------+ | group_replication_applier | f2770e08-e2fd-11eb-a6f7-005056b9aeb1 | ub20-tdm-uat-db1 | 3306 | ONLINE | +---------------------------+--------------------------------------+------------------+-------------+--------------+
db02 和 db03設置
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 mysql -u root -p mysql> set sql_log_bin=0; # 創建複製用戶 mysql> CREATE USER replication@'%' IDENTIFIED WITH 'mysql_native_password' BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%'; mysql> FLUSH PRIVILEGES; mysql> CHANGE MASTER TO MASTER_USER='replication',MASTER_PASSWORD='password' FOR CHANNEL 'group_replication_recovery'; # 加載組複製插件 mysql> install plugin group_replication soname 'group_replication.so'; # 開啟複製 mysql> START GROUP_REPLICATION; # db02 和 db03 開啟複製後查看集群狀況 mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+------------------+-------------+--------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | +---------------------------+--------------------------------------+------------------+-------------+--------------+ | group_replication_applier | cc8d94a1-e2ff-11eb-bc1d-005056b99f7e | ub20-tdm-uat-db2 | 3306 | ONLINE | | group_replication_applier | cf067865-ea95-11eb-b761-005056b9d54d | ub20-tdm-uat-db3 | 3306 | ONLINE | | group_replication_applier | f2770e08-e2fd-11eb-a6f7-005056b9aeb1 | ub20-tdm-uat-db1 | 3306 | ONLINE | +---------------------------+--------------------------------------+------------------+-------------+--------------+
MyCat安裝(三台服務器進行同樣的操作) 1 2 3 4 5 6 7 # 安裝 JAVA 環境 sudo apt install openjdk-8-jre-headless -y # 下載 mycat 安裝包 cd /opt sudo wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz sudo tar -zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz sudo mv mycat /usr/local/
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 # 修改配置文件 sudo vim /usr/local/mycat/conf/schema.xml <?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 数据库配置，与server.xml中的数据库对应 --> <schema name="tdm_uat" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 分片配置 --> <dataNode name="dn1" dataHost="test1" database="tdm_uat" /> <!-- 物理数据库配置 --> <dataHost name="test1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> <heartbeat>select user();</heartbeat> <writeHost host="ub20-tdm-uat-db1" url="192.168.23.81:3306" user="root" password="GErAlGL59a"> <readHost host="ub20-tdm-uat-db2" url="192.168.23.82:3306" user="root" password="GErAlGL59a" /> <readHost host="ub20-tdm-uat-db3" url="192.168.23.83:3306" user="root" password="GErAlGL59a" /> </writeHost> <writeHost host="ub20-tdm-uat-db2" url="192.168.23.82:3306" user="root" password="GErAlGL59a"> <readHost host="ub20-tdm-uat-db1" url="192.168.23.81:3306" user="root" password="GErAlGL59a" /> <readHost host="ub20-tdm-uat-db3" url="192.168.23.83:3306" user="root" password="GErAlGL59a" /> </writeHost> <writeHost host="ub20-tdm-uat-db3" url="192.168.23.83:3306" user="root" password="GErAlGL59a"> <readHost host="ub20-tdm-uat-db1" url="192.168.23.81:3306" user="root" password="GErAlGL59a" /> <readHost host="ub20-tdm-uat-db2" url="192.168.23.82:3306" user="root" password="GErAlGL59a" /> </writeHost> </dataHost> </mycat:schema> sudo vim /usr/local/mycat/conf/server.xml # 將配置文件中內容修改成如下，配置mycat用戶和數據庫 <user name="root" defaultAccount="true"> <property name="password">password</property> <property name="schemas">tdm_uat</property> <property name="defaultSchema">tdm_uat</property> </user> <user name="user"> <property name="password">user</property> <property name="schemas">tdm_uat</property> <property name="readOnly">true</property> <property name="defaultSchema">tdm_uat</property> </user>
添加環境變量
1 2 3 4 5 6 7 # 添加環境變量 sudo vim /etc/profile export PATH=/usr/local/mycat/bin:$PATH # 保存退出後應用 source /etc/profile
添加到系統服務
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 sudo vim /lib/systemd/system/mycat.service # 添加以下內容 [Unit] Description=mycat project After=mycat.service [Service] Type=forking User=root Group=root ExecStart=/usr/local/mycat/bin/mycat start ExecReload=/usr/local/mycat/bin/mycat restart ExecStop=/usr/local/mycat/bin/mycat stop PrivateTmp=true [Install] WantedBy=multi-user.target # 保存逃出後啟動 sudo systemctl start mycat # 添加開機啟動 sudo systemctl enable mycat # 查看mycat端口是否正常 sudo netstat -lntup | grep 8066 # 測試連接mycat sudo mysql -u root -p -P8066 password: (輸入在配置文件中的密碼) # 查看目前目前使用的配置節點，test1=0表示使用第一組配置，也就是db1是寫節點，對應mgr群組的主節點，如發現使用的配置文件和mgr集群的主節點不一致，可先關掉所有讀節點，讓mycat識別到mgr主節點為配置時再開啟讀數據庫並加入集群。 sudo tail -f /usr/local/mycat/conf/dnindex.properties #update #Fri Sep 10 11:02:08 UTC 2021 localhost1=0 test1=0 jdbcDefaultHost=0
Keepalived安裝 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 # db01 sudo apt install -y keepalived # 修改配置文件 sudo vim /etc/keepalived/keepalived.conf global_defs { router_id web } vrrp_script chk_mycat { script "/home/wiz/scripts/check_mycat.sh" interval 2 weight -2 } vrrp_instance VI_1 { state MASTER interface ens160 virtual_router_id 1 priority 101 advert_int 1 virtual_ipaddress { 192.168.23.85/24 } track_script { chk_mycat } } # 編寫檢測mycat存活的腳本 sudo vim /home/wiz/scripts/check_mycat.sh #!/bin/bash count=`/opt/mycat/bin/mycat status |grep 'Mycat-server is running' | wc -l` time=$(date "+%Y-%m-%d %H:%M:%S") if [ $count = 0 ]; then echo "$time : count=$count, mycat is not running..." >> /var/log/keepalived_check.log exit 1 # 返回1说明脚本非正常执行,mycat不在运行中 else echo "$time : count=$count, mycat is running..." >> /var/log/keepalived_check.log exit 0 # 返回0说明脚本正常执行,mycat正在运行中 fi sudo chmod +x /home/wiz/scripts/check_mycat.sh #啟動 keepalived 並加入開機啟動 sudo systemctl restart keepalived && sudo systemctl enable keepalived # 查看是否綁定VIP ip a
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 # db02 sudo apt install -y keepalived # 修改配置文件 global_defs { router_id web } vrrp_script chk_mycat { script "sudo /home/wiz/scripts/check_mycat.sh" interval 2 weight -2 } vrrp_instance VI_1 { state BACKUP interface ens160 virtual_router_id 1 priority 100 advert_int 1 virtual_ipaddress { 192.168.23.85/24 } track_script { chk_mycat } } # 編寫檢測mycat存活的腳本 sudo vim /home/wiz/scripts/check_mycat.sh #!/bin/bash count=`/opt/mycat/bin/mycat status |grep 'Mycat-server is running' | wc -l` time=$(date "+%Y-%m-%d %H:%M:%S") if [ $count = 0 ]; then echo "$time : count=$count, mycat is not running..." >> /var/log/keepalived_check.log exit 1 # 返回1说明脚本非正常执行,mycat不在运行中 else echo "$time : count=$count, mycat is running..." >> /var/log/keepalived_check.log exit 0 # 返回0说明脚本正常执行,mycat正在运行中 fi sudo chmod +x /home/wiz/scripts/check_mycat.sh #啟動 keepalived 並加入開機啟動 sudo systemctl restart keepalived && sudo systemctl enable keepalive
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 # db03 sudo apt install -y keepalived # 修改配置文件 global_defs { router_id web } vrrp_script chk_mycat { script "sudo /home/wiz/scripts/check_mycat.sh" interval 2 weight -2 } vrrp_instance VI_1 { state BACKUP interface ens160 virtual_router_id 1 priority 99 advert_int 1 virtual_ipaddress { 192.168.23.85/24 } track_script { chk_mycat } } # 編寫檢測mycat存活的腳本 sudo vim /home/wiz/scripts/check_mycat.sh #!/bin/bash count=`/opt/mycat/bin/mycat status |grep 'Mycat-server is running' | wc -l` time=$(date "+%Y-%m-%d %H:%M:%S") if [ $count = 0 ]; then echo "$time : count=$count, mycat is not running..." >> /var/log/keepalived_check.log exit 1 # 返回1说明脚本非正常执行,mycat不在运行中 else echo "$time : count=$count, mycat is running..." >> /var/log/keepalived_check.log exit 0 # 返回0说明脚本正常执行,mycat正在运行中 fi sudo chmod +x /home/wiz/scripts/check_mycat.sh #啟動 keepalived 並加入開機啟動 sudo systemctl restart keepalived && sudo systemctl enable keepalive
