MysqlSql

数据库操作相关

• 创建数据库

mysql> CREATE DATABASE CoamDatabase DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

• 删除数据库

mysql> DROP DATABASE CoamDatabase;

11.1.6 Configuring the Character Set and Collation for Applications

---

mysqldump

• 导出数据库:

mysqldump -u 用户名 -p 数据库名 > 导出的文件名

mysqldump -u root -p CoamDatabase > CoamDatabase.sql   (输入后会让你输入进入MySQL的密码)

(如果导出单张表的话在数据库名后面输入表名即可)

• 同时备份多个数据库

mysqldump -h hostname -u username -p password --databases db_name1 db_name2 db_namen >backup.sql

• 只备份数据库的结构,不备份数据

mysqldump -h hostname -u username -p password --no-data --databases db_name1 db_name2 db_namen >backup.sql

• 备份所有的数据库

mysqldump -h hostname -u username -p password --all-databases >backup.sql

// 其它

mysqldump —opt —skip-comments —extended-insert=false -uroot -p1234 inotseeyou_com > inotseeyou_com.sql

• 导入数据库

首先创建相应的 Database

CREATE DATABASE CoamDatabase DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
use CoamDatabase;
source CoamDatabase.sql;

mysqldump.html

---

用户相关

• 查看所有用户

mysql> SELECT User, Host, Password FROM mysql.user;

• 添加新用户 - 以下方式适用于 5.7 以前的版本

mysql> INSERT INTO mysql.user (User,Host,Password) VALUES('demouser','localhost',PASSWORD('demopassword'));

• 添加新用户 - 以下方式适用于 5.7 以后的版本

mysql> GRANT USAGE ON *.* TO 'zhangyanxi'@'localhost' IDENTIFIED BY 'yanxi312' WITH GRANT OPTION;
mysql> GRANT ALL ON *.* TO 'zhangyanxi'@'localhost';        ======> 给用户授权
mysql> flush privileges;

• 给用户授权相关

mysql> GRANT ALL PRIVILEGES ON demodb.* to demouser@localhost;
GRANT SELECT, INSERT ON *.* TO 'zhangyanxi'@'localhost';
GRANT ALL ON *.* TO 'zhangyanxi'@'%';

• 检查用户授权

mysql> SHOW GRANTS FOR 'demouser'@'localhost';

---