# 报错：ECDSA host key for 108.61.163.242 has changed and you have requested strict checking.# Host key verification failed.#解决方案：
ssh-keygen -R 你要访问的IP地址
# vscode remote ssh
Host ALiYun
HostName 182.92.79.103
User root
Host 130-Win7t0
HostName 192.168.1.130
User win7
Port 22
IdentityFile ~/.ssh/id_rsa
ForwardAgent yes
# linuxsudovi /etc/mysql/my.cnf
[mysqld]
skip-grant-tables
# 执行sudoservice mysql restart
mysql -u root -p
# windows# 管理员身份启动终端
mysqld --skip-grant-tables
mysql -u root -p
4.2 找回或新建MySQL的用户与密码
# 设置root@localhost的登录密码为空
update mysql.user setauthentication_string='' where user="root" and host="localhost";
flush privileges;
# 查看mysql8密码策略
show variables like 'validate_password%';# mysql8修改密码策略
SET GLOBAL validate_password.length =7; -- 设置密码最小长度
SET GLOBAL validate_password.policy = LOW; -- 设置密码复杂度策略为低
SET GLOBAL validate_password.mixed_case_count =0; -- 不要求密码包含大小写字符
SET GLOBAL validate_password.number_count =0; -- 不要求密码包含数字
SET GLOBAL validate_password.special_char_count =0; -- 不要求密码包含特殊字符
# 设置root的登录密码为root123，密码加密方式为mysql_native_password，以下三类语法，择一可用的即可# 1
alter user 'root'@'%' identified with mysql_native_password by'root123';
ALTER USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';# 2
SET PASSWORD FOR 'root'@'%'= PASSWORD('root123');
SET PASSWORD FOR 'root'@'localhost'= PASSWORD('root123');# 3
UPDATE mysql.user SET authentication_string = PASSWORD('root123') WHERE user ='root' AND host='%';
UPDATE mysql.user SET authentication_string = PASSWORD('root123') WHERE user ='root' AND host='localhost';# 有时root@%用户不存在，亦或是root@localhost用户不存在，则需要创建对应用户：
CREATE USER'root'@'%' IDENTIFIED with mysql_native_password BY 'root123';
CREATE USER'root'@'localhost' IDENTIFIED with mysql_native_password BY 'root123';# 授予root用户所有权限，择一# 1
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'root123' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;# 2
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;
# Linux开启MySQL登录验证，而Windows端只需关闭终端或进程即可sudovi /etc/mysql/my.cnf
[mysqld]# skip-grant-tablessudoservice mysql restart
4.3 mysql的user表的plugin列丢失
ALTER TABLE mysql.user ADD COLUMN `plugin` CHAR(64) DEFAULT 'mysql_native_password';
4.4 mysql允许外网访问
# mysql8需要编辑/etc/mysql/my.cnf修改如下内容：[mysqld]
bind-address=0.0.0.0 # 绑定本地ip而非127.0.0.1# 允许执行脚本时输入密码[mysqldump]user=root
password=root
# 登入MySQL的root用户修改时如下内容：
use mysql;select host, user from user;# 修改用户的访问ip权限，如果是%就不用改
update user sethost='%' where user='root';
FLUSH PRIVILEGES;
4.5 创建mysql用户并分配权限
# 加密方式为mysql_native_password
CREATE USER'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';
CREATE USER'root'@'%' IDENTIFIED WITH mysql_native_password BY 'root123';# 或 以下方法
CREATE USER'root'@'%';
CREATE USER'root'@'localhost';
SET PASSWORD FOR 'root'@'%'= PASSWORD('root123');
SET PASSWORD FOR 'root'@'localhost'= PASSWORD('root123');# 或 以下方法
CREATE USER'root'@'%';
CREATE USER'root'@'localhost';
alter user 'root'@'%' identified with mysql_native_password by'root123';
alter user 'root'@'localhost' identified with mysql_native_password by'root123';# 或 授予一个用户所有权限，同时若该用户不存在则默认创建该用户
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;
4.6 解决报错（ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’）或报错（Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (111)）
# sqlplus登录oracle
sqlplus sys/root123 as sysdba
sqlplus sys/root123 as sysoper
sqlplus root/root123
# 登录到指定数据库
sqlplus root/root123@paradb
# 查看当前登录到的用户
SELECT USER FROM DUAL;# 查看当前登录连接到的数据库
SELECT name FROM v$database;
SELECT instance_name FROM v$instance;
SELECT value FROM v$parameter WHERE name ='service_names';
SELECT sys_context('USERENV', 'DB_NAME') AS database_name FROM dual;# 查看当前用户拥有的所有表
SELECT table_name FROM USER_TABLES;
SELECT table_name FROM TABS;
SELECT table_name FROM ALL_TABLES WHERE owner ='ROOT';
SELECT table_name FROM DBA_TABLES WHERE owner ='ROOT';# 查看数据库所有表
SELECT table_name FROM ALL_TABLES;
SELECT table_name FROM DBA_TABLES;# 查看所有的数据库
SELECT name FROM v$services;# 查看所有用户（一个用户绑定一个模式）
SELECT username FROM ALL_USERS;
SELECT username FROM DBA_USERS;# 查看拥有表的所有用户
SELECT DISTINCT OWNER FROM ALL_TABLES;
SELECT DISTINCT OWNER FROM DBA_TABLES;# 查看某个用户的系统权限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE ='ROOT';# 查看某个用户的角色权限
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE ='ROOT';# 查看某个用户直接拥有的对于哪些表的权限
SELECT * FROM ALL_TAB_PRIVS_RECD WHERE GRANTEE ='ROOT';
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE ='ROOT';# 查看某个用户直接拥有的对于哪些表的某些列的权限
SELECT * FROM DBA_COL_PRIVS WHERE GRANTEE ='你的用户名';# 查看TNS解析数据库ORCL服务
tnsping orcl
# 停止oracle监听器
lsnrctl stop
# 启动oracle监听器
lsnrctl start
# 查看oracle监听器状态（服务名、ip、端口）
lsnrctl status
# 导入dmp数据
imp root/root123 -file=./PW_para.dmp fromuser=paradb touser=root
# 导入dmp数据，指定编码
imp root/root123 -file=./PW_para.dmp fromuser=paradb touser=root charset=ZHS16GBK
# 查看当前默认字符编码select userenv('language') from dual;
SELECT * FROM nls_database_parameters WHERE parameter ='NLS_CHARACTERSET';# 修改字符集
ALTER DATABASE CHARACTER SET ZHS16GBK;# 设置编码环境变量，不需在导入命令中使用charset参数指定编码
NLS_LANG
AMERICAN_AMERICA.ZHS16GBK
# 表空间'USER_DATA'不存在，创建
CREATE TABLESPACE USER_DATA DATAFILE 'user_data.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M;# 检查表空间USER_DATA是否已存在
SELECT tablespace_name FROM dba_tablespaces WHERE tablespace_name ='USER_DATA';
1703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
