1. 安装客户端和服务器:mysql 和 mysql-server
2. 服务器启动:
systemctl start mysqld.service ##启动mysql服务器
systemctl enable mysqld.service ##使mysql服务器自动运行
手动安装MySQL-server-5.6.10时,没有systemctl启动文件,需要用service命令:service mysql start/stop
3. 客户端登录:
mysql -h 110.110.110.110 -u root -pabcd123
mysql --user=user_name -p
4. 用户管理:
添加用户:
参考:http://blog.csdn.net/shenyan008/article/details/8609446,第三部分
查看所有用户:
SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;
修改普通密码:
mysql -u root #登录mysql
UPDATE user SET password=PASSWORD("123") WHERE user='shenyan';
FLUSH PRIVILEGES;
修改root密码:
mysql> UPDATE mysql.user SET password=PASSWORD(’新密码’) WHERE User=’root’;
mysql> FLUSH PRIVILEGES;
查看用户权限:
select * from mysql.user where user='cactiuser'
show grants for user-name
5. dump和恢复
还原一个数据库:mysql -h localhost -u root -p123456 database-name< /tmp/xxx.sql
备份一个数据库:mysqldump -h localhost -u root -p123456 database-name > /tmp/xxx.sql
6. 查看 MySQL 数据库中每个表占用的空间大小
use information_schema
SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS FROM TABLES WHERE TABLE_SCHEMA='数据库名' AND TABLE_NAME='表名'
7. mysql 执行包含sql语句的文件
mysql> source 文件名
8. 权限
show grants for user_name; 显示权限
show grants for community@localhost;
grant all on *.* to 'abc'@'%';
添加权限,/usage/all/select/delete/alter/create ...,具体权限参考:http://dev.mysql.com/doc/refman/5.1/en/grant.html,
特殊权限值:usage,Synonym for “no privileges”
revoke all on `mysql`.`componet` from 'community'@'localhost'; 收回权限