查看数据库版本
select version();
登陆数据库
mysql -u queuecloud -p
linux执行SQL
source 路径/datebase.sql(SQL文件) show databases;(查看数据库) show tables;(查看选中数据库下的所有表) use mysql(使用数据库);
数据库打开防火墙端口3306
firewall-cmd --zone=public --add-port=3306/tcp --permanent
查询用户(表)详细信息
select Host,User,Create_priv,password_last_changed from user;
查看mysql.db表 指定用户的库权限
select Host, Db ,User from mysql.db where User='zhangshuai';
查看用户对单个数据列的权限
select Host,User from mysql.columns_priv where User like 'zhangshuai';
查看用户对单个表的权限
select Host,User from mysql.tables_priv where User like 'zhangshuai';
查询数据库用户网段
select user,host from mysql.db where user='zzzd';
查看用户权限
#show grants for 用户@'网段';
show grants for zhzcdb@'10.253.163.%';
增加用户权限
#grant 权限级别 on `库名` . * to '用户名'@'用户访问范围';
grant select,insert,update,delete,create on `db_zzd` . * to `username`@'10.209.xx.%';
revoke撤销用户权限
#revoke..alter on..from
revoke insert,select,update,delete,drop,create,alter on huanqiu.* from wang@'%';
#revoke..from
revoke SELECT,INSERT,UPDATE,DELETE,CREATE ON `zzzddb`.* from 'zhzcdb'@'10.253.163.%';
mysql刷新权限,提交命令
FLUSH PRIVILEGES; #flush privileges;权限刷新
commit;#提交
修改用户访问IP范围
#rename user ..to
rename user zhzcdb@'%' to zhzcdb@'10.253.163.%';
修改数据库用户密码
UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
#5.7版本以下
update mysql.user set authentication_string=password('密码') where User="用户" and Host="10.209.6.%";
数据库备份
数据库备份恢复:mysql -u root -p test</home/test1.sql
数据库备份(忽略gtid信息):mysqldump -u root -p --set-gtid-purged=OFF userdb > userdb.sql
单库备份:mysqldump -uroot -p test >/download/testbak_$(date +%F).sql
压缩备份:mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz
多库备份;mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz
单表备份:mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql