前言

主服务器:192.168.2.204
从服务器:192.168.2.201
并确认mysql可以正常运行

主数据库配置自定义编号，以及开启binlog

/etc/my.cnf

[mysqld] server-id = 1log_bin = mysql-bin

在主库上为从库添加一同步帐号

  1. GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
  2. FLUSH PRIVILEGES;

从数据库配置自定义编号，不同于主库

/etc/my.cnf

[mysqld] server-id =2

read_only=1只读模式，不会影响slave同步复制的功能

read_only=1限定的是普通用户进行数据修改的操作，但不会限定具有super权限的用户的数据修改操作

但是如果设置了”super_read_only=on“， 则就会限定具有super权限的用户的数据修改操作了

read_only=1

备份

主库安装xtrabackup

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release enable-only tools release
percona-release enable-only tools
yum install -y percona-xtrabackup-24

开始备份

#模板
innobackupex --defaults-file=/etc/my.cnf  -H 主库ip --user=用户名 --password=密码 --no-timestamp 备份路径
#案例
innobackupex  --defaults-file=/etc/my.cnf -H 192.168.2.204  --user=dev --password=dev /data/backup/

完成后会打印一个主库的备份节点，关键点加粗了

201113 11:52:54 Executing UNLOCK TABLES201113 11:52:54 All tables unlocked201113 11:52:54 [00] Copying ib_buffer_pool to /data/backup/ib_buffer_pool201113 11:52:54 [00] …done201113 11:52:54 Backup created in directory ‘/data/backup/‘MySQL binlog position: filename ‘mysql-bin.000001’, position ‘6385747’201113 11:52:54 [00] Writing /data/backup/backup-my.cnf201113 11:52:54 [00] …done201113 11:52:54 [00] Writing /data/backup/xtrabackup_info201113 11:52:54 [00] …donextrabackup: Transaction log of lsn (2534758) to (2534758) was copied.201113 11:52:54 completed OK!

主服务器上备份主库的数据并传输到从服务器参考（未实施，本文档中可以忽略）

参考 ：https://my.oschina.net/adailinux/blog/2108501

#备份到远程服务器并压缩，看着有问题
#innobackupex --defaults-file=/etc/my.cnf --no-lock --user=dev  --password=dev --stream=tar ./ | sshpass -p pass\!234 root@192.168.2.201 \ "cat - > /data/backup/database/`date +%Y%m%d`/`date +%H-%M`-backup.tar"

备份从服务器的数据

service mysqld stop
mv /data/mysql/data_bak /data/mysql/data`date +%Y%m%d`_bak

将主服务器备份的数据传输到从服务器上

scp -r `date +%Y-%m-%d_%H_`* root@192.168.2.201:/data/mysql/data

从库还原主库的数据

#修改从库mysql数据权限
chown -R mysql:mysql /data/mysql/data
innobackupex --defaults-file=/etc/my.cnf --user=dev --password=dev  --apply-log /data/mysql/data

打印日志

InnoDB: Starting crash recovery.InnoDB: Removed temporary tablespace data file: “ibtmp1”InnoDB: Creating shared tablespace for temporary tablesInnoDB: Setting file ‘./ibtmp1’ size to 12 MB. Physically writing the file full; Please wait …InnoDB: File ‘./ibtmp1’ size is now 12 MB.InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.InnoDB: 32 non-redo rollback segment(s) are active.InnoDB: 5.7.32 started; log sequence number 2534933xtrabackup: starting shutdown with innodb_fast_shutdown = 1InnoDB: FTS optimize thread exiting.InnoDB: Starting shutdown…InnoDB: Shutdown completed; log sequence number 2534952201113 12:20:58 completed OK!

启动从库

service mysqld restart

配置从库的Slave

# log_file和postion就是备份时候的bin日志和postion节点位置
change master to master_host='192.168.2.204',master_user='slave',master_password='slave',
master_log_file='mysql-bin.000001',master_log_pos=6385747;
start slave;
#查看主从状态确认两个yes
show slave status \G;
#创建只读用户，提供给开发同学的吧！
grant select,show view on *.* to readonly@"%" identified by "readonly";
flush privleges;

备注

跳过一个报错

stop slave;
set global sql_slave_skip_counter=1;
start slave;

查看数据库是否只读

show global variables like "%read_only%";