MySQL其他和备份

事务

事务指的是一组操作，要么执行成功，要么都执行失败

我们来看下面一个例子：

我去银行给朋友汇款，我卡上有1000元，朋友卡有1000元，我给朋友转账100元（无手续费）。如果我的钱刚被扣完，这时候网络突然断了，而朋友账户的钱又没加，怎么办？

我们来模拟一下这个操作：


mysql> create table user(
	id int auto_increment primary key,
    name varchar(32) not null default '',
    salary int not null default 0
)charset=utf8;

# 向表中插入数据
mysql> insert into user(name,salary) values ("qinyj",1000);
mysql> insert into user(name,salary) values ("jack",1000);

# 此时我们打开两个终端 查看数据
# 一个终端代表qinyj，另一个代表jack
mysql> select * from user where name="qinyj";

mysql> select * from user where name="jack";

# 当我开始转账的时候在我的窗口进行操作
mysql> update user set salary=900 where name="qinyj";
# 这时候模拟网突然断了，转不了账了
# 我们再来查看qinyj的账户
mysql> select * from user where name="qinyj";
# 我们再来看jack的账户
mysql> select * from user where name="jack";

# 发现qinyj已经转账了，账户的钱已经少了100，但是jack的账户的钱却没有增加，那么这就造成了一个数据上的不安全。

模拟完上面的操作之后呢，我们来解决这个问题

解决这个问题就需要用到mysql的事务：

start transaction：使用事务

commit/rollback：提交事务/回滚事务

例子：

mysql> start transaction;	# 使用事务
Query OK, 0 rows affected (0.00 sec)

mysql> update user set salary=900 where name="qinyj";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update user set salary=1100 where name="jack";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;				# 提交事务
Query OK, 0 rows affected (0.01 sec)

# 如果在commit之前 所有转账sql已经执行完毕了，这时候突然断网了，那么mysql会默认执行rollback操作，将事务中所有操作回滚到使用事务之前的状态，保证了数据的安全
mysql> rollback;			# 回滚提交
Query OK, 0 rows affected (0.00 sec)

# 我们再来看两个账户的金额：
mysql> select * from user where name="qinyj";

mysql> select * from user where name="jack";

pymysql使用事务

import pymysql

conn = pymysql.connect(
    host="192.168.32.130",
    password="123",
    user="root",
    database="test",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

try:
    sql = "start transaction"
    cursor.execute(sql)

    sql = "update user set salary=800 where name='qinyj'"
    cursor.execute(sql)

    import time
    print(".......")
    time.sleep(20)

    sql = "update user set salary=1200 where name='jack'"
    cursor.execute(sql)
    sql = "commit"
    cursor.execute(sql)
except Exception as e:

    print("事务处理失败")
    sql = "rollback"
    cursor.execute(sql)


    cursor.close()
    conn.close()

那么我们来总结一下事务的特性特征：

原子性：原子意思为最小的粒子，即不能再分的书屋，要么全部执行，要么全部取消，就像上面银行转账的例子。
一致性：指事务发生前和发生后数据的总额依然匹配。
隔离性：指得是某个事务的操作对其他事务不可见，隔离开来的。
持久性：当事务完成后，影响应该保留下来，不能撤销，只能通过“补偿性事务”来取消之前的错误。

存储引擎

存储引擎负责MySQL中的数据的存储和提取。

MySQL存储引擎有很多，不同的存储引擎保存数据和索引的方式是不同的。每一种存储引擎都有它的优势和劣势

InnoDB存储引擎

InnoDB是默认的事务型存储引擎，也是最重要，使用最广泛的存储引擎。在没有特殊情况下，一般优先使用InnoDB存储引擎。

数据存储形式

使用InnoDB时，会将数据表分为.frm 和 idb两个文件进行存储。

锁的粒度

InnoDB采用MVCC(多版本并发控制)来支持高并发，InnoDB实现了四个隔离级别，默认级别是REPETABLE READ，并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁。【通过MVCC实现，MVCC在稍后会进行介绍】

事务

InnoDB是典型的事务型存储引擎，并且通过一些机制和工具，支持真正的热备份。

数据的存储特点

InnoDB表是基于聚簇索引建立的，聚簇索引对主键的查询有很高的性能，不过他的二级索引（非主键索引）必须包含主键列，索引其他的索引会很大。

MyISAM存储引擎

数据存储形式

MyISAM采用的是索引与数据分离的形式，将数据保存在三个文件中.frm.MYD,.MYIs。

锁的粒度

MyISAM不支持行锁，所以读取时对表加上共享锁，在写入是对表加上排他锁。由于是对整张表加锁，相比InnoDB，在并发写入时效率很低。

事务

MyISAM不支持事务。

数据的存储特点

MyISAM是基于非聚簇索引进行存储的。

其他

MyISAM提供了大量的特性，包括全文索引，压缩，空间函数，延迟更新索引键等。

进行压缩后的表是不能进行修改的，但是压缩表可以极大减少磁盘占用空间，因此也可以减少磁盘IO，从而提供查询性能。

全文索引，是一种基于分词创建的索引，可以支持复杂的查询。

延迟更新索引键，不会将更新的索引数据立即写入到磁盘，而是会写到内存中的缓冲区中，只有在清除缓冲区时候才会将对应的索引写入磁盘，这种方式大大提升了写入性能。

对比与选择

两种存储引擎各有各的有点，MyISAM专注性能，InnoDB专注事务。

如何在两种存储引擎中进行选择？

① 是否有事务操作？有，InnoDB。

②是否存储并发修改？有，InnoDB。

③是否追求快速查询，且数据修改较少？是，MyISAM。

④是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。

我们在创建表的时候可以指定存储引擎，如果没有指定，默认在mysql5.5版本以上创建的表默认是InnoDB引擎

两个引擎的区别：

InnoDB：支持事务；支持行锁
MyIsam：不支持事务；支持表锁

查看当前默认存储引擎：

show engines;

在创建表的时候指定引擎：

create table test(id int auto_increment primary key)engine=InnoDB;
create table test(id int auto_increment primary key)engine=MyIsam;

在配置文件中指定：

default-storage-engine=INNODB

常见存储引擎以及使用场景：

InnoDB：事务处理应用程序，对于类似击飞系统或者对数据要求比较高的
MyIsam：对事务完整性，并发性要求不高，可以使用这个存储引擎。

视图

视图是一种虚拟存在的表，通俗的说视图就是执行select语句后返回的结果。

增加视图：

create view 视图名 as sql语句

删除视图：

drop view 视图名

例子：

# 如果我们对下面的sql语句查询非常频繁并且这个sql非常长，那么我们可以创建出来一个视图
mysql> select * from user where name="qinyj";

# 创建视图
mysql> create view v1 as select * from user where name="qinyj";
Query OK, 0 rows affected (0.00 sec)

# 再查看表，会看到有一个v1的表
show tables;

# 那么我们如果想要去查第一个sql，并且这个sql非常长，又不想重新输入一遍，那么我们可以查这个视图，出来效果是一样的。
mysql> select * from v1;

触发器

假设现在有这样一个场景，有两张表，

订单表库存表

当我们下一个订单的时候，订单表中需要增加一条记录，同时库存表中需要减1，这两个操作是同时发生的，并且前一个操作触发下一个操作，就可以使用触发器

使用方法：

增加触发器：

delimiter //
create trigger boy_before_insert_girl before insert on boy for each row
begin
	insert into girl (girl_name) values ("aa");
end //
delimiter ;

例子：

mysql> delimiter //
mysql> create trigger boy_before_insert_girl before insert on boy for each row
    -> begin
    -> insert into girl (girl_name) values ("aa");
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;	# 结束符

mysql> show triggers\G;

# 当我们在向boy表中插入数据的时候，我们再来查询girl表的数据会发现会多一条记录

mysql> insert into boy (boy_name) values ("qinyj");
Query OK, 1 row affected (0.01 sec)
mysql> select * from boy;
mysql> select * from girl;

删除触发器：

drop trigger 触发器名

存储过程

像一个SQL函数，一组sql语句集，存储过程就是一段预执行的sql语句，都是一组sql集，存储过程主动调用的时候才会执行。

创建存储过程：

delimiter //
create procedure p1()
begin
	select * from boy where id=2;
end //
delimiter ;

查看存储过程：

mysql> show procedure status\G;

删除存储过程：

mysql> drop procedure p1;

调用存储过程：

mysql> call p1();

函数

参考：https://www.runoob.com/mysql/mysql-functions.html

备份

物理备份

可以使用percona-xtrabackup-2.0.7-552.rhel6.x86_64.rpm对mysql进行备份。

xtrabackup官方网址：https://www.percona.com/downloads/XtraBackup/

基本语法--多实例备份--语法解释：

--user=数据库用户
--password=数据库密码
--socket=指定socket
--default-file=指定配置文件

最后面是存放位置

例子：

innobackupex --user=root --port=3306 --host=127.0.0.1 --defaults-file=/etc/my.cnf --defaults-group=mysqld3306 --stream=tar /usr/local/backup |gzip > 127.0.0.1_3306.tar.gz

全数据还原

/etc/init.d/mysqld stop //停掉mysql
mv /var/lib/mysql /var/lib/mysql_bak //数据目录备份
mkdir -p /var/lib/mysql //重建数据目录
--apply-log选项的命令是准备在一个备份上启动mysql服务
--copy-back 选项的命令从备份目录拷贝数据,索引,日志到my.cnf文件里规定的初始位置
innobackupex --defaults-file=/etc/my.cnf --user=root --apply-log /home/tank/backup/2014-09-18_16-35-12
chown -R mysql.mysql /var/lib/mysql //改变文件所属
/etc/init.d/mysqld stop //启动mysql

备份脚本

#!/bin/bash
#       Version V1.1
###############################################
# 保留备份的天数
DAY=3
#等待n秒后再执行备份
wait=60
#是否开启增量备份默认0为开启1为关闭
open_increase_bak=1
#全备时间点
default_time=4
#设置不进行备份的时间点
exclude_time="0 5"
###############################################

#载入环境变量，否则无法找到/usr/local/mysql/bin/mysql路径

source /etc/profile

DBIP="127.0.0.1"

DBUSER="xxx"

DBPWD="root"

DATE=`date +%F_%H-%M`

Hour=`date '+%H'`

PORT=`netstat -tunlp|grep mysql |awk '{print $4}'|awk -F: '{print $2}'`

MYCNF="/etc/my.cnf"

LOG_FILE="/home/msbakscript/logs/xtrabackup.log"

BACKUP_PATH="/usr/local/hero_all_backup"

MYSQL_LOG="/home/msbakscript/logs/mysql.log"

INNOBACKUPEX="/usr/bin/innobackupex"

bak_log="/home/msbakscript/logs/mysqlbak.log"

MYSQL_PATH="/usr/local/mysql/var"

HOST=$(awk -F= '/IPADDR=/ {print $2}' /etc/sysconfig/network-scripts/ifcfg-eth1)

CHECKPOINT=$(awk '/to_lsn/ {print $3}' /usr/local/hero_all_backup/xtrabackup_checkpoints 2>/dev/null)

all_bak=("$HOST"_"$DATE")

increase_bak=("$HOST"_"$DATE""-increase")

 

if [ $# -eq 0 -o $# -eq 1 -o $# -eq 2 ];then

        echo "OK" > /dev/null

else

        echo "sh $0 allbakfile 或者 sh $0 allbakfile incbakfile"

        exit 1

fi

[ ! -d ${BACKUP_PATH} ] && mkdir -p ${BACKUP_PATH}

[ ! -d /home/msbakscript/logs ] && mkdir -p /home/msbakscript/logs

 

if [ -z "`/sbin/pidof -s mysqld`" ];then

        echo "mysqld is not running"

        exit 1

fi

 

if [ `rpm -qa |grep -c xtrabackup-0.9-2.rhel4` -eq 1 ];then

        rpm -e xtrabackup-0.9-2.rhel4

fi

if [ ! -f /usr/bin/innobackupex ];then

        echo "xtrabackup is not installed"

        rpm -i --nodeps http://122.228.194.133:8080/percona-xtrabackup-2.0.7-552.rhel5.x86_64.rpm

elif [ -f /usr/bin/innobackupex ] && [ `ps -ef|grep -v grep |grep -c  /usr/bin/innobackupex` -ge 1 ];then

        echo "xtrabackup process already exist."

        exit 1

fi

 

complete_bak ()

{

        cd ${BACKUP_PATH}

        ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --stream=tar ${BACKUP_PATH}/ 2>>${LOG_FILE} |gzip >${BACKUP_PATH}/${all_bak}_${P

ORT}.tar.gz

        if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then

                tar zxvfi ${BACKUP_PATH}/${all_bak}_${PORT}.tar.gz xtrabackup_checkpoints

                echo "${all_bak}_${PORT}.tar.gz backup successed" >>${bak_log}

                echo -en "ok\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                sh /home/msbakscript/redis_backup.sh

                chown nobody.nobody *

        else

                echo "${all_bak}_${PORT}.tar.gz backup failed" >>${bak_log}

                echo -e "failure\n${all_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                exit 1

        fi

}

complete_bak

increase_bak ()

{

        cd ${BACKUP_PATH}

        if [ ! -f ${BACKUP_PATH}/xtrabackup_checkpoints -o -z ${BACKUP_PATH}/xtrabackup_checkpoints ];then

                echo "xtrabackup_checkpoints does not exist" >>/{LOG_FILE}

                complete_bak

                exit 0

        fi

        ${INNOBACKUPEX} --user=${DBUSER} --password=${DBPWD} --port=${PORT} --host=${DBIP} --defaults-file=${MYCNF} --no-timestamp --incremental --throttle=30 ${BACKUP_PATH}/${increase_bak}_${PORT

} --incremental-lsn=${CHECKPOINT} >>${LOG_FILE} 2>&1

        if [ $? -eq 0 ] && [ `tail -10 "${LOG_FILE}" | grep -ic "completed OK"` -eq 1 ];then

                tar zcfi ${increase_bak}_${PORT}.tar.gz ${increase_bak}/

                rm -rf ${increase_bak}_${PORT}

                echo "${increase_bak}_${PORT}.tar.gz backup successed" >>${bak_log}

                echo -e "ok\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

        else

                echo "${increase_bak}_${PORT} backup failed" >>${bak_log}

                echo -e "failure\n${increase_bak}_${PORT}.tar.gz" >${MYSQL_LOG}

                exit 1

        fi
}

increase_bak

del_bakfile ()

{

        for dbfile in `find "${BACKUP_PATH}/" -name "[0-9]*.tar.gz" -type f -mtime +${DAY}`; do

                rm -f ${dbfile}

        done

}

del_bakfile

bakmysql ()

{

        for i in ${exclude_time[@]};do

                [ "${Hour}" -eq "$i" ] && exit 0

        done

        if [  "${Hour}" -eq "${default_time}" ];then

                complete_bak

        elif [  "${Hour}" -ne "${default_time}" -a ${open_increase_bak} -eq 0 ];then

                increase_bak

        else

                exit 0

        fi

}

bakmysql

 

case $# in

        0)

        bakmysql

        del_bakfile

        ;;

        1)

        if [ "$1" = "all" ];then

                complete_bak

                del_bakfile

        elif [  "$1" = "inc" ];then

                increase_bak

                del_bakfile

        else

                echo "sh $0 all or sh $0 inc" && exit 0

        fi

        ;;

esac

逻辑备份

mysqldump工具：逻辑备份工具，适用所有存储引擎温备；支持完全或部分备份；对InnoDB存储引擎支持热备；Schema（数据库的定义）和数据存储在一起。

mysqldump参考：
https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
语法： 
 mysqldump [OPTIONS] database [tables] 
 mysqldump [OPTIONS] –B DB1 [DB2 DB3...]
 mysqldump [OPTIONS] –A [OPTIONS]

选项：
    -A：备份所有库
    -B db_name1,[db_name2,...]：备份指定库
    -E：备份相关的所有event scheduler
    -R：备份所有存储过程和存储函数
    --triggers：备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
    --master-data={1|2}：
         1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定默认为1
         2：记录为注释的CHANGE MASTER TO语句，注意：此选项会自动关闭--lock-tables功能，自动打开--lock-all-tables功能（除非开启--single-transaction）
    -F：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A时，会导致刷新多次数据库，在同一时刻执行转储和日志刷新，
则应同时使用--flush-logs和-x，--master-data或-single-transaction,此时只刷新一次；建议：和-x，--master-data或 --single-transaction一起使用
    --compact 去掉注释，适合调试，生产不使用
    -d：只备份表结构
    -t：只备份数据，不备份create table
    -n：不备份create database，可被-A或-B覆盖
    --flush-privileges：备份前刷新授权表，备份mysql库或相关时需要使用
    -f：忽略SQL错误，继续执行
    --hex-blob：使用十六进制符号转储二进制列（例如，“abc”变为0x616263），受影响的数据类型包括BINARY， VARBINARY，BLOB，BIT
    -q：不缓存查询，直接输出，加快备份速度

InnoDB建议备份策略：
    mysqldump –uroot –A –F –E –R  --single-transaction --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

MyISAM建议备份策略：
    mysqldump –uroot –A –F –E –R –x --master-data=1 --flush-privileges  --triggers --hex-blob >$BACKUP/fullbak_$BACKUP_TIME.sql

补充
1、 null 和空值有什么区别？
null 占用空间、空不占空间
在查询的时候：
count会忽略null的行数，但不会忽略空值

2、约束条件限制了not null可以插入空值吗？
可以插入空值
不可以插入null

3、 null 和空哪个查询效率更高
使用 is not null的时候可以过滤掉 null的值，但是空值过滤不掉
使用count会过滤掉null值，但不会过滤掉空值

posted @ 2019-11-01 18:47 GeminiMp 阅读(148) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

GeminiMp

路漫漫其修远兮，吾将上下而求索。没有对比就没有动力。

MySQL其他和备份

事务

存储引擎

InnoDB存储引擎

数据存储形式

锁的粒度

事务

数据的存储特点

MyISAM存储引擎

数据存储形式

锁的粒度

事务

数据的存储特点

其他

对比与选择

视图

触发器

存储过程

函数

备份

物理备份

逻辑备份

公告

GeminiMp

路漫漫其修远兮，吾将上下而求索。 没有对比就没有动力。

MySQL其他和备份

事务

存储引擎

InnoDB存储引擎

数据存储形式

锁的粒度

事务

数据的存储特点

MyISAM存储引擎

数据存储形式

锁的粒度

事务

数据的存储特点

其他

对比与选择

视图

触发器

存储过程

函数

备份

物理备份

逻辑备份

公告

路漫漫其修远兮，吾将上下而求索。没有对比就没有动力。