目录
MySQL 数据物理备份与完全备份
备份概述
-
数据丢失或损坏，可以使用备份数据恢复
数据备份方式
-
物理备份
—— 冷备：cp、tar、.. ..
-
逻辑备份
—— mysqldump? ? //备份命令
—— mysql? ? ? ? ? ?? //恢复命令
物理备份及恢复
-
备份操作
—— cp -r /var/lib/mysql 备份目录/mysql.bak
—— tar -zcvf /root/mysql.tar.gz /var/lib/mysql/*
[root@DB ~]# ls /var/lib/mysql? ? ? ? //查看数据库目录
[root@DB ~]# cp -r /var/lib/mysql /root/mysql.bak? ? ? //拷贝数据库目录
[root@DB ~]# ls /root/mysql.bak? ? ?? //查看数据库目录备份
[root@DB ~]# tar -zcf /root/mysql.tar.gz /var/lib/mysql/*? ? ? ? //压缩数据库目录进行备份
[root@DB ~]# ls /root? ? ? ? //查看数据库目录备份的压缩包
-
恢复操作
—— cp -r 备份目录/mysql.bak /var/lib/mysql
—— tar -zxvf /root/mysql.tar.gz -C /var/lib/mysql
—— chown -R mysql:mysql /var/lib/mysql
主机DB的IP地址为：192.168.233.130
主机DB2的IP地址为：192.168.233.150
主机DB进行数据库的数据备份，将主机DB的数据库的数据同步到主机DB2上。
主机DB：
[root@DB ~]# scp -r /root/mysql.bak root@192.168.233.150:/opt/? ? ? ? //将主机192.168.233.130的数据库目录备份传到主机192.168.233.150上
主机DB2：
1、关闭数据库服务
[root@DB2 ~]# netstat -lntup | grep 3306? ? ?? //查看主机DB2数据库服务是否运行
tcp6? ? ?? 0? ? ? 0 :::3306? ? ? ? ? ? ? ?? :::*? ? ? ? ? ? ? ? ? ? LISTEN? ? ? 1939/mysqld? ? ? ??
[root@DB2 ~]# systemctl stop mysqld? ? ? ? //关闭主机DB2的数据库服务
2、拷贝备份的数据库目录
[root@DB2 ~]# rm -rf /var/lib/mysql? ? ? ? ? //删除主机DB2的数据库目录
[root@DB2 ~]# cp -r /opt/mysql.bak/ /var/lib/mysql? ? ? ? //将主机DB的数据库备份拷贝到主机DB2的数据库目录下
[root@DB2 ~]# ls? -l /var/lib/mysql? ? ? ?? //查看主机DB2的数据库目录，发现和主机DB的数据库目录一样
//注：如图所示，由于拷贝操作是由root用户执行的，所以拷贝过来的文件夹的所属者和所属组都是root，我们应该把这些文件夹的所属者和所属组改成mysql服务进程的所有者，这样才能对这些文件有读写执行权限。
3、修改拷贝的数据库目录的权限
[root@DB2 ~]# chown -R mysql:mysql /var/lib/mysql? ? ?? //通过递归的方式，将目录/var/lib/mysql下的文件的所属者和所属组修改为mysql
[root@DB2 ~]# ls -ld /var/lib/mysql? ? ?? //目录本身的所属者和所属组的变为mysql
drwxr-x--x. 8 mysql mysql 4096 Dec 31 16:34 /var/lib/mysql
[root@DB2 ~]# ls -l /var/lib/mysql
4、关闭SELinux
[root@DB2 ~]# systemctl start mysqld? ? ? ?? //启动mysql服务，出现错误
[root@DB2 ~]# journalctl -xe? ? ? ? //查看错误详细信息，找出错误
//如图所示，根据错误信息提示，mysql服务启动失败可能与SELinux有关
[root@DB2 ~]# getenforce? ? ? ? //我们查看SELinux，发现SELinux没有关
Enforcing
[root@DB2 ~]# setenforce 0? ? ? ?? //关闭SELinux
[root@DB2 ~]# getenforce
Permissive
5、重启数据库服务进行验证
[root@DB2 ~]# systemctl start mysqld? ? ?? //运行正常，此时启动的mysql服务读取的就是当前数据库目录下的数据
[root@DB2 ~]# netstat -lntup | grep 3306? ? ?? //查看数据库监听端口，监听正常说明服务启动cheng'g
tcp6? ? ?? 0? ? ? 0 :::3306? ? ? ? ? ? ? ?? :::*? ? ? ? ? ? ? ? ? ? LISTEN? ? ? 12316/mysqld
[root@DB2 ~]# mysql -uroot -p123456? ?? //启动mysql服务器，此时加载的是主机DB拷贝过来的授权库，那么主机DB2管理员信息就记录在主机DB的数据库中mysql库中的user表里，所以此时密码应该输入的是主机DB管理员的密码
mysql> show databases;? ? ?? //查看所有库，发现与主机DB的数据一样
逻辑备份
-
使用软件自带的备份程序（备份命令）对数据做备份，在执行备份命令时，会根据已有的数据生成对应的命令，把命令存放到指定的备份文件里。恢复时，执行备份文件里的命令恢复数据。
数据备份策略
-
完全备份
—— 备份所有数据
-
增量备份
—— 备份上次备份后，所有新产生的数据
-
差异备份
—— 备份完全备份后，所有新产生的数据
完全备份及恢复
-
完全备份
—— mysqldump -uroot -p密码 库名 > 目录/xxx.sql
-
完全恢复
—— mysql -uroot -p密码 [库名] < 目录/xxx.sql
-
备份时库名表示方式
—— --all-databases 或 -A? ? ? ? //所有库
—— 数据库名? ? ? ? ? ? ? ? ? ? ? ? ? ? //单个库? ?? db3
—— 数据库名 表名? ? ? ? ? ? ? ? ? ? //单张表? ?? db3 user
—— -B 数据库1 数据库2? ? ? ? ?? //多个库? ? ? -B db1 db2 db3
-
注意事项
—— 无论备份还是恢复，都要验证用户权限！！！
-
完全备份示例
在主机192.168.233.130进行备份
-
-
备份所有库
-
[root@DB ~]# mysqldump -uroot -p123456 --all-databases > /mybak/alldb.sql? ? ? ?? //备份数据库所有的库。[Warning]警告，密码在命令行上显示不安全，不是报错
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@DB ~]# ls /mybak/
alldb.sql
[root@DB ~]# vim /mybak/alldb.sql? ? ?? //查看备份文件内容
//如图所示，备份的文件里面的内容就是创建数据库时的sql语句，当恢复备份的时候，其实就是在新的数据库服务器上在执行一遍sql语句。
-
-
备份单个库
-
[root@DB ~]# mysqldump -uroot -p123456 db3 > /mybak/db3.sql? ? ? ? //备份db3库
-
-
备份单张表
-
[root@DB ~]# mysqldump -uroot -p123456 db3 user > /mybak/db3_user.sql? ? ? ? //备份db3库下的user表
-
-
备份多个库
-
[root@DB ~]# mysqldump -uroot -p123456 -B db2 db3? > /mybak/twodb.sql? ? ?? //备份db2库和db3库
[root@DB ~]# ls /mybak/? ? ? //查看备份文件
alldb.sql? db3.sql? db3_user.sql? twodb.sql
-
完全恢复示例
在主机192.168.233.150上进行恢复
将备份数据拷贝给目标主机，将主机192.168.233.130的备份数据拷贝到主机192.168.233.150上，即谁要备份数据就把备份文件拷贝到哪个主机中。
-
-
完全恢复单个库
-
主机192.168.159.130：
[root@DB ~]# scp /mybak/db3.sql root@192.168.233.150:/root/
主机192.168.233.150：
[root@DB2 ~]# ls /root/db3.sql
/root/db3.sql
[root@DB2 ~]# mysql -uroot -p123456
mysql> show databases;
mysql> drop database db2;? ? ? ? //删除上次的备份数据
mysql> drop database db3;
mysql> drop database db4;
mysql> show databases;? ? ?? //将数据库恢复初始状态
mysql> exit
[root@DB2 ~]# mysql -uroot -p123456 db3 < /root/db3.sql? ? ? //将备份数据恢复到数据库中
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1049 (42000): Unknown database 'db3'
//出现错误，错误原因：备份文件里面内容其实是sql语句，我们在备份的时候其实就是将sql语句在执行一遍，但在备份单个库的时候，备份文件中的sql语句是没有create建库这条命令的，所以我们需要先创建好要备份的库，否则就会报错说没有这个库。
[root@DB2 ~]# mysql -uroot -p123456
mysql> create database db3;? ? ? ? //创建库db3
mysql> system mysql -uroot -p123456 db3 < /root/db3.sql? ? ?? //备份库db3
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> use db3;
mysql> show tables;? ?? //db3库下的表已被恢复，备份成功
-
-
完全恢复多个库
-
主机192.168.233.130：
[root@DB ~]# scp /mybak/twodb.sql root@192.168.233.150:/root/
主机192.168.233.150：
[root@DB2 ~]# ls /root/twodb.sql
/root/twodb.sql
mysql> show databases;
mysql> drop database db3;? ? ? ? //删除上次实验的备份数据
mysql> show databases;? ? ? ? //将数据库恢复到初始状态
mysql> system mysql -uroot -p123456 < /root/twodb.sql //恢复备份数据
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases; //查看已有的库，看见db2库和db3库，数据恢复成功
//注：恢复全部库和多个库时，不需要先创建库，直接使用命令恢复就行了，因为在备份文件中，有create创建库的命令。但是在恢复单个库和单张表时，需要先创建库，如果直接恢复的话，会出现找不到库的错误，因为在备份文件中，没有create创建库的命令。
注：在使用mysql -uroot -p密码 [库名] < 目录/xxx.sql命令时，如果恢复的是单个库和单张表，要指定库名，因为备份文件里没有create创建该库，也没有use切换到库的命令。如果恢复全部库和多个库时，不需要指定库名，因为备份文件里有create创建该库，也有use切换到库的命令，即mysql -uroot -p密码 < 目录/xxx.sql。