侧边栏壁纸
博主头像
liuxy博主等级

细水长流,吃穿不愁

  • 累计撰写 39 篇文章
  • 累计创建 30 个标签
  • 累计收到 6 条评论

目 录CONTENT

文章目录

Mysql8.0安装过程中那些坑

liuxy
2021-06-23 / 0 评论 / 4 点赞 / 607 阅读 / 869 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-08-22,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

mysql安装过程中那些坑

修改密码

alter user 'root'@'localhost' identified with mysql_native_password by 'test@123';
flush privileges;
  • 继续修改密码操作
alter user 'root'@'%' identified with mysql_native_password by 'test@123';
flush privileges;
exit;
service mysql stop
service mysql start

update mysql.user set authentication_string=PASSWORD('test@123') where user = 'root';

创建用户,授权

create user 'test'@'%' identified by 'test@123';
grant all on *.* to 'test'@'%';
grant all on *.* to 'root'@'%';
grant all privileges on *.* to 'root'@'%' identified by 'test@123' with grant option;
  • 设置访问权限,默认是有localhost访问
update mysql.user set `host`='%' where user='root';

service mysql status

ERROR! MySQL is not running, but lock file (/var/lock/subsys/mysql) exists

cd /usr/local/mysql/
rm -rf data/
./bin/mysqld --user=mysql --lower_case_table_names=1 --initialize-insecure --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

./bin/mysqld --user=mysql --lower_case_table_names=1 --initialize-insecure --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ 

./bin/mysqld --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/ --initialize

cp -a ./support-files/mysql.server /etc/init.d/mysql

2021-06-05T10:49:04.531205Z 0 [Warning] [MY-011070] [Server] ‘Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it’ is deprecated and will be removed in a future release.
2021-06-05T10:49:04.531310Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.20) initializing of server in progress as process 30156
2021-06-05T10:49:04.532577Z 0 [Warning] [MY-013242] [Server] --character-set-server: ‘utf8’ is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2021-06-05T10:49:04.538815Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-06-05T10:49:04.957705Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-06-05T10:49:06.091245Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

service mysql start

mysql 2059

alter user 'root'@'%' identified by 'test@123' password expire never;
alter user 'root'@'%' identified with mysql_native_password by 'test@123';
flush privileges;

mysql 1920

ERROR 1290 (HY000):
The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
my.cnf

[mysqld] + skip-grant-tables -> 跳过密码
mysql -uroot 
flush privileges;

mysql 2003

linux  my.cnf  [mysqld]  bind-address=0.0.0.0
mysql -uroot -p
flush privileges;
exit;
service mysql restart

mysql: 1055

this is incompatible with sql_mode=only_full_group_by

show session VARIABLES  
show global VARIABLES  

select @@sql_mode

set session 
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

永久解决1055

在 my.cnf [mysqld]
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
mysql -uroot -p
flush privileges;
exit;
service mysql restart

mysql 1130

1130 - Host ‘127.0.0.1’ is not allowed to connect to this MySQL server

mysql -uroot -p密码
show databases;
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql> select host from user where user = 'root';
+------+
| host |
+------+
| %    |
+------+
1 row in set (0.00 sec)

看到如图所示的配置:localhost
update user set host = '%' where user ='root';

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

添加字段

alter table sys_role_permission add column operate_date datetime(0)   null default null comment '操作时间' after data_rule_ids;

alter table sys_role_permission add column operate_ip varchar(20)     character set utf8 collate utf8_general_ci null default null         comment '操作ip' after operate_date;

命令解释:
sys_role_permission:表名
operate_date:添加的字段名称
datetime(0):字段类型和长度
DEFAULT NULL:默认为NULL
‘操作时间’:字段含义
AFTER user_email:在user_email字段之后添加data_rule_ids字段

4
广告 广告

评论区