mysql基础测试

20160905mysqlday6
----------------------
1.什么是mysql？
1）数据库管理系统，表为最小单位 2）c/s模型 3）标准的sql语句 4）关系型数据库 5）开放源码 6）多os 7）mariaab》sun》oracle->mysql   mariadb
2.存储引擎的概念
myisam  innodb
3.知名的两大存储引擎MYISAM和INNODB的区别
事务 锁机制 适用场景
MYISAM no table 线下数据分析 
INNODB yes line 线上金钱交易OLTP在线事务型
4.红帽企业版rhel7.2默认的数据库软件版本为？mariadb5.5
5.mariadb5.5的服务名service为？ 守护进程名daemon为？默认监听的端口号为？
mariadb mysqld 3306
6.mariadb5.5的rpm包安装后默认的数据文件存放路径为？ 启动日志（排错日志）存放的目录为？
/var/bin/mysql /var/log/mariadb/mariadb.log
7.sql语句的分类：1） 2） 3） 4）
ddl dml dcl dql
/usr/local/bin/rht-clearcourse: line 102: /dev/tcp/foundation39.i
8.如何破解mariadb5.5的root密码？
1.停止服务 systemctl stop mariadb
2.跳过授权表启动服务 mysqld_safe --skip-grant-tables &  
   查看服务名称的方法： (ps -ef|grep mysqld;rpm -ql mariadb)
3.mysql-->update mysql.user set password=password('uplooking')   where user='root'; 
4.停止跳过授权表启动服务 kill -9
5.启动服务 systemctl start mariadb
9.判断题：先授权越优先？F
10.判断题：授权越精确越优先？T
11.通过客户端程序连接登陆mariadb服务器，客户端软件名为？登陆的命令为？mariadb 5.5 mysql -uroot   -p
12.第一次修改数据库root密码的命令为mysqladmin -uroot password ‘ ’
---------------
11.备份的分类 冷备、热备、异地灾备
12.冷备和热备的区别
概念 优点 缺点
冷备 以物理隔离的方式保存数据备份数据不受原数据影响 机器故障和人为误操作    恢复速度慢
热备 冗余备份人为的增加重复 恢复速度快     不能解决人为误操作
1.分类：冷备、热备、异地灾备
2.冷备：将数据以隔离的方式保存，备份数据不受源数据的影响。
优点：1）硬件故障 2）人为误操作
缺点：还原（恢复）速度太慢 
3.热备：冗余 多余的重复的 人为地增加重复的部分
冗余环境：应用程序---》主服务器 从服务器
优点：还原（恢复）速度快，能够瞬间还原
缺点：只能解决硬件故障，无法解决人为误操作
12.冷备的分类
1）物理备份
2）逻辑备份
14.备份的两大要素
1）数据一致 2）服务可用
15.备份的工具
1）tar 2）lvm
3）mysqldump
16.备份的过程
1）tar 
1）停止服务 systemctl stop mariadb
2）备份数据 tar -cf /tmp/mysql.all.tar /var/lib/mysql 
3）启动服务 systemctl start mariadb
2）lvm
## os支持lvm方式 lv1---/var/lib/mysql
1)fdisk 
2)pv
3)vg
4)lv
5)mkfs ext4 (xfs)
6)停止服务
7)全备份tar
8)挂接/var/lib/mysql/
9)导入数据(注意权限，ugo，selinux)  #setenforce 0
10)启动服务


## lvm快照备份数据
### 与数据库服务相关的操作
1)添加全局的读锁（只能读不能写---》数据不会变）> flush tables with read lock; #mysql中操作
2)创建快照 lvcreate -s -L 1G -n snap1 /dev/vgmysql/lv1 --->/dev/vgmysql/snap1 #linux中操作
3)解锁 > unlock tables; #mysql中操作


### 与数据库服务无关的操作
4)挂接快照 mount /dev/vgmysql/snap1 /mnt (如果是xfs，mount -o nouuid /dev/vgmysql/snap1 /mnt )
5)tar打包 cd /mnt;tar -cf /tmp/mysql.2.tar ./*
6)umount umount /mnt
7)删除快照 lvremove /dev/vgmysql/snap1
3）mysqldump
# mysqldump备份步骤
INNODB mysqldump -uroot -puplooking -A --single-transaction > /tmp/mysql.201608301600.sql
MYISAM mysqldump -uroot -puplooking -A --lock-all-tables > /tmp/mysql.xxx.sql
17.还原的标准流程
1）tar（物理备份恢复）
## tar还原

1）停止服务 systemctl stop mariadb
2）清环境   rm -rf /var/lib/mysql/*
3）导入数据 tar -xf /tmp/mysql.all.tar -C /
4）启动服务 systemctl start mariadb
5）测试   > select * from db1.t1; 
2）mysqldump（逻辑备份恢复）


# mysqldump还原步骤
1）停止服务
2）清空环境
  *3）启动服务（启动之后，/var/lib/mysql/目录下会产生初始化的文件）
*4）导入数据
*5）刷新授权
6）测试
18.增量备份---实时增量备份与还原（基于时间点的恢复）
)全备份 mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.11.mysql
-----------------------------------------------------------------
1)停止服务 systemctl stop mariadb
2)清空环境 rm -rf /var/lib/mysql/*
3)启动服务 systemctl start mariadb
4)导入全备份数据 mysql < /tmp/mysql.11.sql
5)刷新授权 > flush privileges;
6)测试，全备份数据是否正确 > select * from db1.t1;
7)查看分析二进制日志---》得到正确的操作命令，跳过错误的 mysqlbinlog /var/lib/mysql-log/master.00000X
* 自己记下来，做全备份的时候
* 请别人记，全备份文件 --master-data=2 文件的第22行
##sed -n '22p' /tmp/mysql.11.mysql
分析：
* 自己看，用眼睛找
##mysqlbinlog /var/lib/mysql-log/master.00000X
##mysqlbinlog /var/lib/mysql-log/mastera.000001 |sed 's@\/\*.*\*\/@@'|sed -n '/BEGIN/,$p' #去除注释内容
* grep 截取关键字 ，delete drop -B 显示前面的几行
##mysqlbinlog /var/lib/mysql-log/master.00000X | grep delete -B (-A -C) 5
#B关键词上N行，A下N行，C 上下个N行


8)导入增量备份 通过管道导入数据库
mysqlbinlog --stop-datetime='2016-08-31 11:19:12' /var/lib/mysql-log/mastera.000002 | mysql -uroot -puplooking
9)测试，查看增量备份数据是否正确
10)全备份 mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.12.mysql


------




1）如何打开二进制日志，详细步骤
1>  
2>
3>
1)configure 修改配置文件/etc/my.cnf
2)log-bin= 添加二进制日志存放的路径，以及二进制日志的名字log-bin=/var/lib/mysql-log/mastera
3)mkdir  创建目录
4)UGO,selinux 修改权限mysql:mysql;selinux 允许
5)restart mariadb 重启服务
2）如何查看二进制日志
1> 命令
2> 通过时间来截取
3> 通过位置编号来截取
index 日志的索引
000001 日志

命令 mysqlbinlog mariadb-5.5
mysqlbinlog /var/lib/mysql-log/mariadb.000001
--start-datetime=name 起始时间点
  --stop-datetime=name  结束时间点
mysqlbinlog --stop-datetime="2016-08-31 11:19:12"
--start-position=pos  位置编号 唯一，增大 at
--stop-position=pos
3）全备份数据时记录备份开始后的所有写操作记录在新的日志中，并记录该日志的日志名，参数分别为
1> 刷新日志 --flush-logs
2> 记录日志名和位置编号  --master-data=2
4）如何导入二进制日志
mysql < /tmp/mysql.11.sql
19.mysql日志的分类
1） 2） 3）
启动日志 排错日志 /var/log/mariadb/mariadb.log 排错的  log-error=
写日志 二进制日志 默认不打开，记录写操作ddl dcl dml 备份 log-bin=/var/lib/mysql-log/mastera
读日志 慢查询日志 默认不打开，记录读操作dql 性能调优


----
20.主从同步的原理
初始化数据同步
二进制日志必须打开
读取 master 日志
产生中继日志
重演中继日志




初始化数据同步      缺点 解决办法
二进制日志必须打开 延迟  网络 花钱解决
读取 master 日志       重演 上 mysql5.7 版本或者 mariadb10.1 版本
产生中继日志 单点故障 master 读写分离multi-source
重演中继日志




21.单主从的配置


主服务器
1）
2）
3）
从服务器
1）
2）
3）
4）
5）
6）


-----
# 主服务器
1）修改配置文件 vim /etc/my.cnf
[mysqld]
log-bin 
server-id=1
（重启服务）
2）授权从机 grant replication slave on *.* to slave@172.25.8.12 identified by 'uplooking';


3）初始化数据一致
mysqldump -uroot -puplooking -A --single-transaction --master-data=2 --flush-logs > /tmp/mysql.all.mysql
---》传输给从机器
scp mysql.all.mysql 172.25.8.12:/tmp


# 从服务
1）install     yum -y install mariadb-server 
2）修改配置文件 server-id=2
3）初始化数据一致 导入全备数据
rm -rf /var/lib/mysql/* #清环境
systemctl start mariadb #重启服务
mysql </tmp/mysql.all.mysql #导入数据
mysql #登陆mysql
>flush privileges; #刷新授权
4）> change master to master_host='172.25.0.11' #主服务器地址
master_user='slave' #授权的用户名
master_password='uplooking' #授权的密码
master_log_file='' #二进制文件    sed -n '22p' /tmp/mysql.11.mysql
master_log_pos='' #二进制文件开始点
##change master to master_host='172.25.8.11',master_user='slave',master_password='uplooking',master_log_file='mastera.000028',MASTER_LOG_POS=245;
5)> start slave;
6)> show slave status\G;
---------------------------------------------------------------------
22.主从同步中的延迟问题，不同版本的解决方法
1）主从同步本身的架构导致
Msyql 5.5 从1sql线程 延迟问题最严重
Mysql 5.6  1库1线程  没有解决问题
Mysql 5.7  1组1线程 GTID 




学生提问：
1.（mariadb5.5多级主从架构）mastera和masterb互为主从，‘mastera的从机slavea和slaveb’能够同步mastera的数据，而mastera又能同步masterb的数据，那么为什么mastera的从机不能间接同步masterb的数据呢？


2.（mysql5.7多级主从+multisource架构）为什么老师在演示的时候只做了一次授权，后面的从机都没有授权呢？
grant replication slave on *.* to slave@'172.25.0.%' identified by '(Uploo00king)';
 slave@'172.25.0.12' '(Uploo00king123)'
172.25.0.0/24
172.25.0.1
172.25.0.254


3.mariadb5.5的mysql.user表是MyISAM存储引擎的表，为什么在用mysqldump逻辑备份的时候只考虑了innodb存储引擎的特性--single-transaction，而没有用--lock-all-tables呢？
而percona工具有分成两个存储引擎分别备份。


4.perconna软件进行还原时，ibdata中既有完整事务的数据，又有不完整事务的数据，所以通过事务日志归滚筛选事务的数据，如果这样的话，那之前用tar和lvm快照备份还原，就是不管事务是否完整都会还原回去吗？


1.
begin;
insert 1;
\q
2.
stop
tar 
start


=-----
1.stop
2.rm
3.tar
4.start
5.