linux数据库备份和恢复

一、直接用log日志备份

（注：操作前先停止网站数据再次进入，生成新的SQL语句）

1、查看所有数据库操作语句
/usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000010 > /opt/backup/aaa.sql

2、恢复（设置截止时间或截止id）
mysqlbinlog --database=test --stop-datatime="2017-12-08 15:03:00" /usr/local/mysql/var/mysql-bin.000011 | mysql -u root -p
二、做主从备份
http://blog.csdn.net/qmhball/article/details/8233769

（注：下文为引入，防止原文件删除）
主机（master）配置：
1.修改mysql配置文件my.cnf
在[mysqld]标签下添加以下几行

log-bin #开启二进制日志
server-id=id #主服务器id号
binlog-do-db=db_nameA #指定对db_nameA记录二进制日志
binlog-ignore-db=db_namB #指定不对db_namB记录二进制日志

注意：
log-bin，server-id是配置文件中必须添加的内容。此时主服务器默认对所有数据库进行备份。如果需要特殊指明只对某个数据库进行备份或不备份，则可以加入binlog-do-db和binlog-ignore-db选项。有关（log-bin的详细说明见附录1）

在测试主机上，我们实际添加入如下内容：

log-bin
server-id=1
binlog-do-db=test

2.为从服务器添加mysql账户并配置权限
在主服务器上，必须为从服务器创建一个用来连接主服务器的用户，并设置replication slave权限。所用具体命令如下：

grant replication slave
on *.*
to '帐号' @ '从服务器IP' identified by '密码';

在测试机上，我们实际执行：

grant replication slave
on *.*
to 'replication'@'%' identified by 'sosotest';

这时在mysql库的user表中使用

select * from user where user = 'replication' \G;

可以看到Repl_slave_priv项对就的值为Y。
3.导出主服务器数据
导出主服务器数据，以备之后将其导入从服务器，使主从服务器的初始状态保持一致。

在测试机上，我们实际执行：

mysqldump test > test.bak

4.记录File 及Position 项的值
　　重启mysql，使用show master status\G;查看主服务器状态，记录File 及Position 项的值，以便之后对从服务器进行配置。
　　
在测试机上，我们实际执行时看到的结果如下：
*************************** 1. row ***************************
File: simba-bin.000008
Position: 79
Binlog_Do_DB: test
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

其中File为imba-bin.000008；Position为79。

至此主服务器配置完毕

从机（slave）配置：
1.修改mysql配置文件my.cnf
在[mysqld]标签下添加以下面一行：

server-id=id #从机id

实际中，我们添加：

server-id=2

2.导入主机数据库
在测试机上，我们实际执行：
mysql test < test.bak

3.重启mysql数据库并设置相关参数

change master to
master_host = '10.1.146.133',
master_user = 'replication',
master_password = 'sosotest',
master_log_file = 'darkstar-bin.000008',
master_log_pos = 79;
slave start;

4.检查数据库查看相关参数
　　使用show slave status \G;查看相关参数是否正确。
　　在实际执行时，我们在从机上看到的结果如下（只摘取了部分输出）：
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.146.133
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: darkstar-bin.000006
Read_Master_Log_Pos: 79
Relay_Log_File: simba-relay-bin.000003
Relay_Log_Pos: 171
Relay_Master_Log_File: darkstar-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
……

对部分参数的解释：
Slave_IO_State: Waiting for master to send event 
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
以上参数说听slave正常运行，正等待主服务器发来的消息。此时若用netstate命令可以看到从机与主机间已经建立了一条边接。

特别需要注意的两个参数是：
Master_Log_File和Read_Master_Log_Pos。Master_Log_File代表主机上用于主备同步的日志文件名，Read_Master_Log_Pos代表上一次成功同步到的日志文件中的位置。
如果这两项与先前在主服务器上看到的File及Position的值不相符，则无法正确进行同步。

三、解决同步状态的问题：
http://blog.51cto.com/kerry/277414

（注：下文为引入）

Slave_SQL_Running: No mysql同步故障解决

     今天检查数据库发现一台MySQL Slave未和主机同步，查看Slave状态：
mysql> show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: No
Last_Errno: 1062
....
Seconds_Behind_Master:NULL
原因：
1.程序可能在slave上进行了写操作 
2.也可能是slave机器重起后，事务回滚造成的.

解决办法I：
1.首先停掉Slave服务：slave stop

2.到主服务器上查看主机状态：
记录File和Position对应的值。
mysql> show master status;
+------------------+-----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000020 | 135617781 | | |
+------------------+-----------+--------------+------------------+
1 row in set (0.00 sec)

3.到slave服务器上执行手动同步：
mysql> change master to 
> master_host='master_ip',
> master_user='user', 
> master_password='pwd', 
> master_port=3307, 
> master_log_file='mysql-bin.000020', 
> master_log_pos=135617781;
1 row in set (0.00 sec)
mysql> slave start;
1 row in set (0.00 sec)
再次查看slave状态发现：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...
Seconds_Behind_Master: 0

解决办法II：
mysql> slave stop;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> slave start;

 

自己的使用体会:方法一是强制性从某一个点开始同步,会有部分没有同步的数据丢失,后续主服务器上删除记录同步也会有一些错误信息,不会影响使用.方法二不一定会有效果.

 

 

=======================================================================================]

1,主从不能同步:

show slave status;报错:Error xxx dosn't exist
且show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL

解决方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

之后Slave会和Master去同步 主要看:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0，0就是已经同步了

2,还需要做的一些优化与监视:
show full processlist; //查看mysql当前同步线程号
skip-name-resolve       //跳过dns名称查询，有助于加快连接及同步的速度
max_connections=1000    //增大Mysql的连接数目，(默认100)
max_connect_errors=100 //增大Mysql的错误连接数目,(默认10)

 

查看日志一些命令
1,  show master status\G;
           在这里主要是看log-bin的文件是否相同。
    show slave status\G;
    在这里主要是看:
                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes
   如果都是Yes,则说明配置成功.

2,在master上输入show processlist\G;
     mysql> SHOW PROCESSLIST\G
     *************************** 1. row ***************************
       Id: 2
       User: root
       Host: localhost:32931
       db: NULL
       Command: Binlog Dump
       Time: 94
       State: Has sent all binlog to slave; waiting for binlog to
         be updated
       Info: NULL

   如果出现Command: Binlog Dump,则说明配置成功.

 

 

stop slave    #停止同步 
start slave    #开始同步，从日志终止的位置开始更新。 
SET SQL_LOG_BIN=0|1  #主机端运行，需要super权限，用来开停日志，随意开停，会造成主机从机数据不一致，造成错误 
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 客户端运行，用来跳过几个事件，只有当同步进程出现错误而停止的时候才可以执行。 
RESET MASTER  #主机端运行,清除所有的日志，这条命令就是原来的FLUSH MASTER 
RESET SLAVE   #从机运行，清除日志同步位置标志，并重新生成master.info
虽然重新生成了master.info,但是并不起用，最好，将从机的mysql进程重启一下， 
LOAD TABLE tblname FROM MASTER #从机运行，从主机端重读指定的表的数据，每次只能读取一个，受timeout时间限制，需要调整timeout时间。执行这个命令需要同步账号有 reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 
LOAD DATA FROM MASTER  #从机执行，从主机端重新读入所有的数据。执行这个命令需要同步账号有reload和super权限。以及对相应的库有select权限。如果表比较大，要增加net_read_timeout 和 net_write_timeout的值 
CHANGE MASTER TO master_def_list  #在线改变一些主机设置，多个用逗号间隔,比如
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='bigs3cret' 
MASTER_POS_WAIT() #从机运行 
SHOW MASTER STATUS #主机运行，看日志导出信息 
SHOW SLAVE HOSTS #主机运行，看连入的从机的情况。 
SHOW SLAVE STATUS (slave) 
SHOW MASTER LOGS (master) 
SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ] 
PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'