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
字段
评论区