目录
1.1、更改my.cnf文件,在[mysqld]模块下添加skip-grant-tables
1.4、去掉skip-grant-tables,注释掉,更改my.cnf文件,在[mysqld]模块下添加skip-grant-tables
2.1、开启远程访问权限的方式,主要包括两种:改表法和授权法
一、mysql 常见问题
1、mysql密码忘记,重置密码(mysql 5.7)
1.1、更改my.cnf文件,在[mysqld]模块下添加skip-grant-tables
[mysqld]
server-id=1306
port=3306
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql/
max_connections=2000
wait_timeout=86400
interactive_timeout=86400
bulk_insert_buffer_size=120M
max_allowed_packet=32M
net_buffer_length=256k
sort_buffer_size=2M
innodb_buffer_pool_size=1280M
innodb_log_buffer_size=64M
transaction-isolation=READ-COMMITTED
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
log_bin_trust_function_creators=true
character_set_server=utf8
collation_server=utf8_bin
lower_case_table_names=1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip-host-cache
skip-name-resolve
skip-grant-tables
1.2、重新启动mysql
# docker 重启方式
docker-compose down
docker-compose up -d
# systemctl 系统服务重启方式
systemctl restart mysqld
1.3、登录mysql
root@sddi-24:/# mysql -uroot -p -hlocalhost
Enter password: 此处直接回车
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> UPDATE user SET authentication string=password('newpassword') WHERE User='root';
mysql> flush privileges;
mysql> exit
Bye
1.4、去掉skip-grant-tables,注释掉,更改my.cnf文件,在[mysqld]模块下添加skip-grant-tables
[mysqld]
server-id=1306
port=3306
log-bin=mysql-bin
log-slave-updates=1
datadir=/var/lib/mysql/
max_connections=2000
wait_timeout=86400
interactive_timeout=86400
bulk_insert_buffer_size=120M
max_allowed_packet=32M
net_buffer_length=256k
sort_buffer_size=2M
innodb_buffer_pool_size=1280M
innodb_log_buffer_size=64M
transaction-isolation=READ-COMMITTED
binlog_format=ROW
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
symbolic-links=0
log_bin_trust_function_creators=true
character_set_server=utf8
collation_server=utf8_bin
lower_case_table_names=1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
skip-host-cache
skip-name-resolve
# skip-grant-tables
1.5、重新启动mysql
# docker 重启方式
docker-compose down
docker-compose up -d
# systemctl 系统服务重启方式
systemctl restart mysqld
1.6、如过提示密码需要重置
ALTER USER 'root'@'localhost' IDENTIFIED BY '<new_password>';
2、开启远程访问
2.1、开启远程访问权限的方式,主要包括两种:改表法和授权法
- 查看MySQL当前访问远程访问权限
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 User,authentication_string,Host from user;
+---------------+-------------------------------------------+-----------+
| User | authentication_string | Host |
+---------------+-------------------------------------------+-----------+
| root | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| mysql.session | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| mysql.sys | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | localhost |
| root | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx | % |
- 方式一:改表法
顾名思义,该方法就是直接修改更改"mysql"数据库里的"user"表里的"host"项,从"localhost"改为"%"
update user set host='%' where user='root';
- 方式二:授权法
通过GRANT命令可以授予主机远程访问权限
-- 赋予任何主机访问权限:
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
-- 允许指定主机(IP地址)访问权限:
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'root' WITH GRANT OPTION;
-- 通过GRANT命令赋权后,需要通过FLUSH PRIVILEGES刷新权限表使修改生效:
flush privileges;