mysql备份

冷备份

因为mysql中库即目录（database is folder）所以冷备份其实就是备份整个目录。

停掉mysql服务，直接将当前目录（/var/lib/mysql）下的东西都拷贝到其他地方就行，恢复的时候直接拷回来就行了，并且将所有者和组都改为musql。备份还原都需要需要停止服务

myisam engine 的将目录直接拷贝（三个文件 xt701.frm记录表结构 xt701.myd 存放数据 xt701.myi 存放索引）

innodb engine的需要将innodb及相关日志文件考走，不考日志恢复的时候起不来服务

热备份

mysql自带mysqldump命令

命令语法
mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

备份

全备
[root@convirt tmp]# mysqldump -A -x > quanbei.sql

备份某个库
[root@convirt tmp]# mysqldump db1 > vvv.sql
            or
[root@convirt tmp]# mysqldump --databases db1 > /opt/5.sql

同时备份多个库  db1  and test
[root@instructor ~]# mysqldump --database db1 test > /opt/backup/db1_no2.sql

备份单表  db1库的T6表
mysqldump -x db1 T6 > /opt/t6.sql

备份表结构
[root@convirt tmp]# mysqldump --no-data --databases db1 > bb.sql

基于binlog日志备份
1）开启二进制日志 
vim /etc/my.cnf
server-id=1
log-bin=logbin

2)mysqlbinlog 命令
-start-datetime 和--stop-datetime 选项可以用来指定从二进制日志的某个时间点来进行恢复。
-–start-position=#               指定开始和结束position
-–stop-position=# 
[root@convirt mysql]#  /usr/local/mysql/bin/mysqlbinlog --start-position=370 --stop-position=440  /var/lib/mysql/mysql-bin.000002

# --start-position=370 --stop-position=440 #这里面数字从哪儿来的呢？

[root@convirt mysql]#  mysqlbinlog /usr/local/mysql/bin/mysqlbinlog
# at 370
#100929 21:35:25 server id 1  end_log_pos 440 Query    thread_id=1    exec_time=0    error_code=0
SET TIMESTAMP=1285767325/*!*/;

从某个时间点开始还原【不需要提前删表】
mysqlbinlog binlog.[0-10]* --start-datetime="2014-03-02 10:30:00" |mysql

导入到某个时间点之前 【先删表，后还原】
mysqlbinlog /var/lib/mysql/binlog.[0-7]* --stop-datetime="2014-02-26 11:38:58" |mysql

还原

恢复单个库
1）先去建库
2）导入备份文件
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@convirt tmp]# mysql db1 < vvv.sql
恢复某个表
[root@convirt tmp]# mysql -u root -p [database name]  < [backup file name]

mysql AB备份

mysql数据库同步方法

1、主库创建/etc/my.cnf，修改
里边的键值增加
server-id=1
log-bin=logbin

2、主库增加用户，用于从库读取主库日志。
mysql> grant replication slave,reload,super on *.* to 'slave'@'10.255.254.109' identified by "123456";
mysql> flush privileges;

3、从库连接主库进行测试。mysql -u slave -p123456 -h 192.168.0.1
如果连接成功说明主库配置成功

4、停从库，修改从库/etc/my.cnf，增加选项：
server-id=2
master_host=10.255.254.129
master_user=slave
master_password=123456
relay_log=/var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index

5、启动从库，进行主从库数据同步
/opt/mysql/share/mysql/mysql start
/opt/mysql/bin/mysql -u root -p 
mysql>load data from master;
说明：这一步也可以用数据库倒入或者直接目录考过来。

6、进行测试：
①主库查看当前存在的库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               | 
+--------------------+
3 rows in set (0.01 sec)
②从库查看当前存在库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               | 
+--------------------+
3 rows in set (0.01 sec)
说明两者中的数据保持了一致性
③主库创建表，
mysql> create database xxx;
Query OK, 1 row affected (0.00 sec)
打开从库，察看：
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| mysql              |  
| test               |
| xxx                |       | 
+--------------------+
4 rows in set (0.01 sec)
说明主从数据库创建成功。


7、主从数据库相关命令：
slave stop; slave start ; 开始停止从数据库。
show slave status\G; 显示从库状态信息
show master status\G;显示主库状态信息
purge master logs to ’binlog.000004’; 此命令非常小心，删除主数据库没用的二进制日志文件。如果误删除，那么从库就没有办法自动更新了。
change master；从服务器上修改参数使用

另外，如果你当前操作的从库以前曾经与其他服务器建立过主从关系，你可能会发现即使你在my.cnf文件中更改了主服务器的位置，但是MSQL仍然在试图连接就旧的主服务器的现象。
发生这种问题的时候，我们可以通过清除master.info这个缓存文件或者在mysql中通过命令来进行设置。方式如下：
a、删除master.info方法
这个文件位于数据文件存放目录里。默认是在/var/lib/mysql中的。你可以直接
将其删除，然后重新启动服务器。

b、mysql命令方法
如果你不方便重新启动服务器的话，那么就只能使用mysql命令来帮助你做到。
首先登录到主服务器上，查看当前服务器状态：
mysql> show master status\G;
+---------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| mysql-bin.003 | 73 | test | manual,mysql |
+---------------+----------+--------------+------------------+
记录下File和Position的值。然后登录从服务器，进行如下操作：
mysql> slave stop;
mysql> CHANGE MASTER TO
-> MASTER_HOST='master_host_name', //主服务器的IP地址
-> MASTER_USER='replication_user_name', //同步数据库的用户
-> MASTER_PASSWORD='replication_password', //同步数据库的密码
-> MASTER_LOG_FILE='recorded_log_file_name', //主服务器二进制日志的文件名(前面要求记录的参数)
-> MASTER_LOG_POS=recorded_log_position; //日志文件的开始位置(前面要求记录的参数)
mysql> slave start;

AB复制排错

AB复制注意事项：
1.第一次启动slave库以后  show slave status \G;  
检查  两个线程  I／O   SQL 是否是YES状态

2.测试
添加或删除数据 （主服务器）   查看辅助的是否同步
如果不同步
 1）查看辅助的sql日志    tailf /var/log/mysqld.log
     140304 11:59:18 [Note] Slave I/O thread: connected to master 'slave@192.168.18.254:3306',  replication started in log 'binlog.000011' at position 294
       ＃从服务器是否可以顺利到达主服务器
            如果发现你的用户名密码有错误  /etc/my.cnf   /var/lib/mysql/master.info


 2)查看sql线程是否报错
   140304 11:59:08 [Note] Slave SQL thread initialized, starting replication in log 'binlog.000011' at position 294, relay log '/var/lib/mysql/mysql-relay-bin.000001' position: 4
           手动去执行同步
                1）mysql> slave stop;
                2)mysql> CHANGE MASTER TO MASTER_HOST='192.168.18.254',MASTER_USER='slave',MASTER_PASSWORD='123456',MASTER_LOG_FILE='binlog.000011';
                3)mysql > slave start;

如果出现开始同步  且AB同步后数据不一致
备份主库   去  手动同步到一致状态   再去执行手动同步中的1 ，2， 3步

 

AB互为主备

• AB复制正反做两次