MySQL高级特性
MySQL管理
用户管理
CREATE USER username IDENTIFIED BY 'password'; 新建用户 CREATE USER@’%’ IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON *.* TO username@'%'; 赋予对应的权限 FLUSH PRIVILEGES;
新建用户之后可以使用如下命令来删除用户:
DROP USER username; 删除用户 DELETE FROM user where User='username'; 删除用户,需要首先use mysql
grant语句赋予用户相应的权限,通俗的讲,grant相当于以下sql语句:
grant priv_set on dbname to username;
如何查看用户的相应权限呢?
SHOW GRANTS FOR username;
如何撤销用户对应的权限?
REVOKE ALL PRIVILEGES ON dbname.table FROM username; REVOKE INSERT ON dbname.table FROM username; 撤销创建表权限
用户管理更新过后请使用FLUSH PRIVILEGES,否则可能会出现ERROR 1396错误。
表数据复制
利用select可以实现表结构与数据的同步复制。
CREATE TABLE tablename SELECT * FROM oldtablename; CREATE TABLE tablename SELECT * FROM oldtablename where id < 10; CREATE TABLE tablename LIKE oldtablename; 利用linke语句也可以实现表的复制
数据库备份与恢复
数据库的导出:
mysqldump -h x.x.x.x -u root database > backup.sql -p; 输入数据密码后数据库导出到backup.sql文件中 mysql -h x.x.x.x -u root database < backup.sql -p; 执行数据恢复操作
执行数据库的恢复操作时database必须存在,否则恢复数据时找不到对应的数据库。
利用source命令,也可以执行数据恢复操作:
mysql> use xxx; mysql> source backup.sql
MySQL的存储引擎
存储引擎就是表的类型,MySQL有多种存储引擎,不同的存储引擎有不同的存储机制、索引技术和锁定水平。
MySQL存储引擎分为事务安全表的引擎和非事务安全表的引擎,事务是数据库中一个重要概念,事务具有原子性、一致性、隔离性和持久性4中特性。事务是为了保护数据的安全性,防止数据库出现故障而导致数据库中数据不一致。事务安全表可以使用COMMIT语句合并多条语句,适合需要经常更新的表;非事务安全表由于没有事务支持,处理速度较快,存储时占用内存较小,数据表执行更新占用内存少。
MyISAM引擎
MyISAM适合读操作次数远大于写操作的数据库,不支持事务操作,由于不需要处理事务记录,其占用内存较小,查询效率较高。每个MyISAM表对应两个磁盘文件:一个是用于存储数据的数据文件,其相对路径为dbname/tablename.MYD;另一个是存储索引的索引文件,其相对路径为dbname/tablename.MYI。
InnoDB引擎
InnoDB是MySQL的默认存储引擎,InnoDB引擎管理事务安全表,使用多版本并发控制和行级锁来提供对事务的支持,除了提供事务支持外,InnoDB还支持外键约束,拥有故障恢复能力。InnoDB设计目标是以最大的效率处理海量数据,其CPU利用率是基于所有磁盘的关系数据库引擎中最高的,因此,InnoDB引擎非常适合支持事务且并发读写频率较高的数据库。
InnoDB和MyISAM对比
InnoDB和MyISAM是MySQL中最常用的两个存储引擎,这两个存储引擎各有优劣,对存储引擎的选择应该根据具体应用来选择,只有这样才能最大限度发挥MySQL数据库的性能优势。
InnoDB和MyISAM区别总结如下:
- MyISAM不支持事务处理和外键约束等高级处理,而InnoDB存储引擎支持
- MyISAM强调的是性能,其执行速度比InnoDB更快,而InnoDB存储引擎则强调的是数据安全性。
- MyISAM支持表级锁定,而InnoDB支持行级锁定。
- MyISAM支持全文索引,而InnoDB不支持。