公司沒有 DBA，Mysql 運維自己來

• 2020 年 2 月 13 日
• 筆記

如果你的公司有 DBA，那麼我恭喜你，你可以無視 Mysql 運維。如果你的公司沒有 DBA，那你就好好學兩手 Mysql 基本運維操作，行走江湖，防身必備。 環境：CentOS7 版本：

一、虛擬機部署

本文僅介紹 rpm 安裝方式

安裝 mysql yum 源

官方下載地址：https://dev.mysql.com/downloads/repo/yum/

（1）下載 yum 源

$ wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

（2）安裝 yum repo 文件並更新 yum 快取

$ rpm -ivh mysql80-community-release-el7-1.noarch.rpm

執行結果：

會在 /etc/yum.repos.d/ 目錄下生成兩個 repo 文件

$ ls | grep mysql  mysql-community.repo  mysql-community-source.repo

更新 yum：

$ yum clean all  $ yum makecache

（3）查看 rpm 安裝狀態

$ yum search mysql | grep server  mysql-community-common.i686 : MySQL database common files for server and client  mysql-community-common.x86_64 : MySQL database common files for server and  mysql-community-test.x86_64 : Test suite for the MySQL database server                         : administering MySQL servers  mysql-community-server.x86_64 : A very fast and reliable SQL database server

通過 yum 安裝 mysql 有幾個重要目錄：

# 配置文件  /etc/my.cnf  # 資料庫目錄  /var/lib/mysql/  # 配置文件  /usr/share/mysql（mysql.server命令及配置文件）  # 相關命令  /usr/bin（mysqladmin mysqldump等命令）  # 啟動腳本  /usr/lib/systemd/system/mysqld.service （註冊為 systemd 服務）

（4）安裝 mysql 伺服器

$ yum install mysql-community-server

mysql 服務管理

通過 yum 方式安裝 mysql 後，本地會有一個名為 mysqld 的 systemd 服務。

其服務管理十分簡便：

# 查看狀態  systemctl status mysqld  # 啟用服務  systemctl enable mysqld  # 禁用服務  systemctl disable mysqld  # 啟動服務  systemctl start mysqld  # 重啟服務  systemctl restart mysqld  # 停止服務  systemctl stop mysqld

初始化資料庫密碼

查看一下初始密碼

$ grep "password" /var/log/mysqld.log  2018-09-30T03:13:41.727736Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: %:lt+srWu4k1

執行命令：

mysql -uroot -p<臨時密碼>

輸入臨時密碼，進入 mysql，如果要修改密碼，執行以下指令：

ALTER user 'root'@'localhost' IDENTIFIED BY '你的密碼';

註：密碼強度默認為中等，大小寫字母、數字、特殊符號，只有修改成功後才能修改配置再設置更簡單的密碼

配置遠程訪問

CREATE USER 'root'@'%' IDENTIFIED BY '你的密碼';  GRANT ALL ON *.* TO 'root'@'%';  ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '你的密碼';  FLUSH PRIVILEGES;

跳過登錄認證

vim /etc/my.cnf

在 [mysqld] 下面加上 skip-grant-tables

作用是登錄時跳過登錄認證，換句話說就是 root 什麼密碼都可以登錄進去。

執行 systemctl restart mysqld，重啟 mysql

二、基本運維

創建用戶

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

說明：

• username：你將創建的用戶名
• host：指定該用戶在哪個主機上可以登陸，如果是本地用戶可用 localhost，如果想讓該用戶可以從任意遠程主機登陸，可以使用通配符%
• password：該用戶的登陸密碼，密碼可以為空，如果為空則該用戶可以不需要密碼登陸伺服器

示例：

CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';  CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';  CREATE USER 'pig'@'%' IDENTIFIED BY '123456';  CREATE USER 'pig'@'%' IDENTIFIED BY '';  CREATE USER 'pig'@'%';

授權

命令：

GRANT privileges ON databasename.tablename TO 'username'@'host'

說明：

• privileges：用戶的操作許可權，如SELECT，INSERT，UPDATE等，如果要授予所的許可權則使用ALL
• databasename：資料庫名
• tablename：表名，如果要授予該用戶對所有資料庫和表的相應操作許可權則可用*表示，如*.*

示例：

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';  GRANT ALL ON *.* TO 'pig'@'%';  GRANT ALL ON maindataplus.* TO 'pig'@'%';

注意：

用以上命令授權的用戶不能給其它用戶授權，如果想讓該用戶可以授權，用以下命令:

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

撤銷授權

命令:

REVOKE privilege ON databasename.tablename FROM 'username'@'host';

說明:

privilege, databasename, tablename：同授權部分

例子:

REVOKE SELECT ON *.* FROM 'pig'@'%';

注意:

假如你在給用戶'pig'@'%'授權的時候是這樣的（或類似的）：GRANT SELECT ON test.user TO 'pig'@'%'，則在使用REVOKE SELECT ON *.* FROM 'pig'@'%';命令並不能撤銷該用戶對 test 資料庫中 user 表的SELECT 操作。相反，如果授權使用的是GRANT SELECT ON *.* TO 'pig'@'%';則REVOKE SELECT ON test.user FROM 'pig'@'%';命令也不能撤銷該用戶對 test 資料庫中 user 表的Select許可權。

具體資訊可以用命令SHOW GRANTS FOR 'pig'@'%'; 查看。

更改用戶密碼

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是當前登陸用戶用:

SET PASSWORD = PASSWORD("newpassword");

示例：

SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

備份與恢復

Mysql 備份數據使用 mysqldump 命令。

mysqldump 將資料庫中的數據備份成一個文本文件，表的結構和表中的數據將存儲在生成的文本文件中。

備份：

備份一個資料庫

語法：

mysqldump -u <username> -p <database> [<table1> <table2> ...] > backup.sql

• username 資料庫用戶
• dbname 資料庫名稱
• table1 和 table2 參數表示需要備份的表的名稱，為空則整個資料庫備份；
• BackupName.sql 參數表設計備份文件的名稱，文件名前面可以加上一個絕對路徑。通常將資料庫被分成一個後綴名為 sql 的文件

備份多個資料庫

mysqldump -u <username> -p --databases <database1> <database2> ... > backup.sql

備份所有資料庫

mysqldump -u <username> -p --all-databases > backup.sql

恢復一個資料庫

Mysql 恢複數據使用 mysqldump 命令。

語法：

mysql -u <username> -p <database> < backup.sql

恢復所有資料庫

mysql -u <username> -p --all-databases < backup.sql

卸載

（1）查看已安裝的 mysql

$ rpm -qa | grep -i mysql  perl-DBD-MySQL-4.023-6.el7.x86_64  mysql80-community-release-el7-1.noarch  mysql-community-common-8.0.12-1.el7.x86_64  mysql-community-client-8.0.12-1.el7.x86_64  mysql-community-libs-compat-8.0.12-1.el7.x86_64  mysql-community-libs-8.0.12-1.el7.x86_64

（2）卸載 mysql

$ yum remove mysql-community-server.x86_64

主從節點部署

假設需要配置一個主從 Mysql 伺服器環境

• master 節點：192.168.8.10
• slave 節點：192.168.8.11

主節點上的操作

（1）修改配置並重啟

執行 vi /etc/my.cnf ，添加如下配置：

[mysqld]  server-id=1  log_bin=/var/lib/mysql/binlog

• server-id – 伺服器 ID 號。在主從架構中，每台機器的 ID 必須唯一。
• log_bin – 同步的日誌路徑及文件名，一定注意這個目錄要是 mysql 有許可權寫入的；

修改後，重啟 mysql 使配置生效：

$ systemctl restart mysql

（2）創建用於同步的用戶

進入 mysql 命令控制台：

$ mysql -u root -p  Password:

執行以下 SQL：

-- 創建 slave1 用戶，並指定該用戶只能在主機 192.168.8.11 上登錄  mysql> CREATE USER 'slave1'@'192.168.8.11' IDENTIFIED WITH mysql_native_password BY '密碼';  -- 為 slave1 賦予 REPLICATION SLAVE 許可權  mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'192.168.8.11';  -- 刷新授權表資訊  mysql> FLUSH PRIVILEGES;

（3）加讀鎖

為了主庫與從庫的數據保持一致，我們先為 mysql 加入讀鎖，使其變為只讀。

mysql> FLUSH TABLES WITH READ LOCK;

（4）查看主節點狀態

mysql> show master status;  +------------------+----------+--------------+---------------------------------------------+-------------------+  | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |  +------------------+----------+--------------+---------------------------------------------+-------------------+  | mysql-bin.000001 |     4202 |              | mysql,information_schema,performance_schema |                   |  +------------------+----------+--------------+---------------------------------------------+-------------------+  1 row in set (0.00 sec)

注意：需要記錄下 File 和 Position，後面會用到。

（5）導出 sql

$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

（6）解除讀鎖

mysql> UNLOCK TABLES;

（7）將 sql 遠程傳送到從節點上

$ scp dbdump.sql [email protected]:/home

從節點上的操作

（1）修改配置並重啟

執行 vi /etc/my.cnf ，添加如下配置：

[mysqld]  server-id=2  log_bin=/var/lib/mysql/binlog

• server-id – 伺服器 ID 號。在主從架構中，每台機器的 ID 必須唯一。
• log_bin – 同步的日誌路徑及文件名，一定注意這個目錄要是 mysql 有許可權寫入的；

修改後，重啟 mysql 使配置生效：

$ systemctl restart mysql

（2）導入 sql

$ mysql -u root -p < /home/dbdump.sql

（3）在從節點上建立與主節點的連接

進入 mysql 命令控制台：

$ mysql -u root -p  Password:

執行以下 SQL：

-- 停止從節點服務  mysql> STOP SLAVE;    mysql> CHANGE MASTER TO      -> MASTER_HOST='192.168.8.10',      -> MASTER_USER='slave1',      -> MASTER_PASSWORD='密碼6',      -> MASTER_LOG_FILE='binlog.000001',      -> MASTER_LOG_POS=4202;

MASTER_LOG_FILE 和 MASTER_LOG_POS 參數要分別與 show master status 指令獲得的 File 和 Position 屬性值對應。

（4）啟動 slave 進程

mysql> start slave;

（5）查看主從同步狀態

mysql> show slave statusG;

說明：如果以下兩項參數均為 YES，說明配置正確。

• Slave_IO_Running
• Slave_SQL_Running

（6）將從節點設為只讀

mysql> set global read_only=1;  mysql> set global super_read_only=1;  mysql> show global variables like "%read_only%";  +-----------------------+-------+  | Variable_name         | Value |  +-----------------------+-------+  | innodb_read_only      | OFF   |  | read_only             | ON    |  | super_read_only       | ON    |  | transaction_read_only | OFF   |  +-----------------------+-------+

註：設置 slave 伺服器為只讀，並不影響主從同步。

三、配置

大部分情況下，默認的基本配置已經足夠應付大多數場景，不要輕易修改 Mysql 伺服器配置，除非你明確知道修改項是有益的。

配置文件路徑

配置 Mysql 首先要確定配置文件在哪兒。

不同 Linux 作業系統上，Mysql 配置文件路徑可能不同。通常的路徑為 /etc/my.cnf 或 /etc/mysql/my.cnf 。

如果不知道配置文件路徑，可以嘗試以下操作：

# which mysqld  /usr/sbin/mysqld  # /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'  Default options are read from the following files in the given order:  /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

配置項語法

Mysql 配置項設置都使用小寫，單詞之間用下劃線或橫線隔開（二者是等價的）。

建議使用固定的風格，這樣檢索配置項時較為方便。

# 這兩種格式等價  /usr/sbin/mysqld --auto-increment-offset=5  /usr/sbin/mysqld --auto_increment_offset=5

常用配置項說明

這裡介紹比較常用的基本配置，更多配置項說明可以參考：Mysql 伺服器配置說明

先給出一份常用配置模板，內容如下：

[mysqld]  # GENERAL  # -------------------------------------------------------------------------------  datadir = /var/lib/mysql  socket  = /var/lib/mysql/mysql.sock  pid_file = /var/lib/mysql/mysql.pid  user = mysql  port = 3306  default_storage_engine = InnoDB  default_time_zone = '+8:00'  character_set_server = utf8mb4  collation_server = utf8mb4_0900_ai_ci    # LOG  # -------------------------------------------------------------------------------  log_error = /var/log/mysql/mysql-error.log  slow_query_log = 1  slow_query_log_file = /var/log/mysql/mysql-slow.log    # InnoDB  # -------------------------------------------------------------------------------  innodb_buffer_pool_size = <value>  innodb_log_file_size = <value>  innodb_file_per_table = 1  innodb_flush_method = O_DIRECT    # MyIsam  # -------------------------------------------------------------------------------  key_buffer_size = <value>    # OTHER  # -------------------------------------------------------------------------------  tmp_table_size = 32M  max_heap_table_size = 32M  query_cache_type = 0  query_cache_size = 0  max_connections = <value>  thread_cache = <value>  open_files_limit = 65535    [client]  socket  = /var/lib/mysql/mysql.sock  port = 3306

• GENERAL
  • datadir – mysql 數據文件所在目錄
  • socket – scoket 文件
  • pid_file – PID 文件
  • user – 啟動 mysql 服務進程的用戶
  • port – 服務埠號，默認 3306
  • default_storage_engine – mysql 5.1 之後，默認引擎是 InnoDB
  • default_time_zone – 默認時區。中國大部分地區在東八區，即 +8:00
  • character_set_server – 資料庫默認字符集
  • collation_server – 資料庫字符集對應一些排序等規則，注意要和 character_set_server 對應
• LOG
  • log_error – 錯誤日誌文件地址
  • slow_query_log – 錯誤日誌文件地址
• InnoDB
  • innodb_buffer_pool_size – InnoDB 使用一個緩衝池來保存索引和原始數據，不像 MyISAM。這裡你設置越大，你在存取表裡面數據時所需要的磁碟 I/O 越少。
    • 在一個獨立使用的資料庫伺服器上,你可以設置這個變數到伺服器物理記憶體大小的 60%-80%
    • 注意別設置的過大，會導致 system 的 swap 空間被佔用，導致作業系統變慢，從而減低 sql 查詢的效率
    • 默認值：128M，建議值：物理記憶體的 60%-80%
  • innodb_log_file_size – 日誌文件的大小。默認值：48M，建議值：根據你系統的磁碟空間和日誌增長情況調整大小
  • innodb_file_per_table – 說明：mysql5.7 之後默認開啟，意思是，每張表一個獨立表空間。默認值 1，開啟。
  • innodb_flush_method – 說明：控制著 innodb 數據文件及 redo log 的打開、刷寫模式，三種模式：fdatasync(默認)，O_DSYNC，O_DIRECT。默認值為空，建議值：使用 SAN 或者 raid，建議用 O_DIRECT，不懂測試的話，默認生產上使用 O_DIRECT
    • fdatasync：數據文件，buffer pool->os buffer->磁碟；日誌文件，buffer pool->os buffer->磁碟；
    • O_DSYNC： 數據文件，buffer pool->os buffer->磁碟；日誌文件，buffer pool->磁碟；
    • O_DIRECT： 數據文件，buffer pool->磁碟； 日誌文件，buffer pool->os buffer->磁碟；
• MyIsam
  • key_buffer_size – 指定索引緩衝區的大小，為 MYISAM 數據表開啟供執行緒共享的索引快取，對 INNODB 引擎無效。相當影響 MyISAM 的性能。
    • 不要將其設置大於你可用記憶體的 30%，因為一部分記憶體同樣被 OS 用來緩衝行數據
    • 甚至在你並不使用 MyISAM 表的情況下，你也需要仍舊設置起 8-64M 記憶體由於它同樣會被內部臨時磁碟表使用。
    • 默認值 8M，建議值：對於記憶體在 4GB 左右的伺服器該參數可設置為 256M 或 384M。
    • 注意：該參數值設置的過大反而會是伺服器整體效率降低！
• OTHER
  • tmp_table_size – 記憶體臨時表的最大值，默認 16M，此處設置成 128M
  • max_heap_table_size – 用戶創建的記憶體表的大小，默認 16M，往往和 tmp_table_size 一起設置，限制用戶臨時表大小。超限的話，MySQL 就會自動地把它轉化為基於磁碟的 MyISAM 表，存儲在指定的 tmpdir 目錄下，增大 IO 壓力，建議記憶體大，增大該數值。
  • query_cache_type – 這個系統變數控制著查詢快取功能的開啟和關閉，0 表示關閉，1 表示打開，2 表示只要 select 中明確指定 SQL_CACHE 才快取。
  • query_cache_size – 默認值 1M，優點是查詢快取可以極大的提高伺服器速度，如果你有大量的相同的查詢並且很少修改表。缺點：在你表經常變化的情況下或者如果你的查詢原文每次都不同，查詢快取也許引起性能下降而不是性能提升。
  • max_connections – 最大連接數，可設最大值 16384，一般考慮根據同時在線人數設置一個比較綜合的數字，鑒於該數值增大並不太消耗系統資源，建議直接設 10000。如果在訪問時經常出現 Too Many Connections 的錯誤提示，則需要增大該參數值
  • thread_cache – 當客戶端斷開之後，伺服器處理此客戶的執行緒將會快取起來以響應下一個客戶而不是銷毀。可重用，減小了系統開銷。默認值為 9，建議值：兩種取值方式，
    • 方式一，根據物理記憶體，1G —> 8；2G —> 16； 3G —> 32； >3G —> 64；
    • 方式二，根據 show status like 'threads%'，查看 Threads_connected 值。
  • open_files_limit – MySQL 打開的文件描述符限制，默認最小 1024;
    • 當 open_files_limit 沒有被配置的時候，比較 max_connections*5 和 ulimit -n 的值，哪個大用哪個，
    • 當 open_file_limit 被配置的時候，比較 open_files_limit 和 max_connections*5 的值，哪個大用哪個
    • 注意：仍然可能出現報錯資訊 Can't create a new thread；此時觀察系統 cat /proc/mysql 進程號/limits，觀察進程 ulimit 限制情況
    • 過小的話，考慮修改系統配置表，/etc/security/limits.conf 和 /etc/security/limits.d/90-nproc.conf

四、常見問題

Too many connections

現象

嘗試連接 Mysql 時，遇到 Too many connections 錯誤。

原因

資料庫連接執行緒數超過最大值，訪問被拒絕。

解決方案

如果實際連接執行緒數過大，可以考慮增加伺服器節點來分流；如果實際執行緒數並不算過大，那麼可以配置 max_connections 來增加允許的最大連接數。需要注意的是，連接數不宜過大，一般來說，單庫每秒有 2000 個並發連接時，就可以考慮擴容了，健康的狀態應該維持在每秒 1000 個並發連接左右。

（1）查看最大連接數

mysql> show variables like '%max_connections%';  +------------------------+-------+  | Variable_name          | Value |  +------------------------+-------+  | max_connections        | 151   |  | mysqlx_max_connections | 100   |  +------------------------+-------+

（2）查看伺服器響應的最大連接數

mysql> show global status like 'Max_used_connections';  +----------------------+-------+  | Variable_name        | Value |  +----------------------+-------+  | Max_used_connections | 142   |  +----------------------+-------+  1 row in set (0.00 sec)

（3）臨時設置最大連接數

set GLOBAL max_connections=256;

注意：當伺服器重啟時，最大連接數會被重置。

（4）永久設置最大連接數

修改 /etc/my.cnf 配置文件，在 [mysqld] 添加以下配置：

max_connections=256

重啟 mysql 以生效

（5）修改 Linux 最大文件數限制

設置了最大連接數，如果還是沒有生效，考慮檢查一下 Linux 最大文件數

Mysql 最大連接數會受到最大文件數限制，vim /etc/security/limits.conf，添加 mysql 用戶配置

mysql hard nofile 65535  mysql soft nofile 65535

（6）檢查 LimitNOFILE

如果是使用 rpm 方式安裝 mysql，檢查 mysqld.service 文件中的 LimitNOFILE 是否配置的太小。

時區（time_zone）偏差

現象

資料庫中存儲的 Timestamp 欄位值比真實值少了 13 個小時。

原因

• 當 JDBC 與 MySQL 開始建立連接時，會獲取伺服器參數。
• 當 MySQL 的 time_zone 值為 SYSTEM 時，會取 system_time_zone 值作為協調時區，若得到的是 CST 那麼 Java 會誤以為這是 CST -0500 ，因此會給出錯誤的時區資訊（中國一般是CST +0800，即東八區）。

查看時區方法：

通過 show variables like '%time_zone%'; 命令查看 Mysql 時區配置：

mysql> show variables like '%time_zone%';  +------------------+--------+  | Variable_name    | Value  |  +------------------+--------+  | system_time_zone | CST    |  | time_zone        | SYSTEM |  +------------------+--------+

解決方案

方案一

mysql> set global time_zone = '+08:00';  Query OK, 0 rows affected (0.00 sec)    mysql> set time_zone = '+08:00';  Query OK, 0 rows affected (0.00 sec)

方案二

修改 my.cnf 文件，在 [mysqld] 節下增加 default-time-zone='+08:00' ，然後重啟。

五、腳本

這裡推薦我寫的幾個一鍵運維腳本，非常方便，歡迎使用：

• Mysql 安裝腳本
• Mysql 備份腳本

參考資料

• 《高性能 MySQL》
• https://www.cnblogs.com/xiaopotian/p/8196464.html
• https://www.cnblogs.com/bigbrotherer/p/7241845.html
• https://blog.csdn.net/managementandjava/article/details/80039650
• http://www.manongjc.com/article/6996.html
• https://www.cnblogs.com/xyabk/p/8967990.html
• MySQL 8.0 主從（Master-Slave）配置
• Mysql 主從同步實戰
• MySQL 備份和恢復機制