1. Mysql Version
2.install & uninstall
2.1 Linux
3.Configure
3.1 设置IP访问
3.1.1 修改my.cnf配置,指定IP
在unbuntu下找到/etc/mysql/mysql.conf.d/mysqld.cnf文件,
把bind-address=127.0.0.1修改bind-address=0.0.0.0
#tips:
# vi 会乱码,vim编辑好使。
3.1.2 创建用户并设置权限
一般用第二种更好点,虽然麻烦。
3.1.2.1 方法一:改表法
--登入mysql后,更改"mysql"数据库中"user"表里的 "host" 字段,把"localhost"改称"%",即可。
mysql -u root -p
--输入密码
mysql>use mysql;
mysql>update user set host = '%' where user = 'root';
mysql>select host, user from user;
3.1.2.2 方法二:授权法
--mysql 8.0 前版本语法
--例如,你想用户myuser使用密码mypassword通过 IP 地址连接到 MySQL 服务器,使用:
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
--如果你想允许用户myuser从 IP 为192.168.1.3的主机连接到 MySQL 服务器,并使用mypassword作为密码
GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY '123456' WITH GRANT OPTION;
flush privileges;
--第一行命令解释如下,*.*:第一个*代表数据库名;第二个*代表表名。这里的意思是所有数据库里的所有表都授权给用户。root:授予root账号。“%”:表示授权的用户IP可以指定,这里代表任意的IP地址都能访问MySQL数据库。“password”:分配账号对应的密码,这里密码自己替换成你的mysql root帐号密码。
--第二行命令是刷新权限信息,也即是让我们所作的设置马上生效。
--mysql8.0后版本语法
create user root@'%' identified by '123456';
grant all privileges on *.* to root@'%' with grant option;
flush privileges;
--指定其他IP能访问本机mysql服务
--例指定192.168.1.3能访问本机mysql服务
create user roo@'192.168.1.3' identified by '123456'; --给ip 192.168.1.3创建roo账号并设置密码'123456'连接至本机服务器。
grant all privileges on *.* to roo@'192.168.1.3' with grant option; --授予所有数据库及表权限给该账号
flush privileges; --刷新权限信息,使其立即生效。
3.2 防火墙设置
关闭防火墙,或设置指定端口可通过防火墙。
3.2.1 ubuntu
sudo ufw enable|disable
sudo ufw default allow|deny #设置默认策略 (比如 “mostly open” vs “mostly closed”)
sudo ufw allow|deny [service] #打开或关闭某个端口,例如:
sudo ufw allow 53 #允许外部访问53端口(tcp/udp)
sudo ufw deny 53 #关闭外部访问53端口(tcp/udp)
3.3 误删root用户,解决办法(ubuntu)
1.暂停mysql服务
systemctl stop mysql.service
2.修改mysql配置文件
vim /etc/mysql/mysqld.cnf
在最下面加上这句并保存:
skip-grant-tables
3.启动mysql服务就可以直接进入了。
4.增加root用户
use mysql;
insert into user set user='root',Host='localhost',ssl_cipher='',x509_issuer='',x509_subject='';
5.添加所有权限
update mysql.user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Reload_priv='Y',Shutdown_priv='Y',Process_priv='Y',File_priv='Y',Grant_priv='Y',References_priv='Y',Index_priv='Y',Alter_priv='Y',Show_db_priv='Y',Super_priv='Y',Create_tmp_table_priv='Y',Lock_tables_priv='Y',Execute_priv='Y',Repl_slave_priv='Y',Repl_client_priv='Y',Create_view_priv='Y',Show_view_priv='Y',Create_routine_priv='Y',Alter_routine_priv='Y',Create_user_priv='Y',Event_priv='Y',Trigger_priv='Y',Create_tablespace_priv='Y' where User='root' and Host='localhost';
6.重新设置root用户密码
use mysql;
UPDATE mysql.user SET authentication_string='1' WHERE user='root' and host='localhost';
7.暂停mysql服务,恢复2.增加的内容
8.重新启动mysql服务,用 mysql -u root-p登录。
至此全部完成。
4. Grammar
4.1 注释
4.2 其他注意事项
4.3 Sql语法构成
DML(data manipulation language)是数据操纵语言:
它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言。
DDL(data definition language)是数据定义语言:
DDL比DML要多,主要的命令有CREATE、ALTER、DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用。
DCL(DataControlLanguage)是数据库控制语言:
是用来设置或更改数据库用户或角色权限的语句,包括(grant,deny,revoke等)语句。
4.3.1 Column Type
4.3.2 操作符
4.3.3 Common Command
systemctl status mysql.service
systemctl start mysql.service
systemctl stop mysql.service
systemctl restart mysql.service
--查
show engines; --查看支持的引擎
show table status like 'user' \G --查看已有表信息
show databases;
select database(); --查看当前所在数据库名
use databasename;
show tables;
--增
create database yourdb;
CREATE TABLE user(
username char(50) NULL,
passwd char(50) NULL
)ENGINE=InnoDB;
insert into tablename(column1,column2) values(value1,value2);
--改
update tablename set column1 = value1 where column2 = 1
--删
drop table tablename;
drop table if exists tablename ;
delete from tablename where column1<2
--other
exit --退出连接
/q --退出连接
4.4 Mysql Common Engines
4.4.1 InnoDB
InnoDB是MySQL默认的事务型引擎,也是最重要、最广泛的存储引擎。
它的设计是用来处理大量短期事务,短期事务大部分是正常提交的,很少回滚。
InnoDB的性能和自动崩溃恢复特性,使得它在非事务型存储的需求中,也很流行。除了非常特别的原因需要使用其他引擎,InnoDB也是非常好值得花时间研究的对象。
InnoDB的数据存储在表空间中,表空间是由InnoDB管理的黑盒文件系统,由一系列系统文件组成。
InnoDB可以将每个表的数据和索引存放在单独的文件中。InnoDB也可以使用裸设备作为表空间存储介质。
InnoDB通过间隙锁(next-key locking)防止幻读的出现。
InnoDB是基于聚簇索引建立,与其他存储引擎有很大的区别,聚簇索引对主键查询有很高的性能,不过它的二级索引(secondary index,非主键索引)必须包含主键列。
所以如果主键列很大的话,索引会很大。
4.4.2 MyISAM
在5.1之前,MyISAM是默认的引擎,MyISAM有大量的特心态,包括全文索引、压缩、空间函数。但是MyISAM不支持事务和行级锁,而且在崩溃后无法安全恢复。即使后续版本中MyISAM支持了事务,但是很多人的概念中依然是不支持事务的引擎。
MyISAM并不是无所事处。对于一些只读数据,或者表空间较小,可以忍受恢复操作,可以使用MyISAM。MyISAM会将表存储在两个文件中:数据文件、索引文件。分别是.MYD、.MYI扩展名。MyISAM表可以包含动态或者静态行。MySQL会根据表定义选择那种行格式。MyISAM表的行记录数,取决于磁盘空间和操作系统中的单个文件最大尺寸。
在MySQL中,默认配置只能存储256TB的数据。因为指向数据记录的指针长度是6字节。需要修改可以修改表的MAX_ROWS和AVG_ROW_LENGTH选项。两个相乘是最大的大小。会导致重建索引。
MyISAM是对整个表加锁,而不是行锁,读取的时候对表加共享锁,写入的时候加排他锁。但是在表有读取查询的同时,也可以往表内写入记录。
对于MyISAM,即使是Blob,Text等等长字段,也可以基于前500字符创建索引,MyISAM支持全文索引,这是一个基于分词创建的索引,也可以支持复杂的查询。
MyISAM可以选择延迟更新索引键,在创建表的时候指定delay_key_write选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘,而是写到缓存区,只有在清理缓存区或者关闭表的时候才会将索引写入磁盘。这可以极大的提升写入性能,但是在主机崩溃时会造成索引损坏,需要执行修复操作。
MyISAM另一个特性是支持压缩表。如果数据在写入后不会修改,那么这个表适合MyISAM压缩表。可以使用myisampack对MyISAM表进行打包,压缩表是不可以修改数据的。压缩表可以极大的减少磁盘占用,因此可以减少磁盘IO,提升性能,压缩表也支持索引,但是索引也是只读的。
整体来说MyISAM并没有那么不堪,但是由于没有行锁机制,所以在海量写入的时候,会导致所有查询处于Locked状态。
4.4.3 其他存储引擎
Archive、Blackhole、CSV、Federated、Memroy、Merge、NDB 集群 引擎
4.4.4 特殊引擎
4.4.4.1 PERFORMANCE_SCHEMA
MySQL 5.5新增一个存储引擎:命名PERFORMANCE_SCHEMA ,主要用于收集数据库服务器性能参数。MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。
performance_schema提供以下功能:
提供进程等待的详细信息,包括锁、互斥变量、文件信息;
保存历史的事件汇总信息,为提供MySQL服务器性能做出详细的判断;
对于新增和删除监控事件点都非常容易,并可以随意改变mysql服务器的监控周期,例如(CYCLE、MICROSECOND)