mySQL配置文件、备份与恢复

Wesley13
• 阅读 650

##mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序:若在多个配置文件中均有设定,则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

mysql常用配置文件参数:

参数

说明

port = 3306

设置监听端口

socket = /tmp/mysql.sock

指定套接字文件位置

basedir = /usr/local/mysql

指定MySQL的安装路径

datadir = /data/mysql

指定MySQL的数据存放路径

pid-file = /data/mysql/mysql.pid

指定进程ID文件存放路径

user = mysql

指定MySQL以什么用户的身份提供服务

skip-name-resolve

禁止MySQL对外部连接进行DNS解析,使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项,则所有远程主机连接授权都要使用IP地址方式,否则MySQL将无法正常处理连接请求

##mysql数据库备份 ###数据库常用备份方案

数据库备份方案:

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

###mysql备份工具mysqldump 语法:

mysqldump [OPTIONS] database [tables ...]
mysqldump [OPTIONS] --all-databases [OPTIONS]
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

常用选项:

-uUSERNAME 指定数据库用户名
-hHOST     指定服务器主机,请使用ip地址
-pPASSWORD 指定数据库用户的密码
-P#        指定数据库监听的端口,这里的#需用实际的端口号代替,如-P3


mysql> show tables;
+----------------+
| Tables_in_lynk |
+----------------+
| armor          |
| mastersword    |
+----------------+
2 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lynk               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

mysql> use lynk
Database changed
mysql> show tables;
+----------------+
| Tables_in_lynk |
+----------------+
| armor          |
| mastersword    |
+----------------+
2 rows in set (0.00 sec)

#全备
[root@lynk ~]# mysqldump -uroot -p -h127.0.0.1 --all-databases > all-201902211531.sql
Enter password: 
[root@lynk ~]# ls
all-201902211531.sql  anaconda-ks.cfg

#备份lynk库的mastersword和armor表
[root@lynk ~]# mysqldump -uroot -p -h127.0.0.1 lynk mastersword armor > table-201902211533.sql
Enter password: 
[root@lynk ~]# ls
all-201902211531.sql  anaconda-ks.cfg  table-201902211533.sql

#备份lynk库
[root@lynk ~]# mysqldump -uroot -p -h127.0.0.1 --databases lynk > lynk-201902211536.sql
Enter password: 
[root@lynk ~]# ls
all-201902211531.sql  anaconda-ks.cfg  lynk-201902211536.sql  table-201902211533.sql

###数据恢复

#模拟误删数据库
mysql> drop database lynk;
Query OK, 2 rows affected (0.03 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

#恢复lynk数据库
[root@lynk ~]# mysql -uroot -p -h127.0.0.1 < all-201902211531.sql 
Enter password: 
[root@lynk ~]# mysql -uroot -p
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lynk               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.01 sec)

#恢复表
mysql> use lynk
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> source table-201902211533.sql
Query OK, 0 rows affected (0.00 sec)
···
mysql> show tables;
+----------------+
| Tables_in_lynk |
+----------------+
| armor          |
| mastersword    |
+----------------+
2 rows in set (0.00 sec)

#模拟删除整个数据库
mysql> drop database lynk;
Query OK, 2 rows affected (0.04 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

#恢复整个数据库
[root@lynk ~]# mysql -uroot -p -h127.0.0.1 < all-201902211531.sql 
Enter password: 
[root@lynk ~]# mysql -uroot -p -h127.0.0.1 -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lynk               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

###差异备份 差异备份与全备和增备的操作不同, 差异备份是通过记录对数据库的操作而进行备份还原,其优点在于可以恢复到任意状态,而不是备份时的状态。

#开启mysql二进制日志功能
[root@lynk ~]# cat >> /etc/my.cnf <<EOF
server-id=1
log-bin=mysql_bin
EOF
[root@lynk ~]# service mysqld restart

#这是我的数据库表格
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| lynk               |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from lynk;
+----------------+
| Tables_in_lynk |
+----------------+
| student        |
+----------------+
1 row in set (0.00 sec)

mysql> select * from lynk.student;
+------+-------------+------+
| id   | name        | age  |
+------+-------------+------+
|    1 | tom         |   20 |
|    2 | jerry       |   23 |
|    3 | wangqing    |   25 |
|    4 | sean        |   28 |
|    5 | zhangshan   |   26 |
|    6 | zhangshan   |   20 |
|    7 | lisi        | NULL |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
11 rows in set (0.00 sec)

#先对数据库进行一次全备
[root@lynk ~]# mysqldump -uroot -plynk123~ --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-20190222.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@lynk ~]# ll
总用量 630088
-rw-r--r--. 1 root root    802782 2月  22 19:18 all-20190222.sql
-rw-------. 1 root root      1269 2月  18 17:34 anaconda-ks.cfg

#新增内容
mysql> use lynk
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 student;
+------+-------------+------+
| id   | name        | age  |
+------+-------------+------+
|    1 | tom         |   20 |
|    2 | jerry       |   23 |
|    3 | wangqing    |   25 |
|    4 | sean        |   28 |
|    5 | zhangshan   |   26 |
|    6 | zhangshan   |   20 |
|    7 | lisi        | NULL |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
11 rows in set (0.00 sec)

mysql> update student set age = 4 where id = 1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+------+-------------+------+
| id   | name        | age  |
+------+-------------+------+
|    1 | tom         |    4 |
|    2 | jerry       |   23 |
|    3 | wangqing    |   25 |
|    4 | sean        |   28 |
|    5 | zhangshan   |   26 |
|    6 | zhangshan   |   20 |
|    7 | lisi        | NULL |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
11 rows in set (0.00 sec)

###mysql差异备份恢复数据

#模拟删库
[root@lynk ~]# mysql -uroot -plynk123~ -e 'drop database lynk;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lynk ~]# mysql -uroot -plynk123~ -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

#模拟跑路ε=ε=(ノ ゚Д ゚)ノ(误)

#刷新二进制文件来创建一个新的
[root@lynk ~]# mysqladmin -uroot -plynk123~ flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@lynk ~]# ll /opt/data
总用量 122952
-rw-r-----. 1 mysql mysql       56 2月  22 15:20 auto.cnf
-rw-r-----. 1 mysql mysql      346 2月  22 19:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 2月  22 19:24 ibdata1
-rw-r-----. 1 mysql mysql 50331648 2月  22 19:24 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 2月  22 15:20 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 2月  22 19:18 ibtmp1
-rw-r-----. 1 mysql mysql     8648 2月  22 19:12 localhost.localdomain.err
-rw-r-----. 1 mysql mysql     4315 2月  22 19:12 lynk.err
drwxr-x---. 2 mysql mysql     4096 2月  22 15:20 mysql
-rw-r-----. 1 mysql mysql      636 2月  22 19:26 mysql_bin.000002
-rw-r-----. 1 mysql mysql      154 2月  22 19:26 mysql_bin.000003
-rw-r-----. 1 mysql mysql       38 2月  22 19:26 mysql_bin.index
-rw-r-----. 1 mysql mysql        6 2月  22 19:12 mysql.pid
drwxr-x---. 2 mysql mysql     8192 2月  22 15:20 performance_schema
drwxr-x---. 2 mysql mysql     8192 2月  22 15:20 sys

#进行全备恢复
[root@lynk ~]# mysql -uroot -plynk123~ < all-20190222.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
#但是我们将进行过全备之后,我们的数据是处于新增内容之前的状态
[root@lynk ~]# mysql -uroot -plynk123~ -e 'select * from lynk.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+------+
| id   | name        | age  |
+------+-------------+------+
|    1 | tom         |   20 |
|    2 | jerry       |   23 |
|    3 | wangqing    |   25 |
|    4 | sean        |   28 |
|    5 | zhangshan   |   26 |
|    6 | zhangshan   |   20 |
|    7 | lisi        | NULL |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+

#进行差备恢复
#查询误删数据库位置
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.23-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 |         291 | BEGIN                                 |
| mysql_bin.000002 | 291 | Table_map      |         1 |         345 | table_id: 110 (lynk.student)          |
| mysql_bin.000002 | 345 | Update_rows    |         1 |         401 | table_id: 110 flags: STMT_END_F       |
| mysql_bin.000002 | 401 | Xid            |         1 |         432 | COMMIT /* xid=479 */                  |
| mysql_bin.000002 | 432 | Anonymous_Gtid |         1 |         497 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000002 | 497 | Query          |         1 |         589 | drop database lynk                    |
| mysql_bin.000002 | 589 | Rotate         |         1 |         636 | mysql_bin.000003;pos=4                |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
10 rows in set (0.00 sec)
#可以看到是在位置497进行了drop操作,所以我们要从497的位置进行恢复
[root@lynk ~]# mysqlbinlog --stop-position=497 /opt/data/mysql_bin.000002 |mysql -uroot -plynk123~
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@lynk ~]# mysql -uroot -plynk123~ -e 'select * from lynk.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+------+
| id   | name        | age  |
+------+-------------+------+
|    1 | tom         |    4 |
|    2 | jerry       |   23 |
|    3 | wangqing    |   25 |
|    4 | sean        |   28 |
|    5 | zhangshan   |   26 |
|    6 | zhangshan   |   20 |
|    7 | lisi        | NULL |
|    8 | chenshuo    |   10 |
|    9 | wangwu      |    3 |
|   10 | qiuyi       |   15 |
|   11 | qiuxiaotian |   20 |
+------+-------------+------+
点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
Ubuntu 16 MySQL主从备份配置
环境准备1.机器准备master192.168.1.101slave192.168.1.1022.操作系统Ubuntu163.MySQL版本5.7配置文件说明1.Ubuntu中mysql配置文件在/etc/mysql可以找到其结构如下/etc/mysql
Stella981 Stella981
3年前
Android蓝牙连接汽车OBD设备
//设备连接public class BluetoothConnect implements Runnable {    private static final UUID CONNECT_UUID  UUID.fromString("0000110100001000800000805F9B34FB");
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
10个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这