mysql高可用之MHA用法及說明
更新時(shí)間：2025年09月09日 16:52:57 作者：海綿(寶?寶)
MHA是MySQL高可用解決方案,實(shí)現(xiàn)0-30秒故障切換及數(shù)據(jù)一致性,Atlas是360讀寫分離中間件,提升查詢效率,兩者均用于數(shù)據(jù)庫高可用架構(gòu)優(yōu)化,MHA側(cè)重故障恢復(fù),Atlas側(cè)重負(fù)載均衡與讀寫分離
MySQL高可用架構(gòu)之MHA
1.簡介
MHA（Master High Availability）目前在MySQL高可用方面是一個相對成熟的解決方案，它由日本DeNA公司youshimaton（現(xiàn)就職于Facebook公司）開發(fā)，是一套優(yōu)秀的作為MySQL高可用性環(huán)境下故障切換和主從提升的高可用軟件。在MySQL故障切換過程中，MHA能做到在0~30秒之內(nèi)自動完成數(shù)據(jù)庫的故障切換操作，并且在進(jìn)行故障切換的過程中，MHA能在最大程度上保證數(shù)據(jù)的一致性，以達(dá)到真正意義上的高可用。
該軟件由兩部分組成：MHA Manager（管理節(jié)點(diǎn)）和MHA Node（數(shù)據(jù)節(jié)點(diǎn)）。MHA Manager可以單獨(dú)部署在一臺獨(dú)立的機(jī)器上管理多個master-slave集群，也可以部署在一臺slave節(jié)點(diǎn)上。MHA Node運(yùn)行在每臺MySQL服務(wù)器上，MHA Manager會定時(shí)探測集群中的master節(jié)點(diǎn)，當(dāng)master出現(xiàn)故障時(shí)，它可以自動將最新數(shù)據(jù)的slave提升為新的master，然后將所有其他的slave重新指向新的master。整個故障轉(zhuǎn)移過程對應(yīng)用程序完全透明。
在MHA自動故障切換過程中，MHA試圖從宕機(jī)的主服務(wù)器上保存二進(jìn)制日志，最大程度的保證數(shù)據(jù)的不丟失，但這并不總是可行的。例如，如果主服務(wù)器硬件故障或無法通過ssh訪問，MHA沒法保存二進(jìn)制日志，只進(jìn)行故障轉(zhuǎn)移而丟失了最新的數(shù)據(jù)。使用MySQL 5.5的半同步復(fù)制，可以大大降低數(shù)據(jù)丟失的風(fēng)險(xiǎn)。MHA可以與半同步復(fù)制結(jié)合起來。如果只有一個slave已經(jīng)收到了最新的二進(jìn)制日志，MHA可以將最新的二進(jìn)制日志應(yīng)用于其他所有的slave服務(wù)器上，因此可以保證所有節(jié)點(diǎn)的數(shù)據(jù)一致性。
目前MHA主要支持一主多從的架構(gòu)，要搭建MHA,要求一個復(fù)制集群中必須最少有三臺數(shù)據(jù)庫服務(wù)器，一主二從，即一臺充當(dāng)master，一臺充當(dāng)備用master，另外一臺充當(dāng)從庫，因?yàn)橹辽傩枰_服務(wù)器，出于機(jī)器成本的考慮，淘寶也在該基礎(chǔ)上進(jìn)行了改造，目前淘寶TMHA已經(jīng)支持一主一從。
我們自己使用其實(shí)也可以使用1主1從，但是master主機(jī)宕機(jī)后無法切換，以及無法補(bǔ)全binlog。master的mysqld進(jìn)程crash后，還是可以切換成功，以及補(bǔ)全binlog的。
1.1.展示了如何通過MHA Manager管理多組主從復(fù)制。
1.2.可以將MHA工作原理總結(jié)
（1）從宕機(jī)崩潰的master保存二進(jìn)制日志事件（binlog events）;
（2）識別含有最新更新的slave；
（3）應(yīng)用差異的中繼日志（relay log）到其他的slave；
（4）應(yīng)用從master保存的二進(jìn)制日志事件（binlog events）；
（5）提升一個slave為新的master；
（6）使其他的slave連接新的master進(jìn)行復(fù)制；
MHA軟件由兩部分組成，Manager工具包和Node工具包，具體的說明如下。
1.3.Manager工具包主要包括以下幾個工具
- masterha_check_ssh 檢查MHA的SSH配置狀況
- masterha_check_repl 檢查MySQL復(fù)制狀況
- masterha_manger 啟動MHA
- masterha_check_status 檢測當(dāng)前MHA運(yùn)行狀態(tài)
- masterha_master_monitor 檢測master是否宕機(jī)
- masterha_master_switch 控制故障轉(zhuǎn)移（自動或者手動）
- masterha_conf_host 添加或刪除配置的server信息
1.4.Node工具包
（這些工具通常由MHA Manager的腳本觸發(fā)，無需人為操作）主要包括以下幾個工具：
- save_binary_logs 保存和復(fù)制master的二進(jìn)制日志
- apply_diff_relay_logs 識別差異的中繼日志事件并將其差異的事件應(yīng)用于其他的slave
- filter_mysqlbinlog 去除不必要的ROLLBACK事件（MHA已不再使用這個工具）
- purge_relay_logs 清除中繼日志（不會阻塞SQL線程）
注意：
為了盡可能的減少主庫硬件損壞宕機(jī)造成的數(shù)據(jù)丟失，因此在配置MHA的同時(shí)建議配置成MySQL 5.5的半同步復(fù)制。關(guān)于半同步復(fù)制原理各位自己進(jìn)行查閱。（不是必須）
以上修改的一些文件，把注釋和空格都要去掉
2.部署MHA
2.1.搭建主從復(fù)制（一主兩從）
設(shè)備：
- master：10.0.0.41
- slave1：10.0.0.42
- slave2：10.0.0.43
- slave3：10.0.0.44
Master主操作：
2.1.1關(guān)閉防火墻，seliunx（四臺）
systemctl stop firewalld systemctl disable firewalld setenforce 0 sed -i '/^SELINUX/s#enforcing#disabled#g' /etc/selinux/config
2.1.2.設(shè)置同步（四臺）
echo "*/5 * * * * /usr/sbin/ntpdate ntp1.aliyun.com >/dev/null 2>&1">>/var/spool/cron/root
2.1.3修改主機(jī)名
[root@ c7m01 ~]# hostname c7m01 [root@ c702 ~]# hostname c702 [root@ c703 ~]# hostname c703 [root@ c704 ~]# hostname c704
2.1.4.hosts解析（四臺）
[root@ c7m01 ~]# vim /etc/hosts 10.0.0.41 c7m01 10.0.0.42 c702 10.0.0.43 c703 10.0.0.44 c704
2.1.5.配置免密登錄（四臺執(zhí)行同樣的操作）
[root@ c7m01 ~]# cat ssh.sh
#!/bin/bash
yum -y install sshpass &> /dev/null
read -p "請輸入服務(wù)器密碼：" passwd
UserName=root
IP="10.0.0."
#創(chuàng)建密鑰
ssh-keygen -t dsa -f ~/.ssh/id_dsa -P "" &>/dev/null
#分發(fā)公鑰
for i in 41 42 43 44
do
sshpass -p "$passwd" ssh-copy-id -i ~/.ssh/id_dsa.pub -p 22 -o StrictHostKeyChecking=no $UserName@$IP$i &>/dev/null
done
2.1.5.執(zhí)行（四臺）
[root@ c7m01 ~]# chmod +x ssh.sh [root@ c7m01 ~]# sh ssh.sh 請輸入服務(wù)器密碼：123456
2.1.6.測試
[root@ c7m01 ~]# ssh root@10.0.0.43 Last login: Tue May 19 15:09:48 2020 from 10.0.0.1 [root@ c703 ~]# exit logout Connection to 10.0.0.43 closed. [root@ c702 ~]# ssh root@10.0.0.41 Last login: Tue May 19 15:09:44 2020 from 10.0.0.1 [root@ master ~]# exit logout Connection to 10.0.0.41 closed.
2.2.mysql安裝yum repo（三臺41,42,43）
[root@ c7m01 ~]# wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm [root@ c7m01 ~]# rpm -ivh mysql-community-release-el7-5.noarch.rpm [root@ c7m01 ~]# yum -y install mysql-server
2.3.啟動mariadb（三臺41,42,43）
[root@ c7m01 ~]# systemctl start mysql [root@ c7m01 ~]# netstat -anpt | grep 3306 tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 3131/mysqld
2.4.修改mysql密碼（三臺41,42,43）
[root@ c7m01 ~]# mysql
mysql> update mysql.user set password=password('123456') where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
2.5.主服務(wù)上操作
2.5.1在master服務(wù)器上更改mysql配置文件
[root@ c7m01 ~]# vim /etc/my.cnf [mysqld] server-id=1 log-bin=mysql-bin #禁止mysql自動刪除relaylog工能 relay_log_purge = 0 #mysql5.6已上的特性，開啟gtid，必須主從全開 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 [root@ c7m01 ~]# systemctl restart mysql
2.5.2創(chuàng)建同步用戶
[root@ c7m01 ~]# mysql -uroot -p123456 mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456'; Query OK, 0 rows affected (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
2.5.3.查看c7m01主庫的master狀態(tài)
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 530
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: c5173e3b-9860-11ea-89d2-000c29de703c:1-2
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show master status;
+------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.000001 | 530 | | | c5173e3b-9860-11ea-89d2-000c29de703c:1-2 |
+------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
#查看GTID狀態(tài)
mysql> show global variables like '%gtid%';
+---------------------------------+------------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------------+
| binlog_gtid_simple_recovery | OFF |
| enforce_gtid_consistency | ON |
| gtid_executed | c5173e3b-9860-11ea-89d2-000c29de703c:1-2 |
| gtid_mode | ON |
| gtid_owned | |
| gtid_purged | |
| simplified_binlog_gtid_recovery | OFF |
+---------------------------------+------------------------------------------+
7 rows in set (0.00 sec)
2.6.slave1從服務(wù)器上操作
2.6.1在c702服務(wù)器上更改mysql配置文件
[root@ c702 ~]# vim /etc/my.cnf [mysqld] server-id=2 log-bin=mysql-bin #禁止mysql自動刪除relaylog工能 relay_log_purge = 0 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 skip-name-resolve [root@ c702 ~]# systemctl restart mysql
2.6.2.創(chuàng)建同步的用戶
[root@ c702 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
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: 530
Relay_Log_Space: 519
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
Master_UUID: 3cd11b99-9a33-11ea-95b4-000c29b6ec09
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O threadto update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 82b534da-9a33-11ea-95b6-000c29a7553d:1-2
Auto_Position: 0
1 row in set (0.09 sec)
ERROR:
No query specified
2.7.slave2從服務(wù)器上操作
2.7.1在c703服務(wù)器上更改mysql配置文件
[root@ c703 ~]# vim /etc/my.cnf [mysqld] server-id=3 log-bin=mysql-bin #禁止mysql自動刪除relaylog工能 relay_log_purge = 0 gtid_mode = on enforce_gtid_consistency = 1 log_slave_updates = 1 skip-name-resolve [root@ c703 ~]# systemctl restart mysql
2.7.2.創(chuàng)建同步的用戶
[root@ c703 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'rep'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.12 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.10 sec)
mysql> change master to master_host='10.0.0.41',master_user='rep',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=530;
Query OK, 0 rows affected, 2 warnings (0.11 sec)
mysql> start slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.41
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 530
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 314
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: 530
Relay_Log_Space: 519
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
Master_UUID: 3cd11b99-9a33-11ea-95b4-000c29b6ec09
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O threadto update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 82b534da-9a33-11ea-95b6-000c29a7553d:1-2
Auto_Position: 0
1 row in set (0.09 sec)
ERROR:
No query specified
3.在服務(wù)器上安裝MHA的依賴（四臺）
[root@ c7m01 ~]# yum -y install perl-DBD-mysql [root@ c7m01 ~]# yum -y install perl-Config-Tiny epel-release perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes
3.1.授權(quán)MHA管理用戶（三臺41,42,43）
[root@ c7m01 ~]# mysql -uroot -p123456 mysql> grant all privileges on *.* to 'mha'@'10.0.0.%' identified by 'mha'; Query OK, 0 rows affected (0.04 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)
3.2.安裝MHA node節(jié)點(diǎn)（四臺）
#上傳mha4mysql-node-0.58-0.el7.centos.noarch.rpm [root@ c7m01x ~]# rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm
3.3.在slave3安裝（10.0.0.44）
[root@ c704 ~]# rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm Preparing... ################################# [100%] Updating / installing... 1:mha4mysql-manager-0.58-0.el7.cent################################# [100%]
3.4.配置MHA（10.0.0.44）
[root@ c704 ~]# mkdir -p /etc/mha [root@ c704 ~]# mkdir -p /var/log/mha/app1 [root@ c704 ~]# vim /etc/mha/app1.cnf [root@ c704 ~]# cat /etc/mha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1 master_binlog_dir=/var/lib/mysql user=mha password=mha ping_interval=2 repl_password=123456 repl_user=rep ssh_user=root [server1] hostname=10.0.0.41 port=3306 [server2] hostname=10.0.0.42 port=3306 [server3] hostname=10.0.0.43 port=3306 ignore_fail=1 no_master=1
注意以上不要有注釋和空格
3.4.1.測試（10.0.0.44）
[root@ c704 ~]# masterha_check_ssh --conf=/etc/mha/app1.cnf Wed May 20 15:35:55 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed May 20 15:35:55 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Wed May 20 15:35:55 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Wed May 20 15:35:55 2020 - [info] Starting SSH connection tests.. Wed May 20 15:35:56 2020 - [debug] Wed May 20 15:35:55 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.42(10.0.0.42:22).. Wed May 20 15:35:56 2020 - [debug] ok. Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.41(10.0.0.41:22) to root@10.0.0.43(10.0.0.43:22).. Wed May 20 15:35:56 2020 - [debug] ok. Wed May 20 15:35:56 2020 - [debug] Wed May 20 15:35:55 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.41(10.0.0.41:22).. Wed May 20 15:35:56 2020 - [debug] ok. Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.42(10.0.0.42:22) to root@10.0.0.43(10.0.0.43:22).. Wed May 20 15:35:56 2020 - [debug] ok. Wed May 20 15:35:57 2020 - [debug] Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.41(10.0.0.41:22).. Wed May 20 15:35:56 2020 - [debug] ok. Wed May 20 15:35:56 2020 - [debug] Connecting via SSH from root@10.0.0.43(10.0.0.43:22) to root@10.0.0.42(10.0.0.42:22).. Wed May 20 15:35:57 2020 - [debug] ok. Wed May 20 15:35:57 2020 - [info] All SSH connection tests passed successfully. [root@ c704 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf Wed May 20 15:36:15 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed May 20 15:36:15 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Wed May 20 15:36:15 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Wed May 20 15:36:15 2020 - [info] MHA::MasterMonitor version 0.58. Wed May 20 15:36:16 2020 - [info] GTID failover mode = 1 Wed May 20 15:36:16 2020 - [info] Dead Servers: Wed May 20 15:36:16 2020 - [info] Alive Servers: Wed May 20 15:36:16 2020 - [info] 10.0.0.41(10.0.0.41:3306) Wed May 20 15:36:16 2020 - [info] 10.0.0.42(10.0.0.42:3306) Wed May 20 15:36:16 2020 - [info] 10.0.0.43(10.0.0.43:3306) Wed May 20 15:36:16 2020 - [info] Alive Slaves: Wed May 20 15:36:16 2020 - [info] 10.0.0.42(10.0.0.42:3306) Version=5.6.48-log (oldest major version between slaves) log-bin:enabled Wed May 20 15:36:16 2020 - [info] GTID ON Wed May 20 15:36:16 2020 - [info] Replicating from 10.0.0.41(10.0.0.41:3306) Wed May 20 15:36:16 2020 - [info] 10.0.0.43(10.0.0.43:3306) Version=5.6.48-log (oldest major version between slaves) log-bin:enabled Wed May 20 15:36:16 2020 - [info] GTID ON Wed May 20 15:36:16 2020 - [info] Replicating from 10.0.0.41(10.0.0.41:3306) Wed May 20 15:36:16 2020 - [info] Not candidate for the new Master (no_master is set) Wed May 20 15:36:16 2020 - [info] Current Alive Master: 10.0.0.41(10.0.0.41:3306) Wed May 20 15:36:16 2020 - [info] Checking slave configurations.. Wed May 20 15:36:16 2020 - [info] read_only=1 is not set on slave 10.0.0.42(10.0.0.42:3306). Wed May 20 15:36:16 2020 - [info] read_only=1 is not set on slave 10.0.0.43(10.0.0.43:3306). Wed May 20 15:36:16 2020 - [info] Checking replication filtering settings.. Wed May 20 15:36:16 2020 - [info] binlog_do_db= , binlog_ignore_db= Wed May 20 15:36:16 2020 - [info] Replication filtering check ok. Wed May 20 15:36:16 2020 - [info] GTID (with auto-pos) is supported. Skipping all SSHand Node package checking. Wed May 20 15:36:16 2020 - [info] Checking SSH publickey authentication settings on the current master.. Wed May 20 15:36:16 2020 - [info] HealthCheck: SSH to 10.0.0.41 is reachable. Wed May 20 15:36:16 2020 - [info] 10.0.0.41(10.0.0.41:3306) (current master) +--10.0.0.42(10.0.0.42:3306) +--10.0.0.43(10.0.0.43:3306) Wed May 20 15:36:16 2020 - [info] Checking replication health on 10.0.0.42.. Wed May 20 15:36:16 2020 - [info] ok. Wed May 20 15:36:16 2020 - [info] Checking replication health on 10.0.0.43.. Wed May 20 15:36:16 2020 - [info] ok. Wed May 20 15:36:16 2020 - [warning] master_ip_failover_script is not defined. Wed May 20 15:36:16 2020 - [warning] shutdown_script is not defined. Wed May 20 15:36:16 2020 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
如果報(bào)錯（10.0.0.43）
[root@ c704 ~]# masterha_check_repl --conf=/etc/mha/app1.cnf Wed May 20 15:27:26 2020 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping. Wed May 20 15:27:26 2020 - [info] Reading application default configuration from /etc/mha/app1.cnf.. Wed May 20 15:27:26 2020 - [info] Reading server configuration from /etc/mha/app1.cnf.. Wed May 20 15:27:26 2020 - [info] MHA::MasterMonitor version 0.58. Wed May 20 15:27:26 2020 - [error][/usr/share/perl5/vendor_perl/MHA/Server.pm, ln180]Got MySQL error when connecting 10.0.0.42(10.0.0.42:3306) :1045:Access denied for user 'mha'@'slave1' (using password: YES), but this is not a MySQL crash. Check MySQL server settings. Wed May 20 15:27:26 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln301] at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297. Wed May 20 15:27:27 2020 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm,ln309] Got fatal error, stopping operations Wed May 20 15:27:27 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm,ln427] Error happened on checking configurations. at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 329. Wed May 20 15:27:27 2020 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm,ln525] Error happened on monitoring servers. Wed May 20 15:27:27 2020 - [info] Got exit code 1 (Not master dead). MySQL Replication Health is NOT OK! 這個報(bào)錯 在所有的機(jī)器上添加 vim /etc/my.cnf skip-name-resolve systemctl restart mysql mysql -uroot -p123456 show slave status \G; 看一下是否有雙yes
3.4.2.啟動MHA（10.0.0.44）
[root@ c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 8069 [root@ c704 ~]# ps -ef | grep mha root 8069 7320 2 15:52 pts/0 00:00:00 perl /usr/bin/masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover root 8080 7320 20 15:53 pts/0 00:00:00 grep --color=auto mha
3.4.3.查MHA狀態(tài)（10.0.0.44）
[root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:8069) is running(0:PING_OK), master:10.0.0.41
3.5.關(guān)閉MHA（10.0.0.44）
[root@ c704 ~]# masterha_stop --conf=/etc/mha/app1.cnf
3.6.從庫重新加入新主（10.0.0.44）
[root@ c704 ~]# grep -i "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
3.7.測試MHA故障轉(zhuǎn)移（在master10.0.0.41）
停掉master主庫10.0.0.41
[root@ c7m01 ~]# systemctl stop mysql
3.7.1.查看c703的狀態(tài)，發(fā)現(xiàn)master_host變成了10.0.0.42
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1589
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.7.2.查看slave1的狀態(tài)，變成了master（slave2 10.0.0.0.42）
[root@c702 ~]# mysql -uroot -p123456 -e "show master status;" Warning: Using a password on the command line interface can be insecure. +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+ | mysql-bin.000001 | 190 |
3.8.查看c704的配置文件
發(fā)現(xiàn)這里少了server1
[root@c704 ~]# cat /etc/mha/app1.cnf [server default] manager_log=/var/log/mha/app1/manager.log manager_workdir=/var/log/mha/app1 master_binlog_dir=/var/lib/mysql password=mha ping_interval=2 repl_password=123456 repl_user=rep ssh_user=root user=mha #少了server1 [server2] hostname=10.0.0.42 port=3306 [server3] hostname=10.0.0.43 ignore_fail=1 no_master=1 port=3306
4.MHA故障還原
首先將宕掉mysql修復(fù)，然后加入到mysql一主兩從集群
[root@c704 ~]# grep "CHANGE MASTER TO MASTER" /var/log/mha/app1/manager.log | tail -1
Thu May 21 19:05:05 2020 - [info] All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='xxx';
[root@c7m01 ~]# systemctl restart mysql
[root@c7m01 ~]# mysql -uroot -p123456 -e "CHANGE MASTER TO MASTER_HOST='10.0.0.42', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='rep', MASTER_PASSWORD='123456';"
[root@c7m01 ~]# mysql -uroot -p123456 -e "start slave;"
[root@c7m01 ~]# mysql -uroot -p123456 -e "show slave status \G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.42
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1797
Relay_Log_File: mysqld-relay-bin.000003
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.1.重新在將[server1]標(biāo)簽添加到MHA配置文件，并且啟動MHA
[root@c704 ~]# vim /etc/mha/app1.cnf [server1] hostname=10.0.0.41 port=3306 [root@c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 7092 #查看狀態(tài) [root@c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:7092) is running(0:PING_OK), master:10.0.0.42
4.2.配置vip漂移
漂移的兩種方式
4.3.MHA腳本方式
4.3.1.修改MHA配置文件
[root@ c704 ~]# vim /etc/mha/app1.cnf [server default] master_ip_failover_script=/usr/bin/master_ip_failover ##添加到第二行就行了
4.3.2.編寫漂移腳本
[root@ c7-44 ~]# vim /usr/bin/master_ip_failover
#!/usr/bin/env perl
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
my (
$command, $ssh_user, $orig_master_host, $orig_master_ip,
$orig_master_port, $new_master_host, $new_master_ip, $new_master_port
);
my $vip = '10.0.0.49/24';
my $key = '0';
my $ssh_start_vip = "/sbin/ifconfig ens33:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig ens33:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
);
exit &main();
sub main {
print "\n\nIN SCRIPT TEST====$ssh_stop_vip==$ssh_start_vip===\n\n";
if ( $command eq "stop" || $command eq "stopssh" ) {
my $exit_code = 1;
eval {
print "Disabling the VIP on old master: $orig_master_host \n";
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\\@$new_master_host \" $ssh_start_vip \"`;
}
sub stop_vip() {
return 0 unless ($ssh_user);
`ssh $ssh_user\\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
[root@ c704 ~]# chmod +x /usr/bin/master_ip_failover
[root@ c704 ~]# ll /usr/bin/master_ip_failover
-rwxr-xr-x 1 root root 2170 May 22 15:50 /usr/bin/master_ip_failover
4.3.3.手動綁定vip
[root@ c704 ~]# ifconfig ens33:1 10.0.0.49/24
[root@ c704 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:5c:02:6a brd ff:ff:ff:ff:ff:ff
inet 10.0.0.44/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fe5c:26a/64 scope link
valid_lft forever preferred_lft forever
4.3.4.重啟mha
[root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:3139) is running(0:PING_OK), master:10.0.0.41 [root@ c704 ~]# masterha_stop --conf=/etc/mha/app1.cnf Stopped app1 successfully. [1]+ Exit 1 nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 [root@ c704 ~]# nohup masterha_manager --conf=/etc/mha/app1.cnf --remove_dead_master_conf --ignore_last_failover < /dev/null > /var/log/mha/app1/manager.log 2>&1 & [1] 4163 [root@ c704 ~]# masterha_check_status --conf=/etc/mha/app1.cnf app1 (pid:4163) is running(0:PING_OK), master:10.0.0.41
4.4.模擬主庫宕機(jī)vip漂移
4.4.1.關(guān)閉c702的主庫mysql
[root@c702 ~]# systemctl stop mysql
[root@c702 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:cd:14:44 brd ff:ff:ff:ff:ff:ff
inet 10.0.0.42/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fecd:1444/64 scope link
valid_lft forever preferred_lft forever
4.4.2.查看c7m01上master狀態(tài)和vip
[root@c7m01 ~]# ip a show ens33
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:de:70:3c brd ff:ff:ff:ff:ff:ff
inet 10.0.0.41/24 brd 10.0.0.255 scope global ens33
valid_lft forever preferred_lft forever
inet 10.0.0.49/24 brd 10.0.0.255 scope global secondary ens33:1
valid_lft forever preferred_lft forever
inet6 fe80::20c:29ff:fede:703c/64 scope link
valid_lft forever preferred_lft forever
[root@c7m01 ~]# mysql -uroot -p123456 -e 'show master status\G'
Warning: Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
File: mysql-bin.000002
Position: 1325
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 5ab78bda-9b49-11ea-9cc9-000c29de703c:1-7,
75b3a4ef-9b49-11ea-9cca-000c29cd1444:1-6
#故障還原
5.Mysql之Atlas(讀寫分離)
Atlas是由Qihoo 360公司W(wǎng)eb平臺部基礎(chǔ)架構(gòu)團(tuán)隊(duì)開發(fā)維護(hù)的一-個基于MySQL協(xié)議的數(shù)據(jù)中間層項(xiàng)目。
它在MySQL言方推出的MySQL-Proxy 0.8.2版本的基礎(chǔ)上,修改了大量bug ,添加了很多功能特性。
目前該項(xiàng)目在360公司內(nèi)部得到了廣泛應(yīng)用,很多MySQL業(yè)務(wù)已經(jīng)接入了Atlas平臺,每天承載的讀寫請求數(shù)達(dá)幾十億條。
5.1.什么是讀寫分離
讀寫分離,基本的原理是讓主數(shù)據(jù)庫處理事務(wù)性增、刪、改操作( INSERT. DELETE、 UPDATE ) , 而從數(shù)據(jù)庫處理SELECT查詢操作。
數(shù)據(jù)庫復(fù)制把主數(shù)據(jù)庫操作的變更同步到集群中的從數(shù)據(jù)庫。
5.2.為什么讀寫分離
因?yàn)閿?shù)據(jù)庫的“寫" (寫10000條數(shù)據(jù)到oracle可能要3分鐘)操作是比較耗時(shí)的。
但是數(shù)據(jù)庫的“讀" (從oracle讀10000條數(shù)據(jù)可能只要5秒鐘)。
所以讀寫分離,解決的是，數(shù)據(jù)庫的寫入，影響了查詢的效率。
5.3.什么時(shí)候要讀寫分離
數(shù)據(jù)庫不一定要讀寫分離,如果程序使用數(shù)據(jù)庫較多時(shí),而更新少,查詢多的情況下會考慮使用,利用數(shù)據(jù)庫主從同步可以減少數(shù)據(jù)庫壓力,提高性能。
當(dāng)然,數(shù)據(jù)庫也有其它優(yōu)化方案。memcache 或是表折分,或是搜索引學(xué)。都是解決方法。
6.Atlas的功用與應(yīng)用場景
6.1.Atlas的功能有
讀寫分離、從庫負(fù)載均衡、自動分表、IP過濾、 SQL語句黑白名單、DBA可平滑上下線DB、自動摘除宕機(jī)的DB。
6.2.Atlas的使用場景
Atlas是一個位于前端 應(yīng)用與后端MySQL數(shù)據(jù)庫之間的中間件，它使得應(yīng)用程序員無需再關(guān)心讀寫分離、分表等與MySQL相關(guān)的細(xì)節(jié)，可以專注于編寫業(yè)務(wù)邏輯,同時(shí)使得DBA的運(yùn)維工作對前端應(yīng)用透明，上下線DB前端應(yīng)用無感知。
7.Atlas的安裝過程
注意：
1.Atlas只能安裝運(yùn)行在64位的系統(tǒng)上 2.Centos 5.X安裝Atlas-XX.el5.x86_ _64.rpm , Centos 6.X安裝Atlas-XX.el6.x86 _64.rpm(經(jīng)過測試centos7也可以使用6 的版本) 3、后端mysq|版本應(yīng)大于5.1 ,建議使用Mysql 5.6以上=
###安裝altas
[root@c704 ~]# wget https://github.com/Qihoo360/Atlas/releases/download/2.2.1/Atlas-2.2.1.el6.x86_64.rpm
[root@c704 ~]# rpm -ivh Atlas-2.2.1.el6.x86_64.rpm
###修改配置文件
[root@c704 ~]# cp /usr/local/mysql-proxy/conf/test.cnf{,.bak}
[root@c704 ~]# egrep -v '^#|^$' /usr/local/mysql-proxy/conf/test.cnf
[mysql-proxy]
admin-username = user
admin-password = pwd
proxy-backend-addresses = 10.0.0.49:3306
proxy-read-only-backend-addresses = 10.0.0.42:3306,10.0.0.43:3306
pwds = rep:/iZxz+0GRoA=,mha:O2jBXONX098=
daemon = true
keepalive = true
event-threads = 8
log-level = message
log-path = /usr/local/mysql-proxy/log
sql-log =ON
proxy-address = 0.0.0.0:1234
admin-address = 0.0.0.0:2345
charset = utf8
###加密方式 /usr/local/mysql-proxy/bin/encrypt 密碼
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt 123456
/iZxz+0GRoA=
[root@c704 ~]# /usr/local/mysql-proxy/bin/encrypt mha
O2jBXONX098=
###啟動atlas
[root@c704 ~]# /usr/local/mysql-proxy/bin/mysql-proxyd test start
OK: MySQL-Proxy of test is started
###查看是否啟動atlas成功
[root@c704 ~]# ps -ef | grep mysql-proxy
root 9097 1 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9098 9097 0 20:20 ? 00:00:00 /usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/test.cnf
root 9364 8350 0 20:28 pts/0 00:00:00 grep --color=auto mysql-proxy
7.1.Atlas讀寫分離測試
7.1.1.讀測試
[root@c704 ~]# mysql -umha -pmha -P1234 -h10.0.0.44 mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 3 | +-------------+ 1 row in set (0.00 sec) mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 2 | +-------------+ 1 row in set (0.00 sec)
解釋說明:發(fā)現(xiàn)上面’server. id每次的結(jié)果都不-樣，分別是2臺從庫的server. id ,并且每執(zhí)行- -次命令 , server. id就會變換一次，這是因?yàn)槟J(rèn)讀操作的權(quán)重都是1，兩臺從DB默認(rèn)就是負(fù)載均衡。
7.1.2.寫測試
[root@c704 ~]# mysql -umha -pmha -P1234 -h10.0.0.44 mysql> begin;select @@server_id;commit; Query OK, 0 rows affected (0.00 sec) +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> create database www; Query OK, 1 row affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | wg | | www | +--------------------+ 5 rows in set (0.00 sec)
7.2.Altas管理操作
7.2.1.登錄管理接口
[root@c704 ~]# mysql -uuser -ppwd -P2345 -h 10.0.0.44
7.2.2.查看幫助信息
mysql> select * from help; +----------------------------+---------------------------------------------------------+ | command | description | +----------------------------+---------------------------------------------------------+ | SELECT * FROM help | shows this help | | SELECT * FROM backends | lists the backends and their state | | SET OFFLINE $backend_id | offline backend server, $backend_id is backend_ndx's id | | SET ONLINE $backend_id | online backend server, ... | | ADD MASTER $backend | example: "add master 127.0.0.1:3306", ... | | ADD SLAVE $backend | example: "add slave 127.0.0.1:3306", ... | | REMOVE BACKEND $backend_id | example: "remove backend 1", ... | | SELECT * FROM clients | lists the clients | | ADD CLIENT $client | example: "add client 192.168.1.2", ... | | REMOVE CLIENT $client | example: "remove client 192.168.1.2", ... | | SELECT * FROM pwds | lists the pwds | | ADD PWD $pwd | example: "add pwd user:raw_password", ... | | ADD ENPWD $pwd | example: "add enpwd user:encrypted_password", ... | | REMOVE PWD $pwd | example: "remove pwd user", ... | | SAVE CONFIG | save the backends to config file | | SELECT VERSION | display the version of Atlas | +----------------------------+---------------------------------------------------------+ 16 rows in set (0.00 sec)
補(bǔ)充說明（自己的理解）：
第一條：查看幫助信息 第二條：查看后端信息 第三條：設(shè)置脫機(jī)$后端id 第四條：設(shè)置聯(lián)機(jī)$后端id 第五條：添加MASTER$后端 第六條：添加從$backend 第七條：刪除后端 id 第八條：從客戶端選擇 第九條：添加客戶端 第十條：刪除客戶端 第十一條：從pwds中選擇 第十二條：加上PWD 第十三條：刪除PWD 第十四條：保存配置 第十五條：選擇版本
7.2.3.查看后端的代理庫
mysql> select * from backends; +-------------+----------------+-------+------+ | backend_ndx | address | state | type | +-------------+----------------+-------+------+ | 1 | 10.0.0.49:3306 | up | rw | | 2 | 10.0.0.42:3306 | up | ro | | 3 | 10.0.0.43:3306 | up | ro | +-------------+----------------+-------+------+ 3 rows in set (0.00 sec)
7.2.4.下線后端節(jié)點(diǎn)
mysql> set offline 3; +-------------+----------------+---------+------+ | backend_ndx | address | state | type | +-------------+----------------+---------+------+ | 3 | 10.0.0.43:3306 | offline | ro | +-------------+----------------+---------+------+ 1 row in set (0.00 sec) mysql> select * from backends; +-------------+----------------+---------+------+ | backend_ndx | address | state | type | +-------------+----------------+---------+------+ | 1 | 10.0.0.49:3306 | up | rw | | 2 | 10.0.0.42:3306 | up | ro | | 3 | 10.0.0.43:3306 | offline | ro | +-------------+----------------+---------+------+
7.2.5.上線后端節(jié)點(diǎn)
mysql> set off online 3; ERROR 1105 (07000): use 'SELECT * FROM help' to see the supported commands mysql> set online 3; +-------------+----------------+---------+------+ | backend_ndx | address | state | type | +-------------+----------------+---------+------+ | 3 | 10.0.0.43:3306 | unknown | ro | +-------------+----------------+---------+------+ 1 row in set (0.00 sec) mysql> select * from backends; +-------------+----------------+---------+------+ | backend_ndx | address | state | type | +-------------+----------------+---------+------+ | 1 | 10.0.0.49:3306 | up | rw | | 2 | 10.0.0.42:3306 | up | ro | | 3 | 10.0.0.43:3306 | unknown | ro | +-------------+----------------+---------+------+ 3 rows in set (0.00 sec)
總結(jié)
以上為個人經(jīng)驗(yàn)，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關(guān)文章
經(jīng)常提到數(shù)據(jù)庫的事務(wù)，那你知道數(shù)據(jù)庫還有事務(wù)隔離的說法嗎，本文主要介紹了mysql的四種隔離級別，具有一定的參考價(jià)值，感興趣的可以了解一下2021-09-09
詳解MySQL與Spring的自動提交(autocommit)
這篇文章主要介紹了MySQL與Spring的自動提交(autocommit)的的相關(guān)資料，幫助大家更好的理解和使用MySQL與spring，感興趣的朋友可以了解下2021-01-01
MySQL5.6下windows msi安裝詳細(xì)介紹
這篇文章主要介紹了MySQL5.6下windows msi安裝詳細(xì)介紹,介紹的非常詳細(xì)，具有參考借鑒價(jià)值,需要的朋友可以參考下2016-08-08
mysql如何按字段查詢重復(fù)的數(shù)據(jù)
這篇文章主要介紹了mysql如何按字段查詢重復(fù)的數(shù)據(jù)問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-05-05
Windows MySQL修改配置文件my.ini不生效問題
在Windows Server 2019上修改MySQL 5.6的安裝目錄下my.ini文件后,需要通過修改注冊表中的ImagePath值來確保MySQL讀取新的配置文件,修改時(shí)應(yīng)確保配置文件路徑正確,并且新配置不會覆蓋原有配置,以保證修改生效2025-01-01
關(guān)于sql?count(列名)、count(常量)、count(*)之間的區(qū)別
這篇文章主要介紹了關(guān)于sql?count(列名)、count(常量)、count(*)之間的區(qū)別及說明,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2023-08-08
mysql正則表達(dá)式（regexp和rlike）的搜索功能實(shí)例分析
這篇文章主要介紹了mysql正則表達(dá)式（regexp和rlike）的搜索功能,結(jié)合實(shí)例形式分析了mysql正則表達(dá)式使用regexp和rlike的搜索功能相關(guān)原理與實(shí)現(xiàn)技巧,需要的朋友可以參考下2019-12-12
MySQL帶AND關(guān)鍵字的多條件查詢，MySQL中，使用AND關(guān)鍵字，可以連接兩個或者多個查詢條件，只有滿足所有條件的記錄，才會被返回2017-07-07
這篇文章主要介紹了MySQL的WHERE語句中BETWEEN與IN的使用教程,是MySQL入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下2015-12-12
mysql5.7創(chuàng)建用戶授權(quán)刪除用戶撤銷授權(quán)
這篇文章主要介紹了mysql5.7創(chuàng)建用戶授權(quán)刪除用戶撤銷授權(quán)的方法,非常不錯，具有參考借鑒價(jià)值，需要的朋友可以參考下2017-02-02