MySQL数据库设置主从同步
[toc]
MySQL主从同步的作用
- 可以作为一种备份机制，相当于热备份
- 可以用来做读写分离，均衡数据库负载
MySQL主从同步的步骤
一、准备操作
- 主从数据库版本一致，建议版本5.5以上
- 主从数据库数据一致
二、主数据库master修改
- 修改MySQL配置
#主从数据库之间的唯一ID
server_id=96
#要同步的数据库
binlog-do-db=p2s
#不要同步的数据库
binlog-ignore-db=mysql
#要两步的数据库的日志文件名
log-bin=p2s-3-bin
# 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire-logs-days = 30
#主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
# 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
#可以从服务器再再同步下去
relay_log=p2s-3-relay-bin
log_slave_updates=1
read_only=1
- 启动/重启Master数据库服务，登录数据库，创建数据同步用户，并授予相应的权限
mysql> grant replication slave, replication client \
on *.* to 'username'@'%' identified by 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| p2s-3-bin.001 | 414 | p2s | mysql | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
三、从数据库slave修改
- 修改配置文件
#在 [mysqld] 中增加以下配置项
#设置server_id，一般设置为IP
server_id=206
#复制过滤：需要备份的数据库，输出binlog
binlog-do-db=p2s
#制过滤：不需要备份的数据库，不输出（mysql库一般不同步）
binlog-ignore-db=mysql
#开启二进制日志，以备Slave作为其它Slave的Master时使用
log-bin=edu-mysql-slave1-bin
#为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size = 1M
#主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
#二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=30
#跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
#如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
#relay_log配置中继日志
relay_log=mysql-relay1-bin
#log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
#防止改变数据(除了特殊的线程)
read_only=1
- 保存后重启MySQL服务
service mysql restart
- 登录Slave数据库，添加相关参数
mysql> change master to \
master_host='{110.1.2.3: 主数据库IP}', \
master_user='{user: 访问主数据库的帐号}',\
master_password='{passwd: 相应的密码}', \
master_port={3306: 端口号},\
master_log_file='{p2s-1-bin.000001: 主数据库的日志文件名}', \
master_log_pos={106: 日志的postion}, \
master_connect_retry={30: 错误重连时间(秒)};
mysql> show slave status\G;
主要看以下两个参数，这两个参数如果是Yes就表示主从同步正常
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
可查看master和slave上线程的状态。在master上，可以看到slave的I/O线程创建的连接：
mysql> show processlist\G;
注意：如果在Slave没做只读控制的情况下，千万不要在Slave中手动插入数据，那样数据就会不一致，主从就会断开，就需要重新配置了。
