![[PukiWiki]](image/pukiwiki.png "[PukiWiki]")
Tag: MySQL8
SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8mb4_bin;") AS ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="mydb"
AND TABLE_TYPE="BASE TABLE";
mysql --skip-column-names < collate.sql > collate_change.sql
ALTER TABLE mydb.test_table COLLATE utf8mb4_bin; (...)
mysql < collate_change.sql
SELECT user, host, plugin FROM mysql.user;
[mysqld] default_authentication_plugin=mysql_native_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pass';
SHOW MASTER LOGS;
PURGE MASTER LOGS before now();
disable-log-bin
2022-06-22T05:20:50.904777Z 4 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement '-- Copyright (c) 2018, 2021, Oracle and/or its affiliates. -- -- This program is free software; you can redistribute it and/or modify -- it under the terms of the GNU General Public License as published by -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA -- -- Alters the sys_config table for upgrades -- ALTER TABLE sys_config CHARACTER SET utf8mb4; ' failed with error code = 3958, error message = 'Failed to delete sdi for sys.sys_config in sys/sys_config due to missing record.'. 2022-06-22T05:20:50.907978Z 0 [ERROR] [MY-013380] [Server] Failed to upgrade server.
/opt/local/lib/mysql8/bin/mysqld --user=_mysql --upgrade=MINIMAL
$ mysqldump --no-data --routines --databases sys > sys_dump.sql #別のマシン $ mysql < sys_dump.sql # エラーが発生したマシン
mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'root'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
SELECT * FROM mysql.component;
bind-address=0.0.0.0 skip-networking=0
$ mysql -b -e "SHOW GLOBAL VARIABLES like 'bind_address'"
select user,host from mysql.user; | root | localhost |
create user root@'%' identified by 'hogehoge'; GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
mysql > GRANT PROCESS ON *.* TO "your-user";
