【MYSQL】報錯排解記錄

 問題1.Unknown storage engine 'FEDERATED'報錯

[rosalie@test]# mysqldump -uroot -p   --all-databases > all_db_backup.sql

Enter password:

mysqldump: Got error: 1286: Unknown storage engine 'FEDERATED' when using LOCK TABLES

[rosalie@test]# mysql -uroot -p  < all_db.sql

ERROR 1286 (42000) at line 902: Unknown storage engine 'FEDERATED'

解決方式

修改my.cnf 增加  federated  字樣後，後再重啟DB，重啟後再次操作即可！

---

問題2.mysqlslap: Error when connecting to server: 1040 Too many connections 報錯

連結至MYSQL出現 Too many connections 表示連接到 MySQL 的連線數超出了 MySQL 的連線數上限, 預設上限是 100。

---

解決方式

修改 /etc/my.cnf 設定檔

開啟檔案 /etc/my.cnf, 在 [mysqld] 段落下加入這行, 設定連線數上限到 500, 參數內容類似這樣:

[mysqld]

max_connections = 500

---

 問題3.Starting MySQL. ERROR! The server quit without updating PID file報錯

[rosalie@~# vi /etc/my.cnf

增加binlog參數

log_bin = /mysql/data/mysql-bin

加入後重啟DB

[root@ data]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. ERROR! The server quit without updating PID file (/mysql/data/rosalie.pid).

解決方式

**排除權限(資料夾權限)問題、與mysql進程(ps aux | grep mysql)問題

修改my.cnf 增加server-id

server-id=1 

[root@ data]# service mysql restart

Shutting down MySQL.. SUCCESS!

Starting MySQL... SUCCESS!

---

 問題4.2019-05-06 11:35:31 11729 [ERROR] Slave SQL: Error 'SELECT command denied to user 'root'@'192.168.0.0' for table 'table_name'' on query. Default database: 'test'. Query: 'UPDATE users SET remark = 'gefegsd:[invalid_number];' WHERE loginname = 'ggggg'', Error_code: 1142

2019-05-06 11:35:31 11729 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000511' position 921455474

2019-05-06 11:35:31 11729 [ERROR] Slave SQL: Error 'SELECT command denied to user 'root'@'192.168.0.0' for table 'table_name'' on query. Default database: 'test'. Query: 'UPDATE users SET remark = 'gefegsd:[invalid_number];' WHERE loginname = 'ggggg'', Error_code: 1142

2019-05-06 11:35:31 11729 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000511' position 921455474

解決方式

#原因是因為查詢更新的表，在test的資料庫中，並沒有table_name這個表
#停止slave 再重啟即可

mysql> stop slave;

mysql> start slave;

---

問題5.ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'catalog.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

#查詢欄位ID及組合姓名，以部門ID來分組

mysql> select id,group_concat(name) from users group by department_id;

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db_crm_catalog.crm_users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

解決方式  

#查看當前sql_mode參數

mysql> SELECT @@GLOBAL.sql_mode;

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

| @@GLOBAL.sql_mode                                                                                                         |

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

| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION                                                                                                             |

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

1 row in set (0.00 sec)

#排除ONLY_FULL_GROUP_BY參數mysql5.7默認開啟only_full_group_by

mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

Query OK, 0 rows affected (0.00 sec)

mysql> select id,group_concat(name) from users group by department_id;                        

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

| id | group_concat(name)                                                                                                 |

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

| 23 | Yuna,Sonia,Fedrick,Vicky,June,Ben,Mandy,Evaine,Miya                                               |

|  2 | Jack,Zhi,Admin,PS,Lawrence,Ivan,Sze Mun,Joshua,Vincent                                          |

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

2 rows in set (0.03 sec)

---

問題6. mysql_install_db初使化失敗

[root@rosalie-mysql01 data]# mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3307

2019-05-22 16:47:56 [WARNING] mysql_install_db is deprecated. Please consider switching to mysqld --initialize

2019-05-22 16:47:59 [WARNING] The bootstrap log isn't empty:

2019-05-22 16:47:59 [WARNING] 2019-05-22T08:47:56.568382Z 0 [Warning] --bootstrap is deprecated. Please consider using --initialize instead

解決方式  

# mysql_install_db 這方法僅適用於 MySQL 5.7.7之前

[root@rosalie-mysql01 data]# /usr/local/mysql/bin/mysqld  --initialize  --user=mysql --basedir=/usr/local/mysql  --datadir=/data/mysql_3307

---

問題7. 設置全局變量報錯  ERROR 1238 (HY000): Variable 'xxx' is a read only variable

mysql> set global sha256_password_private_key_path="private_key.peme";

ERROR 1238 (HY000): Variable 'sha256_password_private_key_path' is a read only variable

解決方式 

#因該參數為只讀參數，需要在配置文件中更改該參數，之後重啟數據庫

vi /etc/my.cnf

修改參數後存檔

重啟DB

---

問題8. 啟動MYSQL報錯  Starting MySQL.. ERROR! The server quit without updating PID file (/data/mysql/rosalie-mysql02.pid).

進入MYSQL資料夾內查看ERROR.LOG

2019-08-28T06:38:23.655853Z 0 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use

2019-08-28T06:38:23.655858Z 0 [ERROR] Do you already have another mysqld server running on port: 3306 ?

2019-08-28T06:38:23.655864Z 0 [ERROR] Aborting

[root@rosalie-mysql02 data]# ps -aux | grep mysql

Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

root     21821  0.0  0.0  11432  1548 pts/2    S    14:25   0:00 /bin/sh /usr/local/mysql//bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/rosalie-mysql02.pid

mysql    21989  0.1  9.6 1867128 391416 pts/2  Sl   14:25   0:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql/ --datadir=/data/mysql --plugin-dir=/usr/local/mysql//lib/plugin --user=mysql --log-error=rosalie-mysql02.err --pid-file=/data/mysql/rosalie-mysql02.pid --socket=/tmp/mysql.sock --port=3306

root     23293  0.0  0.0 103320   884 pts/2    S+   14:39   0:00 grep mysql

[root@rosalie-mysql02 mysql]# service mysql start

Starting MySQL. SUCCESS!