✨ 目录
- mysql 的备份和恢复
- mysql 的分区分表
- mysql 的主从复制读写分离
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文件
mysql -uroot -p 数据库 < sql文件CHANGE MASTER TO MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=154;cd /var/lib/mysqlmysqlbinlog mysqlbinlog mysqlbinlog 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
- 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
yum install https: yum install percona-xtrabackup-24
innobackupex innobackupex innobackupex innobackupex chmod -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 = '表名';CREATE TABLE `归档表表名`( )engine=INNODBALTER TABLE `原数据表表名` exchange PARTITION p0 WITH TABLE `归档表表名`;ALTER TABLE `原数据表表名` DROP PARTITION p0;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;
log_bin = binlogport = 3306server-id = 1binlog_do_db = test
- 检查配置
- 登录
mysql
查看 binlog
日志相关参数是否正确
show master status;show variables like 'log_bin';
- 配置mysql从服务器
- 主服务器已经配置成功,这时要切换到从服务器开始配置
- 在从服务器中开启
binlog
日志和设置要发生主从同步数据库,使用 vim
打开 /etc/my.cnf
文件,修改配置如下
server-id = 2master-host = 192.168.56.2master-user = backupmaster-password = 123456master-port = 3306master-connect-retry = 20replicate-do-db = test
show slave status\G Slave_IO_Running: YesSlave_SQL_Running: Yes