mysqldump做備份時候可能出現效能抖動，mysqldump是先從buffer中找想要備份的內容，如果buffer沒有，就需要訪問磁碟中的資料檔案，然後把資料調回記憶體，形成備份檔案。當把資料從磁碟調到記憶體時，有可能把記憶體裡的熱資料沖掉，這樣就影響了我們對現有業務的訪問。因此，備份最好在業務較低時候做。

一臺機器安裝mysql客戶端工具，最好和資料庫的版本一致，不然可能存在問題。

獲取軟體壓縮包

使用此mysql客戶端工具的mysqldump遠端進行備份。具體如下：

需要能遠端連線資料庫

mysql -h ip -u root -p

一、建立遠端使用者

mysql -u root -p
use mysql
create user 'zcbackuser'@'localhost' identified by 'Enmo123';
flush privileges;
grant all privileges on *.* to 'zcbackuser'@'%' identified by 'Enmo123' with grant option;
#grant all privileges on dbname.* to 'zcbackuser'@'%' identified by 'Enmo123' with grant option;
flush privileges;

二、檢視許可權

mysql> show grants;
+-------------------------------------------------------------+
| Grants for root@%                                           |
+-------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------+
1 row in set (0.04 sec)

如果是 Grants for root@%localhost ，則遠端主機不允許除了localhost以外其他登入。需要修改許可權

%表示都可以連，可以在這裡限制遠端ip

grant all on dbname.* to 'username'@'%' identified by 'password';

附：遠端修改密碼：
mysqladmin -h ip -uroot -p123456 password 密碼
遠端登入主機的密碼是遠端root，而不是要登入的遠端主機它自己本身的密碼，兩者不相同。

三、檢視資料量

1.進入information_schema，檢視所有資料庫大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
mysql> use information_schema;
Database changed
mysql>
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;
+--------+
| data   |
+--------+
| 2.48MB |
+--------+
1 row in set (0.26 sec)

2.檢視指定資料庫大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='資料庫名';

3.檢視指定資料庫的某個表的大小

use information_schema;
select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='home' and table_name='表名';

四、遠端備份策略

1.簡單遠端備份（不加鎖,可以在業務執行備份）

./mysqldump -h 172.20.10.8 -u username -p'passwd' --single-transaction --default-character-set=utf8 --set-gtid-purged=off  --skip-opt database1 > /tmp/database_db.sql 2>&1

！對一個正在執行的資料庫進行備份請慎重！！ 如果一定要 在服務執行期間備份，請新增 --skip-opt選項，禁用–-opt，避免鎖表

--opt
    等同於--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 該選項預設開啟, 可以用--skip-opt禁用.
--single-transaction  （為了獲取一致性備份）
    該選項在匯出資料之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程式且能保證匯出時資料庫的一致性狀態。它只適用於多版本儲存引擎，僅InnoDB。
    這個選項和--lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交。
    要想匯出大表的話，應結合使用--quick 選項。
--set-gtid-purged=off 
     MySQL5.6以後，加入了全域性事務 ID (GTID) 來強化資料庫的主備一致性，故障恢復，以及容錯能力。
     可以透過新增--set-gtid-purged=off ，匯入庫中應該重新生產GTID，而不用原來的。

2.每週天的晚上十點半開始做備份。檔案格式為 /tmp/database_db_2022-08-04-001306.sql

30 22 * * 0 /usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off  --default-character-set=utf8 database1 > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql 2>&1

！如果執行備份的時間間隔比較小，可以選擇在crontab中加入flock檔案鎖去限制檔案讀寫衝突，解決指令碼重複執行問題。

3.需要壓縮如下：

30 22 * * 0 /usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off  --default-character-set=utf8 database1 | gzip > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1
#解壓命令： gunzip backup.sql.gz

4.使用備份指令碼的備份

30 22 * * 0 sh /tmp/back.sh 2>&1

back.sh如下

#！/bin/bash
/usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off  --default-character-set=utf8 database1 > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql

壓縮版 back.sh

#！/bin/bash
/usr/local/mysql/bin/mysqldump -h 172.20.10.8  -u username -p'passwd' --single-transaction --set-gtid-purged=off  --default-character-set=utf8 database1  | gzip > /tmp/database_db_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz

5.可以選擇加上備份報錯日誌 --log-error 把報錯輸出到類似於mysqldump_error_log_2022-08-03-015721.err的日誌

./mysqldump -h 172.20.10.2 -u zcbackuser -p'Enmo123' --single-transaction --default-character-set=utf8 --set-gtid-purged=off --log-error=/tmp/mysqldump_error_log_`date +\%Y-\%m-\%d-\%H\%M\%S`.err  test1| gzip > /tmp/test1_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1

注：可能會出現如下報錯，最好使用同樣版本的客戶端。
mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.“number-of-buckets-specified”’) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘test1’ AND TABLE_NAME = ‘shop’;’: Unknown table ‘column_statistics’ in information_schema (1109);

五、遠端備份的效果基本如下:

[root@localhost bin]# ./mysqldump -h 172.20.10.2 -u zcbackuser -p'Enmo123' --default-character-set=utf8 --set-gtid-purged=off  --single-transaction --log-error=/tmp/mysqldump_error_log_`date +\%Y-\%m-\%d-\%H\%M\%S`.err  test1| gzip > /tmp/test1_`date +\%Y-\%m-\%d-\%H\%M\%S`.sql.gz 2>&1
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bin]# cd /tmp
[root@localhost tmp]# ll
total 12
drwxr-xr-x 2 root root  18 Jul  7 20:54 hsperfdata_root
-rw-r--r-- 1 root root  73 Jul  7 19:22 lua_zvpodh
-rw-r--r-- 1 root root 320 Aug  3 01:57 mysqldump_error_log_2022-08-03-015721.err
-rw-r--r-- 1 root root 608 Aug  3 01:57 test1_2022-08-03-015721.sql.gz
[root@localhost tmp]# gunzip test1_2022-08-03-015721.sql.gz 
[root@localhost tmp]# tail -20 test1_2022-08-03-015721.sql 
--
DROP TABLE IF EXISTS `shop`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `shop` (
  `id` int(20) DEFAULT NULL,
  `name` varchar(25) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `shop`
--
LOCK TABLES `shop` WRITE;
/*!40000 ALTER TABLE `shop` DISABLE KEYS */;
INSERT INTO `shop` VALUES (1,'ysl'),(2,'enmo');
/*!40000 ALTER TABLE `shop` ENABLE KEYS */;
UNLOCK TABLES;

附錄：MySQL的mysqldump預設帶的引數

這些引數，執行mysqldump 命令的時候預設就會帶上的。

 –opt
 –add-drop-table
 –add-locks
 -i,–comments
 -a,–create-options
 -e, –extended-insert
 -l, –lock-tables
 -q, –quick
 -K, –disable-keys
 -Q, –quote-names
 –dump-date
 –ssl
 –triggers
 –tz-utc