Mysql使用技巧与心得

本文章仅作为个人笔记

1.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';）

2.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）

3.Mysql数据导入

    source ****.sql （例：source /tmp/test.sql，如果sql文件包含数据库的创建则无需选中数据库，如果不包含，需先选择数据库）

4.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);

5.常用命令及技巧

    默认配置文件地址：/etc/my.cnf

    显示所有数据库： show tables

    选中库：use 库名（例： use mysql）

    显示当前选择的库的所有表： show tables

    显示某表所有字段描述：desc 表名（例： desc user）

    显示主表状态：show master status

    显示从表状态：show slave status

    命令行执行sql：mysql -hlocalhost -uroot -proot -e "select * from table"  > test.txt