mysql备份与恢复
关于数据库备份方案
冷备份
场景:
一般主要用于非核心业务,这类业务一般都是允许业务中断的,冷备份的特点就是数度快,恢复时也最为简单。通常直接复物理文件来实现冷备份
缺点:
- 需要停止MySQL服务。
- 仅支持在完全相同版本的mysql服务之间实现数据备份、转移、恢复
- 完全备份(全部数据库的全部数据),数据量大时不方便。
实现方法
备份前的数据
# 以fafamcu数据库为例演示
# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 50
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
Database changed
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use fafamcu;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_fafamcu |
+-------------------+
| mcuidx |
| mculist |
| mcuroom |
| meet |
| meetTimeRecord |
| netinfo |
| user |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
+----+---------+
3 rows in set (0.00 sec)
mysql> quit
Bye
进行数据备份
# 查看数据存放路径
root@ubuntu:/var/lib/mysql# vim /etc/mysql/my.cnf
[mysqld]
datadir = /var/lib/mysql
# 直接对数据路径进行打包即可
root@ubuntu:/var/lib/mysql# cd ..
root@ubuntu:/var/lib# tar -zcPf mysql.tar.gz mysql/*
root@ubuntu:/var/lib# ls mysql
debian-5.5.flag fafamcu ibdata1 ib_logfile0 ib_logfile1 mysql mysql_upgrade_info performance_schema
模拟误删数据库
root@ubuntu:/var/lib# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database fafamcu;
Query OK, 7 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> quit
Bye
进行数据恢复
# 停止mysql服务
root@ubuntu:/var/lib# service mysql stop
mysql stop/waiting
root@ubuntu:/var/lib# netstat -anlp| grep :3306
# 转移旧数据目录
root@ubuntu:/var/lib# mv mysql mysql-err
# 恢复备份的数据目录
root@ubuntu:/var/lib# tar -xf mysql.tar.gz
root@ubuntu:/var/lib# ls mysql
debian-5.5.flag fafamcu ibdata1 ib_logfile0 ib_logfile1 mysql mysql_upgrade_info performance_schema
root@ubuntu:/var/lib# service mysql start
mysql start/running, process 2564
root@ubuntu:/var/lib# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 38
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use fafamcu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_fafamcu |
+-------------------+
| mcuidx |
| mculist |
| mcuroom |
| meet |
| meetTimeRecord |
| netinfo |
| user |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
+----+---------+
3 rows in set (0.00 sec)
热备份
逻辑备份
缺点:
- 消耗服务器资源,会对服务器造成压力
- 当数据量较大时(1G时就很明显了),备份及恢复速度慢慢慢
实现方法
备份前的数据
root@ubuntu:/var/lib# mysql -uroot -p
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use fafamcu;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_fafamcu |
+-------------------+
| mcuidx |
| mculist |
| mcuroom |
| meet |
| meetTimeRecord |
| netinfo |
| user |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
+----+---------+
3 rows in set (0.00 sec)
mysql> quit
进行数据备份
root@ubuntu:/var/lib# mysqldump -uroot -p fafamcu --default-character-set=utf8 --opt -Q -R --skip-lock-tables> fafamcu.sql
Enter password:
root@ubuntu:/var/lib# ls fafamcu.sql
fafamcu.sql
模拟误删数据库
root@ubuntu:/var/lib# mysql -uroot -p
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> drop database fafamcu;
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)
mysql> quit
进行数据恢复
# 提前创建数据库
root@ubuntu:/var/lib# mysql -uroot -p
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> create database fafamcu;
Query OK, 1 row affected (0.00 sec)
mysql> quit
Bye
# 导入数据库
root@ubuntu:/var/lib# mysql -uroot -p fafamcu < fafamcu.sql
Enter password:
root@ubuntu:/var/lib# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 46
Server version: 5.5.55-0ubuntu0.14.04.1 (Ubuntu)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| fafamcu |
| mysql |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)
mysql> use fafamcu
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_fafamcu |
+-------------------+
| mcuidx |
| mculist |
| mcuroom |
| meet |
| meetTimeRecord |
| netinfo |
| user |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
+----+---------+
3 rows in set (0.00 sec)
实现脚本
# cat mysql.password
[client]
user = root
password = 'password123'
# cat mysql_db
db1
#db2
db3
# cat mysql_backup.sh
#/bin/bash
mysql_host=localhost
mysql_port=3306
mysql_bin_dump=./mysqldump
mysql_backup_dir=./backup
db_list=`cat mysql_db |grep -vE '^$|^#'` # 读取数据库列表,过滤掉空行和注释行。
chmod 755 ./mysqldump
for db_name in $db_list
do
./${mysql_bin_dump} --defaults-extra-file=./mysql.password --host=${mysql_host} --port=${mysql_port} \
--routines --events --triggers --single-transaction --flush-logs \
--ignore-table=mysql.event --databases ${db_name} \
> ${mysql_backup_dir}/${db_name}.sql
done
[ $? -eq 0 ] && echo "mysql backup successfully! " || \
echo "mysql backup failed! "
/bin/sleep 2
#!/bin/bash
mysql_backup_dir=./backup
mysql_host=localhost
mysql_port=3306
db_list=`cat mysql_db |grep -vE '^$|^#'` #读取数据库列表,过滤掉空行和注释行。
chmod 755 ./mysql
for db_name in $db_list
do
./mysql --defaults-extra-file=./mysql.password --host=${mysql_host} --port=${mysql_port} < ${mysql_backup_dir}/${db_name}.sql
done
[ $? -eq 0 ] && echo "mysql restore successfully! " || \
echo "mysql restore failed! "
一个参考脚本
#!/bin/bash
#使用:./xx.sh -uroot -p'123456',使用前修改脚本进行变量配置
#过程:备份并刷新binlog,将最新的binlog文件名记录并整体压缩打包
#恢复:先进行全量备份,再对根据tim-binlog.txt中的记录,进行逐个恢复
#提示:最多每分钟执行一次,否则会覆盖同分钟内的文件,可以修改脚本来改善
# 出现问题会退出,可以到指定的日志目录查看日志输出
# 同年的tar包超过指定天数的会删除掉
#[变量]
begin_time=`date +%F-%H-%M-%S`
my_sql="/usr/local/mysql/bin/mysql"
bak_sql="/usr/local/mysql/bin/mysqldump"
binlog_dir=/data/mysql/
bak_dir=/ops/bak
log_dir=/ops/log/mybak-all.log
#保存的天数,4周就是28天
save_day=28
#[自动变量]
#当前年月
date_nian=`date +%Y-`
#所有天数的数组
save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))
#开始
/usr/bin/echo >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始全备份" >> ${log_dir}
#检查
${my_sql} $* -e "show databases;" &> /tmp/info_error.txt
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >> ${log_dir}
/usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息
exit 1
fi
#移动到目录
cd ${bak_dir}
bak_time=`date +%F-%H-%M`
bak_timetwo=`date +%F`
#备份
${bak_sql} $* --all-databases --flush-privileges --single-transaction --flush-logs --triggers --routines --events --hex-blob > mybak-all-${bak_time}.sql
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:备份失败"
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:备份失败" >> ${log_dir}
/usr/bin/cat /tmp/bak_error.txt #如果错误则显示错误信息
exit 1
else
bin_dian=`tail -n 1 ${binlog_dir}/mysql-bin.index`
echo "${bin_dian}" > ${bak_time}-binlog.txt
fi
#压缩
if [[ -f mybak-all-${bak_time}.tar.gz ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >> ${log_dir}
/usr/bin/rm -irf mybak-all-${bak_time}.tar.gz ${bak_sql}-binlog.txt
fi
/usr/bin/tar -cf mybak-all-${bak_time}.tar.gz mybak-all-${bak_time}.sql ${bak_time}-binlog.txt
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >> ${log_dir}
exit 1
fi
#删除sql文件
/usr/bin/rm -irf mybak-all-${bak_time}.sql ${bak_time}-binlog.txt
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >> ${log_dir}
exit 1
fi
#整理压缩的日志文件
for i in `ls | grep .tar.gz$`
do
echo $i | grep "^mybak-all.*tar.gz$" &> /dev/null
if [[ $? -eq 0 ]];then
a=`echo ${i%%.tar.gz}`
b=`echo ${a:(-16)}`
c=`echo ${b%-*}`
d=`echo ${c%-*}`
#看是否在数组中,不在则删除
echo ${save_day_zu[*]} |grep -w $d &> /dev/null
if [[ $? -ne 0 ]];then
[[ "$d" != "$bak_timetwo" ]] && rm -rf $i
fi
else
#不是当月的,其他类型压缩包,跳过
continue
fi
done
#结束
last_time=`date +%F-%H-%M-%S`
/usr/bin/echo "begin_time:${begin_time} last_time:${last_time}" >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:全备份完成" >> ${log_dir}
/usr/bin/echo >> ${log_dir}
差量备份
增量备份
一个参考脚本
#!/bin/bash
#使用:./xx.sh -uroot -p'123456',将第一次增量备份后的binlog文件名写到/tmp/binlog-section中,若都没有,自动填写mysql-bin.000001
#过程:增量先刷新binlog日志,再查询/tmp/binlog-section中记录的上一次备份中最新的binlog日志的值
# cp中间的binlog日志,并进行压缩。再将备份中最新的binlog日志写入。
#恢复:先进行全量恢复,再根据全量备份附带的time-binlog.txt中的记录逐个恢复。当前最新的Binlog日志要去掉有问题的语句,例如drop等。
#提示:最多每分钟执行一次,否则会覆盖同分钟内的文件,可以修改脚本来改善
# 出现问题会退出,可以到指定的日志目录查看日志输出
# 同年的tar包超过指定天数的会删除掉
#[变量]
begin_time=`date +%F-%H-%M-%S`
my_sql="/usr/local/mysql/bin/mysql"
bak_sql="/usr/local/mysql/bin/mysqldump"
binlog_dir=/data/mysql/
binlog_index=${binlog_dir}/mysql-bin.index
bak_dir=/ops/bak
log_dir=/ops/log/mybak-section.log
#保存的天数,4周就是28天
save_day=7
#[自动变量]
#当前年
date_nian=`date +%Y-`
#所有天数的数组
save_day_zu=($(for i in `seq 1 ${save_day}`;do date -d -${i}days "+%F";done))
#开始
/usr/bin/echo >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:开始增量备份" >> ${log_dir}
#检查
${my_sql} $* -e "show databases;" &> /tmp/info_error.txt
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:登陆命令错误" >> ${log_dir}
/usr/bin/cat /tmp/info_error.txt #如果错误则显示错误信息
exit 1
fi
#移动到目录
cd ${bak_dir}
bak_time=`date +%F-%H-%M`
bak_timetwo=`date +%F`
#刷新
${my_sql} $* -e "flush logs"
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:刷新binlog失败" >> ${log_dir}
exit 1
fi
#获取开头和结尾binlog名字
last_bin=`cat /tmp/binlog-section`
next_bin=`tail -n 1 ${binlog_dir}/mysql-bin.index`
echo ${last_bin} |grep 'mysql-bin' &> /dev/null
if [[ $? -ne 0 ]];then
echo "mysql-bin.000001" > /tmp/binlog-section #不存在则默认第一个
last_bin=`cat /tmp/binlog-section`
fi
#截取需要备份的binlog行数
a=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${last_bin} | awk -F':' '{print $1}'`
b=`/usr/bin/sort ${binlog_dir}/mysql-bin.index | uniq | grep -n ${next_bin} | awk -F':' '{print $1}'`
let b--
#输出最新节点
/usr/bin/echo "${next_bin}" > /tmp/binlog-section
#创建文件
rm -rf mybak-section-${bak_time}
/usr/bin/mkdir mybak-section-${bak_time}
for i in `sed -n "${a},${b}p" ${binlog_dir}/mysql-bin.index | awk -F'./' '{print $2}'`
do
if [[ ! -f ${binlog_dir}/${i} ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 不存在" >> ${log_dir}
exit 1
fi
cp -rf ${binlog_dir}/${i} mybak-section-${bak_time}/
if [[ ! -f mybak-section-${bak_time}/${i} ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:binlog文件${i} 备份失败" >> ${log_dir}
exit 1
fi
done
#压缩
if [[ -f mybak-section-${bak_time}.tar.gz ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:压缩包mybak-section-${bak_time}.tar.gz 已存在" >> ${log_dir}
/usr/bin/rm -irf mybak-section-${bak_time}.tar.gz
fi
/usr/bin/tar -cf mybak-section-${bak_time}.tar.gz mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) error:压缩失败" >> ${log_dir}
exit 1
fi
#删除binlog文件夹
/usr/bin/rm -irf mybak-section-${bak_time}
if [[ $? -ne 0 ]];then
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:删除sql文件失败" >> ${log_dir}
exit 1
fi
#整理压缩的日志文件
for i in `ls | grep "^mybak-section.*tar.gz$"`
do
echo $i | grep ${date_nian} &> /dev/null
if [[ $? -eq 0 ]];then
a=`echo ${i%%.tar.gz}`
b=`echo ${a:(-16)}` #当前日志年月日
c=`echo ${b%-*}`
d=`echo ${c%-*}`
#看是否在数组中,不在其中,并且不是当前时间,则删除。
echo ${save_day_zu[*]} |grep -w $d &> /dev/null
if [[ $? -ne 0 ]];then
[[ "$d" != "$bak_timetwo" ]] && rm -rf $i
fi
else
#不是当月的,其他类型压缩包,跳过
continue
fi
done
#结束
last_time=`date +%F-%H-%M-%S`
/usr/bin/echo "begin_time:${begin_time} last_time:${last_time}" >> ${log_dir}
/usr/bin/echo "time:$(date +%F-%H-%M-%S) info:增量备份完成" >> ${log_dir}
/usr/bin/echo >> ${log_dir}
# 周六晚3点i分进行全量备份 周一到周六每天进行增量备份, 全量保存4周 增量保存近一周的每天数据
crontab -e 添加计划任务。
1 3 * * 6 /bin/bash /shell/mybak-all.sh -uroot -p'123456'
1 2 * * * /bin/bash /shell/mybak-section.sh -uroot -p'123456'
参考地址:
https://blog.csdn.net/weixin_34032779/article/details/93204960
实现方法
备份前数据
# 进行一次完全备份,基于完全备份按顺序执行之后二进制日志可进行数据的恢复。
root@ubuntu:/var/log/mysql# mysqldump -uroot -p fafamcu --default-character-set=utf8 --opt -Q -R --skip-lock-tables> fafamcu.sql
Enter password:
root@ubuntu:/var/log/mysql# ls
error.log fafamcu.sql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.index
root@ubuntu:/var/log/mysql# mysqladmin -uroot -p flush-logs
Enter password:
root@ubuntu:/var/log/mysql# ls
error.log fafamcu.sql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.index
root@ubuntu:/var/log/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.5.55-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use fafamcu
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
| 4 | 200619 |
+----+---------+
4 rows in set (0.00 sec)
模拟第一次增量备份
# 增加数据
mysql> insert into mcuidx values (5.200620);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into mcuidx values (5,200620);
Query OK, 1 row affected (1.60 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
| 4 | 200619 |
| 5 | 200620 |
+----+---------+
5 rows in set (0.00 sec)
mysql> quit
Bye
模拟第二次增量备份
# 生成新的二进制日志文件
root@ubuntu:/var/log/mysql# mysqladmin -uroot -p flush-logs
Enter password:
root@ubuntu:/var/log/mysql# ls
error.log fafamcu.sql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.index
# 模式失误删除数据
root@ubuntu:/var/log/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.5.55-0ubuntu0.14.04.1-log (Ubuntu)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use fafamcu
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
| 4 | 200619 |
| 5 | 200620 |
+----+---------+
5 rows in set (0.00 sec)
mysql> delete from mcuidx where id=1;
Query OK, 1 row affected (0.00 sec)
mysql> delete from mcuidx where id=2;
Query OK, 1 row affected (0.01 sec)
mysql> select * from mcuidx;
+----+--------+
| id | idx |
+----+--------+
| 3 | 112233 |
| 4 | 200619 |
| 5 | 200620 |
+----+--------+
3 rows in set (0.00 sec)
mysql> quit
Bye
进行数据恢复
# 恢复完全备份
root@ubuntu:/var/log/mysql# mysql -uroot -p fafamcu < fafamcu.sql
Enter password:
root@ubuntu:/var/log/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.5.55-0ubuntu0.14.04.1-log (Ubuntu)
mysql> use fafamcu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from mcudix;
ERROR 1146 (42S02): Table 'fafamcu.mcudix' doesn't exist
mysql> show tables;
+-------------------+
| Tables_in_fafamcu |
+-------------------+
| mcuidx |
| mculist |
| mcuroom |
| meet |
| meetTimeRecord |
| netinfo |
| user |
+-------------------+
7 rows in set (0.00 sec)
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
| 4 | 200619 |
+----+---------+
4 rows in set (0.00 sec)
mysql> quit
Bye
使用二进制日志恢复数据
# 使用第一次增量备份产生的二进制日志恢复第一部分数据
root@ubuntu:/var/log/mysql# ls
error.log fafamcu.sql mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 mysql-bin.000004 mysql-bin.000005 mysql-bin.000006 mysql-bin.index
root@ubuntu:/var/log/mysql# mysqlbinlog --no-defaults mysql-bin.000005 | mysql -uroot -p
Enter password:
root@ubuntu:/var/log/mysql# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.5.55-0ubuntu0.14.04.1-log (Ubuntu)
mysql> use fafamcu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from mcuidx;
+----+---------+
| id | idx |
+----+---------+
| 1 | 112123 |
| 2 | 1122334 |
| 3 | 112233 |
| 4 | 200619 |
| 5 | 200620 |
+----+---------+
5 rows in set (0.00 sec)
关于数据库容灾方案
关于数据库守护方案
关于数据库(1T)备份、还原方案
https://blog.csdn.net/yincg/article/details/71409502
关于数据库备份
参考地址1(推荐,如何实现mysql的1T数据库备份):
https://blog.csdn.net/enweitech/article/details/51612858?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-1.nonecase
参考地址2:
https://blog.csdn.net/longyuhome/article/details/35786487/
发现问题优化
PHP查询数据库慢?
问题现象:
通过测试得出:服务器执行php文件速度正常,从MySQL数据库读取数据速度异常。进一步测试发现,MySQL -uroot -p -h 12.0.2.49过程缓慢。
解决方法:
更改MySQL配置文件my.cnf,添加以下代码:
[mysqld]
skip-name-resolve #Don’t resolve hostnames
重启MySQL服务即可。
问题总结:
服务器做了一个本地dns服务,每次执行访问DB数据库操作时,MySQL就会试图去解析来访问的机器的domain name,如果这时解析不成功,等一段时间会失败,数据才能被取过来。
mysql服务器频繁挂掉(down)&&CPU高
问题现象:
公司网站刚改完版,优化了数据库结构,使用的memcache分布式缓存加mysql,刚开始几天还好,数据库服务几天没down过了.但这几天频繁down机.是个大问题,需要解决.
问题分析:
- 确保服务器硬件配置是合理的
- 查看服务器负载(top命令),见mysql竟然经常百分之九十几的cpu,恐怖.早之前就知道,数据表有些字段没有做索引的,引起MYSQL负载这么高的原因主要应该是索引问题和某些变态SQL语句.
如何知道是MYSQL哪些索引和SQL引起的问题呢?老大教有绝招
解决方法:
编辑MYSQL配置文件my.cnf,加上以下几行:
log_slow_queries=/usr/local/mysql/data/log_slow_queries.log //慢语句日志保存目录
long_query_time=10 //记录SQL查询超过10S的语句
log-queries-not-using-indexes=1 //记录没有使用索引的sql
这样,慢语句就被记录在日志文件中了.
tail -f /usr/local/mysql/data/log_slow_queries.log 查看日志.
四个参数
Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 54
分别意思为:查询时间 锁定时间 查询结果行数 扫描行数
主要看那些扫描行数超多的语句.然后去数据库加上对应的索引
再优化下变态的sql
完成后mysql负载降到了二十几.还行.应该不会down机了.
Mysql 高负载排查思路
问题描述:
top命令 查看服务器负载,发现 mysql竟然百分之两百的cpu,引起Mysql 负载这么高的原因,估计是索引问题和某些变态SQL语句.
排查思路 :
- 确定高负载的类型,top命令看负载高是CPU还是IO。
- mysql 下执行查看当前的连接数与执行的sql 语句。
- 检查慢查询日志,可能是慢查询引起负载高。
- 检查硬件问题,是否磁盘故障问题造成的。
- 检查监控平台,对比此机器不同时间的负载。
确定负载类型(top)
top - 10:14:18 up 23 days, 11:01, 1 user, load average: 124.17, 55.88, 24.70 Tasks: 138 total, 1 running, 137 sleeping, 0 stopped, 0 zombie Cpu(s): 2.4%us, 1.0%sy, 0.0%ni, 95.2%id, 2.0%wa, 0.1%hi, 0.2%si, 0.0%st Mem: 3090528k total, 2965772k used, 124756k free, 93332k buffers Swap: 4192956k total, 2425132k used, 1767824k free, 756524k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 30833 mysql 15 0 6250m 2.5g 4076 S 257.1 49.9 529:34.45 mysqld
查看当前的连接数与执行的sql 语句
>show processlist;
Id User Host db Command Time State Info 192 slave 8.8.8.142:39820 NULL Binlog Dump 58982 Has sent all binlog to slave;
waiting for binlog to be updated NULL 194 slave 8.8.8.120:41075 NULL Binlog Dump 58982 Has sent all binlog to slave;
waiting for binlog to be updated NULL 424891 biotherm 8.8.8.46:57861 biotherm Query 493 Sending data
>SELECT * FROM xxx_list WHERE tid = '1112' AND del = 0 ORDER BY id DESC LIMIT 0, 4
424917 biotherm 8.8.8.49:50984 biotherm Query 488 Sending data
>SELECT * FROM xxx_list WHERE tid = '1112' AND del = 0 ORDER BY id DESC LIMIT 0, 4
430330 biotherm 8.8.8.42:35982 biotherm Query 487 Sending data
>SELECT * FROM xxx_list WHERE tid = '1112' AND del = 0
记录慢查询
编辑Mysql 配置文件(my.cnf),在[mysqld]字段添加以下几行:
log_slow_queries = /usr/local/mysql/var/slow_queries.log #慢查询日志路径
long_query_time = 10 #记录SQL查询超过10s的语句
log-queries-not-using-indexes = 1 #记录没有使用索引的sql
查看慢查询日志
tail /usr/local/mysql/var/slow_queries.log
# Time: 130305 9:48:13 # User@Host: biotherm[biotherm] @ [8.8.8.45]
# Query_time: 1294.881407 Lock_time: 0.000179 Rows_sent: 4 Rows_examined: 1318033 SET timestamp=1363916893; SELECT * FROM xxx_list WHERE tid = '11xx' AND del = 0 ORDER BY id DESC LIMIT 0, 4;
4个参数
Query_time: 0 Lock_time: 0 Rows_sent: 1 Rows_examined: 54
分别意思为:查询时间 锁定时间 查询结果行数 扫描行数
主要看扫描行数多的语句,然后去数据库加上对应的索引,再优化下变态的sql 语句。
极端情况kill sql进程
找出占用cpu时间过长的sql,在mysql 下执行如下命令: show processlist; 确定后一条sql处于Query状态,且Time时间过长,锁定它的ID,执行如下命令: kill QUERY 269815764;
注意:杀死 sql进程,可能导致数据丢失,所以执行前要衡量数据的重要性。
修改mysql密码
1.首先确认服务器出于安全的状态,也就是没有人能够任意地连接MySQL数据库。
因为在重新设置MySQL的root密码的期间,MySQL数据库完全出于没有密码保护的状态下,其他的用户也可以任意地登录和修改MySQL的信息。可以采用将MySQL对外的端口封闭,并且停止Apache以及所有的用户进程的方法实现服务器的准安全状态。最安全的状态是到服务器的Console上面操作,并且拔掉网线。
2.修改MySQL的登录设置:
vi /etc/my.cnf
#在[mysqld]的段中加上一句:skip-grant-tables
#例如:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-grant-tables
#保存并且退出vi。
3.重新启动mysqld
/etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
4.登录并修改MySQL的root密码
# /usr/bin/mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 3.23.56
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE mysql ;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> UPDATE user SET Password = password ( 'new-password' ) WHERE User = 'root' ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 2 Changed: 0 Warnings: 0
mysql> flush privileges ;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye
5.将MySQL的登录设置修改回来
vi /etc/my.cnf
#将刚才在[mysqld]的段中加上的skip-grant-tables删除
#保存并且退出vi。
6.重新启动mysqld
/etc/init.d/mysqld restart
Stopping MySQL: [ OK ]
Starting MySQL: [ OK ]
mysql主从同步故障排错
问题描述:
基本上用mysq都会配置mysql的主从,一方面用mysql的主从做数据库的读写分离,另一方面mysql本身的单机备份不是很强,一般采用主从架构,在从上进行数据备份。在这过程中或多或少出现一些主从不同步的情况,本文将对数据主从不同步的情况进行简单的总结,主要从数据库层面上探讨数据库的主从不一致的情况,并不对主从的本身数据不一致引起的主从不同步进行说明:
问题分析:
- 确认mysql主从服务器的时间是否同步
- 网络的延迟
由于mysql主从复制是基于binlog的一种异步复制,通过网络传送binlog文件,理所当然网络延迟是主从不同步的绝大多数的原因,特别是跨机房的数据同步出现这种几率非常的大,所以做读写分离,注意从业务层进行前期设计。
- 主从两台机器的负载不一致
由于mysql主从复制是主上面启动1个io线程,而从上面启动1个sql线程和1个io线程,当中任何一台机器的负载很高,忙不过来,导致其中的任何一个线程出现资源不足,都将出现主从不一致的情况。
- max_allowed_packet设置不一致
主上面设置的max_allowed_packet比从大,当一个大的sql语句,能在主上面执行完毕,从上面设置过小,无法执行,导致的主从不一致。
- key自增键开始的键值跟自增步长设置不一致引起的主从不一致。
- mysql异常宕机情况下,如果未设置sync_binlog=1或innodb_flush_log_at_trx_commit=1很有可能出现binlog或者relaylog文件出现损坏,导致主从不一致。
- mysql本身的bug引起的主从不同步。
- 版本不一致,特别是高版本是主,低版本为从的情况下,主上面支持的功能,从上面不支持该功能。
mysql的innodb_buffer_pool_size
参考资料:
https://www.cnblogs.com/wanbin/p/9530833.html