mysql常用小命令总结

//删除不必要的用户
show databases;
use mysql;
select Host,User from user;
delete from user where User!='root'
delete from user where Host!='localhost'

//支持root用户允许远程连接mysql数据库,后面的root是密码
GRANT ALL PRIVILEGES ON *.* TO 'lqlroothaha'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;
//指定数据库远程登录
GRANT ALL PRIVILEGES ON chijiavip_microp.* TO 'suxi_test'@'%'  IDENTIFIED BY 'sunxi123456' WITH GRANT OPTION;
FLUSH PRIVILEGES;



//显示所有的用户
SELECT User, Host, Password FROM mysql.user;

//创建用户
CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 
CREATE USER 'baoxian_grwlkj_'@'host' IDENTIFIED BY 'nwSjmH3NCb'; 
 GRANT ALL PRIVILEGES ON *.* TO 'baoxian_grwlkj_'@'%' IDENTIFIED BY 'nwSjmH3NCb' WITH GRANT OPTION;
//刷新用户
FLUSH PRIVILEGES;
//更新密码
UPDATE mysql.user SET password=PASSWORD('newpassword') WHERE User='root';

//mysql 链接不了本地,1067报错
//解决地址:https://jingyan.baidu.com/article/d5c4b52bee6e69da560dc5ec.html
方法一：1、打开my.ini文件，找到default-storage-engine=InnoDB这一行，把它改成default-storage-engine=MyISAM。
2、删除在MySQL安装目录下的Data目录中的ib_logfile0和ib_logfile1
3。找到在配置MySQL服务器时指定的InfoDB目录删除掉ibdata1
根据my.ini文件中：
#*** INNODB Specific options ***        innodb_data_home_dir="D:/"。4。重新启动MySQL的Service 

#MYSQL删除表的记录后如何使ID从1开始,这样不但将数据全部删除，而且重新定位自增的字段
truncate table 表名

//如果要将game数据库进行备份：
mysqldump -u root -p game > game_backup.sql

//如果希望备份所有的数据库：
mysqldump -u root -p --all-databases > all_backup.sql

lql_000_ceshi_grwlkj_co_lql