MySql进阶管理备份操作和Xtrabackup使用
mysql进阶
1.二进制格式mysql安装:
下载软件包：
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
创建用户和组：
[root@chouyu]#cd /usr/src
[root@chouyu src]# groupadd -r mysql
[root@chouyu src]# useradd -M -s /sbin/nologin -g mysql mysql
解压软件到/usr/local
[root@chouyu src]# tar xf mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz.2 -C /usr/local/
创建软连接：
[root@chouyu local]# ln -sv mysql-5.7.31-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.31-linux-glibc2.12-x86_64/'
修改属组属主：
[root@chouyu local]# chown mysql:mysql /usr/local/mysql
配置环境变量：
[root@chouyu local]# ls /usr/local/mysql
bin docs include lib LICENSE man README share support-files
[root@chouyu local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@chouyu local]# . /etc/profile.d/mysql.sh
[root@chouyu local]# echo $PATH
/usr/local/mysql/bin:/usr/local/httpd/bin:/usr/local/httpd-2.4.46/bin/:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@chouyu local]#
创建数据存放目录：
[root@chouyu local]# mkdir /opt/data
[root@chouyu local]# chown mysql:mysql /opt/data/
[root@chouyu local]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 12月 28 05:35 data
[root@chouyu local]#
启动数据库：
[root@localhost ~]# service mysqld start
初始化数据库：
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2020-12-28T11:13:43.830403Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-12-28T11:13:44.093744Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-12-28T11:13:44.121143Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-12-28T11:13:44.127449Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: bf799599-48fd-11eb-95e7-000c295fced8.
2020-12-28T11:13:44.128099Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-12-28T11:13:44.629165Z 0 [Warning] CA certificate ca.pem is self signed.
2020-12-28T11:13:44.822939Z 1 [Note] A temporary password is generated for root@localhost: D8Gd%cXojuv3
[root@localhost ~]#
生成配置文件：
[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# cat /etc/my.cnf
[mysql]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
配置服务启动脚本： [root@localhost init.d]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld [root@localhost init.d]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld [root@localhost init.d]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
重置密码：
[root@localhost ~]# mysql -uroot -p
mysql> set password =password ('123456')
-> ;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
2.MySQL配置文件
- 存放路径/etc/my.cnf
- 关于.my.cnf:存放于家目录，多应用于脚本，不会输出登录步骤
[root@localhost ~]# vim .my.cnf
[client]
user=root
password=123456
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf
| 参数 | 说明 |
|---|---|
| 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将无法正常处理连接请求 |
数据库破解密码
写入跳过授权：
[root@localhost ~]# vim /etc/my.cnf
...
skip-grant-tables
重启数据库服务：
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
进入数据库：
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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>
修改数据库登录密码为：123321123
mysql> use mysql
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 user\G //查看user表
*************************** 1. row ***************************
Host: localhost
User: root
Select_priv: Y
Insert_priv: Y
Update_priv: Y
....
mysql>update user set authentication_string=password('123321123')where User='root' and Host='localhost';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> quit
Bye
[root@localhost ~]# vim /etc/my.cnf //删除skip-grant-tables
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# mysql -uroot -p123321123
3.MYSQL备份
备份方案
| 备份方案 | 特点 |
| 全量备份 | 全量备份就是指对某一个时间点上的所有数据或应用进行的一个完全拷贝。 数据恢复快。 备份时间长 |
| 增量备份 | 增量备份是指在一次全备份或上一次增量备份后，以后每次的备份只需备份 与前一次相比增加和者被修改的文件。这就意味着，第一次增量备份的对象 是进行全备后所产生的增加和修改的文件；第二次增量备份的对象是进行第一次增量 备份后所产生的增加和修改的文件，如此类推。 没有重复的备份数据 备份时间短 恢复数据时必须按一定的顺序进行 |
| 差异备份 | 备份上一次的完全备份后发生变化的所有文件。 差异备份是指在一次全备份后到进行差异备份的这段时间内 对那些增加或者修改文件的备份。在进行恢复时，我们只需对第一次全量备份和最后一次差异备份进行恢复。 |
这里一般我们只会使用全量备份加差异备份
常用备份工具
mysqldump
- -u:指定用户
- -h指定主机
- -p指定密码
数据库全量备份：
[root@localhost ~]# mysqldump -uroot -p123321123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
恢复数据使用:
[root@localhost ~]# mysql -uroot -p123321123 < all.sql
全量备份到文件all.sql
//进入数据库
[root@localhost ~]# mysql -uroot -p123321123
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.31 MySQL Community Server (GPL)
Copyright (c) 2000, 2020, 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> create database chouyu;
Query OK, 1 row affected (0.00 sec)
mysql> use chouyu;
Database changed
mysql> create table student(id int not null primary key auto_increment,name varchar(50),age tinyint);
Query OK, 0 rows affected (0.36 sec)
mysql> insert student(name,age)values('tom',12),('wangwu',25),('liliu',19);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> quit
Bye
//全量备份数据库到all.sql
[root@localhost ~]# mysqldump -uroot -p123321123 --all-databases > all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all.sql anaconda-ks.cfg mysql-5.7.31-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# file all.sql
all.sql: UTF-8 Unicode text, with very long lines
[root@localhost ~]# mysql -uroot -p123321123 -e 'drop database chouyu;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123321123 < all.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -p123321123 -e 'select * from chouyu.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
//查看恢复的数据
[root@localhost ~]# mysql -uroot -p123321123 -e 'select * from chouyu.student;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+--------+------+
| id | name | age |
+----+--------+------+
| 1 | tom | 12 |
| 2 | wangwu | 25 |
| 3 | liliu | 19 |
+----+--------+------+
[root@localhost ~]#
在chouyu数据库中创建表student1,student2，向student1插入数据，备份表student1，备份文件为table_student1.sql
语法：
mysqldump [OPTIONS] database [tables ...] mysqldump [OPTIONS] --all-databases [OPTIONS] mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
常用的OPTIONS：
-uUSERNAME //指定数据库用户名
-hHOST //指定服务器主机，请使用ip地址
-pPASSWORD //指定数据库用户的密码
-P# //指定数据库监听的端口，这里的#需用实际的端口号代替，如-P3307
mysql> create database chouyu;
Query OK, 1 row affected (0.00 sec)
mysql> use chouyu;
Database changed
mysql> create table student1(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
Query OK, 0 rows affected (0.36 sec)
mysql> insert student1(name,age) values('zhangsan',18),('tom',15),('lisi',19),('xiaoming',12);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> create table student2(id int not null primary key auto_increment,name varchar(20) not null,age tinyint);
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_chouyu |
+------------------+
| student1 |
| student2 |
+------------------+
2 rows in set (0.00 sec)
mysql> select * from student1;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 1 | zhangsan | 18 |
| 2 | tom | 15 |
| 3 | lisi | 19 |
| 4 | xiaoming | 12 |
+----+----------+------+
4 rows in set (0.00 sec)
//备份chouyu中所有的表到文件table_school.sql
[root@localhost ~]# mysqldump -uroot chouyu student1 > table_student1.sql
//创建库information，在里面创建表teacher并插入数据，给数据库chouyu和information全备份到文件database.sql
mysql> create database information;
Query OK, 1 row affected (0.00 sec)
mysql> use information;
Database changed
mysql> create table teacher(id int not null primary key auto_increment,name varchar(100)not null,age tinyint(4));
Query OK, 0 rows affected (0.37 sec)
mysql> insert teacher(name,age) values('chouyu',31),('xixi',43),('azhe',28);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> exit
Bye
[root@localhost ~]# mysqldump -uroot --databases chouyu information > database.sql
删除库school，使用备份文件table_student1.sql恢复
mysql> drop database chouyu; Query OK, 2 rows affected (0.24 sec) mysql> create database chouyu; Query OK, 1 row affected (0.00 sec) mysql> source table_student1.sql; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec)
....
mysql> show tables;
+------------------+
| Tables_in_chouyu |
+------------------+
| student1 |
+------------------+
1 row in set (0.00 sec)
删除库chouyu和information，使用备份文件database.sql恢复两个库的数据。
mysql> drop database chouyu;
Query OK, 1 row affected (0.35 sec)
mysql> drop database information;
Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# mysql -uroot -p123 < database.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
差异备份
先完全备份现在状态的所有数据库，往表student2里插入数据，再更改其中一项数据，再删除库chouyu
[root@localhost ~]# vi /etc/my.cnf
....
log-bin=mysql_bin //开启二进制日志功能
server-id=1 //设置服务器标识符
[root@localhost ~]# service mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. SUCCESS!
[root@localhost ~]# rm -rf *.sql
[root@localhost ~]# mysqldump -uroot -p123 --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-202012310225.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
mysql> use chouyu;
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> insert student2(name,age) values('tom',18),('jerry',20),('lisi',21);
Query OK, 3 rows affected (0.09 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> update student2 set age=25 where id=3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> drop database chouyu;
Query OK, 2 rows affected (0.00 sec)
我们要恢复先前库chouyu里的所有数据（包括之前新增的数据），先找到日志存放路径，之后立刻刷新日志(刷新日志方便我们寻找要恢复的时间点)，然后恢复完全备份，最后查找删除库命令位置并恢复差异备份。
[root@localhost ~]# ls /opt/data
auto.cnf ib_buffer_pool information mysql.pid server-key.pem
ca-key.pem ibdata1 localhost.localdomain.err performance_schema sys
ca.pem ib_logfile0 mysql private_key.pem
client-cert.pem ib_logfile1 mysql_bin.000003 public_key.pem
client-key.pem ibtmp1 mysql_bin.index server-cert.pem
[root@localhost ~]# mysqladmin -uroot flush-logs
[root@localhost ~]# ls /opt/data
auto.cnf ib_buffer_pool information mysql_bin.index server-cert.pem
ca-key.pem ibdata1 localhost.localdomain.err mysql.pid server-key.pem
ca.pem ib_logfile0 mysql performance_schema sys
client-cert.pem ib_logfile1 mysql_bin.000003 private_key.pem
client-key.pem ibtmp1 mysql_bin.000004 public_key.pem
[root@localhost ~]# mysql -uroot -p123 < all-202012310225.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql
检查误删数据库的位置在什么地方
mysql> show binlog events in 'mysql_bin.000003'\G
***************************
14. row ***************************
Log_name: mysql_bin.000003
Pos: 799
Event_type: Query
Server_id: 1
End_log_pos: 897
Info: drop database chouyu
//使用mysqlbinlog恢复差异备份
[root@localhost ~]# mysqlbinlog --stop-position=799 /opt/data/mysql_bin.000003|mysql -uroot -p123
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]#
mysql> use chouyu;
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 student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 18 |
| 2 | jerry | 20 |
| 3 | lisi | 25 |
+----+-------+------+
3 rows in set (0.00 sec)
mysql>
增量备份
下载Xtrabackup包，准备环境。
XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
使用innobackupex命令全量备份
wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/8/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
[root@localhost ~]# tar xf Percona-XtraBackup-2.4.21-r5988af5-el8-x86_64-bundle.tar
yum -y install percona*
innobackupex --user=root --password=123 --host=192.168.122.134 /backups/ 进行全量备份
#语法解释说明：
#--user=root 指定备份用户
#--password=123456 指定备份用户密码
#--host 指定主机
#/backups 指定备份目录
[root@localhost backups]# ll
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:10 2020-12-30_14-10-47
总结全库备份与恢复三步曲：
a. innobackupex全量备份，并指定备份目录路径；
b. 在恢复前，需要使用--apply-log参数先进行合并数据文件，确保数据的一致性要求；
c. 恢复时，直接使用--copy-back参数进行恢复，需要注意的是，在my.cnf中要指定数据文件目录的路径。
删除数据库所有文件再恢复
[root@localhost backups]# /etc/init.d/mysqld stop //停止服务
Shutting down MySQL... SUCCESS!
innobackupex --apply-log /backups/2020-12-29_21-41-12/ //合并数据，使数据文件处于一致性的状态
[root@localhost backups]# innobackupex --copy-back /backups/2020-12-30_14-10-47/
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1
xtrabackup: recognized client arguments:
201230 14:16:00 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
//这里显示ok就是可以了
[root@localhost backups]# ll /opt/data/
总用量 111528
-rw-r-----. 1 mysql mysql 56 12月 30 05:53 auto.cnf
-rw-------. 1 mysql mysql 1680 12月 30 05:53 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 ca.pem
drwxr-x---. 2 mysql mysql 100 12月 30 13:31 chouyu
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 client-cert.pem
-rw-------. 1 mysql mysql 1680 12月 30 05:53 client-key.pem
-rw-r-----. 1 mysql mysql 814 12月 30 14:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 30 14:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 30 14:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 30 05:53 ib_logfile1
drwxr-x---. 2 mysql mysql 58 12月 30 13:31 information
-rw-r-----. 1 mysql mysql 31176 12月 30 14:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 12月 30 13:31 mysql
-rw-r-----. 1 mysql mysql 944 12月 30 13:30 mysql_bin.000003
-rw-r-----. 1 mysql mysql 845454 12月 30 14:14 mysql_bin.000004
-rw-r-----. 1 mysql mysql 38 12月 30 13:30 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 12月 30 05:53 performance_schema
-rw-------. 1 mysql mysql 1680 12月 30 05:53 private_key.pem
-rw-r--r--. 1 mysql mysql 452 12月 30 05:53 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 server-cert.pem
-rw-------. 1 mysql mysql 1676 12月 30 05:53 server-key.pem
drwxr-x---. 2 mysql mysql 8192 12月 30 05:53 sys
[root@localhost backups]#
//这里可以看到东西都已经恢复了
[root@localhost backups]# chown -R mysql.mysql /opt/data/ //修改属组属主
[root@localhost backups]# /etc/init.d/mysqld start //启动服务
Starting MySQL. SUCCESS!
[root@localhost backups]# mysql -uroot -p123 -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database |
+--------------------+
| information_schema |
| chouyu |
| information |
| mysql |
| performance_schema |
| sys |
+--------------------+
[root@localhost backups]#
增量备份
[root@localhost backups]# innobackupex --user=root --password=123 --host=192.168.122.134 /backups/ //全量备份
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1
xtrabackup: recognized client arguments:
201230 14:18:33 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
[root@localhost backups]# ll /backups/
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:18 2020-12-30_14-18-33
[root@localhost backups]# innobackupex --user=root --password=123 --host=192.168.122.134 --incremental /backups/ --incremental-basedir=/backups/2020-12-30_14-18-33/ //进行增量备份
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1
xtrabackup: recognized client arguments:
201230 14:20:52 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
[root@localhost backups]# ll
总用量 0
drwxr-x---. 2 root root 6 12月 30 14:18 2020-12-30_14-18-33 //全量备份数据
drwxr-x---. 2 root root 6 12月 30 14:20 2020-12-30_14-20-52 //增量备份数据
使用增量数据恢复数据
[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost ~]# rm -rf /opt/data/
//合并全备数据目录，确保数据的一致性
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-30_14-
2020-12-30_14-18-33/ 2020-12-30_14-20-52/
//将增量备份数据合并到全备数据目录当中
[root@localhost ~]# innobackupex --apply-log --redo-only /backups/2020-12-30_14-18-33/
xtrabackup: recognized server arguments:
xtrabackup: recognized client arguments:
201230 14:25:25 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
//恢复数据
[root@localhost ~]# innobackupex --copy-back /backups/2020-12-30_14-18-33/
xtrabackup: recognized server arguments: --datadir=/opt/data --log_bin=mysql_bin --server-id=1
xtrabackup: recognized client arguments:
201230 14:27:49 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
//查看恢复后的数据
[root@localhost backups]# ll /opt/data/
-rw-r-----. 1 mysql mysql 56 12月 30 05:53 auto.cnf
-rw-------. 1 mysql mysql 1680 12月 30 05:53 ca-key.pem
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 ca.pem
drwxr-x---. 2 mysql mysql 100 12月 30 13:31 chouyu
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 client-cert.pem
-rw-------. 1 mysql mysql 1680 12月 30 05:53 client-key.pem
-rw-r-----. 1 mysql mysql 814 12月 30 14:14 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 12月 30 14:14 ibdata1
-rw-r-----. 1 mysql mysql 50331648 12月 30 14:14 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 12月 30 05:53 ib_logfile1
drwxr-x---. 2 mysql mysql 58 12月 30 13:31 information
-rw-r-----. 1 mysql mysql 31176 12月 30 14:14 localhost.localdomain.err
drwxr-x---. 2 mysql mysql 4096 12月 30 13:31 mysql
-rw-r-----. 1 mysql mysql 944 12月 30 13:30 mysql_bin.000003
-rw-r-----. 1 mysql mysql 845454 12月 30 14:14 mysql_bin.000004
-rw-r-----. 1 mysql mysql 38 12月 30 13:30 mysql_bin.index
drwxr-x---. 2 mysql mysql 8192 12月 30 05:53 performance_schema
-rw-------. 1 mysql mysql 1680 12月 30 05:53 private_key.pem
-rw-r--r--. 1 mysql mysql 452 12月 30 05:53 public_key.pem
-rw-r--r--. 1 mysql mysql 1112 12月 30 05:53 server-cert.pem
-rw-------. 1 mysql mysql 1676 12月 30 05:53 server-key.pem
drwxr-x---. 2 mysql mysql 8192 12月 30 05:53 sys
//修改属主属组
[root@localhost ~]# chown -R mysql.mysql /opt/data/
//启动服务
[root@localhost ~]# /etc/init.d/mysqld start
//查看恢复后的数据
mysql> use chouyu;
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 student2;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 1 | tom | 18 |
| 2 | jerry | 20 |
| 3 | lisi | 25 |
+----+-------+------+
3 rows in set (0.00 sec)
