InnoDB事务原理
事务的(ACID)特征
事物隔离级别
InnoDB与MyISAM区别
行级锁 & 表级锁 & 页级锁
### 行锁
'''
client1中执行:
select * from shop where id=1 for update;
clenet2中执行:
select * from shop where id=2 for update; # 可以正常放回数据
select * from shop where id=1 for update; # 阻塞
'''
# 可以看到:id是主键,当在client1上查询id=1的数据时候,在client2上查询id=2的数据没问题
# 但在client2上查询id=1的数据时阻塞,说明此时的锁时行锁。
# 当client1执行commit时,clinet2查询的id=1的命令立即返回数据。
### 表锁
# 可以看到,client1通过非索引的name字段查询到prod11的数据后,在client2查prod**的数据会阻塞,产生表锁。
'''
client1中执行:
select * from shop where name="prod11" for update;
clenet2中执行:
select * from shop where name="prod**" for update;
'''
锁分类
乐观锁 & 悲观锁
# 悲观锁的实现,往往依靠数据库提供的锁机制
# MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞,排他锁包含行锁、表锁。
# 申请前提:没有线程对该结果集中的任何行数据使用排他锁或共享锁,否则申请会阻塞。
# 适用场景:悲观锁适合写入频繁的场景。
# 注:
# 首先我们需要set autocommit=0,即不允许自动提交
# 用法:select * from tablename where id = 1 for update;
示例:对商品数量-1操作
## 悲观锁实现加一操作代码
# 我们可以看到,首先通过begin开启一个事物,在获得shop信息和修改数据的整个过程中都对数据加锁,保证了数据的一致性。
'''
begin;
select id,name,stock as old_stock from shop where id=1 for update;
update shop set stock=stock-1 where id=1 and stock=old_stock;
commit
'''
## 乐观锁实现加一操作代码
# 我们可以看到,只有当对数量-1操作时才会加锁,只有当程序中值和数据库中的值相等时才正真执行。
'''
//不加锁
select id,name,stock where id=1;
//业务处理
begin;
update shop set stock=stock-1 where id=1 and stock=stock;
commit;
'''
乐观锁解决事物问题
# with语句用法
from django.db import transaction
def viewfunc(request):
# 这部分代码不在事务中,会被Django自动提交
...
with transaction.atomic():
# 这部分代码会在事务中执行
...
'''
from django.db import transaction
# 创建保存点
save_id = transaction.savepoint()
# 回滚到保存点
transaction.savepoint_rollback(save_id)
# 提交从保存点到当前状态的所有数据库事务操作
transaction.savepoint_commit(save_id)
'''
from django.db import transaction
def create(self, validated_data):
"""
保存订单
"""
# 获取当前下单用户
user = self.context['request'].user
# 组织订单编号 20170903153611+user.id
# timezone.now() -> datetime
order_id = timezone.now().strftime('%Y%m%d%H%M%S') + ('%09d' % user.id)
address = validated_data['address']
pay_method = validated_data['pay_method']
# 生成订单
with transaction.atomic():
# 创建一个保存点
save_id = transaction.savepoint()
try:
# 创建订单信息
order = OrderInfo.objects.create(
order_id=order_id,
user=user,
address=address,
total_count=0,
total_amount=Decimal(0),
freight=Decimal(10),
pay_method=pay_method,
status=OrderInfo.ORDER_STATUS_ENUM['UNSEND'] if pay_method == OrderInfo.PAY_METHODS_ENUM['CASH'] else OrderInfo.ORDER_STATUS_ENUM['UNPAID']
)
# 获取购物车信息
redis_conn = get_redis_connection("cart")
redis_cart = redis_conn.hgetall("cart_%s" % user.id)
cart_selected = redis_conn.smembers('cart_selected_%s' % user.id)
# 将bytes类型转换为int类型
cart = {}
for sku_id in cart_selected:
cart[int(sku_id)] = int(redis_cart[sku_id])
# 一次查询出所有商品数据
skus = SKU.objects.filter(id__in=cart.keys())
# 处理订单商品
for sku in skus:
sku_count = cart[sku.id]
# 判断库存
origin_stock = sku.stock # 原始库存
origin_sales = sku.sales # 原始销量
if sku_count > origin_stock:
transaction.savepoint_rollback(save_id)
raise serializers.ValidationError('商品库存不足')
# 用于演示并发下单
# import time
# time.sleep(5)
# 减少库存
new_stock = origin_stock - sku_count
new_sales = origin_sales + sku_count
sku.stock = new_stock
sku.sales = new_sales
sku.save()
# 累计商品的SPU 销量信息
sku.goods.sales += sku_count
sku.goods.save()
# 累计订单基本信息的数据
order.total_count += sku_count # 累计总金额
order.total_amount += (sku.price * sku_count) # 累计总额
# 保存订单商品
OrderGoods.objects.create(
order=order,
sku=sku,
count=sku_count,
price=sku.price,
)
# 更新订单的金额数量信息
order.total_amount += order.freight
order.save()
except ValidationError:
raise
except Exception as e:
logger.error(e)
transaction.savepoint_rollback(save_id)
raise
# 提交事务
transaction.savepoint_commit(save_id)
# 更新redis中保存的购物车数据
pl = redis_conn.pipeline()
pl.hdel('cart_%s' % user.id, *cart_selected)
pl.srem('cart_selected_%s' % user.id, *cart_selected)
pl.execute()
return order
mysql> show variables like '%storage_engine%'; # 查看数据默认引擎
mysql> select table_name,`engine` from information_schema.tables where table_schema = 'weibo'; # 查看"weibo"这个数据库所有表默认引擎
mysql> select CONCAT('alter table ',table_name,' engine=InnoDB;') FROM information_schema.tables WHERE table_schema="weibo" AND ENGINE="MyISAM"; # 生成修改表引擎的语法
alter table auth_group engine=InnoDB;
alter table auth_group_permissions engine=InnoDB;
alter table auth_permission engine=InnoDB;
alter table authtoken_token engine=InnoDB;
alter table django_admin_log engine=InnoDB;
alter table django_content_type engine=InnoDB;
alter table django_migrations engine=InnoDB;
alter table django_session engine=InnoDB;
alter table users_socialuser engine=InnoDB;
alter table users_user engine=InnoDB;
alter table users_user_groups engine=InnoDB;
alter table users_user_user_permissions engine=InnoDB;
共享锁 & 排它锁
# SELECT … LOCK IN SHARE MODE;
# SELECT … FOR UPDATE
数据库引擎
数据库引擎任务
MySQL数据库引擎类别
mysql数据引擎更换方式
# show engines;
我的查询结果如下:
create table mytbl(
id int primary key,
name varchar(50)
)type=MyISAM;
# alter table mytbl2 type = InnoDB;
# 方式1:
show table status from mytest;
# 方式2:
show create table table_name
MyIASM引擎
两种引擎的选择
Index(索引)
MyIASM引擎的索引结构
Innodb引擎的索引结构
两者区别:
什么是索引?为什么要建立索引?
MySQL中索引的优点和缺点和使用原则
索引的分类
索引操作(创建和删除)
# 测试:看是否使用了索引进行查询。
EXPLAIN SELECT * FROM book WHERE year_publication = 1990\G;
创建唯一索引
CREATE TABLE t1
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);
创建主键索引
CREATE TABLE t2
(
id INT NOT NULL,
name CHAR(10),
PRIMARY KEY(id)
);
INSERT INTO t2 VALUES(1,'QQQ');
EXPLAIN SELECT * FROM t2 WHERE id = 1\G;
创建单列索引
创建组合索引
CREATE TABLE t3
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id,name,age)
);
SHOW CREATE t3\G;
解释最左前缀
# 在t3表中,查询id和name字段
EXPLAIN SELECT * FROM t3 WHERE id = 1 AND name = 'joe'\G;
# 在t3表中,查询(age,name)字段,这样就不会使用索引查询。来看看结果
EXPLAIN SELECT * FROM t3 WHERE age = 3 AND name = 'bob'\G;
创建全文索引
CREATE TABLE t4
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
)
ENGINE=MyISAM;
SHOW CREATE TABLE t4\G;
# 使用一下什么叫做全文搜索。就是在很多文字中,通过关键字就能够找到该记录。
INSERT INTO t4 VALUES(8,'AAA',3,'text is so good,hei,my name is bob'),(9,'BBB',4,'my name isgorlr');
SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');
# EXPLAIN SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr');
mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('MySQL');
创建空间索引
CREATE TABLE t5
(
g GEOMETRY NOT NULL,
SPATIAL INDEX spatIdx(g)
)
ENGINE = MyISAM;
SHOW CREATE TABLE t5\G;
在已经存在的表上创建索引
# 格式:ALTER TABLE 表名 ADD[UNIQUE|FULLTEXT|SPATIAL]
[INDEX|KEY] [索引名] (索引字段名)[ASC|DESC]
# 有了上面的基础,这里就不用过多陈述了。
# 命令一:SHOW INDEX FROM 表名\G
# 查看一张表中所创建的索引
SHOW INDEX FROM book\G;
为表添加索引
# 就拿上面的book表来说。本来已经有了一个year_publication,现在我们为该表在加一个普通索引
ALTER TABLE book ADD INDEX BkNameIdx(bookname(30));
使用CREATE INDEX创建索引
# 格式:CREATE [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] 索引名称 ON 表名(创建索引的字段名[length])[ASC|DESC]
# 解释:其实就是换汤不换药,格式改变了一下而已,做的事情跟上面完全一样,做一个例子。
# 在为book表增加一个普通索引,字段为authors。
CREATE INDEX BkBookNameIdx ON book(bookname);
SHOW INDEX FROM book\G; # 查看book表中的索引
注意:第一条截图没截到,因为图太大了,这里只要看到有我们新加进去的索引就证明成功了。。其他索引也是一样的创建。
删除索引
SHOW INDEX FROM表名\G; # \G只是让输出的格式更好看
# 格式一:ALTER TABLE 表名 DROP INDEX 索引名。
# 很简单的语句,现在通过一个例子来看看,还是对book表进行操作,删除我们刚才为其添加的索引。
# 删除book表中的名称为BkBookNameIdx的索引。
ALTER TABLE book DROPINDEX BkBookNameIdx;
SHOW INDEX FROM book\G; # 在查看book表中的索引,就会发现BkBookNameIdx这个索引已经不在了
# 格式二:DROP INDEX 索引名 ON 表名;
# 删除book表中名为BkNameIdx的索引
DROP INDEX BkNameIdx ON book;
SHOW INDEX FROM book\G;
温馨提示
优点主要有
windows上的mysql主从复制搭建
[mysqld]
# mysqld 配置
port=3307
basedir=D:\laravel\mysql2
datadir=D:\laravel\mysql2\data
[mysqld]
port=3306
basedir="F:/myphp_www/PHPTutorial/MySQL/"
datadir="F:/myphp_www/PHPTutorial/MySQL/data/"
net start mysql # 启动mysql服务
Net stop mysql # 停止mysql服务
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO mysql_backup@'%'
IDENTIFIED BY '123456';
# 一个账号为mysql_backup的用户创建成功了,密码为123456
# 刷新数据库权限:flush privileges;
[mysqld]
#mysqld 配置
port=3307
basedir=D:\laravel\mysql2
datadir=D:\laravel\mysql2\data
server-id=1
log-bin=mysql-bin # 开启了二进制文件
binlog_do_db=follow # 主从复制的数据库
binlog_ignore_db=mysql # 不参与数从复制的数据库,例如mysql
binlog_checksum=none # mysql主从复制版本高
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 配置完成只好在cmd中重启我的mysql2
# navicat中重启master之后并查看master的状态:SHOW MASTER STATUS
# 重置master:reset master;
[mysqld]
port=3306
basedir="F:/myphp_www/PHPTutorial/MySQL/"
datadir="F:/myphp_www/PHPTutorial/MySQL/data/"
character-set-server=utf8
default-storage-engine=MyISAM
server_id=2
log-bin=mysql-bin
binlog-do-db=follow #同步的数据库
binlog-ignore-db=mysql #同步的数据库
# 重启mysql之后通过配置的账号密码链接主数据库,根据master的状态自定:CHANGE MASTER TO master_host = '127.0.0.1',
master_user = 'mysql_backup',
master_password = '123456',
master_log_file = 'mysql-bin.000004',
master_port = 3307;
master_log_pos = 120;
# 重启slave:START SLAVE
# 重置slave:RESET SLAVE
# 查看从数据库的状态:show slave status;
# 当相应的结果中,slave_IO_Running与slave_SQL_Running两个线程都为Yes时,主从配置成功。
Linux的mysql主从复制搭建:
server_id=1 #主机的标识
log-bin=mysql-bin.log #确保可写入的日志文件
binlog_format=mixed #二进制日志的格式,
binlog-do-db=master #允许主从复制数据库
binlog-ignore-db=mysql #不允许主从复制的数据库
# ~~~~~~~~~~~~~~~~~~~~重新启动mysql服务
server_id=2 #主机的标识
log-bin=mysql-bin.log #确保可写入的日志文件
binlog_format=mixed #二进制日志的格式,
replicate_wild_do_table=oldboy.%
replicate_wild_ignore_table=mysql.%
position: # 随着bin_log的日志文件更新内容,发生的变化
binlog_do_db: # 主从复制的数据库
binlog_ignore_db: # 不允许主从复制的数据库
(3)unlock tables;
stop slave;
change master to master_host='119.27.169.173',master_user='slave',master_password='1234',master_log_file='mysql-bin.000006',master_log_pos=245;
start slave;
当Slave_IO_Running和Slave_SQL_Running线程都为yes是主从复制配置成功!
事务
的形式保存在磁盘中,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。日志管理
修改配置文件 # my.cnf
配置 log-bin 和 log-bin-index 的值,如果没有则自行加上去。
log-bin=mysql-bin
log-bin-index=mysql-bin.index
# 这里的 log-bin 是指以后生成各 Binlog 文件的前缀,比如上述使用master-bin,那么文件就将会是master-bin.000001、
master-bin.000002 等。
# log-bin-index 则指 binlog index 文件的名称,这里我们设置为master-bin.index,可以不配置。
mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------------------------+
| log_bin | ON |
| log_bin_basename | D:\Program Files\MySQL\data\mysql-bin |
| log_bin_index | D:\Program Files\MySQL\data\mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+---------------------------------------------+
6 rows in set (0.07 sec)
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 202 | No |
| mysql-bin.000002 | 2062 | No |
+------------------+-----------+-----------+
2 rows in set (0.07 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 2062 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.08 sec)
显示正在写入的二进制文件,及当前position
mysql> flush logs;
Query OK, 0 rows affected (0.12 sec)
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 202 | No |
| mysql-bin.000002 | 2109 | No |
| mysql-bin.000003 | 155 | No |
+------------------+-----------+-----------+
3 rows in set (0.07 sec)
mysql> reset master;
D:\Program Files\MySQL
$ bin\mysqlbinlog data\mysql-bin.000002
内容:
BINLOG '
K3L4XBMBAAAARQAAAHEGAAAAAJoCAAAAAAEACmxvbmdodWJhbmcABXRoZW1lAAUDDwUREQWWAAgA
AAABAQACASGhIgQL
K3L4XB4BAAAAPQAAAK4GAAAAAJoCAAAAAAEAAgAF/wA0AQAABGFhYWEAAAAAAMBYQFz4citc+HIr
sXjMIA==
'/*!*/;
# at 1710
#190606 9:53:47 server id 1 end_log_pos 1741 CRC32 0xddb08f33 Xid = 216
COMMIT/*!*/;
# at 1741
#190606 9:53:47 server id 1 end_log_pos 1820 CRC32 0x166b4128 Anonymous_GTID last_committed=5 sequence_number=6 rbr_only=yes original_committed_timestamp=1559786027387679 immediate_commit_timestamp=15597860273
transaction_length=321
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)
# immediate_commit_timestamp=1559786027387679 (2019-06-06 09:53:47.387679 ?D1ú±ê×?ê±??)
/*!80001 SET @@session.original_commit_timestamp=1559786027387679*//*!*/;
/*!80014 SET @@session.original_server_version=80016*//*!*/;
/*!80014 SET @@session.immediate_server_version=80016*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1820
#190606 9:53:47 server id 1 end_log_pos 1901 CRC32 0x47def222 Query thread_id=10 exec_time=0 error_code=0
SET TIMESTAMP=1559786027/*!*/;
BEGIN
/*!*/;
# at 1901
#190606 9:53:47 server id 1 end_log_pos 1970 CRC32 0x5a235198 Table_map: `longhubang`.`theme` mapped to number 666
# at 1970
#190606 9:53:47 server id 1 end_log_pos 2031 CRC32 0x62dc1928 Write_rows: table id 666 flags: STMT_END_F
# A.查询第一个(最早)的binlog日志:
mysql> show binlog events;
# B.指定查询 mysql-bin.000021 这个文件:
mysql> show binlog events in 'mysql-bin.000021';
# C.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起:
mysql> show binlog events in 'mysql-bin.000021' from 8224;
# D.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 10;
# E.指定查询 mysql-bin.000021 这个文件,从pos点:8224开始查起,偏移2行,查询10条
mysql> show binlog events in 'mysql-bin.000021' from 8224 limit 2,10;
内容:
mysql> show binlog events in 'mysql-bin.000002' from 1710 limit 10;
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
| mysql-bin.000002 | 1710 | Xid | 1 | 1741 | COMMIT /* xid=216 */ |
| mysql-bin.000002 | 1741 | Anonymous_Gtid | 1 | 1820 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 1820 | Query | 1 | 1901 | BEGIN |
| mysql-bin.000002 | 1901 | Table_map | 1 | 1970 | table_id: 666 (longhubang.theme) |
| mysql-bin.000002 | 1970 | Write_rows | 1 | 2031 | table_id: 666 flags: STMT_END_F |
| mysql-bin.000002 | 2031 | Xid | 1 | 2062 | COMMIT /* xid=223 */ |
| mysql-bin.000002 | 2062 | Rotate | 1 | 2109 | mysql-bin.000003;pos=4 |
+------------------+------+----------------+-----------+-------------+--------------------------------------+
7 rows in set (0.14 sec)
D:\Program Files\MySQL
$ bin\mysqldump -h127.0.0.1 -p3306 -uroot -phongda$123456 -lF -B longhubang >D:\data\backup\longhubang.dump
mysqldump: [Warning] Using a password on the command line interface can be insecure.
-lF
,注意必须大写F,当备份工作刚开始时系统会刷新log日志,产生新的binlog日志来记录备份之后的数据库“增删改”操作。查看一下:
mysql> show binary logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 202 | No |
| mysql-bin.000002 | 2109 | No |
| mysql-bin.000003 | 374 | No |
| mysql-bin.000004 | 155 | No |
+------------------+-----------+-----------+
4 rows in set (0.10 sec)
也就是说, mysql-bin.000004 是用来记录完全备份命令时间之后对数据库的所有“增删改”操作。
Linux数据备份命令:
/usr/local/mysql/bin/mysqldump -uroot -p123456 -lF --log-error=/root/myDump.err -B zyyshop > /root/BAK.zyyshop.sql
mysql> flush logs;
查看binlog日志:
mysql> show binlog events in 'mysql-bin.000004';
最后一段日志内容:
| mysql-bin.000004 | 3976 | Xid | 1 | 4007 | COMMIT /* xid=2375 */ |
| mysql-bin.000004 | 4007 | Anonymous_Gtid | 1 | 4086 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 4086 | Query | 1 | 4167 | BEGIN |
| mysql-bin.000004 | 4167 | Table_map | 1 | 4236 | table_id: 666 (longhubang.theme) |
| mysql-bin.000004 | 4236 | Delete_rows | 1 | 4505 | table_id: 666 flags: STMT_END_F |
| mysql-bin.000004 | 4505 | Xid | 1 | 4536 | COMMIT /* xid=2393 */ |
| mysql-bin.000004 | 4536 | Anonymous_Gtid | 1 | 4613 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000004 | 4613 | Query | 1 | 4736 | drop database longhubang /* xid=2411 */ |
| mysql-bin.000004 | 4736 | Rotate | 1 | 4783 | mysql-bin.000005;pos=4 |
+------------------+------+----------------+-----------+-------------+------------------------------------------+
70 rows in set (0.21 sec)
先进行完全备份恢复:
D:\Program Files\MySQL
$ bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 -v <D:\data\backup\longhubang.dump
binlog日志恢复:
D:\Program Files\MySQL
$ bin\mysqlbinlog --stop-position=4613 data\mysql-bin.000004 | bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 longhubang
mysql: [Warning] Using a password on the command line interface can be insecure.
增量数据恢复语法格式:
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名
常用选项:
--start-position=953 起始pos点
--stop-position=1437 结束pos点
--start-datetime="2013-11-29 13:18:54" 起始时间点
--stop-datetime="2013-11-29 13:21:53" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库,只限本地log日志)
不常用选项:
-u --user=name Connect to the remote server as username.连接到远程主机的用户名
-p --password[=name] Password to connect to remote server.连接到远程主机的密码
-h --host=name Get the binlog from server.从远程主机上获取binlog日志
--read-from-remote-server Read binary logs from a MySQL server.从某个MySQL服务器上读取binlog日志
小结:实际是将读出的binlog日志内容,通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径;
上面的binlog恢复语句也可以拆分:
D:\Program Files\MySQL
$ bin\mysqlbinlog --stop-position=4613 data\mysql-bin.000004 > D:\data\backup\004.sql
D:\Program Files\MySQL
$ bin\mysql -h127.0.0.1 -p3306 -uroot -phongda$123456 longhubang
mysql: [Warning] Using a password on the command line interface can be insecure.
.......
mysql> source D:\data\backup\004.sql
所谓恢复,就是让mysql将保存在binlog日志中指定段落区间的sql语句逐个重新执行一次而已。
复制是mysql最重要的功能之一,mysql集群的高可用、负载均衡和读写分离都是基于复制来实现的;从5.6开始复制有两种实现方式,基于binlog和基于GTID(全局事务标示符)
其复制的基本过程如下: