mySQL配置文件、備份與恢復_ZenDei技術網路在線

mysql配置文件 mysql的配置文件為/etc/my.cnf 配置文件查找次序：若在多個配置文件中均有設定，則最後找到的最終生效 mysql常用配置文件參數：參數 |說明 : |: port = 3306 | 設置監聽埠 socket = /tmp/mysql.sock | 指定套接字文件位 ...

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 |
+------+-------------+------+