mysql運維案例

一.執行儲存過程/函式報錯賬號不存在

問題描述：執行儲存過程報錯
mysql>call create_no_by_day('STUDENT','CREATE_TIME');
ERROR 1449 (HY000):The user specified as a definer ('TEST_111'@'172.%.%.%') does not exist

分析：這情況是因為當時機器上這儲存過程是由使用者’TEST_111’@'172.%.%.%‘建立，但是儲存過程匯入到了新的機器後，這新機器沒有這個使用者而報錯。
三種解決辦法：
1、重建這個儲存過程，把definer那段程式碼取消。
2、在新機器上建立這個使用者’TEST_111’@‘172.%.%.%’。
3、修改定義者，替換所有儲存過程是這個定義者的為新機器已有賬號。

解決：修改定義者
1.檢視機器不存在這個賬號
select host,user from mysql.user where host='172.%.%.%' and user='TEST_111';

2.替換所有儲存過程是這個定義者的為新機器已有賬號。
UPDATE  mysql.proc set DEFINER='TEST_222@172.%.%.%' where DEFINER='TEST_111@172.%.%.%';

二.遇到DDL變更的時候發生阻塞

問題描述：新增欄位、新增索引等DDL語句時候會被阻塞，show processlist 會看到顯示 Waiting for table metadata lock. 後續的對這些表的select也會被阻塞
分析：autocommit=0，懷疑有未提交事務，導致產生了後設資料鎖。
解決：
1.如果開啟了performance_schema 監控，通過語句定位未提交事務：
SELECT
locked_schema,
locked_table,
locked_type,
waiting_processlist_id,
waiting_age,
waiting_query,
waiting_state,
blocking_processlist_id,
blocking_age,
substring_index(sql_text,“transaction_begin;” ,-1) AS blocking_query,
sql_kill_blocking_connection
FROM
(
SELECT
b.OWNER_THREAD_ID AS granted_thread_id,
a.OBJECT_SCHEMA AS locked_schema,
a.OBJECT_NAME AS locked_table,
“Metadata Lock” AS locked_type,
c.PROCESSLIST_ID AS waiting_processlist_id,
c.PROCESSLIST_TIME AS waiting_age,
c.PROCESSLIST_INFO AS waiting_query,
c.PROCESSLIST_STATE AS waiting_state,
d.PROCESSLIST_ID AS blocking_processlist_id,
d.PROCESSLIST_TIME AS blocking_age,
d.PROCESSLIST_INFO AS blocking_query,
concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection
FROM
performance_schema.metadata_locks a
JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA
AND a.OBJECT_NAME = b.OBJECT_NAME
AND a.lock_status = ‘PENDING’
AND b.lock_status = ‘GRANTED’
AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID
AND a.lock_type = ‘EXCLUSIVE’
JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID
JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID
) t1,
(
SELECT
thread_id,
group_concat( CASE WHEN EVENT_NAME = ‘statement/sql/begin’ THEN “transaction_begin” ELSE sql_text END ORDER BY event_id SEPARATOR “;” ) AS sql_text
FROM
performance_schema.events_statements_history
GROUP BY thread_id
) t2
WHERE
t1.granted_thread_id = t2.thread_id

2.殺程式kill blocking_processlist_id

3.沒有開啟wait/lock/metadata/sql/mdl情況下，針對sleep程式執行kill
SELECT concat(‘kill ‘,processlist_id,’;’)
FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)
JOIN information_schema.processlist c ON b.processlist_id = c.id
WHERE a.sql_text NOT LIKE ‘%performance%’ and command=‘sleep’ order by c.time desc;

三.批量更新資料卡住

問題描述：批量更新很慢，沒新增索引，新增索引被阻塞
分析：事務不自動提交容易造成後設資料鎖衝突
解決：執行SELECT concat(‘kill ‘,processlist_id,’;’)
FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)
JOIN information_schema.processlist c ON b.processlist_id = c.id
WHERE a.sql_text NOT LIKE ‘%performance%’ and command=‘sleep’ order by c.time desc;
殺程式

四.一個環境資料庫佔用空間滿情況

問題描述：一個環境資料庫空間已滿。
分析：資料沒有定時清理，沒有監控報警。一個儲存實時訊息的大表上百億資料佔用過大
問題解決：1.能登陸mysql情況下，truncate table 大表(無用資料，可清除)，回收空間
2.不能登陸mysql情況下，刪除部分binlog日誌，讓mysql啟動起來，再清理其他資料。

五.資料庫遷移

問題描述：已備份了資料，想把資料庫導進另一個環境，但是想換一個資料庫名字，以免把另一個環境的同名資料庫覆蓋
問題解決：
1.匯出資料
# mysqldump -uroot -pxxx_001 --set-gtid-purged=OFF admin > admin.sql

2． 建新庫
mysql>create database admin1;

3． 建立賬號，授權
mysql>CREATE USER admin1@'%' identified by 'Admin1_!@#';
mysql>GRANT ALL PRIVILEGES ON  admin1.* TO admin1@'%';
mysql>flush privileges;

4． 匯入資料到新庫admin1
# mysql -uroot -pxxx_001 admin1 <admin.sql

這樣，就把原庫admin匯出的表資料，匯入了新庫admin1裡面。新賬號admin1已授權訪問新庫admin1,新賬號密碼是Admin1_!@#

注： 操作2,3是sql語句，在mysql裡面執行。 操作1,4是在linux命令列上操作。

六.資料庫日誌出現多個斷連記錄

問題描述：資料庫日誌出現多個斷連記錄，顯示為Got an error writing communication packets
分析：有可能是客戶端異常退出了，應用重啟，也有可能是網路鏈路異常。這種提示一般是[NOTE],屬於提示資訊。
問題解決：
關閉警報
set global log_warning=1;
另：若是出現Got timeout reading communication packets或者Got timeout writing communication packets，屬於客戶端的空連線時間過長，超過了wait_timeout和interactive_timeout的時間，可以調整wait_timeout/interactive_timeout

七.非法斷電造成mysql啟動報錯

問題描述：非法斷電造成mysql資料損壞
分析：突然斷電造成快取資料丟失，跟已刷盤的資料不一致。需要重做從庫。
問題解決：到主庫物理備份資料，恢復到從庫，恢復主從同步。
物理備份恢復過程：
主庫:
mysqlbackup --user=root --password=xxx_001 --backup-dir=/mysql/data/backup --backup-image=./dball.mbi --with-timestamp --compress-level=9 --compress-method=zlib --skip-binlog --skip-relaylog backup-to-image #備份資料
scp dball.mbi root@192.168.137.111:/mysql/data/backup/ #拷貝到目標機器backup目錄

從庫:
cd /mysql/data/backup/
chown mysql.mysql dball.mbi
su - mysql
mysql.server stop
cd /mysql/data/undo
rm -rf * #清空undo日誌
cd /mysql/data/redo
rm -rf * #清空redo日誌
cd /mysql/data/dbs
rm -rf * #清空資料
mysqlbackup --backup-image=/mysql/data/backup/dball.mbi --backup-dir=/mysql/data/backup --uncompress copy-back-and-apply-log --force #恢復資料
mysql.server start #啟動mysql
mysql -uroot -p
reset slave all; #重置
change master to master_host = ‘192.168.137.110’, master_port = 3310, master_user = ‘rpl_user’, master_password = ‘rpl_001’, master_auto_position=1 ;#設定主從同步複製
start slave;#啟動同步
show slave status\G;#檢視同步狀況

八.非法斷電造成mysql同步複製無法啟動

問題描述：relay報錯，error:look foring afer relay.000013
分析：relay得到的gtid比執行的gtid少，得到的部分gtid丟失。
show slave status\G;
Master_UUID: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee
Retrieved_Gtid_Set: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee:2290-2302(少)
Executed_Gtid_Set: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee:1-2352(大),
5324e653-f0c2-11e9-84d0-fa163e897a41:1-363,
5661dce0-e11e-11e9-ab09-fa163e897a41:1-318

問題解決：
調整gtid，以relay獲得的gtid為準，重做主從同步
reset slave all;#重置
reset master;#重置
SET @@GLOBAL.GTID_PURGED=‘37be0d7b-e11e-11e9-bafb-fa163e9dcbee:1-2302(少),5324e653-f0c2-11e9-84d0-fa163e897a41:1-363,5661dce0-e11e-11e9-ab09-fa163e897a41:1-318’;#以relay獲得的gtid為準，設定GTID_PURGED
change master to master_host = ‘192.168.137.110’, master_port = 3310, master_user = ‘rpl_user’, master_password = ‘rpl_001’, master_auto_position=1 ;#設定主從同步複製
start slave; #啟動同步
show slave status\G;#檢視同步狀況

九.mysql壓力測試，插入時間增大，壓不上去

問題描述：壓力測試，資料庫插入出現延時情況。
分析：溝通發現，mysql是個人安裝，非標準化，設定不當，例如32G的記憶體，innodb_buffer_pool_size只預設128M。
問題解決：
1.動態線上增大innodb_buffer_pool_size值。
set global innodb_buffer_pool_size=16 10241024*1024;

2.為了永久生效，在my.cnf裡面設定
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances = 8
重啟mysql

十.通過恢復檔案載入空間恢復表資料

問題描述：出現測試環境資料庫ibdata1損壞，無法啟動。
分析：通過開發環境的mysql全量備份，恢復到測試環境。由於開發環境的admin庫資料庫結構跟測試環境的admin庫一樣，但是資料不一樣，需要用原測試環境資料檔案恢復原表資料。
問題解決：
1.備份資料
mkdir backup
cp dbs/admin/* backup/
2.開發環境mysqlbackup全量備份恢復到測試環境
3.解除安裝庫admin所有表空間
mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ DISCARD tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_discard.sql
vi admin_discard.sql
刪除第一行concat(‘alter table ‘,table_name,’ DISCARD tablespace;’)
mysql -uroot -p admin <admin_discard.sql
4.拷貝回.ibd檔案
cp -f backup/*.ibd dbs/admin/
5.載入庫admin所有表空間
mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ IMPORT tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_import.sql
vi admin_import.sql
刪除第一行concat(‘alter table ‘,table_name,’ IMPORT tablespace;’)
mysql -uroot -p admin <admin_import.sql
6.啟動mysql
mysql.server start

十一.XA事務未提交，更新資料超時

問題描述：出現更新資料超時問題
分析：檢視事務程式，發現有未提交的xa事務，thread_id為0.
問題解決：

1.先列出xa事務
mysql> xa recover;
±---------±-------------±-------------±---------------------------------------------------------------------------------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data |
±---------±-------------±-------------±---------------------------------------------------------------------------------------------------------------------------------+
| 3072 | 64 | 64 | PRBMPAPP4/c734a63e-211f-4202-8aa9-78d2/174064445 3 |
±---------±-------------±-------------±---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)
xa回滾的格式是xa rollback ‘left(data,gtrid_length)’,‘substr(data,gtrid_length+1,bqual_length)’,formatid;
2.轉16制
mysql> set session autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> xa recover convert xid;
±---------±-------------±-------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| formatID | gtrid_length | bqual_length | data |
±---------±-------------±-------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 3072 | 64 | 64 | 0x5052424D50415050342F63373334613633652D323131662D343230322D386161392D373864322F3137343036343434350000000000000000000000000000000033000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 |
±---------±-------------±-------------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.回滾事務
mysql> xa rollback 0x5052424D50415050342F63373334613633652D323131662D343230322D386161392D373864322F31373430363434343500000000000000000000000000000000,0x33000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000,3072;
Query OK, 0 rows affected (0.01 sec)

十二.mysql升級5.7.26，更換驅動後原賬號連不上

問題描述：mysql從5.7.24升級到mysql5.7.26，驅動從mysql connector java更換為mariadb，業務升級前就是這個密碼，執行很久，有3套mysql，有1套重啟資料庫還不能恢復，有2套重啟資料庫恢復了
分析：mariadb驅動對sha256_password識別不好，改用低版本mysql_native_password
問題解決：
1.查詢當前密碼加密方式
SELECT user, host, plugin FROM mysql.user;
mysql> SELECT user, host, plugin FROM mysql.user;
±--------------±--------------±----------------------+
| user | host | plugin |
±--------------±--------------±----------------------+
| root | % | sha256_password |
| mysql.session | localhost | mysql_native_password |
| mysql.sys | localhost | mysql_native_password |
| mysql_om | % | sha256_password |
| rpl_user | 10.110.10.19 | sha256_password |
| root | 10.110.10.156 | sha256_password |
| xxxadmin | % | sha256_password |
| xxxuser | % | sha256_password |
| mysql | % | sha256_password |
±--------------±--------------±----------------------+
14 rows in set (0.00 sec)
2.修改加密方式
ALTER USER ‘xxxuser’@’%’ IDENTIFIED WITH mysql_native_password BY ‘XXX_1234’;
flush privileges;

十三.mysql無法啟動，資料目錄丟失

問題描述： mysql無法啟動，檢視error日誌，提示mysql.user不存在，檢視my.cnf資料所在目錄，發現目錄裡面除了ibdata1和ib_logfile，auto.cnf，沒有其他資料。
分析：掛載mysql資料庫目錄丟失。mount的盤資訊沒寫入fstab裡面，斷電重啟後，沒有掛載磁碟。
問題解決：
1.掛載資料庫目錄
mount -t ext3 /dev/sda3 /mysqldata
2.啟動mysql
service mysql start

十四.無法登陸mysql，can’t connect。。。。mysql.sock

問題描述：can’t connect 。。。。。。’/mysqldata/mysql/mysql.sock’
分析：在資料目錄找不到mysql.sock檔案或者mysql沒有啟動
問題解決：
1.找不到mysql.sock檔案
登入加上-S 引數 或者ln -s 建立軟連或者在my.cnf裡面寫上
mysql -uroot -p -S /usr/mysql.sock
或者 ln -s /usr/mysql.sock /mysqldata/mysql/mysql.sock
2.mysql沒有啟動
mysql.server start

十五.資料目錄存在非法目錄

問題描述：錯誤日誌顯示mysql error invalid (old ) table or database name ‘lost+found’
分析：datadir裡面的一個目錄代表一個資料庫，非法寫入目錄，導致mysql不能識別
問題解決：
刪除目錄’lost+found’。

十六.錯誤日誌報警告資訊長度不匹配

問題描述：[Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name. Please run mysql_upgrade
分析：5.7.17：table_name varchar(64) COLLATE utf8_bin NOT NULL,
5.7.24：table_name varchar(199) COLLATE utf8_bin NOT NULL,
可以看出，5.7.24 版本上的 innodb_index_stats 和innodb_tables_stats 的 table_name 列，長度從64 變成了 199，而升級後的5.7.24中 table_name 還是64

問題解決：
1.執行更新
mysql_upgrade -u root -p –force
2.重啟mysqld程式
mysql.server start

十七.錯誤日誌報警告資訊ignored in --skip-name-resolve mode

問題描述：[Warning] ‘user’ entry ‘mysql.session@localhost’ ignored in --skip-name-resolve mode.
分析：跳過dns解析，開啟了skip_name_resolve後出現
問題解決：
刪除不必要的賬號
delete from mysql.user where user=‘mysql.session’

十八.大量Waiting in connection_control plugin連線

問題描述:show processlist;結果超過2000條結果. 90%連線:Waiting in connection_control plugin
分析：啟動了防爆 破外掛connection_control
問題解決:
show plugins;
select * from information_schema.CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS;
1.修改引數show variables like ‘%connection_control%’;
set global connection_control_failed_connections_threshold=0;
set global connection_control_max_connection_delay=3000;

或者2.重啟mysql服務
mysql.server restart

3.kill程式
select concat('kill ', id, ‘;’) from information_schema.processlist where command= ‘Waiting in connection_control plugin’;

十九.備庫同步複製通道channel值為空

現象描述：多源複製，同步複製通道channel值為空
問題分析：需要重置複製通道
問題解決:
1.停止slave
stop slave;
2.清空同步複製關聯
reset slave all;
3.重置同步複製關聯
change master to master_host = ‘192.168.137.110’, master_port = 3310, master_user = ‘rpl_user’, master_password = ‘xxxxxxxxxx’, master_auto_position=1 for channel ‘rpl1’;
4.啟動slave
start slave;

另外，多源同步複製通道的知識點，官網和網際網路上都很少，以下是經過本人實踐的：
1.多源複製，同步複製通道channel修改。
1）update update mysql.slave_master_info set channel_name=“新通道名”;
2）service mysqld restart
3）show slave status\G; #這時候會多一條通道channel，舊通道和新通道
4）stop slave for channel “舊通道名”;
5）reset slave all for channel “舊通道名”;

2.多源複製，同步複製通道channel多一條，刪除
1）stop slave for channel “刪除通道名”;
2）reset slave all for channel “刪除通道名”;#若是通道名為空，值"".
3）show slave status\G;

二十.mysql時區跟系統資料不一致

問題描述：mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2020-05-27 06:27:24 |
±--------------------+
1 row in set (0.00 sec)
mysql> show global variables like ‘%time_zone%’;
±-----------------±-------+
| Variable_name | Value |
±-----------------±-------+
| system_time_zone | UTC |
| time_zone | SYSTEM |
±-----------------±-------+
2 rows in set (0.01 sec)
mysql@tb6usrdb2:~> date
Wed May 27 09:27:34 CEST 2020
分析：系統是CEST時區，與mysql的時區不一致，差3個小時
問題解決：
1.修改mysql時區：
set time_zone=’+2:00’; #+2,不是+3

2.結果：
mysql> show global variables like ‘%time_zone%’;
±-----------------±-------+
| Variable_name | Value |
±-----------------±-------+
| system_time_zone | UTC |
| time_zone | +02:00 |
±-----------------±-------+
2 rows in set (0.00 sec)

若是要永久生效就是在my.cnf中修改，新增default-time_zone=’+2:00’
然後重啟mysql。
mysql> show global variables like ‘%time_zone%’;
±-----------------±-------+
| Variable_name | Value |
±-----------------±-------+
| system_time_zone | CEST |
| time_zone | +02:00 |
±-----------------±-------+
2 rows in set (0.00 sec)