mysql 主从服务创建、查看和恢复

• 在master创建slave的访问用户和权限

GRANT replication slave  ON *.* TO 'repl'@'%' IDENTIFIED BY '${mysql_account}'

• slave链接到master

change master to
master_host='mysql-master',
master_user='repl',
master_password='password',
master_port=3306;
master_log_file='mysql-bin.000001’;
//master_log_pos=1;
start slave;

• 备份

mysqldump -hmysql-master -root -p${password}  ${database_name} > backup.sql
mysql -h127.0.0.1 -uroot -p${password} -e "create database  ${database_name};"
mysql -h127.0.0.1 -uroot -p${password}  ${database_name}<backup.sql

• 显示master 和 slave 状态

show master status;
show slave status;

• 查看bin log 常用命令

show master status;
show slave status;
show binlog events in 'mysql-bin.000001’;

• 查看

mysqlbinlog --no-defaults --database=${database_name}  --base64-output=decode-rows -v --start-datetime='2019-04-11 00:00:00' --stop-datetime='2019-04-11 15:00:00'  mysql-bin.000001 | more

• 恢复 （按日志记录恢复）

mysqlbinlog --start-position=1257 --stop-position=2520 mysql-bin.000001 | mysql -uroot -p${password} -v

• 使用新的bin-log文件

flush logs