AutoMySQLBackup 3.0的一個小Bug

   使用AutoMySQLBackup 3.0做邏輯備份時，測驗驗證時發現AutoMySQLBackup使用mysqldump匯出的資料庫備份有問題，如下測驗所示：

$ ls

daily  fullschema  latest  monthly  status  tmp  weekly

$ cd daily/

$ ls

kkkk  mysql  performance_schema  sys

$ cd kkkk/

$ ls

daily_kkkk_2021-09-22_16h45m_Wednesday.sql.gz

$ gunzip daily_kkkk_2021-09-22_16h45m_Wednesday.sql.gz

$ more daily_kkkk_2021-09-22_16h45m_Wednesday.sql

-- MySQL dump 10.13  Distrib 5.7.34, for Linux (x86_64)

--

-- Host: 127.0.0.1    Database: kkkk

-- ------------------------------------------------------

-- Server version       5.7.34-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-22 16:45:39

如上所示，測驗發現沒有匯出對應資料庫中的任何資料，經過測驗和驗證，最終發現是因為AutoMySQLBackup使用的資料庫賬號mybackup（引數CONFIG_mysql_dump_username指定的賬號）的權限有問題，如下所示，此賬號是Xtrabackup備份使用的一個賬號，所以也用來做AutoMySQLBackup的賬號使用，但是忘記設定相關權限了

mysql> show grants for mybackup@'127.0.0.1';

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

| Grants for [email protected]                                                                                    |

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

| GRANT RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON *.* TO 'mybackup'@'127.0.0.1'|

| GRANT SELECT, INSERT ON `performance_schema`.* TO 'mybackup'@'127.0.0.1'                                         |

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

2 rows in set (0.00 sec)

mysqldump是需要一些特定權限的，如下所示，

GRANT SELECT, RELOAD, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER  ON *.* TO 'mybackup'@'127.0.0.1';

FLUSH PRIVILEGES;

重新授予相關權限，重新測驗驗證，發現已經正常，如下所示，但是這種情況下，AutoMySQLBackup居然沒有任何錯誤提示，而且也生成了對應的sql壓縮檔案，所以在使用程序中，千萬要小心，一定要注意設定權限，測驗驗證備份檔案是否正常，

$ more daily_kkkk_2021-09-22_16h47m_Wednesday.sql

-- MySQL dump 10.13  Distrib 5.7.34, for Linux (x86_64)

--

-- Host: 127.0.0.1    Database: kkkk

-- ------------------------------------------------------

-- Server version       5.7.34-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;

/*!40103 SET TIME_ZONE='+00:00' */;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--

-- Table structure for table `test`

--

DROP TABLE IF EXISTS `test`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `test` (

  `id` int(11) DEFAULT NULL,

  `name` varchar(12) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

/*!40101 SET character_set_client = @saved_cs_client */;

--

-- Dumping data for table `test`

--

LOCK TABLES `test` WRITE;

/*!40000 ALTER TABLE `test` DISABLE KEYS */;

INSERT INTO `test` VALUES (1,'kery'),(2,'kkk');

/*!40000 ALTER TABLE `test` ENABLE KEYS */;

UNLOCK TABLES;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2021-09-22 16:47:22

標籤：MySQL

上一篇：MySQL45講之InnoDB加鎖規則

下一篇：HDFS原理深入理解