1、使用mysqldump備份資料庫并通過備份及二進制日志還原資料(備份完後再寫入資料,然後再删庫)
[[email protected] ~]# mysql < hellodb_innodb.sql #導入hellodb表用于測試
[[email protected] ~]# mysqldump -A --single-transaction -F --master-data=2 | gzip > /data/all_back_`date +%F-%T`.sql.gz #-A做全備份 -F重新整理binlog位置 --master-data=2注釋chang-master-to語句
MariaDB [hellodb]> show master status; #檢視二進制日志位置
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 | 7655 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
MariaDB [hellodb]> select * from students; #檢視表中的資料
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test',11); #插入新資料
Query OK, 1 row affected (0.00 sec)
[[email protected] data]# gzip -d all_back_2019-06-23-17\:55\:42.sql.gz
[[email protected] data]# ls
all_back_2019-06-23-17:55:42.sql hellodb_innodb.sql log mysql
[[email protected] data]# vim all_back_2019-06-23-17:55:42.sql #檢視二進制日志位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=245;
MariaDB [(none)]> set sql_log_bin=off; #臨時關閉記錄二進制日志
Query OK, 0 rows affected (0.00 sec)
[[email protected] log]# mysqlbinlog mysql-bin.00000{5,6} > /root/incr.sql #根據之前備份檔案中的二進制日志位置将備份中沒有的二進制日志導出
[[email protected] ~]# mysql < all_back_2019-06-23-17:55:42.sql #導入
[[email protected] ~]# mysql < incr.sql
2、使用xtrabackup備份資料并還原
[[email protected] ~]# rpm -ql percona-xtrabackup-24.x86_64 #安裝xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.12
/usr/share/doc/percona-xtrabackup-24-2.4.12/COPYING
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz
[[email protected] ~]# xtrabackup --backup --target-dir=./backup/ #備份
[[email protected] ~]# scp -r ./backup/* 192.168.79.27:/root/backup #将備份檔案複制到遠端伺服器
[[email protected] ~]# xtrabackup --prepare --target-dir=/root/backup/ #準備備份檔案
[[email protected] ~]# xtrabackup --copy-back --target-dir=./backup #将準備好的備份内容複制到資料庫目錄下
[[email protected] mysql]# chown -R mysql.mysql /data/ #更改資料庫目錄權限
[[email protected] ~]# systemctl start mariadb #重新開機服務
3、MySQL資料備份企業實戰(shell或python腳本實作)
使用xtrabackup以每周為一個備份周期做備份(資料庫+二進制日志,備份至本地/data/backup)
提示: 周一某個時間點做一次完全備份,周二、三、四、五、六、日增量
備份存儲目錄"/data/backup/2018/52/1/備份資料" 目錄解釋"/data/backup/年/本年度的第幾周/本周的第幾天/資料" 一年52周一周7天
[[email protected] ~]# cat backup.sh
#!/bin/bash
#
export week=`date +%W`
export user=''
export password=''
export day=`date +%w`
let yesterday=$day-1
export year=`date +%Y`
export backupdir=/data/backup/$year/$week/$day
export basedir=/data/backup/$year/$week/$yesterday
export binlogdir=/data
#
mkdir -p $backupdir
echo 'backupdir created successfully'
if [ $day==1 ];then
xtrabackup --user=$user --password=$password --backup --target-dir=$backupdir &> /dev/null
else
xtrabackup --backup --target-dir=$backupdir --incremental-basedir=$basedir &> /dev/null
fi
cp -a $binlogdir/mysql-bin.* $backupdir
unset week
unset user
unset password
unset day
unset yesterday
unset year
unset backupdir
unset basedir
[[email protected] data]# crontab -e
0 1 * * * bash /root/backup.sh
4、描述MySQL複制工作原理并實作主從,主主,主從級聯及半同步複制
- Mysql的複制：
- 每個節點都有相同的資料集
- 向外擴充
- 二進制日志
- 單向
- 複制的作用：
- 資料分布
- 負載均衡讀
- 備份
- 高可用和故障切換
- Mysql更新測試
MySQL複制原理
- 主從複制：異步複制，主從資料不一緻比較常見
- 主節點:
- dump Thread：為每個slave的IO thread啟動一個dump線程，用于向其發送binary log event
- 從節點：
- IO thread：向master請求二進制日志事件，并儲存于中繼日志中
- SQL thread：從中繼日志中讀取日志事件，在本地完成重放
- 複制相關的檔案：
- master.info：用于儲存slave連接配接至master時的相關資訊，例如賬号、密碼、伺服器位址等
- relay-log.info：儲存目前slave節點上已經複制的目前二進制日志和本地relay-log之間的對應關系
- 複制時二進制日志格式推薦使用ROW
- 主節點:
- 複制架構：
- master/slave，master/master，環狀複制
- 一主多從
- 從伺服器還可以再有從伺服器
- 一從多主：适用于多個不用資料庫
多種複制架構
[[email protected] log]# vim /etc/my.cnf #更改master節點配置檔案
[mysqld]
server-id = 1 #設定節點編号為1
MariaDB [hellodb]> grant replication slave on *.* to 'repluser'@'192.168.79.%' identified by 'replpass'; #建立repluser使用者，密碼為replpass，并允許在192.168.79.0網段的主機登入并對所有表做複制操作
Query OK, 0 rows affected (0.00 sec)
[[email protected] log]# vim /etc/my.cnf #更改slave節點配置檔案
[mysqld]
server-id = 2
read_only=ON
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245; #指定二進制日志檔案位置，即從什麼地方開始複制，指定複制資料使用的賬号密碼和目标主機
MariaDB [(none)]> start slave; #開啟slave線程
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status\G #檢視slave狀态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.79.37
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 8073
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 8357
Relay_Master_Log_File: mysql-bin.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: 8073
Relay_Log_Space: 8653
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: 1
1 row in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test',11); #添加新資料進行測試
Query OK, 1 row affected (0.00 sec)
[[email protected] mysql]# cat mariadb-relay-bin.index #relaylog索引
./mariadb-relay-bin.000002
./mariadb-relay-bin.000003
[[email protected] mysql]# cat relay-log.info #記錄二進制日志和中繼日志之間的位置關系
./mariadb-relay-bin.000003
8599
mysql-bin.000001
8315
#級聯複制，在主從複制的基礎上再增加一個節點從從節點同步資料
[[email protected] log]# vim /etc/my.cnf
[mysqld]
log_bin
log_slave_updates #slavelog實時更新
read_only=on #資料庫隻讀
server_id = 3
[[email protected] log]# systemctl restart mariadb
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.27', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
MariaDB [(none)]> start salve;
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.79.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 8956
Relay_Log_File: mariadb-relay-bin.000003
Relay_Log_Pos: 9241
Relay_Master_Log_File: master-bin.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: 8956
Relay_Log_Space: 9537
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: 2
1 row in set (0.00 sec)
#主主複制
#在兩台伺服器上各自建立一個具有複制權限的使用者；讓兩個資料庫互為主從的關系
[[email protected] log]# vim /etc/my.cnf
server-id = 1
auto-increment-offset = 1
auto-increment-increment = 2
[[email protected] mysql]# vim /etc/my.cnf
auto-increment-increment = 2
auto-increment-offset = 2
log-basename=master
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.27', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37', MASTER_USER='repluser', MASTER_PASSWORD='replpass', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test13',11);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | test | 11 | F | NULL | NULL |
| 27 | test12 | 111 | F | NULL | NULL |
| 29 | test13 | 11 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
28 rows in set (0.00 sec)
MariaDB [hellodb]> insert into students (name,age) values ('test14',11);
Query OK, 1 row affected (0.00 sec)
MariaDB [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 26 | test | 11 | F | NULL | NULL |
| 27 | test12 | 111 | F | NULL | NULL |
| 29 | test13 | 11 | F | NULL | NULL |
| 30 | test14 | 11 | F | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
29 rows in set (0.00 sec)
#半同步複制：需要通過插件的方式實作
MariaDB [hellodb]> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SET GLOBAL rpl_semi_sync_master_enabled=1;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SET GLOBAL rpl_semi_sync_master_timeout = 1000;
Query OK, 0 rows affected (0.00 sec)
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%semi%';
+------------------------------------+-------+
| Variable_name | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled | ON |
| rpl_semi_sync_master_timeout | 1000 |
| rpl_semi_sync_master_trace_level | 32 |
| rpl_semi_sync_master_wait_no_slave | ON |
+------------------------------------+-------+
4 rows in set (0.00 sec)
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE '%semi%';
+--------------------------------------------+-------+
| Variable_name | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients | 0 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+--------------------------------------------+-------+
14 rows in set (0.01 sec)
5、描述MySQL Proxy原理并實作讀寫分離
- ProxySQL:MySQl中間件，有官方版和percona兩個版本，percona版是基于官方版基礎上修改，輕量級但性能優異，具有中間件所需的絕大多數功能，包括：
- 多種方式的讀/寫分離
- 定制基于使用者、基于schema、基于語句的規則對SQL語句進行路由
- 緩存查詢結果
- 後端節點監控
-
基于YUM倉庫安裝
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
基于RPM下載下傳安裝：https://github.com/sysown/proxysql/releases
[[email protected] ~]# rpm -ql proxysql
/etc/init.d/proxysql
/etc/proxysql.cnf
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl
MySQL [(none)]> select * from mysql_servers;
Empty set (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.79.37',3306); #設定mysql_servers主機資訊
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.79.27',3306);
Query OK, 1 row affected (0.00 sec)
MySQL [(none)]> load mysql servers to runtime; #加載配置
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql servers to disk; #存盤
Query OK, 0 rows affected (0.01 sec)
MySQL [(none)]> select * from mysql_servers;
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10 | 192.168.79.37 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | 192.168.79.27 | 3306 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+---------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
master
MariaDB [(none)]> grant replication client on *.* to [email protected]'192.168.79.%' identified by 'sigeling'; #建立監控使用者
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show variables like 'mysql-monitor%';
+-----------------------------------------------------+----------+
| Variable_name | Value |
+-----------------------------------------------------+----------+
| mysql-monitor_enabled | true |
| mysql-monitor_connect_timeout | 600 |
| mysql-monitor_ping_max_failures | 3 |
| mysql-monitor_ping_timeout | 1000 |
| mysql-monitor_read_only_max_timeout_count | 3 |
| mysql-monitor_replication_lag_interval | 10000 |
| mysql-monitor_replication_lag_timeout | 1000 |
| mysql-monitor_groupreplication_healthcheck_interval | 5000 |
| mysql-monitor_groupreplication_healthcheck_timeout | 800 |
| mysql-monitor_replication_lag_use_percona_heartbeat | |
| mysql-monitor_query_interval | 60000 |
| mysql-monitor_query_timeout | 100 |
| mysql-monitor_slave_lag_when_null | 60 |
| mysql-monitor_wait_timeout | true |
| mysql-monitor_writer_is_also_reader | true |
| mysql-monitor_username | monitor |
| mysql-monitor_password | sigeling |
| mysql-monitor_history | 600000 |
| mysql-monitor_connect_interval | 60000 |
| mysql-monitor_ping_interval | 10000 |
| mysql-monitor_read_only_interval | 1500 |
| mysql-monitor_read_only_timeout | 500 |
+-----------------------------------------------------+----------+
22 rows in set (0.00 sec)
MySQL [(none)]> load mysql variables to runtime;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> save mysql variables to disk;
Query OK, 97 rows affected (0.01 sec)
監控子產品的名額儲存在monitor庫的log表中
MySQL [main]> select * from mysql_server_connect_log; #檢視日志看連接配接情況是否正常
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| hostname | port | time_start_us | connect_success_time_us | connect_error |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
| 192.168.79.27 | 3306 | 1561377241510143 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377242522008 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377301511225 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377302534131 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377361511760 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377362408165 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377421512835 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377422485322 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377481513867 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377482309172 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377541514951 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.27 | 3306 | 1561377542415633 | 0 | Access denied for user 'monitor'@'192.168.79.17' (using password: YES) |
| 192.168.79.37 | 3306 | 1561377550327921 | 1484 | NULL |
| 192.168.79.27 | 3306 | 1561377551092608 | 2051 | NULL |
| 192.168.79.27 | 3306 | 1561377610328914 | 1467 | NULL |
| 192.168.79.37 | 3306 | 1561377611285835 | 2024 | NULL |
| 192.168.79.37 | 3306 | 1561377670329384 | 1941 | NULL |
| 192.168.79.27 | 3306 | 1561377671022958 | 1409 | NULL |
| 192.168.79.37 | 3306 | 1561377730329710 | 793 | NULL |
| 192.168.79.27 | 3306 | 1561377731376983 | 1674 | NULL |
| 192.168.79.27 | 3306 | 1561377790329952 | 2625 | NULL |
| 192.168.79.37 | 3306 | 1561377791464267 | 1471 | NULL |
+---------------+------+------------------+-------------------------+------------------------------------------------------------------------+
22 rows in set (0.00 sec)
MySQL [main]> select * from mysql_replication_hostgroups;
Empty set (0.00 sec)
MySQL [main]> insert into mysql_replication_hostgroups values(10,20,"test"); #設定分組資訊，指定寫組的id為10，讀組的id為20
Query OK, 1 row affected (0.00 sec)
MySQL [main]> load mysql servers to runtime;
MySQL [main]> save mysql servers to disk;
Query OK, 0 rows affected (0.01 sec)
Monitor子產品監控後端的read_only值，按照read_only的值将節點自動移動到讀/寫組
MySQL [main]> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+---------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+---------------+------+--------+--------+
| 10 | 192.168.79.37 | 3306 | ONLINE | 1 |
| 20 | 192.168.79.27 | 3306 | ONLINE | 1 |
+--------------+---------------+------+--------+--------+
2 rows in set (0.00 sec)
MariaDB [(none)]> grant all on *.* to [email protected]'192.168.79.%' identified by 'sigeling'; #建立通路使用者
Query OK, 0 rows affected (0.00 sec)
MySQL [main]> insert into mysql_users(username,password,default_hostgroup) values('sqluser','sigeling',10); #配置proxysql，将使用者sqluser添加到mysql_users表中，default_hostgroup預設組設定為寫組10，當讀寫分離的路由規則不符合時，會通路預設組的資料庫
Query OK, 1 row affected (0.00 sec)
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select @@server_id' #使用sqluser測試是否能酷郵到預設的10寫組實作資料讀寫
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'create database testdb'
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'use testdb;create table t(id int)'
MariaDB [(none)]> desc testdb.t;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
MySQL [main]> insert into mysql_query_rules #配置路由規則，實作讀寫分離，插入路由規則而，将select語句分離到20的都組，select語句中有一個特殊語句select ... for update會申請寫鎖，應當路由到10的寫組
-> (rule_id,active,match_digest,destination_hostgroup,apply)VALUES
-> (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
Query OK, 2 rows affected (0.00 sec)
注意：因ProxySQL根據rule_id順序進行規則比對，select ... for update規則的rule_id必須要小于普通的select規則的rule_id
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select @@server_id' +-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'start transaction;select @@server_id;commit;select @@server_id;'
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'insert testdb.t values(1)'
[[email protected] ~]# mysql -usqluser -psigeling -P6033 -h127.0.0.1 -e 'select id from testdb.t'
MySQL [main]> select * from stats_mysql_query_digest order by sum_time desc; #查詢stats庫中的stats_mysql_query_digest表
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 10 | testdb | sqluser | 0x4E6025FB1E51A2E5 | create table t(id int) | 1 | 1561378573 | 1561378573 | 5327 | 5327 | 5327 |
| 10 | information_schema | sqluser | 0xDA65260DF35B8D13 | select @@server_id | 4 | 1561378489 | 1561379221 | 3979 | 565 | 1841 |
| 20 | information_schema | sqluser | 0xDA65260DF35B8D13 | select @@server_id | 2 | 1561379136 | 1561379221 | 2295 | 381 | 1914 |
| 10 | information_schema | sqluser | 0xA3A8AA9A5EC1ED82 | start trancsaction | 2 | 1561379207 | 1561379213 | 2205 | 857 | 1348 |
| 10 | information_schema | sqluser | 0x8CC3B08B69E5ED49 | insert testdb.t values(?) | 1 | 1561379265 | 1561379265 | 1903 | 1903 | 1903 |
| 10 | information_schema | sqluser | 0x326F4F2B935EC266 | start transaction | 1 | 1561379221 | 1561379221 | 1506 | 1506 | 1506 |
| 10 | information_schema | sqluser | 0x620B328FE9D6D71A | SELECT DATABASE() | 2 | 1561378565 | 1561378573 | 1377 | 461 | 916 |
| 20 | information_schema | sqluser | 0x7D040729C574DF03 | select id from testdb.t | 1 | 1561379279 | 1561379279 | 1140 | 1140 | 1140 |
| 10 | information_schema | sqluser | 0x9461F19B72760588 | create database testdb | 1 | 1561378528 | 1561378528 | 628 | 628 | 628 |
| 10 | testdb | sqluser | 0x02A595FB8F1DFC7C | create table(id int) | 1 | 1561378565 | 1561378565 | 599 | 599 | 599 |
| 10 | information_schema | sqluser | 0x4CD0FEC20B21FB99 | create databases testdb | 1 | 1561378523 | 1561378523 | 567 | 567 | 567 |
| 10 | information_schema | sqluser | 0xDB3A841EF5443C35 | commit | 1 | 1561379221 | 1561379221 | 378 | 378 | 378 |
| 10 | information_schema | sqluser | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 13 | 1561378489 | 1561379279 | 0 | 0 | 0 |
+-----------+--------------------+----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
13 rows in set (0.00 sec)
MySQL [main]> SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
+----+----------+------------+----------------------------------+
| hg | sum_time | count_star | digest_text |
+----+----------+------------+----------------------------------+
| 10 | 5327 | 1 | create table t(id int) |
| 10 | 3979 | 4 | select @@server_id |
| 20 | 2295 | 2 | select @@server_id |
| 10 | 2205 | 2 | start trancsaction |
| 10 | 1903 | 1 | insert testdb.t values(?) |
| 10 | 1506 | 1 | start transaction |
| 10 | 1377 | 2 | SELECT DATABASE() |
| 20 | 1140 | 1 | select id from testdb.t |
| 10 | 628 | 1 | create database testdb |
| 10 | 599 | 1 | create table(id int) |
| 10 | 567 | 1 | create databases testdb |
| 10 | 378 | 1 | commit |
| 10 | 0 | 13 | select @@version_comment limit ? |
+----+----------+------------+----------------------------------+
6、使用MHA及galera實作MySQL的高可用性
- MHA：Master High Availability
- 對主節點進行監控，可實作自動故障轉移至其他從節點；通過提升某一從節點為新的主節點，基于主從複制實作，還需要用戶端配合實作，目前MHA主要支援一主多從的架構，要搭建MHA，要求一個複制叢集中最少有三台資料庫伺服器，一主二從，即一台充當master，一台充當備用master，另外一台充當從庫
- 工作原理：
- 從當機崩潰的master儲存二進制日志（binlog events）
- 識别含有最新更新的slave
- 應用差異的中繼日志（relay log）到其他的slave
- 應用從master儲存的二進制日志（binlog event）
- 提升一個slave為新的master
- 使其他的slave連接配接新的master進行複制
- 軟體構成：
- Manager工具包：
- masterha_check_ssh：檢查MHA的SSH配置狀況
- masterha_check_repl：檢查資料庫複制狀況
- masterha_manager：啟動MHA
- masterha_check_status：檢測目前MHA運作狀态
- masterha_master_monitor：檢測master是否當機
- masterha_master_swith：故障轉移（自動或手動）
- masterha_conf_host：添加或删除配置的server資訊
- Node工具包：通常由MHA manager的腳本出發，無需人為操作
- save_binary_logs：儲存和複制master的二進制日志
- apply_diff_relay_logs：識别差異的中繼日志事件并将其差異的事件應用于其他的slave
- filter_mysqlbinlog：去除不必要的ROLLBACK事件（MHA已不再使用此工具）
- purge_relay_logs：清除中繼日志（不會阻塞SQL線程）
- 自定義擴充：
- secondary_check_script：通過多條網絡路由檢測master的可用性
- master_ip_failover_scipt：更新Application使用的masterip
- shutdown_script：強制關閉master節點
- report_script：發送報告
- init_conf_load_script：加載初始配置參數
- master_ip_online_change_script：更新master節點ip位址
- Manager工具包：
- 注意：為了盡可能減少主庫硬體損壞當機造成的資料丢失，是以在配置MHA的同時建議配置成MySQL5.5的半同步複制
- 配置檔案：
- global配置，為各application提供預設配置
-
application配置：為每個主從複制叢集提供配置
mha叢集架構
mha實作原理 mariadb備份 mariadb備份
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm #各個節點都需要安裝
yum -y install mha4mysql-manager-0.56-0.el6.noarch.rpm #僅管理節點需要安裝
[[email protected] ~]# vim /etc/mastermha/app1.cnf #配置mastermha，添加mysql主機以及配置mysql資料目錄等屬性
[server default]
user=mhauser
password=sigeling
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
master_binlog_dir=/data/log/
ssh_user=root
repl_user=repluser
repl_password=sigeling
ping_interval=1
[server1]
hostname=192.168.79.27
candidate_master=1
[server2]
hostname=192.168.79.37
candidate_master=1
[server3]
hostname=192.168.79.47
[[email protected] ~]# vim /etc/my.cnf #配置master節點
skip-name-resolve=1
log-bin=/data/log/mysql-bin
server-id = 1
MariaDB [(none)]> show master logs
-> ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 30358 |
| mysql-bin.000002 | 1038814 |
| mysql-bin.000003 | 264 |
| mysql-bin.000004 | 245 |
+------------------+-----------+
4 rows in set (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to [email protected]'192.168.79.%' identified by 'sigeling'; #建立擁有複制權限的使用者
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant all on *.* to [email protected]'192.168.79.%' identified by 'sigeling'; #建立mhauser使用者用于遠端對master庫進行操作
Query OK, 0 rows affected (0.00 sec)
[[email protected] ~]# vim /etc/my.cnf #配置slave節點
log-bin=/data/log/mysql-bin
server-id = 2
read_only=ON
relay-log-purge=0
skip-name-resolve=1
MariaDB [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.79.37',MASTER_USER='repluser', MASTER_PASSWORD='sigeling', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=245; #配置主從複制
Query OK, 0 rows affected (0.01 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show slave status;
Empty set (0.00 sec)
ssh-keygen #各節點實作ssh免密登入，各節點使用相同的密鑰對
ssh-copy-id 192.168.79.17
ssh-copy-id 192.168.79.27
ssh-copy-id 192.168.79.37
[[email protected] ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf #檢測各節點ssh通信狀态
Mon Jun 24 10:13:28 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:13:28 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:13:28 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:13:28 2019 - [info] Starting SSH connection tests..
Mon Jun 24 10:13:29 2019 - [debug]
Mon Jun 24 10:13:28 2019 - [debug] Connecting via SSH from [email protected](192.168.79.27:22) to [email protected](192.168.79.37:22)..
Mon Jun 24 10:13:28 2019 - [debug] ok.
Mon Jun 24 10:13:28 2019 - [debug] Connecting via SSH from [email protected](192.168.79.27:22) to [email protected](192.168.79.47:22)..
Mon Jun 24 10:13:29 2019 - [debug] ok.
Mon Jun 24 10:13:30 2019 - [debug]
Mon Jun 24 10:13:29 2019 - [debug] Connecting via SSH from [email protected](192.168.79.37:22) to [email protected](192.168.79.27:22)..
Mon Jun 24 10:13:29 2019 - [debug] ok.
Mon Jun 24 10:13:29 2019 - [debug] Connecting via SSH from [email protected](192.168.79.37:22) to [email protected](192.168.79.47:22)..
Mon Jun 24 10:13:29 2019 - [debug] ok.
Mon Jun 24 10:13:31 2019 - [debug]
Mon Jun 24 10:13:29 2019 - [debug] Connecting via SSH from [email protected](192.168.79.47:22) to [email protected](192.168.79.27:22)..
Mon Jun 24 10:13:29 2019 - [debug] ok.
Mon Jun 24 10:13:29 2019 - [debug] Connecting via SSH from [email protected](192.168.79.47:22) to [email protected](192.168.79.37:22)..
Mon Jun 24 10:13:30 2019 - [debug] ok.
Mon Jun 24 10:13:31 2019 - [info] All SSH connection tests passed successfully.
[[email protected] ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf #檢測個節點複制功能是否正常
Mon Jun 24 10:16:41 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:16:41 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:16:41 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:16:41 2019 - [info] MHA::MasterMonitor version 0.56.
Mon Jun 24 10:16:42 2019 - [info] GTID failover mode = 0
Mon Jun 24 10:16:42 2019 - [info] Dead Servers:
Mon Jun 24 10:16:42 2019 - [info] Alive Servers:
Mon Jun 24 10:16:42 2019 - [info] 192.168.79.27(192.168.79.27:3306)
Mon Jun 24 10:16:42 2019 - [info] 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] 192.168.79.47(192.168.79.47:3306)
Mon Jun 24 10:16:42 2019 - [info] Alive Slaves:
Mon Jun 24 10:16:42 2019 - [info] 192.168.79.27(192.168.79.27:3306) Version=5.5.60-MariaDB (oldest major version between slaves) log-bin:enabled
Mon Jun 24 10:16:42 2019 - [info] Replicating from 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] Primary candidate for the new Master (candidate_master is set)
Mon Jun 24 10:16:42 2019 - [info] 192.168.79.47(192.168.79.47:3306) Version=5.5.60-MariaDB (oldest major version between slaves) log-bin:enabled
Mon Jun 24 10:16:42 2019 - [info] Replicating from 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] Current Alive Master: 192.168.79.37(192.168.79.37:3306)
Mon Jun 24 10:16:42 2019 - [info] Checking slave configurations..
Mon Jun 24 10:16:42 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.79.27(192.168.79.27:3306).
Mon Jun 24 10:16:42 2019 - [warning] relay_log_purge=0 is not set on slave 192.168.79.47(192.168.79.47:3306).
Mon Jun 24 10:16:42 2019 - [info] Checking replication filtering settings..
Mon Jun 24 10:16:42 2019 - [info] binlog_do_db= , binlog_ignore_db=
Mon Jun 24 10:16:42 2019 - [info] Replication filtering check ok.
Mon Jun 24 10:16:42 2019 - [info] GTID (with auto-pos) is not supported
Mon Jun 24 10:16:42 2019 - [info] Starting SSH connection tests..
Mon Jun 24 10:16:45 2019 - [info] All SSH connection tests passed successfully.
Mon Jun 24 10:16:45 2019 - [info] Checking MHA Node version..
Mon Jun 24 10:16:45 2019 - [info] Version check ok.
Mon Jun 24 10:16:45 2019 - [info] Checking SSH publickey authentication settings on the current master..
Mon Jun 24 10:16:45 2019 - [info] HealthCheck: SSH to 192.168.79.37 is reachable.
Mon Jun 24 10:16:46 2019 - [info] Master MHA Node version is 0.56.
Mon Jun 24 10:16:46 2019 - [info] Checking recovery script configurations on 192.168.79.37(192.168.79.37:3306)..
Mon Jun 24 10:16:46 2019 - [info] Executing command: save_binary_logs --command=test --start_pos=4 --binlog_dir=/data/log/ --output_file=/data/mastermha/app1//save_binary_logs_test --manager_version=0.56 --start_file=mysql-bin.000005
Mon Jun 24 10:16:46 2019 - [info] Connecting to [email protected](192.168.79.37:22)..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/log/, up to mysql-bin.000005
Mon Jun 24 10:16:46 2019 - [info] Binlog setting check done.
Mon Jun 24 10:16:46 2019 - [info] Checking SSH publickey authentication and checking recovery script configurations on all alive slave servers..
Mon Jun 24 10:16:46 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.79.27 --slave_ip=192.168.79.27 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.60-MariaDB --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Mon Jun 24 10:16:46 2019 - [info] Connecting to [email protected](192.168.79.27:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mariadb-relay-bin.000003
Temporary relay log file is /data/mysql/mariadb-relay-bin.000003
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 24 10:16:46 2019 - [info] Executing command : apply_diff_relay_logs --command=test --slave_user='mhauser' --slave_host=192.168.79.47 --slave_ip=192.168.79.47 --slave_port=3306 --workdir=/data/mastermha/app1/ --target_version=5.5.60-MariaDB --manager_version=0.56 --relay_log_info=/data/mysql/relay-log.info --relay_dir=/data/mysql/ --slave_pass=xxx
Mon Jun 24 10:16:46 2019 - [info] Connecting to [email protected](192.168.79.47:22)..
Checking slave recovery environment settings..
Opening /data/mysql/relay-log.info ... ok.
Relay log found at /data/mysql, up to mariadb-relay-bin.000003
Temporary relay log file is /data/mysql/mariadb-relay-bin.000003
Testing mysql connection and privileges.. done.
Testing mysqlbinlog output.. done.
Cleaning up test file(s).. done.
Mon Jun 24 10:16:46 2019 - [info] Slaves settings check done.
Mon Jun 24 10:16:46 2019 - [info]
192.168.79.37(192.168.79.37:3306) (current master)
+--192.168.79.27(192.168.79.27:3306)
+--192.168.79.47(192.168.79.47:3306)
Mon Jun 24 10:16:46 2019 - [info] Checking replication health on 192.168.79.27..
Mon Jun 24 10:16:46 2019 - [info] ok.
Mon Jun 24 10:16:46 2019 - [info] Checking replication health on 192.168.79.47..
Mon Jun 24 10:16:46 2019 - [info] ok.
Mon Jun 24 10:16:46 2019 - [warning] master_ip_failover_script is not defined.
Mon Jun 24 10:16:46 2019 - [warning] shutdown_script is not defined.
Mon Jun 24 10:16:46 2019 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[[email protected] ~]# masterha_manager --conf=/etc/mastermha/app1.cnf #類似于守護程序，當master節點挂了之後會更改slave節點的配置，将slave節點自動提升為master節點，功能完成後腳本将退出，修複完原master節點後不會自動切換回去，可選擇将原master節點配置為新的salve節點或者手動切換，重新啟動相關程序，重新配置該master節點為slave節點
Mon Jun 24 10:17:08 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:17:08 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:17:08 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
[[email protected] ~]# systemctl stop mariadb
[[email protected] app1]# masterha_manager --conf=/etc/mastermha/app1.cnf
Mon Jun 24 10:25:01 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:25:01 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:25:01 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
Creating /data/mastermha/app1 if not exists.. ok.
Checking output directory is accessible or not..
ok.
Binlog found at /data/log/, up to mysql-bin.000005
Mon Jun 24 10:25:09 2019 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Mon Jun 24 10:25:09 2019 - [info] Reading application default configuration from /etc/mastermha/app1.cnf..
Mon Jun 24 10:25:09 2019 - [info] Reading server configuration from /etc/mastermha/app1.cnf..
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.79.27
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000005
Read_Master_Log_Pos: 245
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 529
Relay_Master_Log_File: mysql-bin.000005
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: 245
Relay_Log_Space: 825
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: 2
1 row in set (0.00 sec)
Galera Cluster叢集架構
如上圖所示，三個節點組成了一個叢集，與普通的主從架構不同，它們都可以作為主節點，三個節點是對等的，成為multi-master架構，當有用戶端要寫入或者讀取資料時，連接配接哪一個示例都是一樣的，讀到的資料是相同的，寫入某一個節點之後。叢集載自己會将新資料同步到其他節點上，這種架構不共享任何資料，是一種高備援架構
- Galera Cluster：wsrep(MySQL extended with the Write Set Replication)
- 通過wsrep協定在全局實作複制；任何一個節點都可讀寫，不需要主從複制，實作多主讀寫
- 多主架構：真正的多點讀寫的叢集，在任何時候讀寫資料，都是最新的
- 同步複制：叢集不同節點之間資料同步，沒有延遲，在資料庫挂掉之後資料不會丢失
- 并發複制：從節點apply資料時，支援并行執行，有更好的性能
- 故障切換：在出現資料庫故障時，因支援多點寫入，切換容易
- 熱插拔：在服務期間，如果資料庫挂了，隻要監控程式發現的夠快，不可服務事件就會非常少，在節點故障期間，節點本身對叢集的影響非常小
- 自動節點克隆：在新增節點，或者停機維護時，增量資料或者基礎資料不需要人工手動備份提供，Galera Cluster會自動拉取線上節點資料，最終叢集會變為一緻
- 對應用透明：叢集的維護，對應用程式時透明的
Galera Cluster高可用叢集實作原理
Galera Cluster包括兩個元件
Galera replication library (galera-3)
WSREP：MySQL extended with the Write Set Replication
WSREP複制實作：
PXC：Percona XtraDB Cluster，是Percona對Galera的實作
MariaDB Galera Cluster
參考倉庫：https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.59/yum/centos7-amd64/
至少需要三個節點，不能安裝mariadb-server
[[email protected] ~]# vim /etc/yum.repos.d/ganlera.repo
[galera]
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.64/yum/centos7-amd64/
gpgcheck=0
[[email protected] ~]# yum -y install MariaDB-Galera-server
[[email protected] ~]# vim /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://192.168.79.27,192.168.79.37,192.168.79.47"
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_node_name="node3"
wsrep_node_address="192.168.79.47"
# this is only for embedded server
以上操作三個節點都需要完成，配置檔案中指定叢集中節點位址和目前主機node名稱和ip位址
[[email protected] galera-26.4.2]# /etc/init.d/mysql start --wsrep-new-cluster
Starting MariaDB.190626 14:24:27 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
190626 14:24:27 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
... SUCCESS!
#在其中一個節點執行如上操作，對叢集進行初始化
#而後正常啟動其他節點
[[email protected] ~]# service mysql start
Starting MariaDB.190626 06:25:17 mysqld_safe Logging to '/var/lib/mysql/localhost.localdomain.err'.
190626 06:25:17 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
...SST in progress, setting sleep higher. SUCCESS!
MariaDB [hellodb]> SHOW STATUS LIKE 'wsrep_cluster_size'; #查詢節點元件，之後在三個節點分别進行寫測試即可
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| wsrep_cluster_size | 3 |
+--------------------+-------+
1 row in set (0.00 sec)