Mysql备份与AB复制

虚幻0516

　　Mysql备份与AB复制
　　复制有两种方式：同步复制（msylq-cluster） 异步复制（mysql-m/s主从备份又称作双机热备）
　　一、Mysql完全备份（纯复制方式和mysqldump工具）
　　1、纯复制方式：（必须得停掉mysql服务）
　　/etc/init.d/mysqld stop
　　tar -zcvf mysql_backup.tgz /var/lib/mysql/
　　/etc/init.d/mysqld start
　　恢复方式：tar包解压覆盖原路径文件，缺点是不同版本数据不兼容
　　2、mysqldump工具：
　　mysqldump -uroot -p test > /srv/test.sql (备份test数据库)
　　mysqldump -uroot -p --all-databases > /srv/alldata.sql (备份所有的数据库)
　　恢复方式：mysql -uroot -p test < /srv/test.sql(导入test数据库)
　　mysql -uroot -p < /srv/alldata.sql(导入所有数据)
　　二、增量备份（使用时间来恢复和使用位置号来恢复）
　　mysql的增量备份使用的方法的是用过mysql的二进制日志来实现的,二进制日志中保存了对mysql的所有操作sql语句；mysql的二进制日志功能默认是关闭的，我们需要自己手动打开；
　　打开方式： vim /etc/my.cnf 添加
　　log-bin=noah 默认为log-bin=binlog；
　　重新启动服务后就会在/var/lib/mysql 中发现binlog 二进制文件；
　　二进制的日志文件我们需要使用命令mysqlbinlog 来查看；
　　1、使用时间来恢复:
　　例如：mysqlbinlog --start-date=&quot;2012-02-29 9:46:04&quot; --stop-date=&quot;2012-02-29 13:47:24&quot; /var/lib/mysql/binlog.000004 |mysql -uroot -pwestos (帐号root密码westos，对start和stop时间段的sql语句交给mysql执行，就能恢复这段时间的数据)
　　2、使用位置号来恢复：
　　例如：mysqlbinlog --start-position=&quot;106&quot; --stop-position=&quot;1472&quot; /var/lib/mysql/binlog.000003 |mysql -uroot -pwestos
　　注：时间和位置号都可以通过mysqlbinlog命令查看。
　　三、Mysql的AB复制：（同步和异步复制）
　　下面做单向热备实验：
　　主库master：192.168.0.57 从库slave：192.168.0.95
　　1、在master和slave上安装mysql
　　yum install mysql mysql-server -y (主从机都要安装mysql)
　　2、在master上修改/etc/my.cnf文件，添加
　　server-id=1 //区别master和slave
　　log-bin=mysql-bin //启动二进制日志文件功能
　　binlog-do-db=test //二进制文件需要同步的数据库名（不写为同步所有数据）
　　binlog-ignore-db=mysql //不同步mysql数据库数据,以免发生因同步了用户信息而从机出现登录问题
　　然后重启服务：service  mysqld restart
　　3、授权给用户：
　　master登录数据库：
　　[root@desktop57 ~]# mysql -uroot -pwestos

　　mysql> grant replication slave,reload,super on *.* to 'slave'@'192.168.0.95'>　　mysql> flush privileges;
　　给主机192.168.0.95用户slave授权，密码10086。
　　在从库slave上测试：[root@desktop95 ~]# mysql -uslave -p10086 -h 192.168.0.57
　　OK正常登录，用户建立成功。
　　4、在slave上修改/etc/my.cnf文件，添加
　　server-id=2 //从库ID号，和主库区别开 (注意这一行的注释去掉，不然mysql起不来，可看日志)
　　master-host=192.168.0.57 //指定主库IP地址
　　master-user=slave //指定授权的用户名
　　master-password=10086 //指定授权用户的密码
　　replicate-do-db=test //要同步的数据库
　　replicate-ignore-db=mysql //忽略mysql数据库的同步
　　5、在master上查看主库状态：
　　[root@desktop57 ~]# mysql -uroot -pwestos
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000001 |      571 | test         | mysql            |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec)
　　可以知道File(mysql-bin.000001),Position(571)
　　6、在slave上查看从库状态：
　　[root@desktop95 ~]# mysql -uroot -pwestos
　　mysql> slave stop;
　　Query OK, 0 rows affected, 1 warning (0.00 sec)
　　mysql> change master to master_host='192.168.0.57',master_user='slave',master_password='10086',master_log_file='mysql-bin.000001',master_log_pos=571;  （指定从库应该备份的任务号，以slave用户登录密码10086）
　　Query OK, 0 rows affected (0.13 sec)
　　mysql> slave start;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show slave status\G
　　......
　　Slave_IO_Running: Yes    （从库I/O读写正常）
　　Slave_SQL_Running: Yes    （从库mysql监听正常）
　　......
　　OK啦，没有的话就检查配置文件是否正确吧～～
　　我们测试一下看从库slave是否同步了master的数据呢？
　　[root@desktop57 ~]# mysql -uroot -pwestos
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　+--------------------+
　　3 rows in set (0.00 sec)
　　mysql> use test;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql> show tables;
　　+----------------+
　　| Tables_in_test |
　　+----------------+
　　| grade          |
　　+----------------+
　　1 row in set (0.00 sec)
　　mysql> select * from grade;
　　+-------+-----+-------+--------+
　　| name  | num | grade | notes  |
　　+-------+-----+-------+--------+
　　| kevin | 14  | 98    | great! |
　　+-------+-----+-------+--------+
　　1 rows in set (0.01 sec)
　　OK，已经同步过来啦！！
　　再来做AB双向同步吧：（接着上面的做吧）
　　1、在master上修改/etc/my.cnf 添加：
　　log-bin=mysql-bin
　　binlog-do-db=test
　　binlog-ignore-db=mysql
　　注：上面三条让之前的slave机也做成可同步的主机
　　重启mysql服务：service mysqld restart
　　2、授权给192.168.0.57
　　[root@desktop95 admin]# mysql -uroot -pwestos

　　mysql> grant replication slave,reload,super on *.* to 'slave'@'192.168.0.57'>　　mysql> flush privileges;
　　在192.168。0.57上测试看能不能登录95的数据库：
　　[root@desktop57 ~]# mysql -uslave -p10000 -h 192.168.0.95
　　OK，成功登录。
　　3、在192.168.0.57（master）上修改/etc/my.cnf ,增加：
　　master-host=192.168.0.95 //设置从原 slave 数据库同步更新
　　master-user=slave //更新用户
　　master-password=10000 //密码
　　master-port=3306 //端口
　　replicate-do-db=test //需要更新的库
　　replicate-ignore-db=mysql //屏蔽对mysql库的同步
　　重启mysql服务：service  mysqld restart
　　4、在192.168.0.95（原slave）查看状态：
　　[root@desktop95 admin]# mysql -uroot -pwestos
　　mysql> show master status;
　　+------------------+----------+--------------+------------------+
　　| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
　　+------------------+----------+--------------+------------------+
　　| mysql-bin.000001 |      106 | test         | mysql            |
　　+------------------+----------+--------------+------------------+
　　1 row in set (0.00 sec)
　　记录下File(mysql-bin.000001),Position(106)
　　5、在192.168.0.57（原master）查看状态及加入工作群：
　　mysql> slave stop;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> change master to master_host='192.168.0.95',master_user='slave',master_password='10000',master_log_file='mysql-bin.000001',master_log_pos=106;
　　Query OK, 0 rows affected (0.12 sec)
　　mysql> slave start;
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> show slave status\G
　　*************************** 1. row ***************************
　　Slave_IO_State: Waiting for master to send event
　　Master_Host: 192.168.0.95
　　Master_User: slave
　　Master_Port: 3306
　　Connect_Retry: 60
　　Master_Log_File: mysql-bin.000001
　　Read_Master_Log_Pos: 106

　　>
　　>
　　>　　Slave_IO_Running: Yes    （I/O读写正常）
　　Slave_SQL_Running: Yes    （mysql监听正常）
　　Replicate_Do_DB: test
　　Replicate_Ignore_DB: mysql
　　Replicate_Do_Table:
　　Replicate_Ignore_Table:
　　Replicate_Wild_Do_Table:
　　Replicate_Wild_Ignore_Table:
　　Last_Errno: 0
　　Last_Error:
　　Skip_Counter: 0
　　Exec_Master_Log_Pos: 106

　　>　　Until_Condition: None
　　Until_Log_File:
　　Until_Log_Pos: 0
　　Master_SSL_Allowed: No
　　Master_SSL_CA_File:
　　Master_SSL_CA_Path:
　　Master_SSL_Cert:
　　Master_SSL_Cipher:
　　Master_SSL_Key:
　　Seconds_Behind_Master: 0
　　Master_SSL_Verify_Server_Cert: No
　　Last_IO_Errno: 0
　　Last_IO_Error:
　　Last_SQL_Errno: 0
　　Last_SQL_Error:
　　1 row in set (0.00 sec)
　　OK，都是两yes了，表明数据正常同步
　　6、测试同步情况：看看一台电脑写入数据另一台会同步吗？
　　在192.168.0.95的mysql数据库test中插入数据xiaoming；
　　mysql> use test;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql> create table english;
　　ERROR 1113 (42000): A table must have at least 1 column
　　mysql> create table english(
　　-> name varchar(25) not null,
　　-> grade varchar(25) not null);
　　Query OK, 0 rows affected (0.07 sec)
　　mysql> show tables;
　　+----------------+
　　| Tables_in_test |
　　+----------------+
　　| english        |
　　| grade          |
　　+----------------+
　　2 rows in set (0.00 sec)
　　这里我们增加了表english，有两个键值name和grade；
　　好了我们到192.168.0.57（原master）上看看有没有同步上去吧：
　　[root@desktop57 ~]# mysql -uroot -pwestos
　　mysql> show databases;
　　+--------------------+
　　| Database           |
　　+--------------------+
　　| information_schema |
　　| mysql              |
　　| test               |
　　+--------------------+
　　3 rows in set (0.00 sec)
　　mysql> use test;
　　Reading table information for completion of table and column names
　　You can turn off this feature to get a quicker startup with -A
　　Database changed
　　mysql> show tables;
　　+----------------+
　　| Tables_in_test |
　　+----------------+
　　| english        |
　　| grade          |
　　+----------------+
　　2 rows in set (0.00 sec)
　　行了！双向同步完成！