Window环境下配置MySQL 5.6的主从复制、备份恢复

  1. 云栖社区>
  2. 博客>
  3. 正文

Window环境下配置MySQL 5.6的主从复制、备份恢复

推荐码发放 2019-04-24 14:02:58 浏览427
展开阅读全文

Window环境下配置MySQL 5.6的主从复制、备份恢复
1、环境准备
Windows 7 64位
MySQL 5.6
主库:192.168.103.207
从库:192.168.103.208
2、配置新数据库的主从复制
2.1、修改主库配置文件
先在主数据库中创建新数据库rep_test。

然后编辑主数据库的 my.ini 文件

在 [mysqld] 节点中增加或修改如下内容:

server-id=1 #指定唯一的ID,1至32,必须的
log-bin=mysql-log-bin #指定二进制日志存放路径,必须的
binlog-do-db=rep_test #指定要同步的数据库,必须的

binlog-ignore-db=mysql #指定不要同步的数据库,如果指定了binlog-do-db就不用再指定该项

2.2、配置主库备份账户
重启主数据库,然后在主数据库中建立一个备份账户

mysql>grant replication slave on . to slave@192.168.103.208 identified by 'slave123' ;
mysql>flush privileges;
PS:identified by 指定的slave是账号slave@192.168.1.128 的密码

2.3、查看主服务器状态
显示主服务器的状态信息,并且找到File 和 Position 的值记录下来;

mysql>show master status;

2.4、修改从库配置文件
在从数据库中创建新的数据库rep_test。

然后编辑从数据库的 my.ini 文件

在 [mysqld] 节点中增加如下内容:

server-id=2 #指定唯一的ID,2至32,必须的,并且不能跟主数据库一样
replicate-do-db=rep_test #指定要同步的数据库,必须的

replicate-ignore-db=mysql #指定不要同步的数据库,

2.5、设置从库slave
重启从数据库,设置登录主数据库的账号和密码等信息,然后启动slave

mysql>change master to master_host='192.168.103.207',master_port=3307,master_user='slave',master_password='slave123', master_log_file='mysql-log-bin.000001',master_log_pos=407;
mysql>start slave;
2.6、查看从数据库的信息
mysql>show slave status G;
如果出现: Slave_IO_Running: Yes Slave_SQL_Running: Yes以上两项都为Yes,那说明没问题了

2.7、测试主从复制
在主数据库中创建一个新的表,然后再切换到从数据库查看是否同样多出同名的数据库

2.8、清除主从关系
mysql主从复制中,需要将主从复制关系清除,需要取消其从库角色。这可通过执行 RESET SLAVE ALL 清除从库的同步复制信息、包括连接信息和二进制文件名、位置。从库上执行这个命令后,使用show slave status将不会有输出。

reset slave是各版本Mysql都有的功能,在stop slave之后使用。主要做:
删除master.info和relay-log.info文件;
删除所有的relay log(包括还没有应用完的日志),创建一个新的relay log文件;
从Mysql 5.5开始,多了一个all参数。如果不加all参数,那么所有的连接信息仍然保留在内存中,包括主库地址、端口、用户、密码等。这样可以直接运行start slave命令而不必重新输入change master to命令,而运行show slave status也仍和没有运行reset slave一样,有正常的输出。但如果加了all参数,那么这些内存中的数据也会被清除掉,运行show slave status就输出为空了。
复制代码
mysql>stop slave;
QueryOK, 0 rowsaffected (0,00 sec)

mysql>reset slave all;
QueryOK, 0 rowsaffected (0,04 sec)

mysql> show slave statusG
Emptyset (0,00 sec)
复制代码
RESET MASTER
功能说明:删除所有的binglog日志文件,并将日志索引文件清空,重新开始所有新的日志文件。用于第一次进行搭建主从库时,进行主库binlog初始化工作;
测试如下:

未删除前

复制代码
[root@mysql01 mysql]# pwd
/usr/local/data/mysql
[root@mysql01 mysql]# ls
auto.cnf client-cert.pem ibdata1 ibtmp1 localhost.localdomain.pid mysql-bin.000002 mysql-bin.000005 mysqld_safe.pid public_key.pem sys
ca-key.pem client-key.pem ib_logfile0 imove mysql mysql-bin.000003 mysql-bin.000006 performance_schema server-cert.pem
ca.pem ib_buffer_pool ib_logfile1 localhost.localdomain.err mysql-bin.000001 mysql-bin.000004 mysql-bin.index private_key.pem server-key.pem
[root@localhost bin]# ./mysql -uroot -p123456

mysql> show master status;
File Position Binlog_Do_DB Binlog_Ignore_DB Executed_Gtid_Set
mysql-bin.000006 16663694

1 row in set (0.00 sec)
mysql>
复制代码
当前有6个binlong日志,且Position的位置为16663694
运行RESET MASTER

复制代码
mysql> reset master;
Query OK, 0 rows affected (0.03 sec)

mysql> show master statusG;
1. row **

        File: mysql-bin.000001
    Position: 107
Binlog_Do_DB: 

Binlog_Ignore_DB:
1 row in set (0.00 sec)
复制代码
显示所有的binlog已经被删除掉,且binlog从000001 开始记录

注:当数据库要清理binlog文件的时候,可以通过操作系统进行删除,也可以运行reset master进行删除。但是如果当前是主数据库,且主从数据库正常的时候,千万不能用这种方式删除。

【使用场景】第一次搭建主从数据库时,用于主库的初始化binglog操作。

RESET SLAVE
功能说明:用于删除SLAVE数据库的relaylog日志文件,并重新启用新的relaylog文件;
测试如下:

登录从数据库,未删除前

复制代码
mysql> show slave statusG;
1. row **

           Slave_IO_State: Connecting to master
              Master_Host: 192.168.47.167
              Master_User: server
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000019
      Read_Master_Log_Pos: 12992

Relay_Log_File: mysql02-relay-bin.000004

            Relay_Log_Pos: 4

     Relay_Master_Log_File: mysql-bin.000019

复制代码
当前relaylog为0004;
先停止slave,运行RESET MASTER

复制代码
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.04 sec)
mysql> show slave statusG;
1. row **

           Slave_IO_State: 
              Master_Host: 192.168.47.167
              Master_User: server
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: 
      Read_Master_Log_Pos: 4

** Relay_Log_File: mysql02-relay-bin.000001

            Relay_Log_Pos: 4**

复制代码
RESET SLAVE将使SLAVE忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用场景:当原来的主从关系被破坏之后,从库经过重新初始化后直接连接会报 ERROR 1201的错误,运行reset slave后,重新配置主从连接就可以了;

3、配置旧数据库的主从复制
3.1、修改主库配置文件
如果一开始数据库的架构不是主从复制,并且运行一段时间后已经有数据存在,那配置的方式略有不同。

在从数据库中创建新的数据库landclash。

编辑主数据库的 my.ini 文件,使用以下内容:

server-id=1 #指定唯一的ID,1至32,必须的
log-bin=mysql-log-bin #指定二进制日志存放路径,必须的
binlog-do-db=landclash

binlog-ignore-db=mysql #指定不要同步的数据库,如果指定了binlog-do-db就不用再指定该项

配置主库备份账户
mysql>grant replication slave on . to slave123@192.168.103.208 identified by 'slave123' ;
mysql>flush privileges;
3.2、主数据库中锁定所有的表
重启主数据库,然后在主数据库中锁定所有的表

mysql>flush tables with read lock;
3.3、查看主服务器状态
显示主服务器的状态信息,并且找到File 和 Position 的值记录下来;

mysql>show master status;

3.4、数据拷贝
将主数据库data目录下需要做主从复制的数据库的同名目录拷贝到从数据库的data目录下

注意:拷贝的话也要拷贝 ibdata1 文件,不然会不显示表结构

3.5、修改从库配置文件
编辑从数据库的 my.ini 文件,加上一下内容:

server-id=2 #指定唯一的ID,2至32,必须的,并且不能跟主数据库一样
replicate-do-db=landclash #指定要同步的数据库,必须的

replicate-ignore-db=mysql #指定不要同步的数据库,

3.6、设置从库slave
重启从数据库,因为主数据库在重新配置my.ini后,日志文件变成新的文件,所以需要再次设置登录主数据库的账号和密码等信息

mysql> stop slave;
mysql>change master to master_host='192.168.103.207',master_port=3307,master_user='slave123',master_password='slave123', master_log_file='mysql-log-bin.000001',master_log_pos=742;
mysql>start slave;

3.7、查看从数据库的信息
再次输入查看从数据库状态的命令

mysql>show slave status G;
我们看到红框标注的地方跟之前做新数据库主从复制不一样了。

3.8、主数据库解锁
完成上述配置后,回到主数据库,将表解锁

mysql>unlock tables;
之后在主数据库的修改就能同步到从数据库上了。

4、数据库备份
4.1备份脚本
sql备份 DataBackupSql.bat
复制代码
rem Code Start
@echo off

set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:Program FilesMySQLMySQL Server 5.6binmysqldump" -P3307 -uroot123 -proot123 landclash > c:db_baklandclash_%Ymd%.sql
@echo on
rem Code End
复制代码
文件备份 DataBackupFile.bat
复制代码
Code Start
net stop mysql
xcopy "C:ProgramDataMySQLMySQL Server 5.6datalandclash*.*" "c:db_baklandclash%date:~0,10%" /S /I
xcopy "C:ProgramDataMySQLMySQL Server 5.6dataibdata1" "c:db_baklandclash%date:~0,10%" /S /I
net start mysql
Code End
复制代码
4.2、增量备份
小量的数据库可以每天进行完整备份,因为这也用不了多少时间,但当数据库很大时,就不太可能每天进行一次完整备份了,这时候就可以使用增量备份。增量备份的原理就是使用了mysql的binlog志。

1、首先做一次完整备份:

mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>test.sql这时候就会得到一个全备文件test.sql

在sql文件中我们会看到:
-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指备份后所有的更改将会保存到bin-log.000002二进制文件中。
2、在test库的t_student表中增加两条记录,然后执行flush logs命令。这时将会产生一个新的二进制日志文件bin-log.000003,bin-log.000002则保存了全备过后的所有更改,既增加记录的操作也保存在了bin-log.00002中。

3、再在test库中的a表中增加两条记录,然后误删除t_student表和a表。a中增加记录的操作和删除表a和t_student的操作都记录在bin-log.000003中。

自动增量备份脚本:

IncreaseDB.sql

flush logs;
DataBackupFile_IncreaseDB.bat

Code Start
"C:Program FilesMySQLMySQL Server 5.6binmysql.exe" -P3307 -uroot123 -proot123 < "C:Program FilesMySQLMySQL Server 5.6binIncreaseDB.sql"
Code End
4.3、恢复
1、首先导入全备数据

mysql -h10.6.208.183 -utest2 -p123 -P3310 < test.sql,也可以直接在mysql命令行下面用source导入

2、恢复bin-log.000002

mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310

3、恢复部分 bin-log.000003

在general_log中找到误删除的时间点,然后更加对应的时间点到bin-log.000003中找到相应的position点,需要恢复到误删除的前面一个position点。

可以用如下参数来控制binlog的区间

--start-position 开始点 --stop-position 结束点

--start-date 开始时间 --stop-date 结束时间

找到恢复点后,既可以开始恢复。

mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310

作者:一个大西瓜
出处:https://www.cnblogs.com/wyt007/

网友评论

作者关闭了评论
推荐码发放
+ 关注