加强信息管理 维护网站建设,专业网站建设哪个好,福田外贸网站建设,湖北网站建设检修一.日志
1.MySQL 的日志默认保存位置为
/usr/local/mysql/data2.修改日志配置文件
vim /etc/my.cnf
[mysqld]
##错误日志#xff0c;用来记录当MySQL启动、停止或运行时发生的错误信息#xff0c;默认已开启
log-error/usr/local/mysql/data/mysql_error.log #指定日志的…一.日志
1.MySQL 的日志默认保存位置为
/usr/local/mysql/data2.修改日志配置文件
vim /etc/my.cnf
[mysqld]
##错误日志用来记录当MySQL启动、停止或运行时发生的错误信息默认已开启
log-error/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名##通用查询日志用来记录MySQL的所有连接和语句默认是关闭的
general_logON
general_log_file/usr/local/mysql/data/mysql_general.log##二进制日志(binlog)用来记录所有更新了数据或者已经潜在更新了数据的语句记录了数据的更改可用于数据恢复默认已开启
log-binmysql-bin #也可以 log_binmysql-bin##慢查询日志用来记录所有执行时间超过long_query_time秒的语句可以找到哪些查询语句执行时间长以便于优化默认是关闭的
slow_query_logON
slow_query_log_file/usr/local/mysql/data/mysql_slow_query.log
long_query_time5 #设置超过5秒执行的语句被记录缺省时为10秒3.查看通用查询日志是否开启
systemctl restart mysqld
mysql -u root -p
show variables like general%; 例[rootwww data]# systemctl restart mysqld
[rootwww data]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2
Type help; or \h for help. Type \c to clear the current input statement.
mysql show variables like general%;
-----------------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------------
| general_log | ON |
| general_log_file | /usr/local/mysql/data/mysql_general.log |
-----------------------------------------------------------
2 rows in set (0.00 sec)4.查看二进制日志是否开启
show variables like log_bin%;例mysql show variables like log_bin%;
------------------------------------------------------------------------
| Variable_name | Value |
------------------------------------------------------------------------
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/mysql-bin |
| log_bin_index | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
------------------------------------------------------------------------
5 rows in set (0.01 sec)
5.查看慢查询日功能是否开启
show variables like %slow%; 例mysql show variables like %slow%;
-----------------------------------------------------------------------
| Variable_name | Value |
-----------------------------------------------------------------------
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/mysql_slow_query.log |
-----------------------------------------------------------------------
5 rows in set (0.00 sec)6.查看慢查询时间设置
show variables like long_query_time; 例mysql show variables like long_query_time;
---------------------------
| Variable_name | Value |
---------------------------
| long_query_time | 5.000000 |
---------------------------
1 row in set (0.00 sec)7.在数据库中设置开启慢查询的方法
set global slow_query_logON;例mysql set global slow_query_logON;
Query OK, 0 rows affected (0.00 sec)二.日志类型与作用
1.redo 重做日志
达到事务一致性每次重启会重做确保日志的持久性防止在发生故障脏页未写入磁盘。重启数据库会进行redo log执行重做达到事务一致性
2.undo 回滚日志
保证数据的原子性记录事务发生之前的一个版本用于回滚innodb事务可重复读和读取已提交 隔离级别就是通过mvccundo实现
3.errorlog 错误日志
Mysql本身启动停止运行期间发生的错误信息
4.slow query log 慢查询日志
记录执行时间过长的sql时间阈值10s可以配置只记录执行成功 另一个作用在于提醒优化
5.bin log 二进制日志
用于主从复制实现主从同步 记录的内容是数据库中执行的sql语句
6.relay log 中继日志
用于数据库主从同步将主库发来的bin log保存在本地然后从库进行回放
7.general log 普通日志
记录数据库的操作明细默认关闭开启后会降低数据库性能
三.数据备份的重要性
1.备份的主要目的是灾难恢复 2.在生产环境中数据的安全性至关重要任何数据的丢失都可能产生严重的后果 3.造成数据丢失的原因
程序错误人为操作错误运算错误磁盘故障灾难如火灾、地震)和盗窃
四.Mysql数据库完全备份
1.从物理与逻辑的角度
热备份(联机备份)︰数据库处于运行状态依赖于数据库的日志文件冷备份:数据库锁定表格不可写入但可读)的状态下进行备份操作逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
2.数据库备份的分类
完全备份:每次对数据库进行完整的备份差异备份:备份自从上次完全备份之后被修改过的文件增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
3.常见的备份方法 1物理冷备
在库中新建表并添加数据
use tour;
create table if not exists info1 (
id int(4) not null auto_increment,
name varchar(10) not null,
sex char(10) not null,
hobby varchar(50),
primary key (id));
insert into info1 values(1,user1,male,running);
insert into info1 values(2,user2,female,singing);例mysql use tour;
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 create table if not exists info1 (- id int(4) not null auto_increment,- name varchar(10) not null,- sex char(10) not null,- hobby varchar(50),- primary key (id));
Query OK, 0 rows affected (0.02 sec)
mysql insert into info1 values(1,user1,male,running);
Query OK, 1 row affected (0.01 sec)
mysql insert into info1 values(2,user2,female,singing);
Query OK, 1 row affected (0.00 sec)
备份时数据库处于关闭状态直接打包数据库文件备份速度快恢复时也是最简单的
systemctl stop mysqld
yum -y install xz
tar Jcvf /opt/mysql_all_$(date %F).tar.xz /usr/local/mysql/data/
mv /usr/local/mysql/data/ ~
tar Jxvf /opt/mysql_all_2020-11-22.tar.xz
mv /opt/usr/local/mysql/data/ /usr/local/mysql/例[rootwww data]# systemctl stop mysqld
[rootwww data]# yum -y install xz
已加载插件fastestmirror, langpacks
file:///mnt/repodata/repomd.xml: [Errno 14] curl#37 - Couldnt open file /mnt/repodata/repomd.xml
正在尝试其它镜像。
Loading mirror speeds from cached hostfile
软件包 xz-5.2.2-1.el7.x86_64 已安装并且是最新版本
无须任何处理
[rootwww data]# tar Jcvf /opt/mysql_all_$(date %F).tar.xz /usr/local/mysql/data/
[rootwww data]# mv /usr/local/mysql/data/ ~
[rootwww opt]# tar Jxvf /opt/mysql_all_2023-06-19.tar.xz
[rootwww opt]# mv /opt/usr/local/mysql/data/ /usr/local/mysql/2mysqldump专用备份工具
完全备份一个或多个完整的库包括其中所有的表
mysqldump -u root -p[密码] --databases 库名1 [库名2] … /备份路径/备份文件名.sql #导出的就是数据库脚本文件例[rootwww opt]# systemctl restart mysqld.service
[rootwww opt]# mysqldump -u root -p --databases tour /opt/tour.sql
Enter password:
[rootwww opt]# mysqldump -u root -p --databases mysql tour /opt/mysql-tour.sql
Enter password: 完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases /备份路径/备份文件名.sql例[rootwww opt]# mysqldump -u root -p --all-databases /opt/all.sql
Enter password: 完全备份指定库中的部分表
mysqldump -u root -p[密码] 库名 [表名1] [表名2] … /备份路径/备份文件名.sql例[rootwww opt]# mysqldump -u root -p -d tour info1 sunset /opt/tour_info1.sql
Enter password:查看备份文件
grep -v ^-- /opt/kgc_info1.sql | grep -v ^/ | grep -v ^$例[rootwww opt]# grep -v ^-- /opt/tour_info1.sql | grep -v ^/ | grep -v ^$
DROP TABLE IF EXISTS info1;
CREATE TABLE info1 (id int(4) NOT NULL AUTO_INCREMENT,name varchar(10) NOT NULL,sex char(10) NOT NULL,hobby varchar(50) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT3 DEFAULT CHARSETutf8;
DROP TABLE IF EXISTS sunset;
CREATE TABLE sunset (id int(11) DEFAULT NULL,name char(4) DEFAULT NULL,age int(11) DEFAULT NULL,sex char(2) DEFAULT NULL
) ENGINEInnoDB DEFAULT CHARSETutf8;3启用二进制日志进行增量备份
进行增量备份需要刷新二进制日志
4第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
4.MySQL完全备份
是对整个数据库、数据库结构和文件结构的备份保存的是备份完成时刻的数据库是差异备份与增量备份的基础
5.MySQL完全备份优点
备份与恢复操作简单方便
6.MySQL完全备份缺点
数据存在大量的重复占用大量的备份空间备份与恢复时间长
7.MySQL完全恢复
1恢复数据库
systemctl start mysqld
mysql -u root -p -e drop database tour;
mysql -u root -p -e SHOW DATABASES;
mysql -u root -p /opt/tour.sql
mysql -u root -p -e SHOW DATABASES;例[rootwww opt]# systemctl start mysqld
[rootwww opt]# mysql -u root -p -e drop database tour;
Enter password:
[rootwww opt]# mysql -u root -p -e SHOW DATABASES;
Enter password:
--------------------
| Database |
--------------------
| information_schema |
| bbs |
| mysql |
| performance_schema |
| sun |
| sys |
--------------------
[rootwww opt]# mysql -u root -p /opt/tour.sql
Enter password:
[rootwww opt]# mysql -u root -p -e SHOW DATABASES;
Enter password:
--------------------
| Database |
--------------------
| information_schema |
| bbs |
| mysql |
| performance_schema |
| sun |
| sys |
| tour |
--------------------2恢复数据表 当备份文件中只包含表的备份而不包含创建的库的语句时执行导入操作时必须指定库名且目标库必须存在。
mysqldump -u root -p tour info1 /opt/tour_info1.sql
mysql -u root -p -e drop table tour.info1;
mysql -u root -p -e show tables from tour;
mysql -u root -p tour /opt/tour_info1.sql
mysql -u root -p -e show tables from tour;例[rootwww opt]# mysqldump -u root -p tour info1 /opt/tour_info1.sql
Enter password:
[rootwww opt]# mysql -u root -pabc123 -e drop table tour.info1;
mysql: [Warning] Using a password on the command line interface can be insecure.
[rootwww opt]# mysql -u root -pabc123 -e show tables from tour;
mysql: [Warning] Using a password on the command line interface can be insecure.
----------------
| Tables_in_tour |
----------------
| account |
| kiki |
| moon |
| stars |
| sunset |
| t1 |
----------------
[rootwww opt]# mysql -u root -pabc123 tour /opt/tour_info1.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[rootwww opt]# mysql -u root -pabc123 -e show tables from tour;
mysql: [Warning] Using a password on the command line interface can be insecure.
----------------
| Tables_in_tour |
----------------
| account |
| info1 |
| kiki |
| moon |
| stars |
| sunset |
| t1 |
----------------五.Mysql 增量备份
1开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-binmysql-bin
binlog_format MIXED #默认格式STATEMENT(基于SQL语句)ROW(基于行)、MIXED(混合模式)
server-id 1systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*例[rootwww opt]# systemctl start mysqld
[rootwww opt]# ls -l /usr/local/mysql/data/mysql-bin.*
-rw-r----- 1 mysql mysql 1042 6月 19 15:11 /usr/local/mysql/data/mysql-bin.000001
-rw-r----- 1 mysql mysql 177 6月 19 15:52 /usr/local/mysql/data/mysql-bin.000002
-rw-r----- 1 mysql mysql 10154 6月 19 16:07 /usr/local/mysql/data/mysql-bin.000003
-rw-r----- 1 mysql mysql 57 6月 19 15:52 /usr/local/mysql/data/mysql-bin.index2可每周对数据库或表进行完全备份
mysqldump -u root -p kgc info1 /opt/kgc_info1_$(date %F).sql
mysqldump -u root -p --databases kgc /opt/kgc_$(date %F).sql例[rootwww opt]# mysqldump -u root -p tour info1 /opt/tour_info1_$(date %F).sql
Enter password:
[rootwww opt]# mysqldump -u root -pabc123 --databases tour /opt/tour_$(date %F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.3可每天进行增量备份操作生成新的二进制日志文件(例如 mysql-bin.000002)
mysqladmin -u root -p flush-logs例[rootwww opt]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.4插入新数据以模拟数据的增加或变更
use kgc;
insert into info1 values(3,user3,male,game);
insert into info1 values(4,user4,female,reading);例[rootwww opt]# mysql -troot -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Type help; or \h for help. Type \c to clear the current input statement.
mysql use tour;
Database changed
mysql insert into info1 values(3,user3,male,game);
Ignoring query to other database
mysql insert into info1 values(4,user4,female,reading);
Ignoring query to other database5再次生成新的二进制日志文件(例如 mysql-bin.000003)
mysqladmin -u root -p flush-logs例[rootwww opt]# mysqladmin -u root -pabc123 flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.6查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /opt/
mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/mysql-bin.000002例[rootwww opt]# cp /usr/local/mysql/data/mysql-bin.000002 /opt/
[rootwww opt]# mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/mysql-bin.000002
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#230619 15:31:31 server id 1 end_log_pos 123 CRC32 0xcf970784 Start: binlog v 4, server v 5.7.41-log created 230619 15:31:31 at startup
ROLLBACK/*!*/;
# at 123
#230619 15:31:31 server id 1 end_log_pos 154 CRC32 0x519bcca2 Previous-GTIDs
# [empty]
# at 154
#230619 15:52:29 server id 1 end_log_pos 177 CRC32 0xf8dd4e78 Stop
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;六.Mysql 增量恢复
1一般恢复
模拟丢失更改的数据的恢复步骤
use kgc;
delete from info1 where id3;
delete from info1 where id4;
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p例mysql use tour;
Database changed
mysql delete from info1 where id3;
Ignoring query to other database
mysql delete from info1 where id4;
Ignoring query to other database
mysql quit
Bye
[rootwww opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password:模拟丢失所有数据的恢复步骤
use kgc;
drop table info1;
mysql -u root -p kgc /opt/kgc_info1_2020-11-22.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p例mysql use tour;
Database changed
mysql drop table info1;
Ignoring query to other database
mysql quit
Bye
[rootwww opt]# mysql -u root -p tour /opt/tour_info1_2023-06-19.sql
Enter password:
[rootwww opt]# mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p
Enter password: 2断点恢复
mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/mysql-bin.000002例[rootwww opt]# mysqlbinlog --no-defaults --base64-outputdecode-rows -v /opt/mysql-bin.000002
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE1*/;
/*!50003 SET OLD_COMPLETION_TYPECOMPLETION_TYPE,COMPLETION_TYPE0*/;
DELIMITER /*!*/;
# at 4
#230619 15:31:31 server id 1 end_log_pos 123 CRC32 0xcf970784 Start: binlog v 4, server v 5.7.41-log created 230619 15:31:31 at startup
ROLLBACK/*!*/;
# at 123
#230619 15:31:31 server id 1 end_log_pos 154 CRC32 0x519bcca2 Previous-GTIDs
# [empty]
# at 154
#230619 15:52:29 server id 1 end_log_pos 177 CRC32 0xf8dd4e78 Stop
SET SESSION.GTID_NEXT AUTOMATIC /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPEOLD_COMPLETION_TYPE*/;
/*!50530 SET SESSION.PSEUDO_SLAVE_MODE0*/;基于位置恢复
mysqlbinlog --no-defaults --stop-position623 /opt/mysql-bin.000002 | mysql -uroot -p
mysqlbinlog --no-defaults --start-position623 /opt/mysql-bin.000002 | mysql -uroot -p例[rootwww opt]# mysqlbinlog --no-defaults --stop-position623 /opt/mysql-bin.000002 | mysql -uroot -p
Enter password:
[rootwww opt]# mysqlbinlog --no-defaults --start-position623 /opt/mysql-bin.000002 | mysql -uroot -p
Enter password: 基于时间点恢复
mysqlbinlog --no-defaults --stop-datetime2020-11-22 16:41:24 /opt/mysql-bin.000002 |mysql -uroot -p
mysqlbinlog --no-defaults --start-datetime2020-11-22 16:41:24 /opt/mysql-bin.000002 |mysql -uroot -p例[rootwww opt]# mysqlbinlog --no-defaults --stop-datetime2023-06-19 16:41:24 /opt/mysql-bin.000002 |mysql -uroot -p
Enter password:
[rootwww opt]# mysqlbinlog --no-defaults --start-datetime2023-06-19 16:41:24 /opt/mysql-bin.000002 |mysql -uroot -p
Enter password: 如果恢复某条SQL语句之前的所有数据就stop在这个语句的位置节点或者时间点如果恢复某条SQL语句以及之后的所有数据就从这个语句的位置节点或者时间点start
