Mysql 主从复制+数据恢复

1、主服务器Master配置：

#设置数据库标识

server_id=1

#任何一个事务提交之后就立即写入到磁盘中的二进制文件

sync_binlog=1

#保持数据一致性，建议配置

innodb_flush_log_at_trx_commit=1

#开启服务器二进制日志

log-bin=mysql-bin

#设置log格式

binlog_format=MIXED

#不记录二进制数据库的数据

binlog-ignore-db=test  

2、从服务器Slave配置

#设置数据库标识

server_id = 2

#设置不同步的数据库

replicate-ignore-db=testDB

#设置不同步的数据表

replicate-ignore-table=testDB.testTable

给服务器设置授权账户

设置user账户能从10.10.0.1服务器登陆本机，且拥有所有权限

Mysql>grant all on *.* to user@10.10.0.1 indentified by ‘password’

(密码必须有引号)

user账户只有备份权限

Mysql>grant replication slave on *.* to user@10.10.0.1 indentified by ‘password’

设置其他主机能使用root登陆数据库

Mysql>GRANT ALL PRIVILEGES ON * . * TO 'root'@'%'IDENTIFIED BY ‘password’

 

使添加的账号生效：FLUSH PRIVILEGES;

master服务器查看是否开启二进制日志：show variables like '%bin';

master服务器查看二进制日志状态：show mater status；

Flush logs; 增加一个最新的bin-log日志

Truncate  tables;   清空当前表中的所有数据

Reset master；清空所有的bin-log日志

slave服务器停止同步，输入：stop slave；

slave服务器显示同步状态及设置：show slave status\G

数据库同步

注意数据库同步前，需要在Master数据库锁定当前数据库，可保持数据一致性

锁定：mysql>flushtables with read lock;

解锁：mysql>unlocktables;

开启同步：

CHANGE MASTER TO MASTER_HOST='ServerIP/FQDN',MASTER_USER='ReplClient', MASTER_PASSWORD='ClientPassword',MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=120;

输入show slave status\G

再输入：start slave；重新输入show slave status\G   ，显示如下参数

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

即同步设置成功

 注意：部分服务器可能需要开启3306端口，或关闭防火墙设置才可生效。

进入二进制日志目录，输入下面语句可查看二进制日志：

mysqlbinlog --no-defaults  mysql-bin.000001 | more

若编译安装的Mysql，选择mysql安装目录下执行Mysql/bin/mysqlbinlog 

 

数据恢复

Mysql/bin/mysqlbinlog --no-defaults  mysql-bin.000001 |mysql –u –root –p(password) （可选database）

 

Linux mysql 数据库备份

Mysqldump –uroot –p database –l –F ‘/home/backup.sql’

-l 指锁定数据库表。只能读，不能写。

-F 指flushlogs ， 可以重新生产新的日志文件。包括log-bin日志

 

数据导入：mysql –uroot –p （可选database） -v –f  < /home/backup.sql

-v查看导入的详细信息

-f 当中间遇到错误，可以skip，继续执行下面语句

 

查看bin-log日志，可以根据Position或者根据时间段恢复

例如通过Bin-log日志恢复position段100到500 的数据：

1、先查看position段中日志，确实是否需要恢复

mysqlbinlog –no-defaults mysql-bin.000002 --start-position=”100”--stop-position=”500” |more

注意position100可能不会恢复。因为执行语句在Position日志之上。

--start-position和--stop-position可选择一个执行。无需同时存在。

Btw：时间段恢复参数为：--stop-date= “2014-01-01 12:00:00” 、

--start-date = “2014-01-01 12:00:00”