[Err] 1071 - Specified key was too long; max key length is 767 bytes

另外一台服务器到期了
所以新购了一台服务器
今天安装好了环境
准备将数据迁移过来

执行导入SQL：source /data/sql/77dh_20200407_2034.txt
发现报错如下

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

搜索得知需要将如下变量设置为如下值：

• innodb_file_format = Barracuda
• innodb_file_format_check = ON
• innodb_file_format_max = Barracuda
  
  查看 innodb_file_format 相关变量SQL：
  show variables like '%innodb_file_format%';

更改变量SQL：
set global innodb_file_format=Barracuda;
set global innodb_file_format_check=ON;
set global innodb_file_format_max=Barracuda;

再次执行SQL发现还是报错

[Err] 1071 - Specified key was too long; max key length is 767 bytes

原因是未修改 /etc/my.cnf
增加这两行配置

innodb_large_prefix=on
innodb_file_format = BARRACUDA

重启mysql
再次执行SQL
成功执行！