共计 4375 个字符,预计需要花费 11 分钟才能阅读完成。
🎈 mysql 的备份和复原
- 创立备份管理员
- 创立备份管理员,并授予管理员相应的权限
- 备份所需权限:
select,reload,lock tables,replication client,show view,event,process
# 创立管理员
create user 'backup'@'localhost' identified by '123456';
# 给管理员受权
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
- sql 文件复原之全量复原
- 只有将备份的
sql
文件间接导入数据库即可
mysql -uroot -p 数据库 < sql 文件
- sql 文件复原之基于工夫点的复原
# 首先进行一次基于最近一次的全量备份的文件进行一次全量复原
mysql -uroot -p 数据库 < sql 文件
# 而后查看备份的 sql 文件的 CHANGE MASTER 值,基于该值进行二进制日志的还原
CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;
# 查看二进制日志, 依据工夫点找到误操作前一段时间的二进制日志
cd /var/lib/mysql
mysqlbinlog --base64-output=decode-rows -vv --start-position=154 --database= 数据库名 binlog.000008 | grep -B3 DELETE | more
# 记录最早删除记录的节点值,执行日志导出
mysqlbinlog --start-position= 开始节点 --stop-position= 完结节点 --database= 数据库 二进制日志名 > 导出的 sql 文件名
mysqlbinlog --start-position=154 --stop-position=26158 --database=laravel binlog.000007 > laravel.sql
# 对导出的 sql 文件进行全量的还原
mysql -uroot -p 数据库 < sql 文件
- mysqldump 逻辑备份
- 指定数据库多个表进行备份:
mysqldump [OPTIONS] database [table]
- 指定多个数据库备份:
mysqldump [OPTIONS] database [OPTIONS] DB1 DB2
- 整个数据库实例进行备份:
mysqldump [OPTIONS] --all-database [OPTIONS]
- 参数
--single-transaction
:开启事务保障备份数据的完整性,innodb
特有 - 参数
- l 或 --lock-tables
:顺次锁定备份数据库所有表保障备份数据的完整性 - 参数
- x 或 --lock-all-table
:一次性锁定整个数据库实例所有数据表保障数据完整性 - 参数
--master-data=[1/2]
:CHANGE MASTER TO
语句会被写成一个sql
正文;1
不会被写成正文,2
写成正文,默认1
- 参数
- R 或 --routines
:备份数据库存储过程 - 参数
--triggers
:备份数据库触发器 - 参数
- E 或 --events
:备份数据库调度事件 - 参数
--hex-blob
:16
进制导出bit
列和blob
列数据 防止数据文本不可见 - 参数
--tab=path
:指定门路下为每个数据库生成两个文件:数据结构
、数据
- 参数
- w 或 --where= 过滤条件
:过滤指定数据, 仅反对单表导出 - 留神:
--single-transaction
,--lock-tables
参数是互斥的,所以,如果同一个数据库下同时存在innodb
表和myisam
表只能应用--lock-tables
来保障备份数据的一致性,然而--lock-tables
只能保障某一备份数据库的完整性,不能保障整个实例备份的完整性
mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 数据库 > 备份文件.sql
- XtraBackup 备份
- 自定义下载地址:Percona 存储库
- 命令下载:
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
- 测试储存库装置是否胜利命令:
yum list | grep percona
# 下载 libev 软件包
yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-6.noarch.rpm
# 装置 libev 软件包
yum install percona-xtrabackup-24
# 全量备份
innobackupex --user= 管理员账号 --password= 明码 --parallel=2 备份门路
innobackupex --user=backup --password=Gzjunyu19970925. --parallel=2 /home/db_backup/
# 全量复原,倡议复原前进行 mysql 服务,且清空 mysql 数据文件
innobackupex --datadir=mysql 数据门路 --copy-back 备份门路
innobackupex --datadir=/var/lib/mysql --copy-back /home/db_backup/2018-04-21_10-44-22/
# 批改 mysql 数据门路的权限为 777
chmod -R 777 /var/lib/mysql
🎈 mysql 的分辨别表
- 查看是否反对分辨别表:
show plugins;
- 删除分区命令:
ALTER TABLE 表名 DROP PARTITION 分表名称;
-
分区分类:
范畴分区
、哈希分区
、工夫分区
# 范畴分区 CREATE TABLE ` 表名 `(-- 数据字段)engine=INNODB PARTITION BY RANGE(` 字段名称 `) (PARTITION 分表名称 VALUES LESS THAN(范畴), PARTITION 分表名称 VALUES LESS THAN(范畴) ); # 哈希分区 create table ` 表名 `(-- 数据字段)engine=INNODB PARTITION BY HASH(UNIX TIMESTAMP(` 字段名称 `)) PATITIONS 4; # 工夫分区 create table ` 表名 `(-- 数据字段)engine=INNODB PARTITION BY RANGE(YEAR(` 字段名称 `))(PARTITION p0 VALUES LESS THAN(2017), PARTITION p1 VALUES LESS THAN(2018), PARTITION p2 VALUES LESS THAN(2019) ); # 查看分区状况 SELECT table_name,partition_name,partition_description,table_rows FROM information_schema.`PARTITIONS` WHERE table_name = '表名'; # 归档分区数据,mysql 数据库版本须要大于等于 5.7 -- 分区归档操作步骤 -- 1. 新建和分区表字段统一的数据表 归档表前缀为 arch_ CREATE TABLE ` 归档表表名 `( )engine=INNODB -- 2. 进行数据交换 p0 为分区名 ALTER TABLE ` 原数据表表名 ` exchange PARTITION p0 WITH TABLE ` 归档表表名 `; -- 3. 删除分区,防止对数据的再次写入 ALTER TABLE ` 原数据表表名 ` DROP PARTITION p0; -- 4. 将归档表引擎设置为 archive 在检表语句中 mysql 引擎必须和原数据表引擎统一,否则无奈进行数据交换 ALTER TABLE ` 归档表表名 ` ENGINE=ARCHIVE;
🎈 mysql 的主从复制读写拆散
- 受权近程拜访 mysql 数据库
- 新建相干数据库管理员,受权并开启近程拜访权限
-- 倡议新建一个备份和主从复制的数据库管理员
CREATE USER 'backup'@'localhost' IDENTIFIED BY '明码';
-- 调配相干权限
grant select,reload,lock tables,replication client,show view,event,process on *.* to 'backup'@'localhost';
-- 开启近程拜访权限
GRANT ALL PRIVILEGES ON *.* TO 受权用户名 @被受权服务器的 IP IDENTIFIED BY '受权明码';
FLUSH PRIVILEGES;
- 配置 mysql 主服务器
- 须要在
master
服务器和slave
服务器都建设一个同名的数据(备份数据库) - 在主服务器中开启
binlog
日志和设置要产生主从同步数据库,应用vim
关上/etc/my.cnf
文件,批改配置如下 - 配置结束后,须要重启服务:
service mysqld restart;
#mysql 的 bin-log 日志配置选项, 假如做读写(主从),这个选项在从服务器必须敞开
log_bin = binlog
#端口信息,其实能够不写
port = 3306
#主服务器的 id, 这 id 不肯定设为 1,只有主从不一样就行
server-id = 1
#要做同步的数据库名字,能够是多个数据库,之间用分号宰割。binlog_do_db = test
- 查看配置
- 登录
mysql
查看binlog
日志相干参数是否正确
show master status;
show variables like 'log_bin';
- 配置 mysql 从服务器
- 主服务器曾经配置胜利,这时要切换到从服务器开始配置
- 在从服务器中开启
binlog
日志和设置要产生主从同步数据库,应用vim
关上/etc/my.cnf
文件,批改配置如下
# 从服务器的 id, 必须与主服务器的 id 是不同
server-id = 2
#主服务器的 ip 地址
master-host = 192.168.56.2
#grant 受权的可复制用户账号
master-user = backup
#grant 受权的可复制明码
master-password = 123456
#主服务器的 mysql 端口
master-port = 3306
#这个参数是用来设置在和主服务器连贯失落的时候,重试的工夫距离,默认是 60 秒
master-connect-retry = 20
#须要同步的主服务器数据库
replicate-do-db = test
- 检测主从复制配置是否胜利
show slave status\G
-- 如果后果蕴含如下参数,则证实主从曾经配置胜利
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
正文完