MySQL主从备份设置

功能需求

外网服务器，有独立IP；内网服务器，无独立IP；外网服务器的MYSQL数据库实时更新，并要求同步到内网服务器中。
该需求可使用MYSQL的主从备份实现，其中外网服务器为主服务器（Master），内网服务器为从服务器（Slave）。

实现方式

MYSQL主从同步是在MySQL主从复制（Master-Slave Replication）基础上实现的，通过设置在Master上的binlog，使其处于打开状态；Slave通过一个I/O线程从Master上读取binlog，然后传输到Slave的中继日志中，然后使用SQL线程读取中继日志，并应用到自身数据库中，从而实现主从数据同步功能。

如下图所示：

具体操作

1、主服务器（Master）配置

首先应该保证从服务器对主服务器数据库的访问，即开启MySQL的远程访问，grant和flush以下就行了。

然后需要修改Master的数据库配置，MySQL默认配置文件在/etc/my.cnf，打开后在[mysqld]之后加入如下条目：

log_bin = /var/log/mariadb/mariadb-bin.log
server-id = 1 
binlog_do_db = testmirror

也可以进行更详细的设置：

max_binlog_size = 500M
binlog_cache_size = 128K
binlog-ignore-db = mysql
expire_logs_day=2
binlog_format="MIXED"

设置完后重启MYSQL服务。

各参数意义：

参数	意义
server-id	数据库唯一ID，一组主从中此标识号不能重复。其中1 代表主数据库（源） 2代表辅数据库（目的）
log_bin	开启bin-log，并指定文件目录和文件名前缀
binlog_do_db	需要同步的数据库名字，可以是多个，之间用分号分割
binlog_ignore_db	不需要同步的数据库名字
max_binlog_size	每个bin-log最大大小，当此大小等于500M时会自动生成一个新的日志文件。一条记录不会写在2个日志文件中，所以有时日志文件会超过此大小。
binlog_cache_size	日志缓存大小
binlog-do-db	需要同步的数据库名字，如果是多个，就以此格式在写一行即可。
binlog-ignore-db	不需要同步的数据库名字，如果是多个，就以此格式在写一行即可。
expire_logs_day	设置bin-log日志文件保存的天数，此参数mysql5.0以下版本不支持。
binlog_format	bin-log日志文件格式，设置为MIXED可以防止主键重复。

如果需要有选择性地进行同步，可以在Master端进行（使用binlog_do_db或binlog_Ignore_DB），也可以在Slave端进行（使用下面会说到的Replicate_Ignore_DB）。

2、从服务器（Slave）配置

修改slave的配置文件，同样在/etc/my.cnf下

server-id = 2
read_only = 1
relay_log = /var/log/mariadb/relay.log

当然也可以开启bin_log使它同时作为其它从服务器的Master。
设置完后重启MYSQL服务。

参数	意义
relay_log	配置中继日志
log-slave-updates	表示slave将复制事件写进自己的二进制日志，简单地说，如果从库同时作为其它库的主库，那么需要添加该参数，否则不需要
read_only	它防止改变数据

针对log-slave-updates参数，如果从库也开启了log-bin参数，此时直接往从库写数据，是可以记录log-bin日志的。但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，则是不会记录log-bin日志的，也就是说从库从主库上复制的数据无法记录到从库的log-bin日志上，也无法被从库的从库捕捉到更新。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。

前面说了利用Master的binlog_do_db和binlog_Ignore_DB可以实现过滤，只同步特定的数据库，该功能也可以在Slave端实现。

参数	意义
replicate_do_db	数据库白名单列表
replicate_ignore_db	数据库黑名单列表
replicate_do_table	表白名单列表
replicate_ignore_table	表黑名单列表
replicate_wild_do_table	使用通配符定义表白名单列表
replicate_wild_ignore_table	使用通配符定义表黑名单列表

3、账号权限设置

在Slave上，使用Master上的root@%帐户登陆Master与其通信。

GRANT REPLICATION SLAVE ON . TO root@’%’ IDENTIFIED BY ‘password’;

如果使用的是非root账号，该账号除了在Master上真实存在外，还应该拥有REPLICATION SLAVE 和 READ属性。

4、查看Master状态

在Master的MySQL命令符下，查看Master状态：

MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb_bin.000001 |      245 | testmirror         |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

这里需要记住两个参数，一个File，一个Position。

5、在Slave上开启同步

MySQL从5.1.7以后开始就不支持在my.cnf中对master-host等参数进行配置，而是使用了CHANGE MASTER TO语句。

mysql> CHANGE MASTER TO MASTER_HOST='server1',
    -> MASTER_USER='user_name',
    -> MASTER_PASSWORD='p4ssword',
    -> MASTER_LOG_FILE='mariadb_bin.000001',
    -> MASTER_LOG_POS=245;

这里最后填的两个就是第3步看到的File和position。
然后就可以开始同步了：

mysql> START SLAVE;

可以看一看Slave的运行状态

mysql> SHOW SLAVE STATUS\G

结果很长，主要关注以下几个：

Slave_IO_Running=Yes
Slave_SQL_Running=Yes

……
Exec_Master_Log_Pos: 537

这几句表示slave的I/O和SQL线程都已经开始运行。每次日志的位置（Exec_Master_Log_Pos）增加了，意味着一些事件被获取并执行了。如果你在master上进行修改，可以在slave上看到各种日志文件的位置的变化，同样，也可以看到数据库中数据的变化。