mysql配置文件、数据库备份与恢复

1.mysql配置文件

mysql的配置文件为 etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --defaultextrafile=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.socker	指定套接字文件位置
basedir = /usr/local/mysql	指定mysql的安装路径
datadir = /opt/data	指定mysql数据存放路径
pid-fille = /opt/data/mysqld.pid	指定进程ID文件存放路径
usr = mysql	指定mysql以说明用户的身份提供服务
skip-name-resolve	禁止mysql对外部连接进行DNS解析，使用这一选项可以消除mysql进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用ip地址方式，否则mysql将无法正常处理连接请求

2.mysql数据库备份与恢复

2.1数据库常用备份方案

数据库备份方案：

• 全量备份
• 增量备份
• 差异备份

备份方案	特点
全量备份	全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。数据恢复快，备份时间长
增量备份	增量备份是指在一次全备份或者上一次增量备份后，以后每次的备份只需要备份与前一次相比增减和被修改的文件。这就意味着，第一次增量备份的对象是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次备份后所产生的增加和修改的文件。 没有重复的备份数据 ，备份时间短。恢复数据时必须按一定的顺序进行
差异备份	备份上一次的完全备份后发生变化的所有文件。差异备份是指在一次全备后进行差异备份的这段时间内对那些增加或者修改文件的备份。在进行恢复时，我们只需要对第一全量备份和最后一次差异备份进行恢复

2.2mysql备份工具mysqldump

//语法：
mysqldump [OPTIONS] database [tables …]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS]–databases [OPTIONS] DB1 [DB2 DB3…]

//常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听端口，这里的#要用实际的端口号代替，如：-p3306

备份整个数据库（全备）

 mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lzh | | mysql | | performance_schema | | sys | +--------------------+
5 rows in set (0.00 sec) mysql> use lzh; Database changed
mysql> show tables; +---------------+ | Tables_in_lzh | +---------------+ | abc | | bobo | | hanhan | +---------------+
3 rows in set (0.00 sec) [root@reserve ~]# mysqldump -uroot -p  --all-databases >all-20200809.sql Enter password: [root@reserve ~]# ls all-20200809.sql  anaconda-ks.cfg 

备份lzh数据库

[root@reserve ~]# mysqldump -uroot -p --databases lzh > lzh-20200809.sql Enter password: [root@reserve ~]# ls all-20200809.sql  anaconda-ks.cfg  lzh-20200809.sql  table-20200809.sql 

备份lzh数据库的 abc 和 bobo表

[root@reserve ~]# mysqldump -uroot -p lzh abc bobo > table-20200809.sql Enter password: [root@reserve ~]# ls all-20200809.sql  anaconda-ks.cfg  table-20200809.sql 

模拟误删lzh数据库

[root@reserve ~]# mysqldump -uroot -p --databases lzh > lzh-20200809.sql Enter password: [root@reserve ~]# ls all-20200809.sql  anaconda-ks.cfg  lzh-20200809.sql  table-20200809.sql [root@reserve ~]#  

2.3mysql数据恢复

//恢复lzh数据库 [root@reserve ~]# ls all-20200809.sql  anaconda-ks.cfg  lzh-20200809.sql  table-20200809.sql [root@reserve ~]# mysql -uroot -p < all-20200809.sql Enter password: [root@reserve ~]# mysql -uroot -p -e 'show databases;' Enter password: 
+--------------------+ | Database | +--------------------+ | information_schema | | lzh | | mysql | | performance_schema | | sys | +--------------------+ 

//恢复lzh数据库的abc表和bobo表
mysql> use lzh; Database changed
mysql> source table-20200809.sql
Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) ... ... ... ...

mysql> show tables; +---------------+ | Tables_in_lzh | +---------------+ | abc | | bobo | | hanhan | +---------------+
3 rows in set (0.00 sec) 

2.4差异备份与恢复

2.4.1mysql差异备份

1.开启mysql服务器的二进制日志功能

 [mysqld] basedir = /usr/local/mysql [mysqld] basedir = /usr/local/mysql
datadir = /opt/data
port = 3306
socket = /tmp/mysql.sock
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve

server-id = 1          //设置二进制标识符
log-bin=mysql_bin     //开启二进制日志功能 [root@reserve ~]# service mysqld restart Shutting down MySQL.. SUCCESS! Starting MySQL. SUCCESS! 

2.对数据库进行完全备份

[root@reserve ~]# mysql -uroot -p'123.com' mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | lzh | | mysql | | performance_schema | | sys | +--------------------+
5 rows in set (0.00 sec) mysql> show tables from lzh; +---------------+ | Tables_in_lzh | +---------------+ | abc | | bobo | | hanhan | +---------------+
3 rows in set (0.01 sec) mysql> select * from lzh.bobo; mysql> select * from bobo; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | li | 22 | | 2 | zi | 18 | | 3 | han | 22 | | 4 | xubo | 20 | | 5 | lizihan | 25 | | 6 | tao | 21 | +----+---------+------+
6 rows in set (0.00 sec

//完全备份 [root@reserve ~]# mysqldump -uroot -p'123.com' --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20200809.sql mysqldump: [Warning] Using a password on the command line interface can be insecure. 

3.增加新内容

[root@reserve ~]# mysql -uroot -p'123.com' mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2019, 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 lzh; 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 bobo; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | li | 22 | | 2 | zi | 18 | | 3 | han | 22 | | 4 | xubo | 20 | | 5 | lizihan | 25 | | 6 | tao | 21 | +----+---------+------+
6 rows in set (0.00 sec

mysql> insert into bobo values (7,'taotao',29),(8,'hanhan',20); Query OK, 2 rows affected (0.00 sec) mysql> update bobo set age = 40 where id = 6; Query OK, 1 row affected (0.00 sec) Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from bobo; +----+---------+------+ | id | name | age | +----+---------+------+ | 1 | li | 22 | | 2 | zi | 18 | | 3 | han | 22 | | 4 | xubo | 20 | | 5 | lizihan | 25 | | 6 | tao | 40 | | 7 | taotao | 29 | | 8 | hanhan | 20 | +----+---------+------+
8 rows in set (0.00 sec) 

2.4.2mysql差异备份恢复

模拟误删lzh数据库

[root@reserve ~]# mysql -uroot -p'123.com' -e 'drop database lzh;' mysql: [Warning] Using a password on the command line interface can be insecure. [root@reserve ~]# mysql -uroot -p'123.com' -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+
//由上可以看到lzh数据库已经被删除 

刷新创建新的二进制日志

[root@reserve ~]# ll /opt/data/ 总用量 122944
-rw-r-----. 1 mysql mysql       56 8月   9 15:05 auto.cnf
-rw-r-----. 1 mysql mysql      797 8月   9 16:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:23 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月   9 16:23 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月   9 15:05 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:10 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月   9 15:49 mysql
-rw-r-----. 1 mysql mysql      859 8月   9 16:23 mysql_bin.000002
-rw-r-----. 1 mysql mysql       19 8月   9 16:10 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 8月   9 16:01 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月   9 15:05 performance_schema
-rw-r-----. 1 mysql mysql    12670 8月   9 16:01 reserve.err
drwxr-x---. 2 mysql mysql     8192 8月   9 15:05 sys

//刷新创建新的二进制日志 [root@reserve ~]# mysqladmin -uroot -p'123.com' flush-logs mysqladmin: [Warning] Using a password on the command line interface can be insecure. [root@reserve ~]# ll /opt/data/ 总用量 122948
-rw-r-----. 1 mysql mysql       56 8月   9 15:05 auto.cnf
-rw-r-----. 1 mysql mysql      797 8月   9 16:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:23 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月   9 16:23 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月   9 15:05 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:27 ibtmp1
drwxr-x---. 2 mysql mysql     4096 8月   9 15:49 mysql
-rw-r-----. 1 mysql mysql      906 8月   9 16:29 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 8月   9 16:29 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 8月   9 16:29 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 8月   9 16:01 mysql.pid
drwxr-x---. 2 mysql mysql     8192 8月   9 15:05 performance_schema
-rw-r-----. 1 mysql mysql    12670 8月   9 16:01 reserve.err
drwxr-x---. 2 mysql mysql     8192 8月   9 15:05 sys 

恢复完全备份

[root@reserve ~]# mysql -uroot -p'123.com' < all-20200809.sql  mysql: [Warning] Using a password on the command line interface can be insecure. [root@reserve ~]# mysql -uroot -p'123.com' -e 'show databases;' mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+ | Database | +--------------------+ | information_schema | | lzh | | mysql | | performance_schema | | sys | +--------------------+ [root@reserve ~]# mysql -uroot -p'123.com' -e 'show tables from lzh;' mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+ | Tables_in_lzh | +---------------+ | abc | | bobo | | hanhan | +---------------+ [root@reserve ~]# mysql -uroot -p'123.com' -e 'select * from lzh.bobo;' mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+ | id | name | age | +----+---------+------+ | 1 | li | 22 | | 2 | zi | 18 | | 3 | han | 22 | | 4 | xubo | 20 | | 5 | lizihan | 25 | | 6 | tao | 21 | +----+---------+------+ 

恢复差异备份

1.查看mysql的日志操作文件

[root@reserve ~]# ll /opt/data/ 总用量 123968
-rw-r-----. 1 mysql mysql       56 8月   9 15:05 auto.cnf
-rw-r-----. 1 mysql mysql      797 8月   9 16:01 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:31 ibdata1
-rw-r-----. 1 mysql mysql 50331648 8月   9 16:31 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 8月   9 15:05 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 8月   9 16:27 ibtmp1
drwxr-x---. 2 mysql mysql      118 8月   9 16:31 lzh
drwxr-x---. 2 mysql mysql     4096 8月   9 16:31 mysql
-rw-r-----. 1 mysql mysql      906 8月   9 16:29 mysql_bin.000002     //我的mysql日志操作文件，你的可能与我不一样 
-rw-r-----. 1 mysql mysql   778258 8月   9 16:31 mysql_bin.000003 ... ... 

2.检查误删数据库的位置在什么地方

[root@reserve ~]# mysql -uroot -p123.com 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.25-log, Binlog ver: 4 | | mysql_bin.000002 | 123 | Previous_gtids | 1 | 154 | | | mysql_bin.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 219 | Query | 1 | 290 | BEGIN | | mysql_bin.000002 | 290 | Table_map | 1 | 340 | table_id: 111 (lzh.bobo) | | mysql_bin.000002 | 340 | Write_rows | 1 | 401 | table_id: 111 flags: STMT_END_F | | mysql_bin.000002 | 401 | Xid | 1 | 432 | COMMIT /* xid=503 */ | | mysql_bin.000002 | 432 | Anonymous_Gtid | 1 | 497 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 497 | Query | 1 | 568 | BEGIN | | mysql_bin.000002 | 568 | Table_map | 1 | 618 | table_id: 111 (lzh.bobo) | | mysql_bin.000002 | 618 | Update_rows | 1 | 674 | table_id: 111 flags: STMT_END_F | | mysql_bin.000002 | 674 | Xid | 1 | 705 | COMMIT /* xid=506 */ | | mysql_bin.000002 | 705 | Anonymous_Gtid | 1 | 770 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | mysql_bin.000002 | 770 | Query | 1 | 859 | drop database lzh                    
  
  //此处就是删除数据库的位置，info显示的是“drop database lzh ”
  //记住对应的pos位置是770，等下会用到，你的pos值与我不一样 | mysql_bin.000002 | 859 | Rotate | 1 | 906 | mysql_bin.000003;pos=4 | +------------------+-----+----------------+-----------+-------------+---------------------------------------+
15 rows in set (0.00 sec) 

3.使用mysqlbinlog恢复差异备份

[root@reserve ~]# mysqlbinlog --stop-position=770 /opt/data/mysql_bin.000002 | mysql -uroot -p123.com mysql: [Warning] Using a password on the command line interface can be insecure. [root@reserve ~]# mysql -uroot -p123.com -e 'select * from lzh.bobo;' mysql: [Warning] Using a password on the command line interface can be insecure.
+----+---------+------+ | id | name | age | +----+---------+------+ | 1 | li | 22 | | 2 | zi | 18 | | 3 | han | 22 | | 4 | xubo | 20 | | 5 | lizihan | 25 | | 6 | tao | 40 | | 7 | taotao | 29 | | 8 | hanhan | 20 | +----+---------+------+ 

本文地址：https://blog.csdn.net/qq_45472624/article/details/107883453