mysql进阶（含差异备份）

1.mysql的配置文件

例如：/etc/my.conf

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将无法正常处理连接请求

2. mysql数据库备份

数据库备份方案：
1.全量备份
2.增量备份
3.差异备份

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

2.1mysql的备份工具mysqldump

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

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

MySQL的二进制安装

[root@localhost ~]# yum -y install ncurses-devel openssl-devel openssl cmake mariadb-devel
[root@localhost src]# groupadd -r -g 306 mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g 306 -u 306 mysql
[root@localhost ~]# cd /usr/src/
[root@localhost src]# tar xf mysql-5.7.22-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.22-linux-glibc2.12-x86_64  share
[root@localhost src]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.22-linux-glibc2.12-x86_64/ mysql
‘mysql’ -> ‘mysql-5.7.22-linux-glibc2.12-x86_64/’
[root@localhost local]# ll
lrwxrwxrwx  1 root root  36 Feb 20 16:00 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
[root@localhost ~]# chown -R mysql.mysql /usr/local/mysql
[root@localhost ~]# ll /usr/local/mysql -d
lrwxrwxrwx 1 mysql mysql 36 Feb 20 16:00 /usr/local/mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
[root@localhost ~]# ls /usr/local/mysql
bin  COPYING  docs  include  lib  man  README  share  support-files
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost ~]# . /etc/profile.d/mysql.sh
[root@localhost ~]# echo $PATH
/usr/local/mysql/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@localhost mysql]# mkdir /opt/data
[root@localhost mysql]# chown -R mysql.mysql /opt/data/
[root@localhost mysql]# ll /opt/
total 0
drwxr-xr-x 2 mysql mysql 6  Feb 20 16:54 data
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
...
[Note] A temporary password is generatedfor root@localhost: sjYPJyxm#8ne
[root@localhost ~]# ln -sv /usr/local/mysql/include/ /usr/local/include/mysql
‘/usr/local/include/mysql’ -> ‘/usr/local/mysql/include/’
[root@localhost ~]# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf
[root@localhost ~]# ldconfig -v
...
[root@localhost ~]# ldconfig -p |grep mysql
        libmysqlclient.so.20 (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so.20
        libmysqlclient.so.18 (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so.18
        libmysqlclient.so (libc6,x86-64) => /usr/lib64/mysql/libmysqlclient.so
        libmysqlclient.so (libc6,x86-64) => /usr/local/mysql/lib/libmysqlclient.so
[root@localhost ~]# cat > /etc/my.cnf <<EOF
>[mysqld]
>basedir = /usr/local/mysql
>datadir = /opt/data
>socket = /tmp/mysql.sock
>port = 3306
>pid-file = /opt/data/mysql.pid
>user = mysql
>skip-name-resolve
>EOF
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost ~]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@localhost ~]# service mysqld start
Starting MySQL.. SUCCESS! 
[root@localhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.22

Copyright (c) 2000, 2018, 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> 

mysql> set password = password('pengsuran123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye

然后创建仓库和表

mysql> create database pengsuran;
Query OK, 1 row affected (0.01 sec)
mysql> use pengsuran;
Database changed
mysql> create table student(id int(11)NOT NULL,name char(100)NOT NULL,age tinyint NULL);
Query OK, 0 rows affected (0.06 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| psr                |
| pengsuran            |
| sys                |
+--------------------+
6 rows in set (0.05 sec)
mysql> use pengsuran
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> show tables;
+-------------------+
| Tables_in_pengsuran |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

备份数据库和表

[root@localhost ~]# mysqldump -uroot -p pengsuran student >student-table-20190221.sql
Enter password: 
[root@localhost ~]# ls
all-0925.sql  anaconda-ks.cfg  mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz  student-table-20190221.sql
[root@localhost ~]# mysqldump -uroot -p databases pengsuran>pengsuran-database-20190221.sql
Enter password: 
[root@localhost ~]# ls
all-0925.sql  anaconda-ks.cfg  mysql-5.7.23-linux-glibc2.12-x86_64.tar.gz  pengsuran-database-20190221.sql  student-table-20190221.sql

如果数据库被误删，用以下方法可恢复

[root@localhost ~]# mysql -uroot -p pengsuran < student-table-20190221.sql
Enter password: 
[root@localhost ~]# mysql -uroot -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pengsuran            |
| sys                |
+--------------------+
5 rows in set (0.01 sec)
mysql> use pengsuran;
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> show tables;
+-------------------+
| Tables_in_pengsuran |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)

3.差异备份

[root@psr1 ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
server-id=1          
log-bin=mysql_bin   
[root@psr1 ~]# service mysqld restart 
Shutting down MySQL.............. SUCCESS! 
Starting MySQL............................. SUCCESS! 
mysql> show tables from pengsuran;    
±------------------+
| Tables_in_pengsuran |
±------------------+
| student           |
±------------------+
2 rows in set (0.00 sec)
[root@psr1 ~]# ll /opt/data/|grep mysql_bin
-rw-r-----. 1 mysql mysql     1246 2月  22 16:13 mysql_bin.000001
-rw-r-----. 1 mysql mysql       19 2月  22 16:13 mysql_bin.index
[root@psr1 ~]# mysql -uroot -p
Enter password: 
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)
ow master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000001|   778719 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
[root@psr1 ~]# mysqldump -uroot -p --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-201902230940.sql //进行备份
mysql> select * from pengsuran.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>  insert into student values(12,'MLXG',6),(13,'GOD',7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> select * from pengsuran.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 |
| 12 | MLXG    |    6 |
| 13 | GOD   |    7 |
+----+-------------+------+
13 rows in set (0.00 sec)
[root@psr1 ~]# mysql -uroot -p -e 'drop database pengsuran;'
[root@psr1 ~]# mysql -uroot -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
[root@psr1 ~]# mysqladmin -uroot -p flush-logs
//恢复误删的数据
[root@localhost ~]# mysql -uroot -p <all-201902231020.sql
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| pengsuran            |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)
mysql> show tables from pengsuran;
+-------------------+
| Tables_in_qinyong |
+-------------------+
| student           |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from pengsuran.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> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql_bin.000002 |   785480 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql_bin.000002'\G
...
*************************** 19. row ***************************    删除数据关键节点
   Log_name: mysql_bin.000002
           Pos: 945
 Event_type: Query
  Server_id: 1
End_log_pos: 1014
       Info: drop database pengsuran
...
然后恢复数据
[root@psr1 ~]# mysqlbinlog --stop-position=1014 /opt/data/mysql_bin.000002|mysql -uroot -p
Enter password:
验证：
mysql> select * from pengsuran.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 |
| 12 | MLXG    |    6 |
| 13 | GOD   |    7 |
+----+-------------+------+
13 rows in set (0.00 sec)