mysql 赢在职场 学习

http://www.wyzc.com/study/8652/1859/#6490

mysql版本

mysql community edition 社区版 +加入一些未经过测试的代码，未经过压力测试和性能测试，是企业版的小白鼠，有很多bug，不提供支持，没有企业版的组件

商业版

商业组件有

企业级备份

企业级高可用功能

企业级可扩展性能 enterprise scalability

企业级的安全功能 enterprise security

企业级的审计功能  enterprise audit

企业级的监控   enterprise monitor

工作平台 workbench

技术支持

安装

可以从官网上下载安装包（推荐

下载源代码包

狭隘二进制包

从光盘安装

源代码安装可以更具系统的配置调节参数，使mysql性能得到提高

但是大量安装会很麻烦，一般都是安装包安装

www.mysql.com-->downloads--->

看到

MySQL Community Edition

 (GPL)

MySQL Cluster CGE

 (commercial)

MySQL Enterprise Edition

 (commercial)

在enterprise 版本中有管理的组件和备份等多个组件

在community版本中也有好多相应的组件

下载后

mkdir -pv /user/src/mysql/

tar xvf mysql.tar -C /user/src/mysql

解压后会有好多rpm包 如client.rpm server.rpm devel.rpm 等等

devel.rpm为mysql的开发提供头文件和其他支持如果安装poxy代理，或者manager组件时需要安装devel.rpm

安装server.rpm后会随机产生mysql的密码，会在/root/.mysql_secret看到这个密码，这个事mysql5.5以后的特性，mysql5.5以前是空密码，这样就不安全了。

当第一次登陆mysql的时候，除了set password 其他事情做不了 

额外，你可以在安装后运行/usr/bin/mysql_secrue_installation 来进一步进行安全设置

cat  root/.mysql_secure 

123123

service mysql start (官方从5.1以后，就由mysqld变成mysql)

或者/etc/init.d/mysql start

mysql_secure_installation

输入密码  ***** ：123123

修改root密码：123456

remove anonymous users？y   匿名用户能登陆，但不能操作

disallow root login remotely？y 是否允许root用户远程登陆，一般安全不允许

实验环境可以

remove test databse and access to it？是否移除测试的样品数据库

reload privilege tables now？ 重读授权表，让刚才的操作生效

mysql客户端

交互式 normal

非交互式

交互式的使用：

-u +用什么用户登陆 可选

-h +host缩写，可选

-P +端口 可选 默认为3306

-p +密码 可选 密码紧跟其后 没有空格，如果有空格，则会被认为在回车后输入密码

-p123456 是输入密码

-p 123 表示是数据库名为123 ，空格后为制定的数据库名

mysql -h 192.168.2.1 -u utube -P 3300 -p23213  

本地登陆 

-u不写默认用root

-P不写默认3306

数据库名可以以不写，在进入过选择 

用show databases  、、、use 数据库名

mysql -p

非交互式使用：

#mysql <backup.sql 这个可以在backup.sql中的命令大量的输入到mysql

熟悉mysql

desc table名；

show create table t1；

show create table t1 /G；查看表结构

insert into table tablename SET id=1，name=tube；

insert intotable tablename（id，name） Values （2，kevin），（3，mark）

总结

建议从官网网站上下载mysql

尽量不同用源码编译安装mysql，源代码用于研究和小面积安装

交互式和非交互式

登陆mysql -P -h -u等 -p 注意空格的应用啊

库名和表名注意大小写

--------------------------------------------

--------------------------------------------

yum安装mysql

rpm安装，方便快捷，但是不能自定义参数

在官网上用www.mysql.com--》download---》new1 mysql yumrepository

下载后得到mysql-community-release-el6-5.noarch.rpm

查看这个包的内容

rpm -qpl mysql-community-release-el6-5.noarch.rpm  

-qpl列出RPM软件包内的文件信息

显示如下

/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

/etc/yum.repos.d/mysql-community-source.repo

/etc/yum.repos.d/mysql-community.repo

我们安装这个rpm

rpm -ivh mysql-community-release-el6-5.noarch.rpm  后 就就会自动配置好了yum

这时我们就会yum list | grep MySQL

会列出所用的包

yum install MySQL-client MySQL-server 就会安装好客户端和服务器

如果自己在公司构建yum源呢

vi /etc/yum.repos.d/

vi mysql-community.repo

记录了mysql的多种版本，5.5  5.6等

将地址copy 把地址复制到网页，把所有目录同步过来，就可以做本地的yum源了

源码安装，可以定制

同样去网站 download -在select platform时选择 source code

源码安装可以定制安装位置

功能的开关关闭，精简mysql 

加优化参数

下载后mysql-5.6.17.tar.gz

tar zxvf mysql..gz -C /usr/src/

5.5以后就有所改变了不用configure makeinstall make了

而是用cmake安装，所以要安装cmake 

yum install cmake 

cmake . 表示在当前目录开始编译

gmake

make install

或者cmake && gmake && make install

以上为默认安装

也可以自定义

cmake . +可选项

看mysql 官方文档 在2.4.4

infile 辅助mysql可以调用外部的sql 或系统命令，是否开启

install_bindir mysql的命令放在那里

docreadmedir

includes 

cmake -D参数        1是打开 0关闭

myisam merge memory csv 等存储引擎默认加载

engine archive blackhole example federated nob partition 等存储引擎需要用命令添加

-DWITH_innodebase_storage_egine=1

-DWITH_ARCHIVE_STOREAGE_ENNGINE=1

-DWITH_partition _STOREAGE_ENNGINE=1  分区存储引擎打开

等等

自作一个cmake.sh

cmake -D***********************

如果在cmake 中指定用户是mysql额话，还需要再系统中添加group和user mysql 然后再cmake

通过srpm安装，可以像源码一样的定制，又可以大量方便的部署

官网下载mysql-5.6.17-1.el6.src.rpm

cp mysql.src.rpm /home/tubeiu/

cd /home/tubeliu/

ll

chown tubeliu. mysql.src.rpm想用yubeliu用户安装所以改权限

没错是“.空格”

yum list | grep rpm-build

构建src.rpm必须安装rpm-build

yum -y install rpm-build

su - tubeliu

rpmbuild ~ 构建rpm创建的目录，存放一些中间文件

rpm -ivh mysql.src.rpm

cd /home/tubeliu/repbuild

ls -R递归查看所有目录

在./SPECS下有mysql.spec文件

mysql.spec官方有大部分配置

主要看buid full release 向cmake的配置可以添加删除

-D****

然后保存退出

最后根据mysql.spec创建rpm包了

rpmbuild -bb mysql.spec等等就自动完成了

报错gperf is needed by mysql。。。，从centos 可以下载

安装中报错

yum install libaio-devel

安装完成后，在RPMS下回有大量的rpm包了

谢谢。

索引

索引并非越多越好--写数据也要写索引，和重新计算索引

数据量不多不需要建索引

列中值变化不多不需建索引

唯一性拘束使用唯一索引

注意Hash索引的了解

•    A.Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询
• B.Hash 索引无法被用来避免数据的排序操作
• C.Hash 索引不能利用部分索引键查询
• D.Hash 索引在任何时候都不能避免表扫描
• E.Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高

create table T1

（id int primary key,     --简历

name varchar(10).

index(name));        --简历普通索引

创建普通索引

查看索引效果

mysql> create table t_1 (id int,name char(10),index(name));
Query OK, 0 rows affected (0.08 sec)


mysql> show create table t_1
    -> ;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                   |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
| t_1   | CREATE TABLE `t_1` (
  `id` int(11) DEFAULT NULL,
  `name` char(10) DEFAULT NULL,
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

在已存在的表中添加索引
create index index_name on t1(col);
alter table t1 add index index_name(col);

创建唯一索引

组合索引

组合索引依照最左端原则

如（id name age）作为组合索引

查询id或检索id name或者id age 都会使用索引，如果使用name age 是不会使用索引的必须带上最左端的id

mysql> create table t_4 (
    -> id int,
    -> name varchar(10),
    -> age int,
    -> index MutiIdx (id,name,age)                 组合索引
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> show create table t_4
    -> ;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                  |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_4   | CREATE TABLE `t_4` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  KEY `MutiIdx` (`id`,`name`,`age`)              组合索引
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

索引使用效果的展示

mysql> insert into t_4 values
    -> (1,'AAA',10),
    -> (2,'BBB',11),
    -> (3,'CCC',12),
    -> (4,'DDD',13),
    -> (5,'EEE',14);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

-------------------------------------------------------------------------------------------

只用id使用了mutidx索引

EXPLAIN可以查看一个查询的执行计划，
mysql> explainselect name,age from t_4 where id<3\G; 我们只使用id
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_4
         type: index              使用了索引
possible_keys: MutiIdx      使用的索引名
          key: MutiIdx           
      key_len: 23
          ref: NULL
         rows: 5
        Extra: Using where; Using index
1 row in set (0.00 sec)


ERROR: 
No query specified


mysql> 

------------------------------------------------------------------------

用id和age 也是用了mutidx索引

mysql> explain select name,age from t_4 where id<3 and age<15\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_4
         type: index
possible_keys: MutiIdx
          key: MutiIdx
      key_len: 23
          ref: NULL
         rows: 5
        Extra: Using where; Using index
1 row in set (0.00 sec)


ERROR: 
No query specified


mysql> 

-------------------------------------------------------------------------------------

只用age，

mysql> explain select name,age from t_4 where age<15\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_4
         type: index
possible_keys: NULL
          key: MutiIdx
      key_len: 23
          ref: NULL
         rows: 5
        Extra: Using where; Using index
1 row in set (0.00 sec)


ERROR: 
No query specified


mysql> 

全文索引

是，在MySQL 5.6中只有InnoDB和MyISAM存储引擎支持全文索引

fulltext index_name 

更改索引

alter table t1 add index name（name（20））；添加普通索引

alter table t2 drop index name；

create index ind_name on t_4(id);

drop index ind_name on t_4;

测试索引性能

建测试表mysql> create table testx1(id int,num int,pass varchar(50));

Query OK, 0 rows affected (0.06 sec)

插入数据

在系统提示符下执行创建100万行数据

for((i=1;i<=1000000;i++));do 'mysql -u mysql test -e "insert into testx1 values($i,floor($i+rand()*$i,md5($i));"'done

[root@mysql01 ~]# vi 1.sh 
#!/bin/sh
for((i=1;i<=1000000;i++));
do mysql -u mysql test -e "insert into testx1 values($i,rand()*$i,md5($i));"解释在do后同‘ ****’ 不好用，提示命令无法找到，floor函数在我的体统提示错误，就不用了
done

首先要刷新一下查询缓存，不要因为查询缓存影响我们的测试

reset query cache；

然后select num，pass from testx1 where id>=5000 and id<=5005;

结果为0.05s

如果我们在id上加索引后

reset query cache；

同样的查询为0.00s

------------------------------------------------

先创建索引再导入数据或先导入数据再创建索引哪个导入的时间更短？？？？

insert into test2 select * fromtest1            test2有索引

时间为0、87s

insert into test3 select * from test1            test3没有索引

时间为0、65s

经过测试空表没有索引，导入更快，但是后期建立索引可能会花费更过时间，但是表再建立索引前已经可以撑场使用了

索引常识

•        A.列中包含有NULL的值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。
•  B.一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
• C.不要在列上进行运算，这将导致索引失效而进行全表扫描。
• 索引的设计原则，不是越多越好，过多的索引引起多的写入

• fulltext 索引尽在MYISAM上使用

   数据量巨大的表谨慎重建索引，一定要非常慎重，可能引起数据库慢。

--------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------

mysql Replication

mysql 5.6以后推出GRIDs Replication 那么GRIDs是什么？

GRIDs是全局事务I

5.6以后有个自动的failover

replication实现将一台数据从一台同步到多台上去。默认情况下是异步复制，通过配置可以同步整个库或者部分或表

原理

主将数据改变记录到二进制日志中，从数据库再通过这个日志进行数据操作保存到本地的relay log（二进制），然后数据库再根据relaylog日志进行数据写入

 主                                                        从

DML----binlog -------------------------->relay log ----DML ---DB

replication的作用

故障切换 fail over

备份服务器 backup server

高可用 High Performance        可以读写分离

replication 简单实验

master  ---slave  主从模式

STEP1

slave是通过mysql连接登陆到MaSTER上来读取二进制日志的，因此需要在Master上给Slave配置权限

master>GRANT REPLICATION SLAVE ON *.* TO‘repL’@‘slave_ip’ INDENTIFIED BY ‘password’；

//repl为用户名，这个是创建一个用户名和密码，用户名或密码不需要主或从以前存在

master>FLUSH PRIVILEGES

设置后，可以在从库上测试连接

slave>mysql -h192.168.1.1 -urepl -prepl

如果不成功，检查主iptables -L查看防火墙

STEP2

在MASTER上需要打开二进制日志，并表示server-d（server—id用语唯一标示主机，数字取值范围1···2^32方-1）s

Master#vi  /etc/my.cnf

[mysqld]

log-bin            //打开二进制日志

binlog-format=row          //二进制日志以row行形式记录

syc-binlog=1               

serverid=1

MASTER#service mysql restart   配置好my.cnf后需要重启mysql服务使其生效

STEP3

制作一个master完整备份，并且执行prepare

？为什么能通过二进制文件进行同步还做主备份和从恢复（初始同步干什么）

回答：完全通过二进制同步是可以的，但会占用主大量的磁盘io和带宽。所以才进行备份恢复来完成初始同步。

#innobackupex --user=dba --password=xxx /var/lib/bakcup/       dba是提前创建的

#innobackupex --use-memory=500m --apply-log /var/lib/backup/2015......