场景:
1, 主服务器 192.168.0.225, 从服务器 192.168.0.226. 其中, 主服务器上已有数据.
2, 主从服务器上的 mysql 版本及安装配置相同.
一, 主从备份的原理:
主服务器数据库的每次操作都会记录在二进制日志文件 mysql-bin.xxx 中. 从服务器的 I/O 线程使用专用帐号登陆到主服务器中读取该二进制文件, 并将文件内容写入到自己本地的中继日志 relay-log 文件中. 然后从服务器的 SQL 线程会根据中继日志中的内容执行 SQL 语句.
这要求两台服务器有同样的初态.
二, 同步初态:
1, 将主服务器要同步的数据库加锁, 避免同步时发生改变:
- >use database_name;
- >flush tables with read lock;
2, 使用 mysqldump 工具导出数据:
mysqldump -uroot -pxxx database_name>database_name.sql
3, 备份完成后, 解锁数据库:
>unlock tables;
4, 将初始数据导入从数据库:
- >create database database_name;
- >use database_name;
- >source database_name.sql;
完成以上操作后, 主从服务器就有一样的初态了.
三, 主从同步设置:
1, 配置从数据库:
/etc/my.cnf 主要配置如下:
- log-bin=mysql-bin #开启二进制日志
- server-id = 2 #主数据库 id 为 1, 不能相同.
- replicate_wild_do_table=test.% #只同步 test 库下的表
- relay_log=mysqld-relay-bin #记录中继日志
- log-slave-updates=YES #从服务器同步后记录日志
修改完成后重启 mysql 服务.
2, 查看主服务器日记记录位置:
>show master status\G
显示内容如下:
- ***************** 1. row ****************
- File: mysql-bin.000001 #当前记录的日志
Position: 80647293 #日志中记录的位置
- Binlog_Do_DB:
- Binlog_Ignore_DB:
- 1 row in set (0.00 sec)
3, 主服务器创建允许从服务器同步数据的账户:
>grant replication slave on *.* to 'user_name'@'192.168.0.226' identified by 'ahaii';
4, 从服务器开启同步:
- >change master to
- master_host='192.168.0.225',
- master_user='user_name',
- master_password='ahaii',
- master_log_file='mysql-bin.000001',
- master_log_pos=80647293;
配置完以上后, 重启从服务器 mysql 服务.
5, 查看从服务器是否已经成功开启同步:
>show slave status\G
显示如下:
- *************************** 1. row ***************************
- Slave_IO_State: Waiting for master to send event
- Master_Host: 192.168.0.225
- Master_User: user_name
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: mysql-bin.000001
- Read_Master_Log_Pos: 1114
- Relay_Log_File: mysqld-relay-bin.000004
- Relay_Log_Pos: 1260
- Relay_Master_Log_File: mysql-bin.000002
- Slave_IO_Running: Yes
- Slave_SQL_Running: Yes
- Replicate_Do_DB:
- Replicate_Ignore_DB:
- Replicate_Do_Table:
- Replicate_Ignore_Table:
- Replicate_Wild_Do_Table: test.%
- Replicate_Wild_Ignore_Table:
- Last_Errno: 0
- Last_Error:
- Skip_Counter: 0
- Exec_Master_Log_Pos: 1114
- Relay_Log_Space: 1563
- 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:
- Replicate_Ignore_Server_Ids:
- Master_Server_Id: 1
- 1 row in set (0.00 sec)
其中: Slave_IO_Running 和 Slave_SQL_Running 的状态都是 YES, 说明同步开启成功.
现在就可以去主服务器上的 test 库下创建表开测试同步了.
MySQL 主主备份配置实例
在主从备份配置完毕的基础上实现双主备份:
一, 在从服务器上创建用于同步的帐号:
>grant ...
二, 修改主服务器 mysql.cnf, 加入
- replicate_wild_do_table=test.%
- relay_log=mysqld-relay-bin
- log-slave-updates=YES
三, 记录从服务器二进制日志文件名与位置:
>show master status\G
四, 主服务器开启同步设置:
- >change master to
- master_host='192.168.0.226',
- master_user='user_name',
- master_password='ahaii',
- master_log_file='mysql-bin.000009',
- master_log_pos=107;
然后重启 mysqld 服务.
以上就是 mysql 主从, 主主设置的过程.
===================
四, 疑惑记录:
1, 关于 my.cnf 配置文件中 binlog-format 的值:
mysql 的 binlog-format 有三种格式, 分别是: row,statement 和 mixed
1.1,row: 基于行的复制(row-based replicate,RBP)
优点:
binlog 中可以不记录执行的 sql 语句的上下文相关的信息, 仅需要记录那一条记录被修改成什么了. 所以 rowlevel 的日志内容会非常清楚的记录下每一行数据修改的细节. 而且不会出现某些特定情况下的存储过程, 或 function, 以及 trigger 的调用和触发无法被正确复制的问题
缺点:
所有的执行的语句当记录到日志中的时候, 都将以每行记录的修改来记录, 这样可能会产生大量的日志内容, 比如一条 update 语句, 修改多条记录, 则 binlog 中每一条修改都会有记录, 这样造成 binlog 日志量会很大, 特别是当执行 alter table 之类的语句的时候, 由于表结构修改, 每条记录都发生改变, 那么该表每一条记录都会记录到日志中.
1.2,statement: 基于 SQL 语句的复制(statement-based replicate, SBR)
每一条会修改数据库的 SQL 语句都会被记录在 binlog 中.
优点:
不需要记录每一行的变化, 减少了 binlog 日志量, 节约了 IO, 提高性能.(相比 row 能节约多少性能与日志量, 这个取决于应用的 SQL 情况, 正常同一条记录修改或者插入 row 格式所产生的日志量还小于 Statement 产生的日志量, 但是考虑到如果带条件的 update 操作, 以及整表删除, alter 表等操作, ROW 格式会产生大量日志, 因此在考虑是否使用 ROW 格式日志时应该跟据应用的实际情况, 其所产生的日志量会增加多少, 以及带来的 IO 性能问题.)
缺点:
由于记录的只是执行语句, 为了这些语句能在 slave 上正确运行, 因此还必须记录每条语句在执行的时候的一些相关信息, 以保证所有语句能在 slave 得到和在 master 端执行时候相同 的结果. 另外 mysql 的复制, 像一些特定函数功能, slave 可与 master 上要保持一致会有很多相关问题 (如 sleep() 函数, last_insert_id(), 以及 user-defined functions(udf)会出现问题).
使用以下函数的语句也无法被复制:
- * LOAD_FILE()
- * UUID()
- * USER()
- * FOUND_ROWS()
- * SYSDATE() (除非启动时启用了 --sysdate-is-now 选项)
同时在 INSERT ...SELECT 会产生比 RBR 更多的行级锁.
1.3,mixed: 混合模式复制(mixed-based replicate,MBR)
mysql 的默认模式.
mixed 模式是以上两种模式的混合使用, 一般的语句修改使用 statment 格式保存 binlog, 如一些函数, statement 无法完成主从复制的操作, 则采用 row 格式保存 binlog,MySQL 会根据执行的每一条具体的 sql 语句来区分对待记录的日志形式, 也就是在 Statement 和 Row 之间选择一种. 新版本的 MySQL 中队 row level 模式也被做了优化, 并不是所有的修改都会以 row level 来记录, 像遇到表结构变更的时候就会以 statement 模式来记录. 至于 update 或者 delete 等修改数据的语句, 还是会记录所有行的变更.
2, 关于复制参数: replicate-do-db,replicate-do-table,replicate-wild-do-table
MySQL 官网的介绍: https://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html
2.1,replicate-do-db: 在从服务器上设置需要同步的数据库, 要同步多个库时可以写多行, 每行一个.
缺点: 不适用于跨库数据更新的复制.
2.2,replicate-do-table: 在从服务器上设置需要同步的表, 格式为 db_name.tb_name. 每行一个, 同步多个表时需要写多行.
缺点: 不适用于跨库数据更新的复制.
2.3,replicate-wild-do-table: 在从服务器上设置需要同步的数据库, 格式为 db_name.tb_name. 每行一个, 同步多个表时需要写多行. 可使用通配符如 "%","_", 来匹配多个表, 如同步所有以 share 字符串开头的数据库的所有 use 字符串开头的表 :replicate-wild-do-db=share%.user%.
支持跨库数据更新的复制.
参考链接: https://www.cnblogs.com/ahaii/p/6307648.html
来源: http://www.bubuko.com/infodetail-2607487.html