MySQL

MySQL 核心概念及入门

Posted by leone on 2017-11-23

MySQL

MySQL简介

MySQL原本是一个开放源代码的关系数据库管理系统,原开发者为瑞典的MySQL AB公司,该公司于2008年被昇阳微系统(Sun Microsystems)收购。2009年,甲骨文公司(Oracle)收购昇阳微系统公司,MySQL成为Oracle旗下产品。

MySQL在过去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟,它也逐渐用于更多大规模网站和应用,比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。

但被甲骨文公司收购后,Oracle大幅调涨MySQL商业版的售价,且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展,因此导致自由软件社群们对于Oracle是否还会持续支持MySQL社群版(MySQL之中唯一的免费版本)有所隐忧,MySQL的创始人麦克尔·维德纽斯以MySQL为基础,成立分支计划MariaDB。而原先一些使用MySQL的开源软件逐渐转向MariaDB或其它的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库[6]。

关系型数据库

关系数据库(英语:Relational database),是创建在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。现实世界中的各种实体以及实体之间的各种联系均用关系模型来表示。关系模型是由埃德加·科德于1970年首先提出的,并配合“科德十二定律”。现如今虽然对此模型有一些批评意见,但它还是数据存储的传统标准。标准数据查询语言SQL就是一种基于关系数据库的语言,这种语言执行对关系数据库中数据的检索和操作。

关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。

MySQL特性

MySQL是一种使用广泛的数据库,特性如下:

  • 使用C和C++编写,并使用了多种编译器进行测试,保证源代码的可移植性

  • 支持AIX、FreeBSD、HP-UX、Linux、Mac OS、Novell Netware、OpenBSD、OS/2
    Wrap、Solaris、Windows等多种操作系统。

  • 为多种编程语言提供了API。编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

  • 支持多线程,充分利用CPU资源

  • 优化的SQL查询算法,有效地提高查询速度

  • 既能够作为一个单独的应用程序应用在客户端服务器网络环境中,也能够作为一个库而
    嵌入到其他的软件中提供多语言支持,常见的编码如中文的GB 2312、BIG5,日文的Shift_JIS等都可以用作数据表名和数据列名

  • 提供TCP/IP、ODBC和JDBC等多种数据库连接途径

  • 提供用于管理、检查、优化数据库操作的管理工具

  • 可以处理拥有上千万条记录的大型数据库

MySQL应用

与大型数据库例如Oracle、DB2、SQL Server等相比,MySQL自有它的不足之处,如规模小、功能有限(MySQL Cluster的功能和效率都相对比较差)等,但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说,MySQL提供的功能已经绰绰有余,而且由于MySQL是开放源码软件,因此可以大大降低总体拥有成本。 目前Internet上流行的网站构架方式是LAMP(Linux+Apache+MySQL+PHP),即使用Linux作为操作系统,Apache作为Web服务器,MySQL作为数据库,PHP作为服务器端脚本解释器。由于Linux+Apache+MySQL+PHP都是自由或开放源码软件(FLOSS),因此使用LAMP不用花一分钱就可以建立起一个稳定、免费的网站系统。

MySLQ存储引擎

  • MySQL存储引擎简介

插件式存储引擎是MySQL数据库最重要的特性之一,用户可以根据应用的需要选择如何存储和索引数据库,是否使用事务等。mySQL默认支持多种存储引擎,以适应不同领域的数据库应用需要。用户可以通过选择使用不同的存储引擎提高应用的效率,提供灵活的存储,用户设置可以按照自己的需要定制和使用自己的存储引擎,以实现最大程度的可定制性。
MySQL常用的存储引擎为MyISAM、InnoDB、MEMORY、MERGE,其中InnoDB提供事务安全表,其他存储引擎都是非事务安全表。
MyISAM是MySQL的默认存储引擎。MyISAM不支持事务、也不支持外键,但其访问速度快,对事务完整性没有要求。
InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是比起MyISAM存储引擎,InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。MySQL支持外键存储引擎只有InnoDB,在创建外键的时候,要求附表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。

  • MySQL存储引擎特性

主要体现在性能、事务、并发控制、参照完整性、缓存、故障恢复,备份及回存等几个方面
目前比较普及的存储引擎是MyISAM和InnoDB,而MyISAM又是绝大部分Web应用的首选。MyISAM与InnoDB的主要的不同点在于性能和事务控制上。
MyISAM是早期ISAM(Indexed Sequential Access Method)的扩展实现,ISAM被设计为适合处理读频率远大于写频率的情况,因此ISAM以及后来的MyISAM都没有考虑对事物的支持,不需要事务记录,ISAM的查询效率相当可观,而且内存占用很少。MyISAM在继承了ISAM优点的同时,与时俱进的提供了大量实用的新特性和相关工具。例如考虑到并发控制,提供了表级锁。而且由于MyISAM是每张表使用各自独立的存储文件(MYD数据文件和MYI索引文件),使得备份及恢复十分方便(拷贝覆盖即可),而且还支持在线恢复。
所以如果应用不需要事务,不支持外键,处理的只是基本的CRUD(增删改查)操作,那么MyISAM是不二选择。

MySql常用引擎

InnoDB

Innodb引擎提供了对数据库ACID事务的支持,并且实现了SQL标准的四种隔离级别,关于数据库事务与其隔离级别的内容请见数据库事务与其隔离级别这类型的文章。该引擎还提供了行级锁和外键约束,它的设计目标是处理大容量数据库系统,它本身其实就是基于MySQL后台的完整数据库系统,MySQL运行时Innodb会在内存中建立缓冲池,用于缓冲数据和索引。但是该引擎不支持FULLTEXT类型的索引,而且它没有保存表的行数,当SELECT COUNT(*) FROM TABLE时需要扫描全表。当需要使用数据库事务时,该引擎当然是首选。由于锁的粒度更小,写操作不会锁定全表,所以在并发较高时,使用Innodb引擎会提升效率。但是使用行级锁也不是绝对的,如果在执行一个SQL语句时MySQL不能确定要扫描的范围,InnoDB表同样会锁全表。

适用场景

  • 经常更新的表,适合处理多重并发的更新请求。

  • 支持事务。

  • 可以从灾难中恢复(通过bin-log日志等)。

  • 外键约束。只有他支持外键。

  • 支持自动增加列属性auto_increment。

MyISAM

MyIASM是MySQL默认的引擎,但是它没有提供对数据库事务的支持,也不支持行级锁和外键,因此当INSERT(插入)或UPDATE(更新)数据时即写操作需要锁定整个表,效率便会低一些。
MyIsam 存储引擎独立于操作系统,也就是可以在windows上使用,也可以比较简单的将数据转移到linux操作系统上去。
意味着:引擎在创建表的时候,会创建三个文件,一个是.frm文件用于存储表的定义,一个是.MYD文件用于存储表的数据,另一个是.MYI文件,存储的是索引。操作系统对大文件的操作是比较慢的,这样将表分为三个文件,那么.MYD这个文件单独来存放数据自然可以优化数据库的查询等操作。有索引管理和字段管理。MyISAM还使用一种表格锁定的机制,来优化多个并发的读写操作,其代价是你需要经常运行OPTIMIZE TABLE命令,来恢复被更新机制所浪费的空间。

适用场景

  • 不支持事务的设计,但是并不代表着有事务操作的项目不能用MyIsam存储引擎,可以在service层进行根据自己的业务需求进行相应的控制。

  • 不支持外键的表设计。

  • 查询速度很快,如果数据库insert和update的操作比较多的话比较适用。

  • 整天 对表进行加锁的场景。

  • MyISAM极度强调快速读取操作。

  • MyIASM中存储了表的行数,于是SELECT COUNT(*) FROM TABLE时只需要直接读取已经保存好的值而不需要进行全表扫描。如果表的读操作远远多于写操作且不需要数据库事务的支持,那么MyIASM也是很好的选择。

缺点:

就是不能在表损坏后恢复数据。(是不能主动恢复)

两种数据库引擎的区别

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁,即操作一条数据也会锁住整个表,不适合高并发 行锁,操作时自会锁住某一行不对其他行有影响
缓存 只缓存索引,不缓存真实数据 缓存啊索引还要缓存真实数据
表空间
关注点 性能 事务
默认安装

linux (CentOS7.5_x86_64)下安装mysql8.0(tar.gz安装)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
# 下载mysql 
$ wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

# 解压mysql
$ tar -Jxvf mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz

# 移动mysql到指定位置
$ mv mysql-8.0.15-linux-glibc2.12-x86_64/ /usr/local/mysql

# 添加默认配置文件
$ vim/etc/my.cnf

[client]
port=3306
socket=/tmp/mysql.sock

[mysqld]
port=3306
user=mysql
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data


# 创建mysql组
$ groupadd mysql

# 创建mysql用户
$ useradd -g mysql mysql

# 创建mysql数据目录
$ mkdir $MYSQL_HOME/data

# 初始化mysql
$ /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/


# 初始化报错
> bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

# 解决方法
$ yum install -y libaio

# 初始化成功
> 如果无异常情况日志如下可以看到mysql默认会生成root账号和密码root@localhost: /TI(mjVAs1Ta

[root@localhost mysql]# bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data
2019-01-29T10:19:34.023997Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server in progress as process 4240
2019-01-29T10:19:39.764895Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: /TI(mjVAs1Ta
2019-01-29T10:19:43.041419Z 0 [System] [MY-013170] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.13) initializing of server has completed

# 拷贝mysql启动文件到系统初始化目录
$ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

# 启动mysql服务器
$ service mysqld start

# 使用mysql客户端连接mysql
$ /usr/local/mysql/bin/mysql -u root -p password

# 修改mysql的默认初始化密码
> alter user 'root'@'localhost' identified by 'password';
# 或者
> alter user user() identified by "password";

# 解决远程连接问题
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

linux (CentOS7.5_x86_64)下安装mysql8.0(rpm安装)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 下载mysql rpm包
$ wget http://mirrors.163.com/mysql/Downloads/MySQL-8.0/mysql-8.0.15-1.el6.x86_64.rpm-bundle.tar

# 安装mysql依赖库
$ yum install -y libaio

# 解压tar包
$ tar xvf mysql-8.0.15-1.el6.x86_64.rpm-bundle.tar

# 安装common模块
$ rpm -ivh mysql-community-common-8.0.15-1.el6.x86_64.rpm --force --nodeps

# 安装libs模块
$ rpm -ivh mysql-community-libs-8.0.15-1.el6.x86_64.rpm --force --nodeps

# 安装client模块
$ rpm -ivh mysql-community-client-8.0.15-1.el6.x86_64.rpm --force --nodeps

# 安装server模块
$ rpm -ivh mysql-community-server-8.0.15-1.el6.x86_64.rpm --force --nodeps

# 初始化mysql
$ /usr/sbin/mysqld --initialize --user=mysql

# 查看mysql初始化的用户名和密码
$ cat /var/log/mysqld.log

# 启动mysql
$ service mysqld start

# 关闭mysql
$ service mysqld stop

# 连接mysql
$ mysql -uroot -ppassword

# mysql8.x强制登陆后修改初始化密码
> alter user 'root'@'localhost' identified by 'root';

mysql 基本操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 创建用户 CREATE USER '用户名称'@'主机名称' INDENTIFIED BY '用户密码'
> create user 'jack'@'localhost' identified by 'jack';

# 授予权限 grant 权限 on 数据库.表 to '用户名'@'登录主机' [INDENTIFIED BY '用户密码'];
> grant replication slave on *.* to 'jack'@'localhost';

# 刷新
# $ flush privileges;

# 修改root用户可以远程连接
> update mysql.user set host='%' where user='root';

# 查看mysql所用用户
> select user,host from mysql.user;

# docker 修改mysql的最大连接数
apt-get update
apt-get install vim
vim /etc/mysql/mysql.conf.d/mysqld.cnf
max_connections=1000

> alter user 'root'@'%' identified with mysql_native_password by 'root';

mysql 集群主从复制

准备两台安装好 mysql 机器
192.168.79.15 (master)
192.168.79.16 (slave)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
# 修改主服务器的my.cnf文件添加如下配置(master)
$ vim /etc/my.cnf

# 节点唯一id值
server-id=1

# 开启二进制日志
log-bin=mysql-bin

# 指定日志格式 有mixed|row|statement 推荐mixed(可选配置)
binlog-format=mixed

# 步进值auto_imcrement。一般有n台主MySQL就填n(可选配置)
auto_increment_increment=2

# 起始值。一般填第n台主MySQL。此时为第一台主MySQL(可选配置)
auto_increment_offset=1

# 忽略mysql库(可选配置)
binlog-ignore=mysql

# 忽略information_schema库(可选配置)
binlog-ignore=information_schema

# 要同步的数据库,默认所有库(可选配置)
replicate-do-db=db1


# 编辑从节点my.cnf 文件 (slave)

# 节点唯一id值
server-id=2

# 开启二进制日志
log-bin=mysql-bin

# 步进值auto_imcrement。一般有n台主MySQL就填n(可选配置)
auto_increment_increment=2

# 起始值。一般填第n台主MySQL。此时为第一台主MySQL(可选配置)
auto_increment_offset=2

# 要同步的数据库,默认所有库(可选配置)
replicate-do-db=db1

# 查看 master 的状态 , 尤其是当前的日志及位置
> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 1608 | | | |
+------------------+----------+--------------+------------------+-------------------+

# 在slave节点执行如下命令

注意master_log_file 是对应show master status;中file的值,master_log_pos是对应position的值

> change master to
master_host='192.168.79.15',
master_user='root',
master_password='root',
master_log_file='mysql-bin.000009',
master_log_pos=0;

# 启动 slave 状态 ( 开始监听 msater 的变化 )
> start slave;

# 查看 slave 的状态
> show slave status\G

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.79.15
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000009
Read_Master_Log_Pos: 863
Relay_Log_File: node-6-relay-bin.000002
Relay_Log_Pos: 500
Relay_Master_Log_File: mysql-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 863
Relay_Log_Space: 709
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 6291c709-23af-11e9-99fb-000c29071862
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
1 row in set (0.00 sec)

# 当Slave_IO_Running: Yes和Slave_SQL_Running: Yes都为yes是说明主从复制正常

#重置 slave 状态 .
$ reset slave;

#暂停 slave 状态 ;
$ stop slave;

总结

到此mysql的核心概念和安装,主从配置已经ok了,大家可以按照本文的教程一步步的安装,这都是本人亲自实操的结果,如安装中遇到一些问题欢迎大家留言并指正,谢谢!