1.1 配置mysql主从
主库IP:192.168.8.62
从库IP:192.168.8.65
主库IP:192.168.8.62 操作
mysql -uroot -p
mysql> grant replication slave on *.* to tongbu@'192.168.8.65' identified by '123456';
mysql> show master status \G
*************************** 1. row ***************************
File: mysql-bin.000005
Position: 331
#拿到File 和Position
#锁表
flush tables with read lock; 锁表
1.2 从库IP:192.168.8.65 操作
mysql> change master to master_host='192.168.8.62',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000005',master_log_pos=331;
mysql> start slave;
mysql> show slave status \G
#内容有这两给 YES
Slave_IO_Running:Yes
Slave_SQL_Running:yes
# 在主库上运行 表锁释放命令 192.168.8.62
mysql> unlock tables
# 完成了 主从配置
1.3,配置my.cof
############## 主mysql 的my.cof ##############
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
max_connections=1000
interactive_timeout=31536000
wait_timeout=31536000
lower_case_table_names=1
symbolic-links=0
log-bin=mysql-bin
server-id = 1 # 配置主数据库的ID
replicate-ignore-db=mysql # 配置不同步的库
replicate-ignore-db=test
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
################## 从mysql 的my.cof ########################
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
character-set-server=utf8
max_connections=1000
interactive_timeout=31536000
wait_timeout=31536000
lower_case_table_names=1
symbolic-links=0
log-bin=mysql-bin
server-id = 3 # 配置从数据库的ID
replicate-ignore-db=mysql # 配置不同步的库
replicate-ignore-db=test
replicate-ignore-db=performance_schema
replicate-ignore-db=information_schema
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
#######################################################
2. mysql切换灾备服务器
2.1 登陆mysql 在salve执行:
mysql> stop slave;
mysql> reset master;
##2.2删除mysql目录下
relay-log.info
master.info
rm -f /var/lib/mysql/*.info
##2.3 如果my.cof配置上存在log-slave-updates read_only 等一定注释掉它 ##2.4重启mysql
service mysql restart
##redis 切换 ##2.5 登陆redis
redis-cli
AUTH uhfugqpowzffhDWQc20z-pUadl&2d1@Dkzqlo/adfwQQlo+AdjcdUyakLjFM$YUaxOasGDBeyTail5Is9Ush+0aXPiU
#密码命令
slaveof NO ONE
#停止redis 主从
修改redis.conf 注释掉它 #slaveof 192.168.1.55 #master IP
##2.6 启动tomcat 和zk
#3.1mysql binlog恢复数据 编辑再还原数据
#导出 2019-01-14 15:12分到2019-01-14 15:16:00 的数据
mysqlbinlog -uroot -p'123456' --start-datetime="2019-01-14 15:12:00" --stop-datetime="2019-01-14 15:16:00" /var/lib/mysql/mysql-bin.000003 > mysql_restore.sql
mysql -uroot -p < /tmp/mysql_restore.sql
#直接还原数据
mysqlbinlog -uroot -p --start-datetime="2019-01-14 15:12:00" --stop-datetime="2019-01-14 15:16:00" /var/lib/mysql/mysql-bin.000003 | mysql -u root -p'123456'
#mysql 备份方式恢复数据 ###全量备份数据脚本
MYUSER=script_user
MYSQL_PWD=123123
mysql -e "show databases;" -uroot -p'123456' | egrep -vi "Data|_schema|mysql|test|job|order"| \
xargs mysqldump -u$MYUSER -p$MYSQL_PWD --databases |gzip> /www/backup/mysql_$(date +%F)_dump.all.sql.gz
2)还原数据
gzip -d mysql_dump.all.sql.gz
mysql -u$MYUSER -p$MYSQL_PWD < mysql_dump.all.sql
##redis 切换
登陆redis redis-cli AUTH uhfugqpowzffhDWQc20z-pUadl&2d1@Dkzqlo/adfwQQlo+AdjcdUyakLjFM$YUaxOasGDBeyTail5Is9Ush+0aXPiU
在salve执行 slaveof 192.168.8.62 6379
修改redis.conf 注释掉它 #slaveof 192.168.8.62 #master IP
附 mysql 操作
###锁表 flush tables with read lock; ###表锁释放 unlock tables; ###刷新 logs flush logs
刷新权限
flush privileges; ###运行查看状态 show master status \G