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/mysqlmysqlbinlog --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数据门路的权限为777chmod -R 777 /var/lib/mysql

mysql 的分辨别表

  • 查看是否反对分辨别表：show plugins;
  • 删除分区命令：ALTER TABLE 表名 DROP PARTITION 分表名称;

  • 分区分类：范畴分区、哈希分区、工夫分区

    # 范畴分区CREATE TABLE `表名`(  -- 数据字段)engine=INNODBPARTITION BY RANGE(`字段名称`) (  PARTITION 分表名称 VALUES LESS THAN(范畴),  PARTITION 分表名称 VALUES LESS THAN(范畴));# 哈希分区create table `表名`(  -- 数据字段)engine=INNODBPARTITION BY HASH(UNIX TIMESTAMP(`字段名称`)) PATITIONS 4;# 工夫分区create table `表名`(  -- 数据字段)engine=INNODBPARTITION 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_rowsFROM  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: YesSlave_SQL_Running: Yes