當(dāng)前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

【MySQL】日常整理记录分享

發(fā)布時間：2025/3/20 数据库 31 豆豆

生活随笔收集整理的這篇文章主要介紹了【MySQL】日常整理记录分享小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

文章目錄

優(yōu)化調(diào)試
- - 1. 慢日志:
語法
- - 1. group_concat:
環(huán)境配置
- - 1. [MySQL 數(shù)據(jù)庫安全加固](https://vxhly.github.io/2016/10/mysql-database-user-policy/)
  - 1. MySQL 導(dǎo)入sql 錯誤 Got a packet bigger than 'max_allowed_packet' bytes
  - 2. mysqli::real_connect(): Headers and client library minor version mismatch
  - 3. Unable to start MySQL/MariaDB after changing the innodb_log_file parameter: log file ./ib_logfile0 is of different size
  - 4. Unable to update Plesk: Aria engine is not enabled or did not start
  - 5. MySQL 連接IPv6數(shù)據(jù)庫
  - 6. 查看MySQL日志
  - 7. [配置主從數(shù)據(jù)庫](https://www.cnblogs.com/hanyucq/p/5524921.html)
  - 8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
  - 9. mysql 默認(rèn)不區(qū)分大小寫
  - 10. mysql 索引過長1071-max key length is 767 byte
  - 11. unknown variable 'bind-address=0.0.0.0'
  - 12. [解決 MariaDB無密碼就可以登錄的問題](https://blog.csdn.net/qq_29663071/article/details/81735603)
  - 13. 報錯：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var /lib/mysql/mysql.sock' (2)
  - 14. Can't init tc log
  - truncate 清除所有表數(shù)據(jù)

優(yōu)化調(diào)試

1. 慢日志:

配置慢日志: http://www.cnblogs.com/luyucheng/p/6265594.html
pt-query-digest詳解慢查詢?nèi)罩? http://www.ywnds.com/?p=8179

語法

1. group_concat:

分組查詢按照分組字段分組顯示
函數(shù)CONCAT及GROUP_CONCAT的使用

環(huán)境配置

1. MySQL 數(shù)據(jù)庫安全加固

1. MySQL 導(dǎo)入sql 錯誤 Got a packet bigger than ‘max_allowed_packet’ bytes

在測試mysql備份文件是否可正常還原時，即將備份文件還原到另一臺服務(wù)器實例上，出現(xiàn)了一系列的錯誤。
其中的錯誤如下：
[Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes [Err] 1046 - No database selected [Err] 1231 - Variable 'collation_connection' can't be set to the value of 'NULL'
………………

原因：
主要為第一個錯誤。導(dǎo)入數(shù)據(jù)包大于系統(tǒng)設(shè)置的 max_allowed_packet 大小。
還原的實例版本為：mysql 5.7.13-log
默認(rèn) max_allowed_packet 大小為：4M
在 mysql 中，1個 sql 語句發(fā)送到 MySQL 服務(wù)器，1行數(shù)據(jù)發(fā)送到客戶端，或者二進(jìn)制日志從 master 發(fā)送到 slave ，這些都作為一個包，（mysql 5.7 最大包為 1GB）。而我的備份腳本中，有的表字段類型為 longtext，其插入的值比較長，才導(dǎo)致了錯誤。
解決方法：

4M 既然不夠，那就設(shè)置 20 MB 吧。

查看當(dāng)前 max_allowed_packet 大小：
show variables like 'max_allowed_packet'; show variables where Variable_name = 'max_allowed_packet';
在命令行設(shè)置全局變量，新的連接立即生效：（windows 中寫字節(jié)大小）
set global max_allowed_packet = 20*1024*1024;

同時在配置文件中添加以下參數(shù)：max_allowed_packet = 20M

2. mysqli::real_connect(): Headers and client library minor version mismatch

yum remove php-mysql yum install php-mysqlnd// 重啟mysql服務(wù) systemctl start mysqld// 第一個檢索的“password”后面root@localhost: 后面跟著的密碼應(yīng)該就是默認(rèn)密碼了。 grep password /var/log/mysqld.log

3. Unable to start MySQL/MariaDB after changing the innodb_log_file parameter: log file ./ib_logfile0 is of different size

解決方案

4. Unable to update Plesk: Aria engine is not enabled or did not start

解決方案

5. MySQL 連接IPv6數(shù)據(jù)庫

項目配置: [ipv6地址] (用方括號括起來)

服務(wù)器上: --bind-address=ipv6地址
為支持雙棧, 還可以 --bind-address= ::

6. 查看MySQL日志

# 查看mysql啟動失敗的日志，從日志內(nèi)容尋找解決方案cat /var/log/mysqld.log | tail -30

7. 配置主從數(shù)據(jù)庫

Failed to load slave replication state from table mysql.gtid_slave_pos: 1932: Table 'mysql.gtid_slave_pos' doesn't exist in engine
解決方案
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
第一步: 空格問題看主庫 MySQL配置文件(/etc/my.cnf)中 log-bin=/var/lib/mysql/binlog.index 是否有空格第二步: 看文件查看binlog.index ,或者說查看/var/lib/mysql 的文件情況第三步: 從庫操作mysql > stop slave;mysql > reset slave;mysql > start slave;
重置主從: https://www.cnblogs.com/sunyuxun/archive/2012/09/13/2683338.html
ERROR 1201 (HY000): Could not initialize master info structure for ''; more error messages can be found in the MariaDB error log
重置從庫:　reset slave; -> start slave; -> show slave status\G; OK
MySQL主從不一致情形與解決方法
https://blog.csdn.net/hardworking0323/article/details/81046408?utm_source=blogxgwz0
slave 2013 lost connection to MySQL server at 'reading initial communication packet
[mysqld] 段增加一個啟動參數(shù) skip-name-resolve
Error ‘Can’t find any matching row in the user table’ on query. Default database: ‘’. Query: ‘GRANT CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON . TO ‘mysqld’@’%’ WITH GRANT OPTION’
刪除mysqld 的從庫權(quán)限 revoke replication slave, replication client on *.* from 'mysqld'%'; 報錯: error 1290 (hy000) the mysql server is running with the --skip-grant-tables 執(zhí)行: flush privileges; 重新執(zhí)行: revoke replication slave, replication client on *.* from 'mysqld'%'; 再執(zhí)行 : flush privileges;

8. ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

https://www.cnblogs.com/tongxiaoda/p/7873478.html 檢測配置mysql> SHOW VARIABLES LIKE 'validate_password%'; +--------------------------------------+--------+ | Variable_name | Value | +--------------------------------------+--------+ | validate_password_check_user_name | OFF | | validate_password_dictionary_file | | | validate_password_length | 8 | | validate_password_mixed_case_count | 1 | | validate_password_number_count | 1 | | validate_password_policy | STRONG | | validate_password_special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.02 sec)mysql> set password=password('abc'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set password=password('mysql2017'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set password=password('mysql@)!&'); ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set password=password('MY@)!&sql2017'); Query OK, 0 rows affected, 1 warning (0.00 sec)關(guān)于validate_password_policy-密碼強(qiáng)度檢查等級：Policy Tests Performed 0 or LOW Length 1 or MEDIUM Length; numeric, lowercase/uppercase, and special characters 2 or STRONG Length; numeric, lowercase/uppercase, and special characters; dictionary file

9. mysql 默認(rèn)不區(qū)分大小寫

通過查詢資料發(fā)現(xiàn)需要設(shè)置collate（校對）。 collate規(guī)則：

_bin: 表示的是binary case sensitive collation，也就是說是區(qū)分大小寫的
_cs: case sensitive collation，區(qū)分大小寫
_ci: case insensitive collation，不區(qū)分大小寫
解決方法：

可以將查詢條件用binary。比如： select * from TableA where binary columnA ='aaa';

可以修改該字段的collation 為 binary

創(chuàng)建表時，進(jìn)行設(shè)置

create table some_table(str char(20) binary )

10. mysql 索引過長1071-max key length is 767 byte

問題 create table: Specified key was too long; max key length is 767 bytes原因數(shù)據(jù)庫表采用utf8編碼，其中varchar(255)的column進(jìn)行了唯一鍵索引而mysql默認(rèn)情況下單個列的索引不能超過767位(不同版本可能存在差異)于是utf8字符編碼下，255*3 byte 超過限制解決 1 使用innodb引擎； 2 啟用innodb_large_prefix選項，將約束項擴(kuò)展至3072byte； 3 重新創(chuàng)建數(shù)據(jù)庫；my.cnf配置： default-storage-engine=INNODB innodb_large_prefix=on一般情況下不建議使用這么長的索引，對性能有一定影響；原文鏈接：https://www.cnblogs.com/littleatp/p/4612896.html參考文檔： https://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

11. unknown variable ‘bind-address=0.0.0.0’

出現(xiàn)問題的情況 [mysql] bind-address=:: (127.0.0.1 或者 0.0.0.0)解決方案：在 /etc/mysql/my.cnf 中添加 [mysqld] bind-address=::

12. 解決 MariaDB無密碼就可以登錄的問題

1 正常mysql 2 mysql> select user, plugin from mysql.user where plugin = 'mysql_native_password'; 3 +-----------+-----------------------+ 4 | user | plugin | 5 +-----------+-----------------------+ 6 | root | mysql_native_password | 7 +-----------+-----------------------+ 8 8 rows in set (0.00 sec)1 不正常的 2 3 MariaDB [(none)]> select user, plugin from mysql.user; 4 +------+-------------+ 5 | user | plugin | 6 +------+-------------+ 7 | root | unix_socket | 8 +------+-------------+ 9 1 row in set (0.00 sec) 看到這里應(yīng)該發(fā)現(xiàn)問題了，按照正常的修改就行了如下：1 sudo service mysql stop2 sudo mysqld_safe --skip-grant-tables3 進(jìn)去mysql執(zhí)行如下命令：4 MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('mypassword'), plugin = 'mysql_native_password' WHERE User = 'root' AND Host = 'localhost';5 MariaDB [(none)]> FLUSH PRIVILEGES;6 驗證：7 MariaDB [(none)]> select user, plugin from mysql.user8 -> ;9 +------+-----------------------+ 10 | user | plugin | 11 +------+-----------------------+ 12 | root | mysql_native_password | 13 +------+-----------------------+ 14 1 row in set (0.01 sec) 15 16 先殺死m(xù)ysql kill -9 pid 17 啟動： 18 sudo service mysql start 最后驗證下：需要密碼了root@ubuntu:~# mysql ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) root@ubuntu:~#

13. 報錯：ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var /lib/mysql/mysql.sock’ (2)

一般故障出現(xiàn)時沒有切換到mysql用戶，造成權(quán)限有問題，無法創(chuàng)建mysql授權(quán)表，所以也就無法創(chuàng)建/tmp/mysql.sock 和hostname.pid文件。所以要重新授權(quán)然后安全啟動

mysql_install_db //重建授權(quán)表 mysqld_safe & //mysqld安全啟動 systemctl restart apache2 //重啟服務(wù)器

14. Can’t init tc log

MariaDB Fix “Can’t init tc log” Error February 27, 2018 at 12:12 · Filed under SoftwareImessed up my automation of backups, meaning that after two years my entire VPS had secretly filled up. This lead to MariaDB being unable to initialize. After taking care of the root cause MariaDB still refused to start.$ sudo tail -3 /var/log/mysql/error.log 2018-02-20 12:07:45 140649776292416 [Note] Recovering after a crash using tc.log 2018-02-20 12:07:45 140649776292416 [ERROR] Can't init tc log 2018-02-20 12:07:45 140649776292416 [ERROR] Aborting解決 just removing the zero byte `/var/lib/mysql/tc.log` file took care of the problem. 只需刪除零字節(jié)的“ /var/lib/mysql/tc.log”文件即可解決此問題。

truncate 清除所有表數(shù)據(jù)

select CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') from INFORMATION_SCHEMA.TABLES where table_schema in ('db1','db2');

查詢結(jié)果：

+------------------------------------------------------------+ | CONCAT('truncate TABLE ',table_schema,'.',TABLE_NAME, ';') | +------------------------------------------------------------+ | truncate TABLE db1.tablename1; | | truncate TABLE db1.tablename2; | | truncate TABLE db1.tablename3; | | truncate TABLE db2.tablename1; | | truncate TABLE db2.tablename2; | | truncate TABLE db2.tablename3; | +------------------------------------------------------------+

整理格式，執(zhí)行

truncate TABLE db1.tablename1; truncate TABLE db1.tablename2; truncate TABLE db1.tablename3; truncate TABLE db2.tablename1; truncate TABLE db2.tablename2; truncate TABLE db2.tablename3;

總結(jié)

以上是生活随笔為你收集整理的【MySQL】日常整理记录分享的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。

MySQL

上一篇：【 Laravel 】日常整理记
下一篇：【Prometheus Pushgat