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 status\G
- 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 status\G;
- *************************** 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 status\G;
- *************************** 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 status\G;
- *************************** 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 Files\MySQL\MySQL Server 5.6\bin\mysqldump" -P3307 -uroot123 -proot123 landclash> c:\db_bak\landclash_%Ymd%.sql
- @echo on
- rem *******************************Code End*****************************
文件备份 DataBackupFile.bat
- *******************************Code Start*****************************
- net stop MySQL
- xcopy "C:\ProgramData\MySQL\MySQL Server 5.6\data\landclash\*.*" "c:\db_bak\landclash\%date:~0,10%\" /S /I
- xcopy "C:\ProgramData\MySQL\MySQL Server 5.6\data\ibdata1" "c:\db_bak\landclash\%date:~0,10%\" /S /I
- net start MySQL
- *******************************Code End *****************************
4.2, 增量备份
小量的数据库可以每天进行完整备份, 因为这也用不了多少时间, 但当数据库很大时, 就不太可能每天进行一次完整备份了, 这时候就可以使用增量备份. 增量备份的原理就是使用了 http://www.centos.bz/category/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 Files\MySQL\MySQL Server 5.6\bin\mysql.exe" -P3307 -uroot123 -proot123 < "C:\Program Files\MySQL\MySQL Server 5.6\bin\IncreaseDB.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/p/10754814.html