mysql基础命令

只有查看权限。GRANT select ON ylbs_int.* TO unionx123@’%’ IDENTIFIED BY ‘unionx123’;
创建用户：CREATE USER hisuser@’%’ IDENTIFIED BY ‘&Ygia1#8’;
只可以访问某一个数据库： GRANT ALL ON bhtrans_dev.* TO hisuser@’%’ IDENTIFIED BY ‘&Ygia1#8’;

限制用户权限：
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, CREATE VIEW, SHOW VIEW ON db_test.* TO ‘user_test’@’localhost’;
‘db_test’为指定的数据库，’user_test’是限制的用户，’localhost’表示只能本地访问，如果想让远程IP访问，可将’localhost’换成’%’

清理log-bin ： reset master;
查询指定库下所有表；select table_name from information_schema.tables where table_schema=’ylbs_dev’ and table_type=’base table’;
更新用户权限：
update user set host=’%’ where User=’root’ and Host=’127.0.0.1’;
update user set host=’%’ where User=’root’ and Host=’localhost’;


update mysql.user set host=’127.0.0.2’ where user=’server’;



权限：GRANT ALL PRIVILEGES ON . TO root@’%’ IDENTIFIED BY ‘mysql!@#’ WITH GRANT OPTION;
删库和表：drop database 库名; drop table 表名；
utf8建库：CREATE DATABASE confluence DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
查看MYSQL数据库中所有用户：
mysql> SELECT DISTINCT CONCAT(‘User: ‘’’,user,’’’@’’’,host,’’’;’) AS query FROM mysql.user;
备份：./mysqldump —max_allowed_packet=1024M -uroot -p ylbs_prod -h 111.203.171.147 > ~/ylbs_prod.sql
备份所有数据库：mysqldump —max_allowed_packet=1024M -uroot -p’mysql!@#’ -A > /home/all.sql
还原：mysql -u root -p web < ~/web.sql #把web.sql导入数据库web
保存配置：flush privileges;
删除授权：Delete FROM user Where User=’root’ and Host=’0.0.0.0’;
查看mysql当前连接数 : show full processlist;
查看最大连接数: show variables like ‘%max_connections%’;
show VARIABLES like ‘%max_allowed_packet%’;
修改最大连接数： my.cnf —> max_connections=1000 服务里重起MySQL即可.
临时修改连接数： set global max_connections=1000;(设置最大连接数为1000，可以再次查看是否设置成功)
show processlist;
清除mysql-bin00001 ： reset master;