本文共 22171 字，大约阅读时间需要 73 分钟。
逻辑备份:
备份内容:数据库的结构定义语句+数据内容的插入语句，备份出来的文件是可以编辑的。
适用场景:数据量少的数据库，比较适合100G数据量以内的。
逻辑备份的特点:
1) sql语句组成的文件,可以截取部分单独使用。
2)备份文件比物理的小。
3)可以细化到表/表的内容
4)速度慢
5)可以跨平台恢复/迁移
6）只能在线备份，在线恢复。
逻辑备份的工具:
1) mysqldump(单线程)、mysqlpump(多线程)∶官方MySQL
2) mydumper:开源的，基于mysqldump的一个优化，多线程，速度介于两者之间:
5.7之前主用，5.7之后也用得特别多，特别是互联网的业务，很多都是几个G,几十个G的数据量。
create databases; create table;insert into;
存储过程;触发器函数:调度事件
mysqldump --help | more
:my.cnf
[client]
[mysqldump]
mysqldump -uroot -proot -h192.168.1.51 -P3306
-u--user=
-p –password
-h --host=
-P --port=
--protocol= tcp,sockrt,memory
--max-allowed-packet=
--net-buffer-legth
可以备份:所有数据库、几个数据库、一个数据库、一个表、几个表、一个表里面的内容、存储过程、函数、触发器、调度事件。
-A, --all-databases
-B, --databases database[tables]
-y, -all-tablespaces
-n,--no-create-db 不导致数据库结构
-t,--no-create-info 不导出表结构 只是导入数据
-d,--no-data 不导出数据,只导结构
-R,--routines不导出存储过程和函数，默认不导出
--triggers导出触发器
--skip-triggers不导出触发器
-E,--events 导出调度事件
--ignore-table 不导出某个库的某个表，或者不导出几个表: itpuxdb1.itpuxt1
-w, --where='’
--single-transaction:可以得到一致性的导出结果。只针对innodb，导出过程中不允许运行表的DDL操作。因为事务持有表的metadata lock的共享锁，而DDL会申请metadata lock的互斥锁，所以会阻塞。
--single-transaction还会关掉你默认的--lock-tables选项（即不加锁），因为mysqldump默认会打开一个lock-tables,在导出的过程中，锁定表。那么只能加--master-data才能加锁。
默认打开的。这个锁表是导一个锁一个，导完解锁。
会把所有的表都给锁了，慢慢导,导完解锁。
上面这三个参数是互斥的,只能同时用一个。
导出数据时刷新二进制日志,达到一致性导出。
一般是三个参数一起使用
有3个值:
0:默认就是0，不写入binlog日志记录
1:change master to ....记录binlog文件及终点
2:#change master to ....记录binlog文件及终点
mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --flush-privileges
有3个值:
0:默认就是0，不写入binlog日志记录
1:change master to ....记录binlog文件及终点
2:#change master to ....记录binlog文件及终点
在从库上面使用的，和--master-data参数一样，是为了slave建立下一级的slave
--master-data=1类似
结合--dump-slave=1/2，在导出中加入host和端口
在备份之后，删除master的bin log日志，默认打开--master-data=2，一般不用，因为日志一般不能随便删除。
用于在gtid的环境使用
--set-charset
默认开启，--set-charset=1/0，是否开启字符集
--default-character-set
指定是什么字符集，utf8,gbk,utf8mb4
-N,--no-set-names
关闭--set-charset
--add-drop-database(add:drop database ddl)，先删除数据库
--add-drop-table,先删除表，默认打开的，禁用:--skip-add-drop-table
--add-drop-trigger先删除触发器
--no-create-db,-n不创建数据库
--no-create-info,-t不创建表
-f,--force强制性导出
--log-error=/tmp/1.log
--compatible=(oracle/mssql/postgresql)
需要打开mysql通用日志general_log。
show variables like '%general_log%';
| Variable_name | Value |
| general_log | OFF |
| general_log_file | /mysql/log/3306/itpuxdb-general.err |
mysqldump -uroot -proot --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --all-databases > /mysql/backup/full.sql
more /mysql/log/3306/itpuxdb-general.err
FLUSH /*!40101 LOCAL */ TABLES
closes all open tables, forces all table in use to be closed,and flushes the query cache.
myisam:将脏数据刷到文件，同时关闭文件描述符，关闭文件。
innodb:并不会真正的关闭文件描述符，同时也不会写脏数据，所以这个功能在innodb中用处不大。
FLUSH TABLES WITH READ LOCK
官方简称：FTWRL 执行FLUSH tables操作,会加一个全局读锁，主要是作用一致性备份。
主要是避免比较长的事务没有关闭，会导致FLUSH tables with read lock操作一直得不到锁，就会阻塞其他客户端的操作
FTWRL:一般需要持有两把全局的MDL锁(metadata lock),而且还需要关闭所有的表对象。
FTWRL主要有三个步骤
1 上全局锁
2 清理表缓存
3 上全局commit锁
设置当前的事务隔离级别为RR，避免不可重复读和幻读
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
获取当前数据库的一个快照，由--single-transaction决定。只适合支持事务的表，就是innodb引擎。
主要是在开启事务的时候，对所有的表做一次select操作，得到一个快照，备份时就可以一致。
start transaction:别人插入数据，本会话也能看见，出现备份不一致。
start transaction with consistent snapshot :对之前的数据可见，对后面的新数据不可见。
这个由--master-data参数决定,记录了开始备份时，binlog的状态信息，包括binlog file和log position.
快照加锁以后释放锁
开始查看需要备份的数据库
备份数据库和表结构
SHOW CREATE DATABASE IF NOT EXISTS `itpuxdb`
show create table `dd`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `dd`
开始备份表，但是查询出来的数据，不放在缓存中
备份表都是从这里开始的
默认会加上
SHOW TRIGGERS LIKE 'dd'
SHOW FUNCTION STATUS WHERE Db = 'itpuxdb'
SHOW PROCEDURE STATUS WHERE Db = 'itpuxdb'
2021-02-13T17:23:19.821413+08:00 4 Query SAVEPOINT sp
2021-02-13T17:23:19.826205+08:00 4 Query ROLLBACK TO SAVEPOINT sp
2021-02-13T17:25:36.573080+08:00 4 Query RELEASE SAVEPOINT sp
savepoint sp
rollback to savepoint sp
release savepoint sp
设置savepointh点，然后备份完了后再回滚到savepoint;
这样做的好处，不会阻塞在备份期间对已经备份完的表的DDL操作。
主要是提高并发性
--重要:不能在业务高峰期做备份。
-- mysqldump的大致实现过程是:
连接
初始化信息刷新表
（锁表)
记录偏移量
开启事务(一致性快照)
记录偏移量
解锁表
查看要备份的表
开始备份所有的数据
备份 触发器 和函数和存储过程
总结:会在整个导出过程中lock read local所有的表。针对innodb只能读，myisam别人可以读和插入，但是阻塞update.
总结:请求一个全局的读锁，会阻塞所有有的写入操作(insert, update,delete)，保证数据的一致性，备份完了以后，会话断开自动解锁。
总结:单独使用，不会有任何锁，但是会对备份的表持有metedata lock的共享锁。
加二进制日志
总结:结合使用时，也就是在开始的时候会短暂的请求一个全局的读锁，会阻止所有表的写入操作。
--如果不加--databases参数，是不会出现“创建数据库”的语句在备份脚本中。
--flush-logs 刷新日志
mysqldump -uroot -proot -R -E --flush-logs --all-databases > /mysql/backup/fullbak20210213sql
mysqldump -uroot -proot --all-databases --no-data > /mysql/backup/20210213nodata.sql
mysqldump -uroot -proot itpuxdb itpux_m5 > /mysql/backup/20210213itpux_m5.sql
mysqldump -uroot -proot --no-data itpuxdb itpux_m5 > /mysql/backup/20210213nodataitpux_m5.sql
mysqldump -uroot -proot itpuxdb itpux_m5 itpux_sales > /mysql/backup/20210213m5-sales.sql
mysqldump -uroot -proot --no-create-info itpuxdb itpux_m5 itpux_sales > /mysql/backup/20210213m5-sales.sql
select* from itpux.dept into outfile '/mysql/backup/itpux-dept.sql';
mysqldump -uroot -proot itpuxdb > /mysql/backup/20210213dbitpuxdb.sql
mysqldump -uroot -proot --databases itpuxdb > /mysql/backup/20210213dbitpuxdb.sql
mysqldump -uroot -proot --no-data --databases itpuxdb mysql > /mysql/backup/20210213dbitpuxdb.sql
mysqldump -uroot -proot --databases syj --ignore-table=syj.m5 > /mysql/backup/20210213dbsyjnom5.sql
mysqldump -uroot -proot syj gw --where="MIN_SALARY>7000" > /mysql/backup/20210213dbsyjgw7000.sql
mysqldump -uroot -proot --databases syj gw --where="MIN_SALARY>7000" > /mysql/backup/20210213dbsyjgw7000.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `m5` WHERE MIN_SALARY>7000': Unknown column 'MIN_SALARY' in 'where clause' (1054)
加了--databases 会把这个数据库其他表有这个列的数据导出来，垃圾。
mysqldump -uroot -proot -R -E --single-transaction --master-data=2 --flush-logs --routines --all-databases > /mysql/backup/full.sql
全备Innodb
mysqldump -uroot -proot -R -E --single-transaction --master-data=2 --flush-logs --routines --all-databases > /mysql/backup/innodbdata.sql
备份系统数据库
mysqldump -uroot -proot --skip-lock-tables --databases information_schema performance_schema mysql sys > /mysql/backup/innodbsysdata.sql
备份部分数据库
mysqldump -uroot -proot -R -E --single-transaction --master-data=2 --flush-logs --routines –databases syj > /mysql/backup/innodbdatasyj.sql
备份权限数据库
mysqldump -uroot -proot --flush-privileges –databases mysql> /mysql/backup/innodbdatamysql.sql
全备myisam
mysqldump -uroot -proot -R -E --lock-all-tables --master-data=2 --flush-logs --flush-privileges --routines --all-databases > /mysql/backup/isamdata.sql
将binlog日志保存起来,保存之前刷新日志。
flush logs;
or:
mysqladmin -uroot -proot flush logs
线上数据库备份场景:
1全备:每天晚上2:00执行全备脚本。
2增量:每天13点备份binlog日志。
3 准备目录空间（不能和数据库放在同一个磁盘或存储，安全+性能)
#!/bin/bash
# script mysqldump to Full backup mysql data per day!
DataBakDir=/mysql/backup ##备份的目录
LogOutFile=/mysql/backup/bak-db.log ##备份输出的日志文件
LogErrOutFile=/mysql/backup/bak-db-err.log ##备份输出的错误日志文件
BinLogBakDir=/mysql/backup/backup-binlog ##二进制日志备份的地方
MyCNF=/mysql/data/3306/my.cnf ##MySQL的配置文件
mysql_host=192.168.247.131 ##MySQL的IP
mysql_port=3306 ##MySQL的端口
mysql_user=root ##MySQL的用户名
mysql_pass=root ##MYSQL的米面
Date=`date +%Y%m%d` ## 时间
Begin=`date +"%Y-%m-%d %H:%M:%S"` ## 备份开始时间
cd $DataBakDir
DumpFile="dbbackup-alldb-$Date.sql" ##备份的文件名
GZDumpFile=dbbackup-alldb-$Date.sql.gz ##压缩后的文件名
/mysql/app/mysql/bin/mysqldump -u${mysql_user} -p${mysql_pass} --single-transaction --master-data=2 --routines --flush-logs --all-databases --log-error=$LogErrOutFile > $DumpFile
/mysql/app/mysql/bin/mysqldump -u${mysql_user} -p${mysql_pass} --skip-lock-tables --databases performance_schema information_schema sys | gzip > dbbackup-per-inf-sys-$Date.sql.gz
tar -zcvf $GZDumpFile $DumpFile $MyCNF
Last=`date +"%Y-%m-%d %H:%M:%S"`
#Function export user privileges
###下面是权限脚本
mysql_exp_grants()
{
mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW CREATE USER ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
mysql -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \
sed 's#$#;#g;s/^\(CREATE USER for .*\)/-- \1 /;/--/{x;p;x;}'
mysql -B -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
mysql -u${mysql_user} -p${mysql_pass} -N -P${mysql_port} -f $@ | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
}
mysql_exp_grants > ./mysql_exp_grants_out_$Date.sql
echo "data-backup---Start:$Begin;Complete:$Last;$GZDumpFile Out Complete!" >> $LogOutFile
## find $BinLogBakDir -mtime +7 -name "*bin*.*" -exec rm -rf {} \;
find $DataBakDir -mtime +1 -name "*.sql" -exec rm -rf {} \; ##保留一天的原文件
find $DataBakDir -mtime +15 -name "*.gz" -exec rm -rf {} \; ##保留15天的文件
#!/bin/bash
# #script backup mysql binlog everyday!
BinLogBakDir=/mysql/backup/backup-binlog ##二进制备份的目录
BinLogDir=/mysql/log/3306 ##MySQL二进制文件目录
LogOutFile=/mysql/backup/backup-binlog/bak-bin.log ##工作日志文件
/mysql/app/mysql/bin/mysqladmin -uroot -proot -h192.168.247.131 flush-logs ##刷新日志
BinIndexFile=/mysql/log/3306/binlog.index ##二进制的索引文件
NextLogFile=`tail -n 1 $BinIndexFile`
LogCounter=`wc -l $BinIndexFile |awk '{print $1}'`
NextNum=0
#这个for循环用于比对$Counter,$NextNum这两个值来确定文件是不是存在或最新的
echo "--------------------------------------------------------------------" >> $LogOutFile
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Start... >> $LogOutFile
for binfile in `cat $BinIndexFile`
do
base=`basename $binfile`
#basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./
NextNum=`expr $NextNum + 1`
if [ $NextNum -eq $LogCounter ]
then
echo $base skip! >> $LogOutFile
else
dest=$BinLogBakDir/$base
if(test -e $dest)
#test -e用于检测目标文件是否存在，存在就写exist!到$LogFile去
then
echo $base exist! >> $LogOutFile
else
cp $BinLogDir/$base $BinLogBakDir
echo $base copying >> $LogOutFile
fi
fi
done
echo binlog-backup---`date +"%Y-%m-%d %H:%M:%S"` Bakup Complete! Next LogFile is: $NextLogFile >> $LogOutFile
find $BinLogBakDir -mtime +30 -name "*binlog.**" -exec rm -rf {} \;
##删除30天以前的binlog日志
mysqldump -uroot -proot -R -E --single-transaction --master-data=2 --flush-logs --routines --all-databases > /mysql/backup/full.sql
sh /mysql/script/backup-mysqldump-full.sh
或者使用脚本
sh /mysql/script/backup-mysqldump-full.sh
CREATE table syj.gw select * from itpuxdb.gw;
sh /mysql/script/backup-mysql-binlog.sh
使用脚本
执行清理: purge binary log to 'xxxx ';
mysql> purge binary logs to 'binlog.000008';
Query OK, 0 rows affected (0.03 sec)
mysql> purge binary logs before '2021-02-15 12:00:00';
Query OK, 0 rows affected (0.01 sec)
service mysql stop
cd /mysql/data/3306
mv data data2021
[root@mysqldb backup]# cd /mysql/data/3306
[root@mysqldb 3306]# mv data data2021
[root@mysqldb 3306]# ll
总用量 8
drwxr-xr-x 7 mysql mysql 4096 2月 15 16:49 data2021
-rw-r--r-- 1 mysql mysql 3884 2月 15 14:31 my.cnf
--如果是本机,只需要初始数据库。
--如果是异机,需要安装数据库并初始化，并使用原有的参数文件，备份的时候要有原来的参数文件。
mysqld --defaults-file=/mysql/data/3306/my.cnf --initialize --user=mysql --basedir=/mysql/app/mysql --datadir=/mysql/data/3306/data/ --pid-file=/mysql/data/3306/mysql.pid
tail -100f /mysql/log/3306/itpuxdb-error.err
[Note] A temporary password is generated for root@localhost: PaJ;R3(kwTof
service mysql start
mysql -uroot -p --connect-expired-password
alter user 'root '@'localhost' identified by 'root';
mysql -uroot -proot </mysql/backup/dbbackup-alldb-20210215.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqlbinlog binlog文件>文件.sql
mysql -uroot I-p <文件.sql
more /mysql/backup/dbbackup-alldb-20210215.sql
CHANGE MASTER TO MASTER_LOG_FILE='binlog.00001', MASTER_LOG_POS=154;
就回复1了，1以后做的增量，就不用回复了，是全量备份产生的日志。
-rw-r----- 1 mysql mysql 1586 2月 15 17:09 binlog.000001
-rw-r----- 1 mysql mysql 177 2月 15 17:09 binlog.000002
-rw-r----- 1 mysql mysql 177 2月 15 17:10 binlog.000003
-rw-r----- 1 mysql mysql 105218934 2月 15 17:12 binlog.000004
-rw-r----- 1 mysql mysql 105300912 2月 15 17:12 binlog.000005
-rw-r----- 1 mysql mysql 105310787 2月 15 17:12 binlog.000006
-rw-r----- 1 mysql mysql 105332985 2月 15 17:12 binlog.000007
-rw-r----- 1 mysql mysql 105310011 2月 15 17:12 binlog.000008
-rw-r----- 1 mysql mysql 105300795 2月 15 17:12 binlog.000009
mysqlbinlog -uroot -proot /mysql/log/3306/binlog.000001 > binlog000001.sql
mysql -uroot -proot < binlog000001.sql
##数据回复出来了
select count(*) from syj.gw;
19
drop database syj;
Query OK, 5 rows affected (0.09 sec)
cat /mysql/backup/dbbackup-alldb-20210216.sql | grep -i 'create database' | more
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syj` /*!40100 DEFAULT CHARACTER SET utf8 */;
mysql -uroot -proot -o syj </mysql/backup/dbbackup-alldb-20210216.sql
回复成功
sh /mysql/script/backup-mysqldump-full.sh
select now() from dual;
2021-02-16 14:43:24
drop tables syj.sales;
cat /mysql/backup/dbbackup-alldb-20210216.sql | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `sales` /!d;q' | sed -e '/./{H;$!d;}' -e 'x;/CREATE TABLE `sales` /!d;q';
DROP TABLE IF EXISTS `sales`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `sales` (
`ID` decimal(20,0) NOT NULL COMMENT '会员编号',
`NAME` varchar(20) NOT NULL COMMENT '会员名字',
`LOGTIME` datetime DEFAULT NULL COMMENT '消费时间',
`AMOUNT` decimal(20,2) DEFAULT NULL COMMENT '消费总金额',
`GOODS` varchar(20) DEFAULT NULL COMMENT '商品消费分类',
`SCORE` decimal(6,0) DEFAULT NULL COMMENT '满意度评分',
`CARDID` decimal(30,0) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
cat /mysql/backup/dbbackup-alldb-20210216.sql | grep --ignore-case 'insert into 'sales'';
cat /mysql/backup/dbbackup-alldb-20210216.sql | grep --ignore-case 'insert into `sales`' >/mysql/backup/tables-sales.sql
导入数据
[root@mysqldb backup]# mysql -uroot -proot -o syj </mysql/backup/tables-sales.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
2021-02-16 23:37:17
select count(*) from syj.gw;
19
全备日志是000020以前的都可以删除
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000038', MASTER_LOG_POS=154;
purge binary logs to 'binlog.000038';
Query OK, 0 rows affected (0.00 sec)
show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| binlog.000028 | 154 | | |
update syj.gw set min_salary=min_salary+5000;
2021-02-16 23:40:46
update syj.gw set min_salary=min_salary+55000;
2021-02-16 23:43:12
drop database syj;
cat /mysql/backup/dbbackup-alldb-20210216.sql | grep -i 'create database' | more
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `syj` /*!40100 DEFAULT CHARACTER SET utf8 */;
mysql -uroot -proot -o syj </mysql/backup/dbbackup-alldb-20210216.sql
more /mysql/backup/dbbackup-alldb-20210216.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000038', MASTER_LOG_POS=154;
show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000038 | 106006780 |
| binlog.000039 | 105311235 |
| binlog.000040 | 105298657 |
| binlog.000041 | 105334272 |
| binlog.000042 | 105320674 |
| binlog.000043 | 105301328 |
| binlog.000044 | 55424946 |
show binlog events in 'binlog.000038';
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+------+----------------+-----------+-------------+--------------------------------------------------+
| binlog.000038 | 4 | Format_desc | 1313306 | 123 | Server ver: 5.7.32-log, Binlog ver: 4 |
| binlog.000038 | 123 | Previous_gtids | 1313306 | 154 | |
| binlog.000038 | 154 | Anonymous_Gtid | 1313306 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| binlog.000038 | 219 | Query | 1313306 | 292 | BEGIN |
| binlog.000038 | 292 | Rows_query | 1313306 | 361 | # update syj.gw set min_salary=min_salary+5000 |
| binlog.000038 | 361 | Table_map | 1313306 | 416 | table_id: 156 (syj.gw) |
| binlog.000038 | 416 | Update_rows | 1313306 | 1566 | table_id: 156 flags: STMT_END_F |
| binlog.000038 | 1566 | Xid | 1313306 | 1597 | COMMIT /* xid=2764 */
mysqlbinlog --base64-output=decode-rows -v -v /mysql/backup/backup-binlog/binlog.000038 | grep '210216 23:4'|more
#210216 23:40:26 server id 1313306 end_log_pos 361 CRC32 0x425b59b4 Rows_query
#210216 23:40:26 server id 1313306 end_log_pos 416 CRC32 0x5134129a Table_map: `syj`.`gw` mapped to number 156
#210216 23:40:26 server id 1313306 end_log_pos 1566 CRC32 0x0e8c6f08 Update_rows: table id 156 flags: STMT_END_F
#210216 23:40:26 server id 1313306 end_log_pos 1597 CRC32 0x168631ce Xid = 2764
#210216 23:43:08 server id 1313306 end_log_pos 1662 CRC32 0xc1d1b8f7 Anonymous_GTID last_committed=1 seq
uence_number=2 rbr_only=yes
在 2021-02-16 23:41:00 可以恢复单个数据库
mysqlbinlog --stop-datetime='2021-02-16 23:41:00' --database=syj /mysql/backup/backup-binlog/binlog.000038 >38.sql
mysql -uroot -proot syj <38.sql
在 2021-02-16 23:41:00 可以恢复全局
mysqlbinlog --stop-datetime='2021-02-16 23:41:00' /mysql/backup/backup-binlog/binlog.000038 >38.sql
mysql -uroot -proot <38.sql
在 2021-02-16 23:41:00 可以恢复全局.
show binlog events in 'binlog.000038';
binlog.000038 1735 Rows_query 1313306 1805 # update syj.gw set min_salary=min_salary+55000
binlog.000038 1805 Table_map 1313306 1860 table_id: 156 (syj.gw)
binlog.000038 1860 Update_rows 1313306 3010 table_id: 156 flags: STMT_END_F
binlog.000038 3010 Xid 1313306 3041 COMMIT /* xid=2807 */
binlog.000038 3041 Anonymous_Gtid 1313306 3106 SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
mysqlbinlog --base64-output=decode-rows -v -v /mysql/backup/backup-binlog/binlog.000038 | grep 'pos'|more
210216 23:43:08 server id 1313306 end_log_pos 1860 CRC32 0x653c82ba Table_map: `syj`.`gw` mapped to number 156
#210216 23:43:08 server id 1313306 end_log_pos 3010 CRC32 0x6788b464 Update_rows: table id 156 flags: STMT_END_F
#210216 23:43:08 server id 1313306 end_log_pos 3041 CRC32 0x28795d2b Xid = 2807
210216 23:43:08
mysqlbinlog --stop-datetime='2021-02-16 23:43:20' /mysql/backup/backup-binlog/binlog.000038 >38.sql
mysql -uroot -proot <38.sql
恢复正常OK
不能恢复A在恢复B，只能找一个最后的时间恢复，如果出现复杂的情况，可以先恢复A，导出数据，在恢复B，导出数据，恶心。
1)先备份原来的所有数据库
2)目标库安装mysql，初始化数据库,再启动数据库。
3)将备份的文件拷到目标库上,做恢复。
4)原库刷二进制日志,停库。
5)将原库的binlog二进制日志拷到目标库,增量恢复。
6)数据验证,目标库对外访问。
1)先备份原库的单个表，再锁定原表只能读,不能写。
2〉目标库安装mysql，初始化数据库，再启动数据库，创建对应的数据库。
3)将备份的文件拷到目标库上，做恢复。
4)数据验证,目标库对外访问。
涉及到字符集的三个地方
mysql自身的设置
服务器的字符集设置
使用工具的字符集设置
数据库字符集
[mysql]
default-character-set=utf8
[mysqld]
########basic settings########
character-set-server=utf8
show variables like '%character%';
| Variable_name | Value |
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /mysql/app/mysql-5.7.32-linux-glibc2.12-x86_64/share/charsets/ |
vi ~/ .bash_prorfile
[root@mysqldb ~]# env | grep LANG
LANG=zh_CN.UTF-8
export LANG=en_US.UTF8
mysqldump -uroot -proot --default-character-set=utf8 -R -E --single-transaction --master-data=2 --flush-logs --routines --all-databases > /mysql/backup/full.sql
iconv -t gbk -f utf8 -c /mysql/backup/full.sql > /mysql/backup/fullgbk.sql
-f, --from-code=名称 原始文本编码
-t, --to-code=名称 输出编码
create database syjgbk charset gbk;
create table namegbk (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
insert into namegbk VALUES (1,'豪杰'),(2,'豪强'),(3,'念如娇'),(4,'小姑娘');
导出单个数据库，使用utf8字符，没有数据
mysqldump -uroot -proot --routines --default-character-set=utf8 --no-data syjgbk>/mysql/backup/syjgbk-nodata.sql
修改数据
vim /mysql/backup/syjgbk-nodata.sql
:%s/gbk/utf8/g
## 全局修改
如果varchar(20) 需要扩大2倍最好。
一定要原来的字符编码
mysqldump -uroot -proot --routines --default-character-set=gbk --extended-insert --no-create-info syjgbk>/mysql/backup/syjgbk-gbkdata.sql
建议修改char/varchar的长度为原来的1.5倍，为了安全，导出时加了--extended-insert参数
Vim syjgbk-gbkdata.sql
/*!40101 SET NAMES gbk */;
/*!40101 SET NAMES utf8 */;
INSERT INTO `namegbk` VALUES (1,'è±??<9d>°'),(2,'è±????'),(3,'?????<82>?¨\<87>'),(4,'?°<8f>?§<91>?¨\<98>');
create database syjutf8 charset=utf8;
Query OK, 1 row affected (0.00 sec)
导入结构
mysql -uroot -proot syjutf8 </mysql/backup/syjgbk-nodata.sql
iconv -t utf-8 -f gbk -c /mysql/backup/syjgbk-gbkdata.sql >/mysql/backup/syjgtoutf8.sql
mysql -uroot -proot -o syjutf8 < /mysql/backup/ syjgtoutf8.sql
转载地址：http://sabai.baihongyu.com/