#数据库存储文件 ## 配置文件 ### linux > my.cnf ### windows > my.ini # 存储引擎 ## myisam 存储引擎 > .MYI >> 存储的索引 > .MYD >> 存储的数据 > 数据和索引分开存储 ## innodb 存储引擎 > 存储方式和 myisam不同，它是数据和索引都存在一起 > innodb 数据存储方式是通过b+tree 方式存储，b+tree结构的构成是由索引构成，数据存在底部。 # 日志文件 ## 查询日志 > 主要记录mysql的查询; ```shell mysql> show variables like "%general_log%"; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | OFF | | general_log_file | /var/lib/mysql/839d8ee89a7b.log | +------------------+---------------------------------+ 2 rows in set (0.00 sec) #如果是OFF就是关闭的状态 #开启查询日志 mysql> set global general_log = "ON"; Query OK, 0 rows affected (0.00 sec) mysql> show variables like "%general_log%"; +------------------+---------------------------------+ | Variable_name | Value | +------------------+---------------------------------+ | general_log | ON | | general_log_file | /var/lib/mysql/839d8ee89a7b.log | +------------------+---------------------------------+ 2 rows in set (0.00 sec) #查看日志文件 root@839d8ee89a7b:/var/lib/mysql# cat /var/lib/mysql/839d8ee89a7b.log mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument 2021-07-15T04:16:22.988599Z 7 Query show variables like "%general_log%" 2021-07-15T04:17:31.435971Z 7 Quit ##一般不建议开启查询日志 ``` ## 慢日志 > 主要记录的是mysql中响应超过某个时间段 --log_query_time . > 作用：帮助我们在项目运行一段时间后一些特定的sql进行捕获，通常是比较慢的sql，得到sql之后可以针对性的进行优化。 > 相关 参数 > slow_query_log = 1 -- 开启慢查询日志 > slow_query_log_file -- path + 文件名称 > long_query_time = 3 -- 三秒钟的限制设置最大等待时间三秒钟 ```shell show variables like "%slow%" ---查看墁查询是否开启 set global slow_query_log = "ON" -- 开启慢查询 show variables like "%long%" --慢查询限制时间 set global long_query_time = 3 -- 限制慢查询时间超过三秒就加入日志 SELECT sleep(4) --测试慢查询是否能写入日志 ``` ```shell show variables like "%slow%" ---查看墁查询是否开启 #运行结果 如果是ON就是开启如果是OFF就需要手动开启 slow_query_log ON slow_query_log_file /var/lib/mysql/839d8ee89a7b-slow.log #我们测试下能否记录慢日志 SELECT sleep(4) root@839d8ee89a7b:/var/lib/mysql# tail -f 839d8ee89a7b-slow.log mysqld, Version: 5.7.34 (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /var/run/mysqld/mysqld.sock Time Id Command Argument # Time: 2021-07-15T04:35:25.754620Z # User@Host: root[root] @ [172.17.0.1] Id: 8 # Query_time: 4.000264 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use blog; SET timestamp=1626323725; SELECT sleep(4); #成功记录 ``` ## 错误日志 > 记录mysql服务错误而不是sql执行错误的日志。 ## 二进制日志 > 记录执行的sql(select,update,delete,create)写的操作。 ```shell show variables like '%log_bin%' --查看到binlog日志为OFF关闭状态; show variables like '%datadir%'; --查看data目录 show master status; -当前使用的二进制文件 show binlog events in "mysql-bin.0000001" - 查询指定的二进制文件 show binary logs; --查询当前mysql所有的二进制文件 show binlog events; --查询第一个日志文件 resert master --清空所有二进制文件 flush logs -清空所有日志文件 vim /etc/mysql/my.cnf 编辑配置文件 [mysqld] explicit_defaults_for_timestamp=true pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log ## 加上这两句 log-bin=mysql-bin server-id=1 binlog_format = mixed #混合模式 记录sql语句 #执行 show variables like '%log_bin%' - 开启成功 log_bin ON log_bin_basename /var/lib/mysql/mysql-bin log_bin_index /var/lib/mysql/mysql-bin.index log_bin_trust_function_creators OFF log_bin_use_v1_row_events OFF sql_log_bin ON mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> show binlog events in "mysql-bin.000002"; +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ | mysql-bin.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql-bin.000002 | 123 | Previous_gtids | 1 | 154 | | +------------------+-----+----------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) ``` ### 增量备份以及恢复 >通过日志文件节点恢复数据 根据节点需要我们查看日志文件提供给我们的数据库创建，表创建，数据新增等时创建的语句节点，从而恢复数据。 ```shell #查看二进制文件 mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000003 | 437 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) ##查看要恢复的节点 mysql> show binlog events in "mysql-bin.000003"; +------------------+-----+----------------+-----------+-------------+------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+----------------+-----------+-------------+------------------------------------------------+ | mysql-bin.000003 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql-bin.000003 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000003 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000003 | 219 | Query | 1 | 298 | BEGIN | | mysql-bin.000003 | 298 | Query | 1 | 406 | use `blog`; update test set age=1 where id = 1 | | mysql-bin.000003 | 406 | Xid | 1 | 437 | COMMIT /* xid=10 */ | +------------------+-----+----------------+-----------+-------------+------------------------------------------------+ 6 rows in set (0.00 sec) #POS那一列是节点数据 ``` ###使用二进制恢复数据 * 查看mysqldata目录 ```shell #mysql 执行：show variables like '%datadir%'; #的呆：datadir = /var/lib/mysql/ root@839d8ee89a7b:/var/lib/mysql# cd /var/lib/mysql/ root@839d8ee89a7b:/var/lib/mysql# ls 839d8ee89a7b-slow.log blog client-key.pem ibdata1 mysql-bin.000002 performance_schema server-key.pem 839d8ee89a7b.err ca-key.pem ib_buffer_pool ibtmp1 mysql-bin.000003 private_key.pem sys 839d8ee89a7b.log ca.pem ib_logfile0 mysql mysql-bin.000004 public_key.pem auto.cnf client-cert.pem ib_logfile1 mysql-bin.000001 mysql-bin.index server-cert.pem #mysql 执行：show binary logs; #得到 mysql-bin.000001 177 mysql-bin.000002 698 mysql-bin.000003 460 mysql-bin.000004 154 #每一次启动Mysql都会生成一个新的binlog文件。 ``` 1. 创建一个用于测试的库create database mytest; ```shell mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | blog | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> create database mytest; Query OK, 1 row affected (0.00 sec) mysql> use mytest; Database changed mysql> show tables; Empty set (0.00 sec) mysql> ``` 2. 创建一张用于测试的用户表并新增数据。 ```shell #建表语句 CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 insert into `user`(username,age)values('starsky',10); insert into `user`(username,age)values('ShineYork',10); insert into `user`(username,age)values('Will',10); mysql> CREATE TABLE `user` ( -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `username` varchar(20) DEFAULT NULL, -> `age` int(11) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.01 sec) mysql> insert into `user`(username,age)values('starsky',10); Query OK, 1 row affected (0.01 sec) mysql> insert into `user`(username,age)values('ShineYork',10); Query OK, 1 row affected (0.00 sec) mysql> insert into `user`(username,age)values('Will',10); Query OK, 1 row affected (0.00 sec) #建表完成 mysql> show tables; +------------------+ | Tables_in_mytest | +------------------+ | user | +------------------+ 1 row in set (0.00 sec) #查询数据库 mysql> select * from user; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 1 | starsky | 10 | | 2 | ShineYork | 10 | | 3 | Will | 10 | +----+-----------+------+ 3 rows in set (0.01 sec) ##删除数据库和表 mysql> delete from `user` where `id` in (1,2,3); Query OK, 3 rows affected (0.01 sec) mysql> select count(*) from user; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec) mysql> drop database `mytest`; Query OK, 1 row affected (0.01 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | blog | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) # 现在mytest这张表是没有了，我们通过binlog来试着恢复下。 mysql> show binlog events in "mysql-bin.000004"; +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mysql-bin.000004 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.34-log, Binlog ver: 4 | | mysql-bin.000004 | 123 | Previous_gtids | 1 | 154 | | | mysql-bin.000004 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 219 | Query | 1 | 319 | create database mytest | | mysql-bin.000004 | 319 | Anonymous_Gtid | 1 | 384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 384 | Query | 1 | 659 | use `mytest`; CREATE TABLE `user` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `username` varchar(20) DEFAULT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | | mysql-bin.000004 | 659 | Anonymous_Gtid | 1 | 724 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 724 | Query | 1 | 807 | BEGIN | | mysql-bin.000004 | 807 | Intvar | 1 | 839 | INSERT_ID=1 | | mysql-bin.000004 | 839 | Query | 1 | 969 | use `mytest`; insert into `user`(username,age)values('starsky',10) | | mysql-bin.000004 | 969 | Xid | 1 | 1000 | COMMIT /* xid=81 */ | | mysql-bin.000004 | 1000 | Anonymous_Gtid | 1 | 1065 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1065 | Query | 1 | 1148 | BEGIN | | mysql-bin.000004 | 1148 | Intvar | 1 | 1180 | INSERT_ID=2 | | mysql-bin.000004 | 1180 | Query | 1 | 1312 | use `mytest`; insert into `user`(username,age)values('ShineYork',10) | | mysql-bin.000004 | 1312 | Xid | 1 | 1343 | COMMIT /* xid=82 */ | | mysql-bin.000004 | 1343 | Anonymous_Gtid | 1 | 1408 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1408 | Query | 1 | 1491 | BEGIN | | mysql-bin.000004 | 1491 | Intvar | 1 | 1523 | INSERT_ID=3 | | mysql-bin.000004 | 1523 | Query | 1 | 1650 | use `mytest`; insert into `user`(username,age)values('Will',10) | | mysql-bin.000004 | 1650 | Xid | 1 | 1681 | COMMIT /* xid=83 */ | | mysql-bin.000004 | 1681 | Anonymous_Gtid | 1 | 1746 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 1746 | Query | 1 | 1829 | BEGIN | | mysql-bin.000004 | 1829 | Query | 1 | 1947 | use `mytest`; delete from `user` where `id` in (1,2,3) | | mysql-bin.000004 | 1947 | Xid | 1 | 1978 | COMMIT /* xid=109 */ | | mysql-bin.000004 | 1978 | Anonymous_Gtid | 1 | 2043 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql-bin.000004 | 2043 | Query | 1 | 2143 | drop database `mytest` | +------------------+------+----------------+-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 27 rows in set (0.00 sec) #里面存在着很多信息 我们需要找到开始时候的POS和结束时候的POS进行恢复。 筛选到 154 和 1746 试下。 root@839d8ee89a7b:/# mysqlbinlog --start-position=154 --stop-position=1746 /var/lib/mysql/mysql-bin.000004 | mysql -uroot -p Enter password: mysql> select * from user; +----+-----------+------+ | id | username | age | +----+-----------+------+ | 1 | starsky | 10 | | 2 | ShineYork | 10 | | 3 | Will | 10 | +----+-----------+------+ 3 rows in set (0.00 sec) #成功恢复！ ``` ### 主从复制 最后修改：2021 年 07 月 15 日 © 允许规范转载 打赏 赞赏作者 支付宝微信 赞 如果觉得我的文章对你有用，请随意赞赏
