数据库操作（账号密码忘记免密登录等）

全量
增量
------------------------------------------------------------------------------------------------------
数据库的所有权限
SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
 REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES,
 LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE,
 ALTER ROUTINE, EVENT, TRIGGER, RELOAD, SHUTDOWN, PROCESS, FILE,
 SHOW DATABASES, SUPER, REPLICATION SLAVE, REPLICATION CLIENT,
 CREATE USER, CREATE TABLESPACE
 
Flink使用的权限
grant RELOAD, REPLICATION SLAVE, REPLICATION CLIENT ON *.* to username@'host';
------------------------------------------------------------------------------------------------------
当其出现密码问题时
在my.cnf上添加skip-grant-tables
进入后修改密码

set global validate_password_policy=LOW;
set global validate_password_length=1;
update mysql.user set authentication_string=PASSWORD('root') where User='root';
退出 保存 重启

使用密码root登录root用户后
重新进入后
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';
------------------------------------------------------------------------------------------------------
数据库主从
主库操作
主库创建用户并授权 地址为其的从库地址允许网段
  GRANT RELOAD, SUPER, REPLICATION SLAVE ON *.* TO 'username'@'host' IDENTIFIED BY PASSWORD 'password'
使用该命令拿取 ID与名称
  show master status; 

从库操作
修改my.cnf的server-id=2
change master to master_host='主服务器地址',master_port=3306,master_user='主服务器创建的用户名',master_password='创建用户时设置的密码',master_log_file='主服务器显示的file名称',master_log_pos=主服务器显示position值;
------------------------------------------------------------------------------------------------------
数据库
查看表格
show tables；
查看数据库
show databases；
查看用户和host
select user,host from mysql.user;
查看用户的grant权限
show grants for 'username@'@'host';
查看数据库编码
show create database name；
创建用户
CREATE USER 'user_name'@'host' IDENTIFIED BY 'password'; 
赋予只读权限
GRANT SELECT ON `数据库名`.* TO '用户名'@'地址';

赋予用户权限
grant all privileges on event_sources.* to deveops@"172.27.%";
删除用户
drop user '用户名'@'host';

修改表数据
update mysql.user set host='172.27.%' where user='devops';
update mysql.user set host='%' where user='test';
创建带编码的数据库
create database 数据库名 default character set  utf8mb3 collate utf8mb3_bin;
修改密码
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';
set password for 'admin'@'%' = password('password');
数据库开启binlog
查看
show variables like 'log_bin'    
show variables like '%log_bin%';

模糊查询命令
select * from p_work_order_tpl_data pwotd where form_data like '%tb_middle_product_spu_relation%'
导出命令
mysqldump -h <hostname> -u <username> -p<password> --databases <database_name> > backup.sql;
查看是否开启binlog功能
mysql> SHOW VARIABLES LIKE '%log_bin%';

create user 'backup'@'172.27.%' IDENTIFIED BY 'password';
 mysqldump -h IP -u backup  -p'password' --databases dps_target > /opt/databackup/dps.sql
 mysqldump -h IP -u backup  -p'password' --databases cls > /opt/databackup/dps.sql
 result=$($dps+$cls+$eventsources+$events)
只导出数据
 mysqldump -u root -p -t Yearning2 > 3.sql
update Yearning5.core_accounts set password='pbkdf2_sha256$120000$J2BF7tIQmgNb$BU7pbkv6YaJ/bDsJzPNIPPkWZFLMUjtARjAHPufLCwM=' where username='admin';