One article to understand │ backup recovery, partition table, master-slave replication, read-write separation in mysql

🎈 mysql backup and restore

• Create backup administrator
• Create a backup administrator and grant the administrator the appropriate permissions
• Permissions required for backup: 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';

• Full recovery of sql file recovery
• Just import the backup sql file directly into the database

 mysql -uroot -p 数据库 < sql文件

• Point-in-time recovery of sql file recovery

 # 首先进行一次基于最近一次的全量备份的文件进行一次全量恢复
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 logical backup
• Specify multiple tables in the database for backup: mysqldump [OPTIONS] database [table]
• Specify multiple database backups: mysqldump [OPTIONS] database [OPTIONS] DB1 DB2
• Backup the entire database instance: mysqldump [OPTIONS] --all-database [OPTIONS]
• Parameter --single-transaction : enable transaction to ensure the integrity of backup data, innodb specific
• Parameter -l或--lock-tables : Lock all tables in the backup database in turn to ensure the integrity of the backup data
• Parameter -x或--lock-all-table : Lock all data tables of the entire database instance at one time to ensure data integrity
• 参数--master-data=[1/2] ： CHANGE MASTER TO sql注释； 1注释， 2 Write as a comment, default 1
• Parameters -R或--routines : Backup database stored procedure
• Parameters --triggers : Backup database trigger
• Parameter -E或--events : Backup database scheduling event
• Parameters --hex-blob : 16 binary export bit columns and blob
• Parameters --tab=path : Two files are generated for each database under the specified path: 数据结构 , 数据
• Parameter -w或--where=过滤条件 : filter the specified data, only support single table export
• 注意： --single-transaction ， --lock-tables参数是互斥的，所以，如果同一个数据库下同时innodb表myisam表只You can use --lock-tables to ensure the consistency of backup data, but --lock-tables can only guarantee the integrity of a backup database, not the integrity of the entire instance backup

 mysqldump -ubackup -p --master-data=2 --single-transaction --routines --triggers --events 数据库 > 备份文件.sql

• XtraBackup backup
• Custom download address: Percona repository
• Command download: yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm
• Test if repository installation was successful Command: 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 partition table

• Check whether the partition table is supported: show plugins;
• Delete partition command: ALTER TABLE 表名 DROP PARTITION 分表名称;

• Partition classification: 范围分区 , 哈希分区 , 时间分区

   # 范围分区
  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 master-slave replication read-write separation

• Authorize remote access to the mysql database
• Create a new database administrator, authorize and enable remote access

 -- 建议新建一个备份和主从复制的数据库管理员
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;

• configure mysql master server
• You need to create a data (backup database) with the same name in both the master server and slave server
• Open binlog logs and settings in the master server. To synchronize the database between master and slave, use vim to open the /etc/my.cnf file and modify the configuration as follows
• After the configuration is complete, you need to restart the service: service mysqld restart;

 #mysql的bin-log日志配置选项,假设做读写（主从），这个选项在从服务器必须关闭
log_bin = binlog
#端口信息，其实可以不写
port = 3306
#主服务器的id,这id不一定设为1，只要主从不一样就行
server-id = 1
#要做同步的数据库名字，可以是多个数据库，之间用分号分割。
binlog_do_db = test

• Check configuration
• Log mysql check binlog whether the log related parameters are correct

 show master status;
show variables like 'log_bin';

• Configure mysql slave server
• The master server has been successfully configured, then switch to the slave server to start the configuration
• Open the binlog log and settings in the slave server. If the master-slave synchronization database occurs, use vim to open the /etc/my.cnf file, and modify the configuration as follows

 #从服务器的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

• Check whether the master-slave replication configuration is successful

 show slave status\G
 
-- 如果结果包含如下参数，则证明主从已经配置成功
Slave_IO_Running: Yes
Slave_SQL_Running: Yes