MySQL的实时主备就是保持两个mysql的数据同步,主备及一主一备,对主数据库的操作也会实时反应到备数据库中。数据库的主备是实现读写分离、容灾备份、负载均衡等的基础。

mysql的主备是mysql本身就提供的功能,我们只需要对数据库进行配置就可以了。mysql的主备的原理也不是很复杂,就是主数据库(master)会把自己的每一次改动都记录到 二进制日志 Binarylog 中。从数据库(Slave), 会用master上的账号登陆到 master上, 读取master的Binarylog,  写入到自己的中继日志 Relaylog, 然后自己的sql线程会负责读取这个中继日志,并执行一遍。  然后主数据库(master)上的更改就同步到从数据库(slave)上了。

下面记录下配置的过程(ubuntu14.04 mysql5.5):

1. 在主库(master)上创建用户备份的账户,后面从库(slave)会用这个账户来读取binlog。

grant replication slave on *.* to 'repl'@'%' identified by 'password';

2. 开启主库(master)的binlog

编辑/etc/mysql/my.cnf,在[mysqld]节点下添加:

server-id = 1
binlog_format=mixed
log_bin = /var/log/mysql/mysql-bin
binlog_do_db = sample
auto-increment-increment= 2
auto-increment-offset = 1

这里几项的意思:

server-id:编号,只要保证唯一就可以了

log_bin:binlog的路径和文件名

binlog_do_db:对哪个数据库记录binlog,如果有多个数据库,这一条可以配置多遍

binlog-ignore-db :需要忽略哪些数据库。我这边没有配置这一项,也可配置这一项忽略个别数据库,剩余的都记录,一般和上面的binlog_do_db取一个配就可以了

auto-increment-increment:主键的增涨值

auto-increment-offset:主键的起始值。

最后两项的作用是当有多个数据库做主库,都可以在上面插入数据时防止主键重复的。比如上面的配置,主键起始值为1,增长量为2,那么在这台数据库上,主键就都是奇数1,3,5,7,9.而另一台数据库就会配置主键起始值为2,增长量为2,这样它的主键就都是偶数:2,4,6,8。这时候它们之间同步数据的时候主键就不会冲突了。

到这边主库基本就配置完了,然后重启

service mysql restart
3. 为了保持初始数据一致,需要将主库的数据导出来
导出之前可以先锁一下数据库
FLUSH TABLES WITH READ LOCK;
然后导出
mysqldump --master-data -u root -p sample > sample.sql
解锁
unlock tables;
下面配置从库
4. 创建数据库,导入数据
create database sample default charset utf8;
mysql -u root -p sample < sample.sql
5. 编辑/etc/mysql/my.conf, 在[mysqld]节点下加入如下内容:
server-id = 2
replicate-do-db = sample
relay_log = /var/log/mysql/mysqld-relay-bin
log-slave-updates = ON
如果是主主互备 与主备配置一样,都需要relay_log和log_bin
这里面几项的内容是和上面的差不多的
server-id:编号
replicat-do-db:需要同步的库
replicate-ignore-db:忽略的库
relay_log:relaylog的全路径

log-slave-update:中继日志执行之后,这些变化是否需要计入自己的binarylog。 当你的B服务器需要作为另外一个服务器的主服务器的时候需要打开。  就是双主互相备份,或者多主循环备份。

6. 开启同步

在主库上执行:

mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000055 | 1528312 | sample | |
+------------------+----------+--------------+------------------+
在从库上执行:
CHANGE MASTER TO
MASTER_HOST='192.***.***.***',
MASTER_USER='repl',
MASTER_PASSWORD=****',
MASTER_LOG_FILE='mysql-bin.000055',
MASTER_LOG_POS=1528312;

master_host:主库的ip

master_user:一开使在主库上建的那个用户

master_password:那个用户的密码

master_log_file:binlog文件,就是上面在主库上执行show master status显示的file

master_log_pos:文件位置,也是上面显示的数字。

7. 最后启动slave

start slave

8. 查看同步状态

在从库上执行“show slave status \G;"。一般来说,Slave_IO_Running: Yes,Slave_SQL_Running: Yes,这两个显示yes基本就是正常的。

好了,结束。