Mysql8.0安装过程中那些坑

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字段