問題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 再重啟即可
#停止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
進入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!
沒有留言:
張貼留言