完全备份:就是对整个数据库的数据和数据结构进行备份
优点:直观,恢复简单,可靠
缺点:大量重复数据,占用空间,存储成本高
增量备份:每次备份的数据只是相当于上次备份后增加和修改过的数据
优点:节省空间
缺点:恢复麻烦,任何一个备份文件有问题都会导致恢复失败
差异备份:每次备份的数据相当于上一次全备份之后新增加和修改过的数据。
举例说明:就是假如星期一,管理员对数据库进行了一次完全备份,星期二,增加了一张“资产管理表”,则管理员只需把这张表备份下来即可;星期三,增加了一张“工资表”,则管理员需要把“资产管理表”+“工资表”备份下来;星期四增加了一张“人员表”,则备份的数据是“资产管理表”+“工资表”+“人员表”;
备份组合:
一、完全备份+差异备份
假如在星期一完全备份,星期二至五差异备份,如果在星期五数据被破坏,只需要恢复星期一的完整备份和星期四让差异备份即可。备份时间多,还原时间端。
二、完全备份+增量备份
假如在星期一完全备份,星期二至星期五增量备份,在星期五数据被破坏,则需还原星期一的完整备份和星期二到星期四所有的增量备份。
热备:数据库运行中备份,不影响业务
冷备:数据库停止,业务中断
温备:数据库运行中备份,需要加锁,对业务有部分影响
物理备份:直接copy数据文件,跨平台较差(直接复制备份的数据文件是二进制格式的)
逻辑备份:从数据库中"导出"数据另存而进行的备份(将sql语句给导出到一个文本中,比二进制格式的文件大),备份文件较大,备份较慢,无法保证浮点数的精度,使用逻辑备份数据恢复后,还需手动重建索引,十分消耗CPU资源
错误日志:
mysql> show variables like 'log_error%';
+---------------------+-----------+
| Variable_name | Value |
+---------------------+-----------+
| log_error | ./web.err |
| log_error_verbosity | 3 |
+---------------------+-----------+
2 rows in set (0.00 sec)
mysql> show variables like '%log_warnings';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_warnings | 2 |
+---------------+-------+
1 row in set (0.00 sec)
#log_error定义日志路径
#log_warnings定义是否把警告日志写入错误日志
查询日志:默认关闭,记录所有的操作,占用大量的I/0资源,影响性能
mysql> show variables like 'general_log%';
+------------------+---------------+
| Variable_name | Value |
+------------------+---------------+
| general_log | OFF |
| general_log_file | /data/web.log |
+------------------+---------------+
2 rows in set (0.01 sec)
慢查日志:用于定位问题,建议开启
mysql> show variables like 'slow%';
+---------------------+--------------------+
| Variable_name | Value |
+---------------------+--------------------+
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /data/web-slow.log |
+---------------------+--------------------+
3 rows in set (0.00 sec)
mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
#long_query_time定义超过多少时间记录为慢查询,单位为秒
事务日志:事务日志(InnoDB特有的日志)可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把改修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的,我们通常称之为预写式日志,修改数据需要写两次磁盘。
innodb_flush_log_at_trx_commit | 1
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./
二进制日志:主要用于记录修改数据或有可能引起数据改变的mysql语句,并且记录了语句发生时间、执行时长、操作的数据等等。
mysql> show variables like 'log_bin%';
+---------------------------------+---------------------------+
| Variable_name | Value |
+---------------------------------+---------------------------+
| log_bin | ON |
| log_bin_basename | /mybinlog/mysql-bin |
| log_bin_index | /mybinlog/mysql-bin.index |
mysql> show variables like 'binlog%';
+--------------------------------------------+--------------+
| Variable_name | Value |
+--------------------------------------------+--------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | ABORT_SERVER |
| binlog_format | ROW |
| binlog_group_commit_sync_delay | 0 |
| binlog_group_commit_sync_no_delay_count | 0 |
| binlog_gtid_simple_recovery | ON |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlog_transaction_dependency_history_size | 25000 |
| binlog_transaction_dependency_tracking | COMMIT_ORDER |
+--------------------------------------------+--------------+
15 rows in set (0.00 sec)
#binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型,建议为MIXED。如果设定了二进制日志的格式,却没有启用二进制日志,则MySQL启动时会产生警告日志信息并记录于错误日志中。
查看二进制日志:
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 186587 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 186587 |
+------------------+-----------+
2 rows in set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 186587 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1 | 154 | |
| mysql-bin.000001 | 154 | Stop | 1 | 177 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
3 rows in set (0.00 sec)
导出二进制日志到文本
[root@web wordpress]# mysqlbinlog /mybinlog/mysql-bin.000002 > /tmp/a.sql
删除二进制日志:
mysql>reset master;
中继日志:
从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件,然后从服务器SQL线程会读取relay-log日志的内容并应用到从服务器,从而使从服务器和主服务器的数据保持一致。
mysql> show variables like '%relay%';
+---------------------------+---------------------------+
| Variable_name | Value |
+---------------------------+---------------------------+
| max_relay_log_size | 0 |
| relay_log | |
| relay_log_basename | /data/web-relay-bin |
| relay_log_index | /data/web-relay-bin.index |
| relay_log_info_file | relay-log.info |
| relay_log_info_repository | FILE |
| relay_log_purge | ON |
| relay_log_recovery | OFF |
| relay_log_space_limit | 0 |
| sync_relay_log | 10000 |
| sync_relay_log_info | 10000 |
+---------------------------+---------------------------+
11 rows in set (0.03 sec)
设置mysql日志的方法:
全局变量和会话变量:global,session
临时生效:mysql>set global slow_query_log=1;
永久生效 :编辑my.cnf 在[mysqld]下增加 slow_query_log=1