Mysql使用技巧与心得

本文章仅作为个人笔记

  • Mysql用户操作
    • 查询当前所有用户/IP/密码:
      use mysql;
      select user,host,password from user;
    • 刷新用户表权限(修改用户或者用户权限时需要运行):FLUSH privileges;
    • 添加用户:CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;(例: CREATE USER ‘test’@’localhost’ IDENTIFIED BY ‘test’;)
    • 授权给用户:GRANT privileges ON databasename.tablename TO ‘username’@’host’;(例: GRANT privileges ON mysql.user TO ‘test’@’localhost’;)
    • 授权所有权限给用户:GRANT ALL privileges ON . TO ‘username’@’host’;(例:GRANT ALL privileges ON . TO ‘test’@’localhost’;)
    • 撤销用户权限:REVOKE privilege ON databasename.tablename FROM ‘username’@’host’;(例: REVOKE privilege ON mysql.user FROM ‘test’@’localhost’;)
    • 查看当前用户所有权限:SHOW grants FOR ‘username’@’host’;(例:SHOW grants FOR ‘test’@’localhost’;)
    • 更改用户密码:SET PASSWORD FOR ‘username’@’host’ = PASSWORD(‘newpassword’);(例:SET PASSWORD FOR ‘test’@’localhost’ = PASSWORD(‘test’); )
    • 忘记root密码时更改root用户密码:
    • 先停止mysql服务 service mysql stop
    • 新开一个窗口运行 mysqld_safe –skip-grant-tables& (开启mysql安全模式)
    • 在另一个窗口运行 mysql (登陆mysql)
    • 在mysql命令行中运行 use mysq; update user set password=password(‘password’) where user = ‘username’;(例:update user set password=password(‘root’) where user = ‘root’;)
    • 删除用户:DROP USER ‘username’@’host’;(例:DROP USER ‘test’@’localhost’;)
  • Mysql数据备份
    • 只导表结构:mysqldump –opt -d dbname -u username -p > BackupName.sql(例:mysqldump –opt -d test -uroot -p > BackupName.sql)
    • 整库备份:mysqldump -u username -p –all-databases > BackupName.sql (例:mysqldump -uroot -p –all-databases > all.sql)
    • 多库备份:mysqldump -u username -p –databases dbname1 dbname2 …-> BackupName.sql(例: mysqldump -uroot -p –databases mysql test > all.sql)
    • 单库备份:mysqldump -u username -p dbname > BackupName.sql(例: mysqldump -uroot -p mysql > mysql.sql)
    • 多表备份:mysqldump -u username -p –databases dbname –tables tablename1 tablename2 …> BackupName.sql(例:mysqldump -root -p –databases test –tables tablename1 tablename2 > all.sql)
    • 单表备份:mysqldump -u username -p dbname tablename > BackName.sql(例:mysqldump -uroot -p test tablename1 > all.sql)
  • Mysql数据导入
    • source **.sql (例:source /tmp/test.sql,如果sql文件包含数据库的创建则无需选中数据库,如果不包含,需先选择数据库)
  • Mysql csv相关
    • 导出csv文件:select 字段名1,字段名2.. from dbname.tablename into outfile ‘BackupName.csv’ fields terminated by ‘,’ optionally enclosed by ” lines terminated by ‘/n’;(例:select user,host,password from user into outfile ‘/tmp/test.sql’ fields terminated by ‘,’ optionally enclosed by ” lines terminated by ‘/n’;)
    • 无法导出文件
    • 运行 SHOW VARIABLES LIKE “secure_file_priv”; 查看可输出文件夹
    • 查看可输出文件夹
      • 如果返回的结果为空或者想要更改导出目录,需要更改mysql配置,mysql默认配置文件在/etc/my.cnf,可查看是否有secure_file_priv选项,如果有可以直接更改,如果没有需要添加secure_file_priv=输出文件夹(例:secure_file_priv=/tmp/),如果更改配置需要重启mysql。
    • 导入csv文件:load data local infile ‘BackupName.csv’ into table tablename fields terminated by ‘,’ lines terminated by ‘/n’(first_name,last_name,email);
  • 常用命令及技巧
    • 默认配置文件地址:/etc/my.cnf
    • 显示所有数据库: show tables
    • 选中库:use 库名(例: use mysql)
    • 显示当前选择的库的所有表: show tables
    • 显示某表所有字段描述:desc 表名(例: desc user)
    • 显示主表状态:show master status
    • 显示从表状态:show slave status
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页