标签MySql

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes 解决方案：

1：启用系统变量innodb_large_prefix
注意：光有这个系统变量开启是不够的。必须满足下面几个条件：
1：系统变量innodb_large_prefix为ON
2：系统变量innodb_file_format为Barracuda
3：ROW_FORMAT为DYNAMIC或COMPRESSED

mysql> show variables like '%innodb_large_prefix%';
mysql> set global innodb_large_prefix=on;

show variables like '%innodb_file_format%';
set global innodb_file_format=Barracuda;

对脚本进行修改，添加ROW_FORMAT=DYNAMIC
ALTER TABLE TEST ROW_FORMAT=DYNAMIC;
create table test (........) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

ERROR 1146 (42S02): Table ‘库名.表名’ doesn’t exist

1. 首先退出mysql exit
2. 在命令行界面输入：

mysql_upgrade -uroot -p --force

3. 然后重启mysql即可
   windows重启mysql命令是:
   net stop mysql #关闭mysql
   net start mysql #开启mysql

access denied

vim /etc/my.cnf文件；
[mysqld]
skip-grant-tables
flush privileges;
set password for ‘root’@‘localhost’=password(‘Admin123@qwe’);

sudo systemctl restart mysqld

时间错误

mysql –u root –p
确认时区
show variables like '%time_zone%';
set global time_zone = '+8:00';  ##修改mysql全局时区为北京时间，即我们所在的东8区
flush privileges;  #立即生效

vim /etc/mysql/my.cnf
##在[mysqld]区域中加上
default-time_zone = '+8:00'

修改jdbc url连接数据库参数 添加serverTimezone=Asia/Shanghai参数

sudo docker pull mysql:5.6

sudo docker run -itd -p 3306:3306 --name mysql -v ~/mysql/data:/var/lib/mysql -v ~/mysql/conf:/etc/mysqll/conf.d -v ~/mysql/logs:/var/log/mysql  -e MYSQL_ROOT_PASSWORD=PW123456 --restart always mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
PS:The default configuration for MySQL can be found in /etc/mysql/my.cnf, which may !includedir additional directories such as /etc/mysql/conf.d or /etc/mysql/mysql.conf.d. Please inspect the relevant files and directories within the mysql image itself for more details.

远程不能访问时需要进入docker本地客户端设置远程访问账号

$ sudo docker exec -it mysql bash
$ mysql -u root -p PW123456
mysql> grant all privileges on *.* to root@'%' identified by "password";

修改MySQL配置文件有两种方法：
一是进入容器，修改容器里的MySQL的配置文件，然后重新启动容器：
docker exec -it mysqlserver /usr/bin/bash
然后可以进入容器的命令行模式，接着修改 /etc/mysql/my.cnf 文件即可
二是挂载主机的mysql配置文件 -v /my/custom:/etc/mysql/conf.d

查看编码

show variables like 'character%';
show variables like 'collation_%';