[TOC]

容灾备份介绍

1.容灾备份恢复必备条件
Mysql 数据库开启了log-bin参数记录binlog日志功能,且主库于备份的从库都要开启binlo功能。

1.全量备份
全量数据就是数据库中所有的数据,全量备份就是把数据库中所有的数据进行备份。
案例:

#备份所有库 -F 刷新BINLOG
mysql -uroot -p12345 -S mysql.sock --defalut-character-set=utf8 --single--transaction -F -A | gzip > /backup/mysql.bak.sql.gz

#备份一个库
mysql -uroot -p12345 -S mysql.sock -F -B student | gzip > /backup/mysql.bak.sql.gz

注意:因为-single-transaction选项备份时涉及到的是select语句,所以dml语句是被允许的,ddl不被允许直到回滚保存点撤销select 语句

  1. 增量备份
    增量数据就是从上次全量备份之后更新的数据,遂于MYSQL来说binlog日志就是Mysql增量数据。

2.1 按天全备情况

  • 优点:恢复时间短,维护成本低
  • 缺点:占用空间多,占用系统资源多,经常锁表影响用户体验。

2.2 按周全备情况

  • 优点:占用空间比按天备份小,占用系统资源少,无需锁表用户体验好一些.
  • 缺点:维护成本高,恢复麻烦,时间长。
  1. 常见全量和增量备份的频率
    1) 中小公司:全量每天一次在业务流量低谷执行全备,执行前要进行锁表。
    2) 单台数据库:采用rsyn或者inotify,主从复制(把所有binlog备份到远程服务器中)
    rsync -avz /data/3306/mysql-bin.* [email protected]:bakcp --password-file=/etc/rsync.passwd
    3) 大型公司周备,每周六零点一次全量,下周日-下周六00点前都是增量
    4) 一主多从的方式,一般会有一个从库作为备份延迟同步

Q:mysqldump 备份什么时候派上用场?
A:

  • 迁移或者升级数据库时;
  • 增加从库的时候;
  • 硬件或者特殊异常情况下,主库或者从库宕机,主从相互切换,建立新的从库的时候。
  • 人为的DDL,DML语句,主库没办法了,所有的库都会执行,此时需要备份。
  • 跨机房容灾,需要备份到异地

Q:什么情况下需要增量恢复?
A:常用于一主多从的数据库架构下,从库上开启binlog然后实施定时全备份和实时增量备份。

Q:什么是增量恢复?
利用二进制日志和全备进行的恢复过程,称为增量恢复。

Q: 人为操作数据库SQL语句破坏主库是否需要增量恢复?
A:在主库内部命令行误操作,会导致所有的数据库(包括主从库)数据丢失,这样的场景是需要增量恢复的。

Q:只有一个主库是否需要增量恢复?
A:如果公司里只有一个主库情况下,尽量做全备(一天一次),及增量备份(每隔1-10分钟对bin-log日志做的切割返回备份到其他服务器上,或者本地),宁一种好的方法就是做从库,基于drod(基于磁盘块的)同步。

小结:

  • 人为或者程序逻辑方式在数据库中执行SQL语句等误操作,都需要增量恢复.
  • 从库进行全量和增量方式的备份,可以防止人为对主库的误操作导致数据丢失,确保备份的从库和主库是同步状态。
  • 增量恢复的条件,存放一份全备与时刻同步的增量备份。

0x00 MYSQL数据库存储目录迁徙

测试环境:Ubuntu 16TLS
迁移流程:

#1.在目录迁徙目标文件夹(原始数据),默认目录:/var/lib/mysql ,注意权限700与所属者,所属组都是mysql用户与组.
drwx------ mysql mysql mysql/

#2.建立备份目录
sudo mkdir /databackup
sudo chown -vR mysql:mysql /databackup/
sudo chmod -vR 700 /databackup/

#3.通过cp命令进行复制(由于权限问题需要切换到root权限):
$ su root
$ cp -av /var/lib/mysql/* /databackup/
$ exit

#4.下面需要修改MySQL配置文件:
vim /etc/mysql/my.cnf #12.06 verion
vim /etc/mysql/mysql.conf.d/mysqld.cnf # 16.04 version

#5.修改配置文件,设置新的datadir = /databackup,并用#注销默认数据目录。
#6.修改appormor 配置(Ubuntu里面的软件都受到这个的限制),修改为:/mysqldata/ r, /mysqldata/** rwk,
$sudo vim /etc/apparmor.d/user.sbin.mysqld

#7.重启apparmor | MySQL
$sudo service apparmor reload
$sudo service mysql start

#8.登陆MySQL ( 默认情况下,出于安全的考虑,MySQL在初始状态下不允许远程连接,只允许服务器内部应用的本地连接。 )
mysql -uroot -p

#9.创建数据库,查看迁移目录下的数据库文件 并删除原数据库文件
> create database imooc;
$ su root
$ cd /mysqldata
$ rm -rvf /usr/lib/mysql

WeiyiGeek.apparmor修改


0x01 Mysql数据库|表备份还原的操作

描述:利用mysqldump备份数据库过程,实际上就是把数据从mysql库里以逻辑的SQL语句的形式直接输出或者生成备份的文件的过程;

1.1 备份数据库(全备)
#语法:
#use mysqdump --help
mysqldump -u用户名 -p密码 数据库 数据表 > 数据表备份名字 #不会建立库与切换库 (但会判断库里面的表是不是存在)

mysqldump -u用户名 -p密码 -A > 所有数据库备份名字 #注意这里-A 指定了后面也可以指定-B参数 == --all-databases
mysqldump -u用户名 -p密码 -B 数据库名 > 备份名字 # 推荐 -> -B 会建立库-自动切换库 == --database
mysqldump -u用户名 -p密码 -B 库名1 库名2|gzip > 备份名.gz #指定压缩命令压缩备份多个库 sql

mysqldump -u用户名 -p密码 --compact -d 数据库 数据表 > 数据表备份名字
mysqldump -u用户名 -p密码 --compact -t 数据库 数据表 > 数据表备份名字
mysqldump -u用户名 -p密码 --compact -A -B -F --events|gzip > 数据表备份名字
mysqldump -u用户名 -p密码 --compact --master-data=1 数据库 > 数据表备份名字

#参数:
-A 相当于 -B 数据库中一个一个库放在后面
-B 连接多个库,并且增加create database db 和 use db信息;
--compact 测试使用较多,可以优化内容的大小,让容量更少,适合调试;(不会 set names 、忽略key、添加锁)减小了生成文件的体积
-d : 备份表结构
-t :备份表内容
-F : 刷新切割binlog日志(分段备份导入)
--master-data :增加binlog日志文件名及对应的位置点; #注意需要开启log_bin ON |查看 -e "show variables;" | grep log_bin
-x :锁定所有表 #--lock-all-tables
-l :锁定指定表
--single-transaction : 适合innodb事务数据库备份(保证备份的一致性)| 工作原理隔离级别REPEATABLE READ

#案例:
mysqldump -u root -p f_info user_info > user_info.bak
mysqldump -usystem -psystem -B test > test.sql
mysqldump -usystem -psystem -A > AllTest.sql
mysqldump -usystem -psystem -A -B --events|gzip > AllTest.sql.gz #注意 gzip -d 会删除源文件解压
mysqldump -usystem -psystem -A -B -F --events|gzip > AllTest.sql.gz
mysqldump -uroot -p123456 --master-data=2 --compact -B demo #2会加上注释

#可以直接排除特殊字符查看
grep -E -v "#|\/|^$|--" test.sql

WeiyiGeek.mysqldump使用

1.2 备份表结构与表数据
#实战
{lamb} mysqldump -uroot -p123456 --compact -t demo user | less
{lamb} mysqldump -uroot -p123456 --compact -d demo user | less

WeiyiGeek.备份表结构与表数据

1.3 MYSQL引擎不同的备份比较
# 差备恢复
# MyISAM <= 5.3.5
mysqldump -u用户名 -p密码 -A -F -x --events --master-data=2|gzip > 数据表备份名字.gz #需要锁表 (-F = flush bin-log)

# innoDB >= 5.5.x 多一个事务操作参数
mysqldump -u用户名 -p密码 -A -F -B --events -t --master-data=2 --sigle-transaction|gzip > 数据表备份名字.gz

#涉及了触发器trigeers 与存储过程 routines
1.4 还原数据库|表
#实例
>mysql -uroot -p123456 mydb < /root/data/mydb.bak; #方法1 库名
>mysql -uroot -p123456 mydb mytable< /root/data/mydb.bak; #方法2 带表

#方法2
#先 create database test1,然后 下mysql >下输入source 路径即可,要注意 test.sql 所在的路径!
> create database test1;
> use test1;
> source test.sql

WeiyiGeek.数据库导入

1.5 导出/导入查询的数据到文件
/*语法:*/
select columns FROM into outfile "path/dest_file";
load data infile "path/file_name" into table table_name;

/*案例:*/
select * from test.websites into outfile "/tmp/test.txt";
load data infile '/tmp/test.txt' into table websites; /*注意中文导入乱码*/

WeiyiGeek.outfile与infile

1.6 细说–master-data=1|2 的异同
#--master-data = 1
# 常常在主从同步中使用,从机中拉取主机中的LOG-bin文件及其位置进行复制,相反主机也从从机中抓取数据
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=366;

#--master-data = 2
# 常常在增量备份中使用,只需要知道 bin-log文件 LOG_POS 366 以后的数据信息 (差备恢复)
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=366;

0x02 增量恢复MySQL数据实战

描述:有时需要恢复到一个操作之前的数据库,但又不想丢失这一部分的数据,那么可以用初步增量进行将这段时间参数的数据,恢复到数据库之中;
原理:mysql中的binlog日志,用来记录mysql内部增删改查的等对mysql数据库有更新内容,依靠足够长度的binlog日志和定期的全备,我们可以恢复任意时间点的表单数据。

#语法
#mysqlbinlog 解析mysql的binlog日志
mysqlbinlog -d 库名 bin-log[日志文件] > 库名_operi.sql #可以将bin-log中所有对库操作的SQL语句导出
mysqlbinlog bin-log[日志文件] --start-position=365 --stop-position=456 -r 恢复的库名_operi.sql #位置数范围恢复
mysqlbinlog bin-log[日志文件] --start-datetime="2018-1-1 21:22:22" --stop-datetime="2018-1-3 21:22:22" -r 恢复的库名_operi.sql #通过时间范围恢复数据

#参数:
-d :指定库名
--start-position :指定开始位置 # at 位置数
--stop-position :指定结束位置
--start-datetime : 指定开始时间 # 时间
--stop-datetime : 指定结束时间

注意使用之前必须开启在配置中开启增量备份(主要是完成记录数据库的增删改查):

#author:WeiyiGeek 博客
#配置文件中加入log-bin
[mysqld]
log-bin=mysql-bin # 将前面的#去掉,后面的增量备份名可以更改;

#重启Mysql
/etc/init.d/mysqld restart

#进行对数据库简单操作
create table `test`(
`id` INT(5) NOT NULL DEFAULT 0,
`name` CHAR(128) NOT NULL DEFAULT 'undefine'
);

insert into test value (1,"test"),(2,"www"),(3,"java");

WeiyiGeek.logbin

在mysql安装目录中的data目录中,便能找到存在我们增量备份:
WeiyiGeek.apparmor修改

注意: bin-log 注意它会记录所有数据库的更改记录,所有我们需要对其进行分段,使其更快更好的查找我们需要恢复的数据,如果无bin-log数据有可能导致有些数据删除了不能更快的找回来;

2.1 常用增量恢复方式
#不能停住数据库的思路
>insert into test value (4,"php"),(5,"Apache");
>update test set name to "Python" where name="test";

#下面是切割我们增量数据:
mysqladmin -usystem -p123456 flush-log #如果一个文件太过于庞大的时候或者业务不能停的时候把记录转到00002中去
#备份命令中 mysqldump 的-F参数也能进行刷新切割bin_log数据

WeiyiGeek.apparmor修改

2.2 常用增量文件导出为sql(三种方法)
#方式1.实战将binlog全部导入
mysqlbinlog -d test mysqlback-bin.000001 > mysqlback.sql #过滤test库的数据库进行写入到我们需要恢复的SQL文件中

#方式2.按照条件导入
mysqlbinlog mysqlback-bin.000001 --start-position=356 --stop-position=456 -r mysqlback.sql #选择bin-log中AT位置范围的语句写入到我们需要恢复的SQL文件中
mysqlbinlog mysqlback-bin.000001 --start-datetime="2018-1-1 21:22:22" --stop-datetime="2018-1-3 21:22:22" -r mysqlback.sql #通过时间范围来恢复

#方式3.删除mysqlback.sql中的错误操作,然后进行导入;
mysql -usystem -p123456 test <mysqlback.sql
mysql -usystem -p123456 -e "select * from test.test" 恢复到上次记录;

WeiyiGeek.删除binlog误操作的语句

WeiyiGeek.导入binlog解析的sql


0x03 主从复制之MYSQL增量恢复案例

案例图解:
WeiyiGeek.增量恢复案例
解决流程:

1) 判断关联业务哪一个数据库出现错误,查看SQL语句执行历史查看是什么问题导致业务中断;
2) 我们从10:11接收,停止从库,然后再将Binlog把mysql-bin.0000004 恢复成 bin.sql 并且 去掉drop语句;
3) 把0点的前面全备bak_00:00:00.sql以及10点前的增量bin.sql恢复到从库;
4) 数据丢多少?1010分刷新BINLOG以后的数据 mysql-bin.00003;
5) 停止主库,快速把mysql-bin.00003解析为sql,恢复到从库切换到从库提供服务;
6) 晚上某个时间节点将从库的数据重新灌入到主库中,恢复主从复制;
7) 后期数据库权限关联思想调整


#-------------环境假设---------------------
$ mysql -uroot -p12356 -S /data/3306/mysql.sock

//数据库字符集 utf8 | 数据库校对规则:utf8_general_ci 不区分大小写
mysql> create database weiyigeek DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
//ci是case insensitive的缩写,意思是大小写不敏感;
//相对的是cs,即case sensitive,大小写敏感;
//还有一种是utf8_bin,是将字符串中的每一个字符用二进制数据存储,区分大小写。
mysql> create table study (
`id` int(4) not NULL,
`name` CHAR(255) not NULL
);

//插入数据
mysql> insert into study values(1,'WeiyiGEEK');
Query OK, 1 row affected (0.09 sec)

mysql> select * from study;
+----+-----------+
| id | name |
+----+-----------+
| 1 | WeiyiGEEK |
| 2 | test |
| 2 | testdemo |
| 45 | testdemo |
| 41 | testdemo |
+----+-----------+
5 rows in set (0.00 sec)

//0点全备
[[email protected] data]# mysqldump -uroot [email protected] -S /data/3306/mysql.sock -B weiyigeek -F --events|gzip > bak_`(date +%F)`.sql.gz && ll

//再插入数据
mysql> insert into study values(1204,'Geek');
Query OK, 1 row affected (0.01 sec)

mysql> insert into study values(12,'adddGeek');
Query OK, 1 row affected (0.04 sec)

mysql> insert into study values(12,'GeekAdd');
Query OK, 1 row affected (0.01 sec)

// 10:00 执行了错误得语句
mysql> drop table study;
Query OK, 0 rows affected (0.03 sec)

解决实例:

Step1.增量恢复得重点,判断业务是否可以暂停;通过防火墙禁止WEB等应用向主库写数据或者锁表,穰主库暂时停止更新,然后再进行恢复。

Step2.检查全备并且解压
-rw-r--r-- 1 root root 777 Mar 27 00:12 bak_2019-03-27.sql.gz

[[email protected] 3306]# gzip -d bak_2019-03-27.sql.gz
-rw-r--r-- 1 root root 2094 Mar 27 16:12 bak_2019-03-27.sql

Step3.检查增量备份
[[email protected] data]# grep -i "change" bak_2019-03-27.sql #来判断MYSQL增量文件
[[email protected] data]# grep -i "completed " bak_2019-03-27.sql
-- Dump completed on 2019-03-27 00:12:17
-rw-r----- 1 mysql mysql 1001 Mar 27 10:17 binlog.000006


Step4.当机器不能暂停得时候mysqladmin进行刷新bin-log #我们恢复得目标就是000006了
mysqladmin -uroot [email protected] -S /data/3306/mysql.sock flush-logs


Step5.拷贝我们刷新后binlog文件(恢复目标)到其他文件中,防止误操作 , 采用mysqlbinlog 将binlog 解析为sql
cp binlog.000006 ../server/backup/
$ mysqlbinlog -d weiyieek binlog.000006 > bin.sql

mysqlbinlog mysql-bin.0000* > bin.sql #或者 mysqlbinlog mysql-bin.00001 mysql-bin.00002 > bin1.sql


Step6.找到误操作语句进行删除
$ grep -n "DROP" bin.sql
92:DROP TABLE `study` /* generated by server */


Step7.登陆到数据库找到一个关键 sql_log_bin 点,当我们导入备份数据后得时候不记录到bin-log日志中
#同时如果业务没有中断还在写数据这时候用户得数据也不写进bin-log日志里面了,所以恢复得时候需要注意(业务停还是没有停)。
mysql> show variables like '%log_bin%';
+---------------------------------+-------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------+
| log_bin | ON |
| log_bin_basename | /data/3306/binlog |
| log_bin_index | /data/3306/binlog.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin(important) | ON |
+---------------------------------+-------------------------+
mysql> set GLOABL sql_log_bin = OFF; #当前会话生效。


Step8.导入全备与其增量备份
mysql -uroot [email protected] -S /data/3306/mysql.sock < bak_2 019-03-27.sql
mysql -uroot [email protected] -S /data/3306/mysql.sock < bin.sql


Step9.进入查看恢复得数据
mysql> select * from study;
+------+-----------+
| id | name |
+------+-----------+
| 1 | WeiyiGEEK |
| 2 | test |
| 2 | testdemo |
| 45 | testdemo |
| 41 | testdemo |
| 1204 | Geek |
| 12 | adddGeek |
| 12 | GeekAdd |
+------+-----------+

#以上方案不会有主键冲突问题。

WeiyiGeek.停止外写入恢复Mysql数据库

0x0n 数据库|表备份总结

1) 备份与字符集修改导入

  • 导出所有的数据库(存在乱码)或者库中的表,sed批量修改为utf-8;
  • 修改mysql服务端和客户端的编码为utf-8,使用set names 字符集;
  • 删除原有的库表及数据,建立utf-8的为编码的数据库;
  • 导入mysql的所有数据;
    2) 增量备份总结
    如果不是Drop而是UPDATE破坏数据,解决起来就复杂;一般需要停库或禁止被应用服务写入,然后再恢复。
    1.人为SQL造成的误操作
    2.全备和增量
    3.恢复时建议对外停止更新
    4.恢复全量然后把增量日志中有问题的SQL语句删除,恢复到数据库
    5.

    #增量恢复的核心思想
    1.流程制度控制(业务操作需要层层审批),有效的防止问题发生。
    2.延迟备份解决,建立堡垒机对于高风险语句做黑名单于白名单机制。
    3.业务需求容忍度,可量化的目标,根据需求选择停库或者锁表或者容忍丢失部分数据。


备份shell脚本

批量备份数据库与库中之表

#!/bin/bash
BACKUPPATH=/data/backup/
[ ! -d $BACKUPPATH ] && mkdir -p $BACKUPPATH
UMYSQL="root"
PMYSQL="123456"
SOCKET='/date/3306/mysql.sock'
CONNECT="mysql -u${UMYSQL} -p${PMYSQL} -S ${SOCKET}"
DUMPDB="mysqldump -u${UMYSQL} -p${PMYSQL} -S ${SOCKET} -x -B -F -R"
LISTDB=$($CONNECT -e "show databases;"|sed 1d|egrep -v "_shcema|mysql")
for dbname in $LISTDB
do
#$DUMPDB $dbname|gzip >${BACKUPPATH}/${dbname}_$(date +%F).sql.gz #光备份数据库
LISTTABLE=$( $CONNECT -e "show tables from $dbname;"|sed 1d )
BACKUPPATH=$BACKUPPATH${dbname}
[ ! -d $BACKUPPATH ] && mkdir -p $BACKUPPATH
for tablename in $LISTTABLE
do
$DUMPDB $dbname $tablename|gzip >$BACKUPPATH/${dbname}_${tablename}_$(date +%F).sql.gz
done
done

入坑问题解决

0x01 学习Mysql所遇到得问题

问题1:忘记的Mysql密码重置密码
单实例方法:

#停止mysql服务
/etc/init.d/mysqld stop

#忽略授权登录验证
mysqlsafe --skip-grant-tables --user=mysql &;

#直接空密码登录
mysql -uroot -p

#使用update方式修改密码
UPDATE mysql.user SET password = PASSWORD("new_pass") WHERE user='root' and host='localhost';
flush privileges; #任何时候都要记住改密的啥时候都要更新权限;

#采用mysqladmin来停止mysqlsafe服务
mysqladmin -uroot -pnew_pass shutdwon
/etc/init.d/mysqld start #重启即可

多实例的方法:

#kill掉所有的mysql进程
killall mysql

#使用多实例的配置文件与 --skip且必须放在后面
mysqld_safe --default-file=/data/3306/my.cnf --skip-grant-table;

#然后多实例登录修改密码 (这时密码为空)
mysql -uroot -p -S /data/3306/mysql.sock

#同样采用上面的update方法修改密码
#然后重启服务用新密码登录
killall mysqld
/data/3306/mysql restart


问题2:认证插件问题
在安装mysql5.8后采用了最新的加密插件caching-sha2-password,用客户端连接比如navicate会提示客户端连接caching-sha2-password,是由于客户端不支持这种插件,可以通过如下方式进行修改:authentication plugin ‘caching_sha2_password’

mysql5.8开始将caching_sha2_password作为默认的身份验证插件
在MySQL 5.7中默认的身份验证插件是 mysql_native_password!

# mysql_native_password  密码认证方法
#修改加密规则
ALTER USER 'root'@'localhost' IDENTIFIED BY 'password' PASSWORD EXPIRE NEVER;
#更新密码(mysql_native_password模式)
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '{NewPassword}';
flush privileges;


问题3:Mysql服务器内存太小问题
检查一下 linux 系统的虚拟内存大小,如果内存不足 1G,启动 mysql 的时候可能会产生下面这个错误提示:  

Starting mysqld (via systemctl): Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.[FAILED]

起初安装的时候使用的是虚拟机自动分区,内存设置的是 1G,结果在这上面话费了大量的精力和时间去调试始终也启动没成功。


问题4:用户权限问题不能创建PID
出错如下:Starting MySQL..The server quit without updating PID file (/usr/local/mysql/var/localhost.localdomain.pid)
解决方法:配置PIDfile路径或者使用root用户启动mysql



问题5:读取预读时间太长
问题:Reading table information for completion of table and column names , You can turn off this feature to get a quicker startup with -A
原因:导致一般产生这个问题是由于MYSQL中数据库太大,导致读取预读时间太长,从而显示这个提示,如果之前都没有遇到这个问题,那么产生这个问题的原因可能是由于有改变数据库信息的操作,比如drop一个很大的表(几千万数据)而中途终止

#方法1: 
登录时候指定 -A 参数

#方法2:
mysql> show processlist ;
图中锁表的id为16545618,则可以使用kill命令,结束它.
mysql> kill 16545618;



问题6:多实例mysql无法启动问题
故障:利用脚本启动mysql总是Running,进程中也没有显示ps -ef /data/3306/mysqld start
原因:由于上次没有正常退出,导致sock文件未删除关闭;
解决方法:

$ rm -rf /data/3306/mysql.sock /data/3306/*pid
$ /data/3306/mysql restart



问题7: Mysql数据库备份入坑记录

问题1:mysqldump : got error 1556, you can’t user lock with log tables,when using LOCK TABLES;
原因:mysql默认数据库里的logs表,不能被加锁(Lock tables)引起的

#解决方法1:把mysql这个默认数据库相关的语句清空后,文件可以正常运行 (备份时候最后加入-B/-F)
> mysqldump -uroot -ptest -B -F --single-transaction eshop|gzip>$bakpath/eshop_$(date +%F).sql.gz



问题8: Mysql主从同步入坑记录

问题:主从同步last-error = 1007 ,无法创建数据库(DB_Create_exists)

#解决方法1:主从数据不一致更重要还是保持主从同步只续状态更重要。
> stop slave;
> set global sql_slave_skip_counter = 1; #忽略跳过错误,根据业务需求来决定。
> start slave;

#解决方法2:根据错误号跳过指定的错误(从库my.cnf配置文件修改)
slave-skip-errors = 1032,1062,1007
#或者但不建议使用all值忽略所有的错误信息。
#slave-skip-errors = all
# 不同的数据库版本会引起不同步,低版本到高版本可以,但是高版本不能向低版本同步。



问题9:导入全备恢复到数据库从中

问题:mysql eshop </home/xxx/esho.0624.sql
ERROR 1030 (HY000) at line 46: Got error -1 from storage engine

1.首先看看数据库备份内容是否异常
$less /home/xxx/eshop_ett100.0624.sql
-- MySQL dump 10.13 Distrib5.5.33, for Linux (x86_64)

2. 停止数据库调整配置文件参数
$/etc/init.d/mysqld stop
Shutting down MySQL. SUCCESS!

$vi /etc/my.cnf <==此处顺便调整相关其他参数
innodb_force_recovery= 0 #调整这个参数为0
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 614
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
query_cache_limit = 1M
query_cache_min_res_unit = 2k
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

3.重启MYSQL服务建立utf-8字符集的数据库
mysql> create database eshop CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

4.最后直接导入全备到数据库中

5.更改账号密码权限
grant select,insert,update,delete on eshop.* to'etiantian'@'localhost' identified by 'test110'



问题10:增量数据库恢复规则

mysqlbinlog mysql-bin.000014mysql-bin.000015 –start-datetime=’2014-06-24 02:23:00’ >bin.sql
mysqlbinlog: unknown variable ‘default-character-set=utf8’

#解决方法:添加--no-defaults参数开始增量恢复
$/install/mysql/bin/mysqlbinlog --no-defaultsmysql-bin.000014 mysql-bin.000015 --start-datetime='2014-06-24 02:23:00'>bin.sql
$less bin.sql #查看增量恢复出来的sql



问题11:强制killall停止mysql导致的错误
查看/var/log/mysql/err.log日志可以看到:

InnoDB: Error: auto-extending data file ./ibdata1 is of a different size140624 18:51:58 [ERROR] Plugin 'InnoDB' init function returned error.
140624 18:51:58 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
140624 18:51:58 [ERROR] Unknown/unsupported storage engine: InnoDB
140624 18:51:58 [ERROR] Aborting

#原因:强制终止了进程或者改变了数据文件即强制关闭服务导致innodb表空间或文件异常
#解决流程:

1.根据常规判断及历史记录(history命令行及/root/.mysql_history文件)
2.找到数据库的配置文件/etc/my.cnf
进而找到了数据库安装路径/install/mysql
数据文件路径/data/mysql
binlog的路径/data/mysql
db备份路径/home/xx/
3. 检查binlog是否完整,全备是否有效
4. 然后利用问题9解决方法进行恢复



问题10:mysq8首次登陆显示ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement
远程:MySQL版本5.6.6版本起,添加了password_expired功能,它允许设置用户的过期时间。这个特性已经添加到mysql.user数据表,但是它的默认值是”N”,可以使用ALTER USER语句来修改这个值。

#解决方法
mysql> ALTER USER USER() IDENTIFIED BY 'Xiaoming250'; #MySQL版本5.7.6版开设
ALTER USER 'xiaoming'@'localhost' PASSWORD EXPIRE; #将账号密码强制到期:
ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER; #禁用到期


问题11:MySQL中”Invalid default value”错误解决方法
描述:在插入SQL语句的时候由于设置了time和default 0000-00-00导致报错

#原因1:datetime类型只支持mysql 5.6.5+.
CREATE TABLE `sys_expense` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`createtime` datetime DEFAULT CURRENT_TIMESTAMP,
) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8 COMMENT='报销表';
#解决办法:将datetime修改成为timestamp;

#原因2:由于time、datetime类型在5.7之后默认不在支持0000-00-00,会导致上面的错误
`createtime` time NOT NULL DEFAULT "0000-00-00" COMMIT "登录时间";
#解决办法:在/etc/my.cnf配置文件中插入
sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
#或者将上面的0000-00-00替换成为1970-01-01
``



<hr>

#### 0x02 Mysql的出错代码表
根据mysql的头文件 /mysql/include/mysqld_error.h 整理而成

```c
1005:创建表失败
1006:创建数据库失败
1007:数据库已存在,创建数据库失败
1008:数据库不存在,删除数据库失败
1009:不能删除数据库文件导致删除数据库失败
1010:不能删除数据目录导致删除数据库失败
1011:删除数据库文件失败
1012:不能读取系统表中的记录
1020:记录已被其他用户修改
1021:硬盘剩余空间不足,请加大硬盘可用空间
1022:关键字重复,更改记录失败
1023:关闭时发生错误
1024:读文件错误
1025:更改名字时发生错误
1026:写文件错误
1032:记录不存在
1036:数据表是只读的,不能对它进行修改
1037:系统内存不足,请重启数据库或重启服务器
1038:用于排序的内存不足,请增大排序缓冲区
1040:已到达数据库的最大连接数,请加大数据库可用连接数
1041:系统内存不足
1042:无效的主机名
1043:无效连接
1044:当前用户没有访问数据库的权限
1045:不能连接数据库,用户名或密码错误
1048:字段不能为空
1049:数据库不存在
1050:数据表已存在
1051:数据表不存在
1054:字段不存在
1062:字段值重复,入库失败
1065:无效的SQL语句,SQL语句为空
1081:不能建立Socket连接
1114:数据表已满,不能容纳任何记录
1116:打开的数据表太多
1129:数据库出现异常,请重启数据库
1130:连接数据库失败,没有连接数据库的权限
1133:数据库用户不存在
1141:当前用户无权访问数据库
1142:当前用户无权访问数据表
1143:当前用户无权访问数据表中的字段
1146:数据表不存在
1147:未定义用户对数据表的访问权限
1149:SQL语句语法错误
1158:网络错误,出现读错误,请检查网络连接状况
1159:网络错误,读超时,请检查网络连接状况
1160:网络错误,出现写错误,请检查网络连接状况
1161:网络错误,写超时,请检查网络连接状况
1169:字段值重复,更新记录失败
1177:打开数据表失败
1180:提交事务失败
1181:回滚事务失败
1203:当前用户和数据库建立的连接已到达数据库的最大连接数,请增大可用的数据库连接数或重启数据库
1205:加锁超时
1211:当前用户没有创建用户的权限
1216:外键约束检查失败,更新子表记录失败
1217:外键约束检查失败,删除或修改主表记录失败
1226:当前用户使用的资源已超过所允许的资源,请重启数据库或重启服务器
1227:权限不足,您无权进行此操作
1235:MySQL版本过低,不具有本功能
1250:客户端不支持服务器要求的认证协议,请考虑升级客户端。
2000:像素格式无效。
2001:指定的驱动程序无效。
2002:该操作的窗口样式或类属性无效。
2003:不支持请求的图元文件操作。
2004:不支持请求的转换操作。
2005:不支持请求的剪辑操作。
2010:指定的颜色管理模块无效。
2011:指定的颜色文件配置无效。
2012:找不到指定的标识。
2013:所需的标识不存在。
2014:指定的标识已经存在。
2015:指定的颜色文件配置与任何设备都不相关。
2016:找不到该指定的颜色文件配置。
2017:指定的颜色空间无效。
2018:图像颜色管理没有启用。
2019:在删除该颜色转换时有一个错误。
2020:指定的颜色转换无效。
2021:指定的转换与位图的颜色空间不匹配。
2022:指定的命名颜色索引在配置文件中不存在。
2108:网络连接已成功,但需要提示用户输入一个不同于原始指定的密码。
2202:指定的用户名无效。
2250:网络连接不存在。
2401:在这个网络连接上已存在打开的文件或未处理的请求。
2402:活动的连接仍然存在。
2404:设备正由活动进程使用,无法断开连接。
3000:指定的打印监视程序未知。
3001:指定的打印机驱动程序正在使用中。
3002:找不到假脱机文件。
3003:没有发出:StartDocPrinter:调用。
3004:尚未发出:AddJob:调用。
3005:指定的打印处理程序已经安装。
3006:指定的打印监视程序已经安装。
3007:该指定的打印监视器不具备所要求的功能。
3008:指定的打印机监视器正在使用中。
3009:当打印机有作业排成队列时此操作请求是不允许的。
3010:请求的操作成功。只有重新启动系统,更改才会生效。
3011:请求的操作成功。只有重新启动服务,更改才会生效。
3012:找不到打印机。
4000:WINS:在处理命令时遇到执行错误。
4001:无法删除本地的:WINS。
4002:从文件引入失败。
4003:备份失败。以前执行过完整的备份吗
4004:备份失败。请检查备份数据库的目标目录。
4005:名称在:WINS:数据库中不存在。
4006:不允许进行未配置部分的复制。
4100:DHCP:客户获得一个在网上已被使用的:IP:地址。直到:DHCP:客户可以获得新的地址前,本地接口将被禁用。
4200:WMI:数据提供程序不能识别传来的:GUID:是否有效。
4201:WMI:数据提供程序无法识别传来的实例名是否有效。
4202:WMI:数据提供程序无法识别传来的数据项目标识符是否有效。
4203:无法完成:WMI:请求,请重试一次。
4204:找不到:WMI:数据提供程序。
4205:WMI:数据提供程序引用到一个未注册的实例组。
4206:WMI:数据块或事件通知已启用。
4207:WMI:数据块不再可用。
4208:WMI:数据服务无法使用。
4209:WMI:数据提供程序无法完成请求。
4210:WMI:MOF:信息无效。
4211:WMI:注册信息无效。
4212:WMI:数据块或事件通知已禁用。
4213:WMI:数据项目或数据块为只读。
4214:WMI:数据项目或数据块不能更改。
6118:该工作组的服务器列表当前不可用。
6200:要正常运行,任务计划程序服务的配置必须在系统帐户中运行。单独的任务可以被配置成在其他帐户中运行。
7001:指定的会话名无效。
7002:指定的协议驱动程序无效。
7003:在系统路径上找不到指定的协议驱动程序。
7004:在系统路径上找不到指定的终端连接驱动程序。
7005:不能为这个会话创建一个事件日志的注册键。
7006:同名的一个服务已经在系统中存在。
7007:在会话上一个关闭操作挂起。
7008:没有可用的输出缓冲器。
7009:找不到:MODEM.INF:文件。
7010:在:MODEM.INF:中没有找到调制解调器名称。
7011:调制解调器没有接受发送给它的指令。验证配置的调制解调器与连接的调制解调器是否匹配。:7012:调制解调器没有响应发送给它的指令。验证该调制解调器是否接线正确并且打开了电源开关。
7013:由于断开连接,载波检测失败或载波停止。
7014:在要求的时间内没有发现拨号音。确定电话线连接正确并可使用。
7015:在远程站点回叫时检测到了占线信号。
7016:在回叫时远程站点上检测到了声音。
7017:传输驱动程序错误
7022:找不到指定的会话。
7023:指定的会话名称已处于使用中。
7024:由于终端连接目前正在忙于处理一个连接、断开连接、复位或删除操作,无法完成该请求的操作。
7025:试图连接到其视频模式不受当前客户支持的会话。
7035:应用程序尝试启动:DOS:图形模式。不支持:DOS:图形模式。
7037:您的交互式登录权限已被禁用。请与您的管理员联系。
7038:该请求的操作只能在系统控制台上执行。这通常是一个驱动程序或系统:DLL:要求直接控制台访问的结果。
7040:客户未能对服务器连接消息作出响应。
7041:不支持断开控制台会话。
7042:不支持重新将一个断开的会话连接到控制台。
7044:远程控制另一个会话的请求被拒绝。
7045:拒绝请求的会话访问。
7049:指定的终端连接驱动程序无效。
7050:不能远程控制请求的会话。这也许是由于该会话被中断或目前没有一个用户登录。另外,您不能从该系统控制台远程控制一个会话或远程控制系统控制台。并且,您不能远程控制您自己的当前会话。
7051:该请求的会话没有配置成允许远程控制。
7052:连接到这个终端服务器的申请被拒绝。终端服务器客户许可证目前正在被另一个用户使用。请与系统管理员联系,获取一份新的终端服务器客户,其许可证号码必须是有效的、唯一的。
7053:连接到这个终端服务器的申请被拒绝。还没有为这份终端服务器客户输入您的终端服务器客户许可证号码。请与系统管理员联系,为该终端服务器客户输入一个有效的、唯一的许可证号码。
7054:系统已达到其授权的登录限制。请以后再试一次。
7055:您正在使用的客户没有使用该系统的授权。您的登录请求被拒绝。
7056:系统许可证已过期。您的登录请求被拒绝。