无法启动MySQL服务器“列名table_name”中的长度不匹配

Question

在导入一个大数据库文件32 GB MySQL后，我正在尝试运行length mismatch in the column name服务器

MySQL 5.7使用rh-MySQL 57在使用命令运行服务器后，我得到了一条成功消息：

/etc/init.d/rh-mysql57-mysqld restart

Stopping rh-mysql57-mysqld:                                [  OK  ]
Starting rh-mysql57-mysqld:                                [  OK  ]

我想使用mysql登录到带有/没有密码的mysql，我得到了这个错误

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

这是error.log

2019-06-19T13:24:05.489656Z mysqld_safe Starting mysqld daemon with databases from /var/opt/rh/rh-mysql57/lib/mysql
2019-06-19T13:24:05.751511Z 0 [Note] /opt/rh/rh-mysql57/root/usr/libexec/mysqld (mysqld 5.7.24-log) starting as process 19830 ...
2019-06-19T13:24:05.816168Z 0 [Note] InnoDB: PUNCH HOLE support available
2019-06-19T13:24:05.816207Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2019-06-19T13:24:05.816225Z 0 [Note] InnoDB: Uses event mutexes
2019-06-19T13:24:05.816240Z 0 [Note] InnoDB: GCC builtin __sync_synchronize() is used for memory barrier
2019-06-19T13:24:05.816254Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-06-19T13:24:05.816268Z 0 [Note] InnoDB: Using Linux native AIO
2019-06-19T13:24:05.818025Z 0 [Note] InnoDB: Number of pools: 1
2019-06-19T13:24:05.818276Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2019-06-19T13:24:05.821067Z 0 [Note] InnoDB: Initializing buffer pool, total size = 2G, instances = 8, chunk size = 128M
2019-06-19T13:24:06.062258Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-06-19T13:24:06.095072Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2019-06-19T13:24:06.107846Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-06-19T13:24:06.275233Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-06-19T13:24:06.275514Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-06-19T13:24:06.360584Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2019-06-19T13:24:06.362431Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-06-19T13:24:06.362460Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-06-19T13:24:06.364052Z 0 [Note] InnoDB: 5.7.24 started; log sequence number 322424549252
2019-06-19T13:24:06.364899Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/opt/rh/rh-mysql57/lib/mysql/ib_buffer_pool
2019-06-19T13:24:06.365282Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-06-19T13:24:06.366882Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190619 15:24:06
2019-06-19T13:24:06.381320Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-06-19T13:24:06.381431Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-06-19T13:24:06.387318Z 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
2019-06-19T13:24:06.387370Z 0 [Note] Skipping generation of SSL certificates as certificate files are present in data directory.
2019-06-19T13:24:06.398153Z 0 [Warning] CA certificate ca.pem is self signed.
2019-06-19T13:24:06.398250Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2019-06-19T13:24:06.399654Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2019-06-19T13:24:06.424466Z 0 [Note] IPv6 is not available.
2019-06-19T13:24:06.424509Z 0 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
2019-06-19T13:24:06.424554Z 0 [Note] Server socket created on IP: '0.0.0.0'.
2019-06-19T13:24:06.436535Z 0 [Note] Failed to start slave threads for channel ''
2019-06-19T13:24:06.449394Z 0 [Note] Event Scheduler: Loaded 0 events
2019-06-19T13:24:06.449666Z 0 [Note] /opt/rh/rh-mysql57/root/usr/libexec/mysqld: ready for connections.
Version: '5.7.24-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL)
2019-06-19T13:24:07.101293Z 2 [Note] Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
2019-06-19T13:24:36.051422Z 3 [Note] Access denied for user 'root'@'localhost' (using password: YES)

据我所知，问题就在这里。

2019-06-19T13:24:06.381320Z 0 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
2019-06-19T13:24:06.381431Z 0 [Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

我试着运行mysql_upgrade，结果是

mysql_upgrade: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) while connecting to the MySQL server
Upgrade process encountered an error and will not continue

Answer 1

该问题是由于生产服务器和测试服务器的MySQL表之间的导入冲突造成的。

由于文件很大，很难修改SQL文件的内容。所以mysql数据库也被导入了。

请注意处理前请备份您的数据库！

对我们有效的解决办法和其他的一样。

1-停止mysql服务器

参考文献：https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

2-将下列参数添加到

/etc/my.cnf

参考文献：https://dev.mysql.com/doc/refman/5.7/en/system-schema.html

innodb_force_recovery = 1
skip-grant-tables

3-在数据库目录中，保存文件夹mysql，复制mysql文件夹并清空内容

4-然后执行命令

bin\mysqld --initialize

参考文献：https://dev.mysql.com/doc/refman/5.7/en/data-directory-initialization.html

5-在最后执行命令

bin\mysql_upgrade

参考文献：https://dev.mysql.com/doc/refman/5.7/en/mysql-upgrade.html

6-重新启动mysql服务器

参考文献：https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

Answer 2

我也遇到了同样的问题&在处理码头时出错。

docker exec -it container_name bash -c "mysql_upgrade -uroot -proot"

来源：upgrade in Docker?