🎈 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 分表名称; - 分区分类：
范围分区、哈希分区、时间分区
# 范围分区 CREATETABLE `表名`(-- 数据字段)engine=INNODB PARTITION BY RANGE(`字段名称`)( PARTITION 分表名称 VALUES LESS THAN(范围), PARTITION 分表名称 VALUES LESS THAN(范围));# 哈希分区 createtable `表名`(-- 数据字段)engine=INNODB PARTITION BY HASH(UNIX TIMESTAMP(`字段名称`)) PATITIONS 4;# 时间分区 createtable `表名`(-- 数据字段)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_CREATETABLE `归档表表名`()engine=INNODB -- 2.进行数据交换 p0为分区名ALTERTABLE `原数据表表名` exchange PARTITION p0 WITH TABLE `归档表表名`;-- 3.删除分区，避免对数据的再次写入ALTERTABLE `原数据表表名` DROP PARTITION p0;-- 4.将归档表引擎设置为 archive 在检表语句中mysql引擎必须和原数据表引擎一致，否则无法进行数据交换ALTERTABLE `归档表表名` 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
