003、MySQL多实例安装、存储引擎

DBA的日常工作：
  • 审核开发人员写的SQL语句
  • 备份恢复工作，进入公司之后，首先问备份集在哪，备份策略是什么
单机多实例安装：目的是，不同业务间可以配置多种数据库，单机多实例可以节约成本。

多实例安装需要记住以下几点：
  1. 比如配置两套业务库，erp系统和oa系统，可以有相同的家目录：basedir=/usr/local/mysql
  2. 需要有不同的数据目录和端口号：/data/mysql_3306_erp和/data/mysql_3308_oa
  3. 修改配置文件/etc/my.cnf
  4. 还是三步走，一步曲。

一、初始化

参数文件内容：
[client]
#port   = 3306
#socket = /tmp/mysql.sock
#default-character-set=utf8

[mysql]
#default-character-set=utf8

[mysqld3306]
port    = 3306
socket  = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql_3306
socket          = /tmp/mysql_3306.sock
slow_query_log_file = /data/mysql_3306/slow.log
log-error = /data/mysql_3306/error.log
log-bin = /data/mysql_3306/mysql-bin
sync_binlog = 1
binlog_format=row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld3308]
port    = 3308
socket  = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql_3308
socket          = /tmp/mysql_3308.sock
slow_query_log = 1
slow_query_log_file = /data/mysql_3308/slow.log
log-error = /data/mysql_3308/error.log
long_query_time = 0.05
log-bin = /data/mysql_3308/mysql-bin
sync_binlog = 1
binlog_cache_size = 4M
default-storage-engine=InnoDB
binlog_format=row
transaction_isolation = REPEATABLE-READ
innodb_buffer_pool_size = 100m

[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin
初始化分开进行，先初始化一个：
[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3306_erp --defaults-file=/etc/my.cnf --user=mysql
再初始化第二个：
[root@localhost scripts]# ./mysql_install_db --basedir=/usr/local/mysql --datadir=/data/mysql_3308_oa --defaults-file=/etc/my.cnf --user=mysql

二、启动数据库

多实例数据库启动方式与单实例不同，使用/usr/local/mysql/bin/mysqld_multi启动。
先启动3306端口的数据库：
[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3308
查看是否启动：
[root@localhost ~]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3308 is running
关闭某个实例的方法，比如关闭3308这个端口的实例：
[root@localhost mysql]# mysqld_multi stop 3308
[root@localhost mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3308 is not running
连接某个实例:
[root@localhost mysql]# mysql -S /tmp/mysql_3306.sock
创建一个erp数据库：
mysql> create database erp;
Query OK, 1 row affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| erp                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)
创建oa数据库：
mysql> create database oa;
Query OK, 1 row affected (0.01 sec)


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| oa                 |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)
多实例数据库安装至此完毕，今后课程不会涉及到多实例，一般企业也不会使用单机多实例数据库。
回到单实例。

三、表存储引擎

MySQL5.5之后，默认的表存储引擎是innodb（插件式存储引擎）.  MySQL5.1默认的存储引擎是myisam。
MySQL存储引擎是基于表的，而不是基于数据库的。MySQL的核心就是存储引擎。
创建表指定存储引擎：
mysql> create table t1(id int) engine=innodb;
Query OK, 0 rows affected (0.02 sec)
--5.5之后可以不加engine=innodb，因为默认就是。
INNODB是事务安全的mysql存储引擎，设计上采用类似于oracle的架构，在OLTP的应用中，innodb作为核心应用表的首选存储引擎。
线上生产库还有很多myisam和innodb混合的情况，这种情况对数据库性能影响极大，因为两种存储引擎有自己需要的参数配置，所以应当尽量避免这种情况出现。
在数据目录/mysql下，数据文件以.ibd结尾的都是innodb的表的文件，而.frm结尾的文件是表结构文件。
以.MYI结尾的文件是myisam的索引文件，以.MYD结尾的是myisam的数据文件。
而innodb的索引文件和数据文件放在一起，以.ibd结尾的文件。

根据不同的应用选择存储引擎。
现有的应用系统由OLTP和OLAP两种，OLTP是在线事务处理系统，OLAP是在线分析处理系统。市场上九成以上是OLTP应用，针对OLTP，使用innodb存储引擎。
针对OLTP系统，引出innodb存储引擎的特点：
  1. innodb支持事务，支持行锁，支持外键
  2. 通过多版本并发控制MVCC来获得高并发性，并且实现了sql标准的4种隔离级别（默认为repeatable）
  3. 提供插入缓冲（insert buffer），二次写（double write），自适应哈希索引（ahi），预读（read ahead）
  4. 对于表中数据的存储，innodb采用clustered。每张表的存储都按照主键的顺序存放，如果没有显式的为表定义主键，innodb会为每一行生成一个6字节的rowid，作为主键
名词解释：
  • 多版本：指的是针对某个数据，比如更新，update 1更新成2，相对2来说，1是旧版本，再把2更新成3,2对于3来说就是旧版本。
  • 插入缓冲，二次写，自适应哈希索引被称作innodb的3大特性
针对OLAP系统，使用myisam存储引擎，myisam的特点：
  1. 不支持事务，表级锁，全文索引，对OLAP在线分析处理，操作速度快
  2. myisam存储引擎的表由MYD,MYI组成，MYD用来存放数据文件，MYI用来存放索引
  3. 从MySQL5.0开始，MySQL默认支持256T单表数据
  4. 对于myisam存储引擎，MySQL数据库只缓存其索引文件，数据文件的缓存交由操作系统本身来完成，区别于使用LRU算法缓存数据的大部分数据库
如果面试过程中有这样一个问题：innodb存储引擎和myisam存储引擎的表，谁快？
答案是innodb，因为myisam存储引擎不缓存数据，只缓存索引。而innodb索引和数据在同一个数据文件中，都会进行缓存，操作系统读取缓存速度比直接读取磁盘速度要快很多。

innodb和myisam的区别：
  1. 针对于事务的支持，innodb支持事务，myisam不支持事务
  2. 对于锁的区别，锁粒度不同，innodb支持行级表，myisam支持表级锁。锁影响到并发性，明显行级锁并发性高
  3. 缓存数据不同，innodb同时缓存数据和索引，而myisam只缓存索引
  4. 针对select count(*)的问题：myisam内部有计数器，而innodb可能需要全表扫描
  5. myisam存储引擎的表很容易损坏，需要经常修复

NDB存储引擎：
  1. 数据全部放在内存中，因此主键查找的速度极快，可以线性提高数据库性能，常用于高可用，高性能集群系统
  2. NDB存储引擎的连接操作在MySQL数据库层，而不是在存储引擎层完成，因此复杂的连接操作需要巨大的网络开销

memory存储引擎：
  1. 将表中的数据存放在内存，如果数据库重启或者发生崩溃，表中的数据都将消失。适用于存储临时数据的临时表，数据仓库中的维度表，默认使用hash索引，非B+树索引
  2. 只支持表锁，并发性能差，不支持text和blob类型，存储变长字段varchar时，是按照定长字段char类型存储，因此会浪费内存空间

Archive存储引擎：只支持insert和select操作
Federated存储引擎：并不存放数据，只是指向一台远程MySQL数据库服务器上的表，类似Oracle的DBLINK
Maria存储引擎：缓存数据和索引文件，行锁，提供MVCC功能，支持事务和非事务安全的选项支持以及更好的BLOB字符类型处理性能。

四、MySQL连接方式

1、TCP/IP连接方式：网络中使用的最多的一种方式。一般情况下，客户端在一台服务器上，而MySQL实例在另一台服务器上，两台机器通过一个tcp/ip网络连接。
mysql -u username -p password -P -h IP
通过tcp/ip连接MySQL实例时，MySQL会先检查一张权限视图，用来判断发起请求的客户端ip是否允许连接到MySQL实例，该视图就是MySQL库的user表。
创建用户，限定网段，刷新权限表：
mysql> grant all privileges on *.* to 'zs'@'192.168.100.%' identified by '123456';
Query OK, 0 rows affected (0.07 sec)
mysql> grant all privileges on *.* to 'root'@'%' identified by 'root';

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
连接mysql通过网络：
[root@localhost ~]# mysql -uzs -p -P 3306 -h 192.168.100.111
关于端口号：最好不要用默认端口号3306，修改端口号需要修改my.cnf。
查看连接：
mysql> show full processlist;
+----+------+-----------------------+-------+---------+------+-------+-----------------------+
| Id | User | Host                  | db    | Command | Time | State | Info                  |
+----+------+-----------------------+-------+---------+------+-------+-----------------------+
|  5 | root | localhost             | NULL  | Query   |    0 | init  | show full processlist |
|  6 | zs   | 192.168.100.111:36480 | mysql | Sleep   |    9 |       | NULL                  |
+----+------+-----------------------+-------+---------+------+-------+-----------------------+
2 rows in set (0.00 sec)
2、UNIX域套接字连接
它其实不是一个网络协议，所以只能在MySQL客户端和数据库实例在同一台服务器上的情况下使用。可以在配置文件中指定套接字文件的路径，如：socket=/tmp/mysql.sock
mysql -u username -S /tmp/mysql.sock
[root@localhost ~]# mysql -u root -p -S /tmp/mysql.sock


一条sql语句，进入数据库都做了哪些工作：
1、sql-->query_cache(查询缓存)，查看有无权限，若无权限，直接返回
2、若查询缓存中有需要的数据，直接返回数据，若无数据：query_cache -->解析器-->将sql语句解析成解析树b-tree
3、解析器-->预处理器
4、预处理器-->优化器
5、优化器-->sql(生成最优sql语句执行计划，join,order by等)
6、调用api接口
7、访问数据-->存储引擎-->数据
mysql的server层+存储引擎层=mysql的体系结构




附件列表

     

    posted @ 2021-06-02 15:54  有点菜大人  阅读(58)  评论(0)    收藏  举报