一.原理
1.MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,
通过设置在Master上的binlog,使其处于打开状态;Slave通过一个I/O线程从Master上读取binlog,
然后传输到Slave的中继日志中,然后使用SQL线程读取中继日志,并应用到自身数据库中,从而实现主从数据同步功能
二.主服务器配置
(1.)在mysqld下增加如下参数
[mysqld]
server_id=1 #设置服务器id,为1表示主服务器,实例唯一ID,不能和canal的slaveId重复
log-bin=mysql-bin # 启动MySQ二进制日志系统
binlog-format=ROW #选择row模式
binlog-do-db=DB1 #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
binlog-ignore-db=mysql #不同步mysql系统数据库
(2.)常用参数含义
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可以防止主键重复。
二.从服务器配置
(1.)在mysqld下增加如下参数
[mysqld]
server-id=2 #实例唯一ID,不能和canal的slaveId重复,表示为从数据库
log-bin=mysql-bin #启动MySQL二进制日志系统
binlog-format=ROW #选择row模式
replicate-do-db=DB1 #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
replicate-ignore-db=mysql #不同步mysql系统数据库
(2.)常用参数含义
relay_log 配置中继日志
read_only 它防止改变数据
replicate_do_db 数据库白名单列表
replicate_ignore_db 数据库黑名单列表
replicate_do_table 表白名单列表
replicate_ignore_table 表黑名单列表
replicate_wild_do_table 使用通配符定义表白名单列表
replicate_wild_ignore_table 使用通配符定义表黑名单列表
log-slave-updates 表示slave将复制事件写进自己的二进制日志,简单地说,如果从库同时作为其它库的主库,那么需要添加该参数,否则不需要,
如果从库也开启了log-bin参数,此时直接往从库写数据,是可以记录log-bin日志的。
但是从库通过I0线程读取主库二进制日志文件,然后通过SQL线程写入的数据,则是不会记录log-bin日志的,
也就是说从库从主库上复制的数据无法记录到从库的log-bin日志上,也无法被从库的从库捕捉到更新。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
三.账号权限配置
在Slave上,使用Master上的root@%帐户登陆Master与其通信。
GRANT REPLICATION SLAVE ON . TO root@'%' IDENTIFIED BY 'root';
如果使用的是非root账号,该账号除了在Master上真实存在外,还应该拥有REPLICATION SLAVE 和 READ属性。
四.配置从服务器与主服务器通信
1.查看主服务器Master状态
show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb_bin.000001 | 245 | testmirror | |
+--------------------+----------+--------------+---------------
2.在从服务器上开启同步
(1.)MySQL从5.1.7以后开始就不支持在my.cnf中对master-host等参数进行配置,而是使用了CHANGE MASTER TO语句。
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
-> MASTER_USER='root',
-> MASTER_PASSWORD='root',
-> MASTER_LOG_FILE='mariadb_bin.000001', #这里最后填的两个就是第1步看到的File和position。
-> MASTER_LOG_POS=245;
(2.)开始同步
mysql> START SLAVE;
(3.)查看Slave的运行状态
mysql> SHOW SLAVE STATUS\G
(4.)结果主要关注以下几个:
这几句表示slave的I/O和SQL线程都已经开始运行。每次日志的位置(Exec_Master_Log_Pos)增加了,意味着一些事件被获取并执行了。
如果你在master上进行修改,可以在slave上看到各种日志文件的位置的变化,同样,也可以看到数据库中数据的变化。
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
……
Exec_Master_Log_Pos: 537
1.MYSQL主从同步是在MySQL主从复制(Master-Slave Replication)基础上实现的,
通过设置在Master上的binlog,使其处于打开状态;Slave通过一个I/O线程从Master上读取binlog,
然后传输到Slave的中继日志中,然后使用SQL线程读取中继日志,并应用到自身数据库中,从而实现主从数据同步功能
二.主服务器配置
(1.)在mysqld下增加如下参数
[mysqld]
server_id=1 #设置服务器id,为1表示主服务器,实例唯一ID,不能和canal的slaveId重复
log-bin=mysql-bin # 启动MySQ二进制日志系统
binlog-format=ROW #选择row模式
binlog-do-db=DB1 #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
binlog-ignore-db=mysql #不同步mysql系统数据库
(2.)常用参数含义
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可以防止主键重复。
二.从服务器配置
(1.)在mysqld下增加如下参数
[mysqld]
server-id=2 #实例唯一ID,不能和canal的slaveId重复,表示为从数据库
log-bin=mysql-bin #启动MySQL二进制日志系统
binlog-format=ROW #选择row模式
replicate-do-db=DB1 #需要同步的数据库名,如果有多个数据库,可重复此参数,每个数据库一行
replicate-ignore-db=mysql #不同步mysql系统数据库
(2.)常用参数含义
relay_log 配置中继日志
read_only 它防止改变数据
replicate_do_db 数据库白名单列表
replicate_ignore_db 数据库黑名单列表
replicate_do_table 表白名单列表
replicate_ignore_table 表黑名单列表
replicate_wild_do_table 使用通配符定义表白名单列表
replicate_wild_ignore_table 使用通配符定义表黑名单列表
log-slave-updates 表示slave将复制事件写进自己的二进制日志,简单地说,如果从库同时作为其它库的主库,那么需要添加该参数,否则不需要,
如果从库也开启了log-bin参数,此时直接往从库写数据,是可以记录log-bin日志的。
但是从库通过I0线程读取主库二进制日志文件,然后通过SQL线程写入的数据,则是不会记录log-bin日志的,
也就是说从库从主库上复制的数据无法记录到从库的log-bin日志上,也无法被从库的从库捕捉到更新。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。
三.账号权限配置
在Slave上,使用Master上的root@%帐户登陆Master与其通信。
GRANT REPLICATION SLAVE ON . TO root@'%' IDENTIFIED BY 'root';
如果使用的是非root账号,该账号除了在Master上真实存在外,还应该拥有REPLICATION SLAVE 和 READ属性。
四.配置从服务器与主服务器通信
1.查看主服务器Master状态
show master status;
+--------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb_bin.000001 | 245 | testmirror | |
+--------------------+----------+--------------+---------------
2.在从服务器上开启同步
(1.)MySQL从5.1.7以后开始就不支持在my.cnf中对master-host等参数进行配置,而是使用了CHANGE MASTER TO语句。
mysql> CHANGE MASTER TO MASTER_HOST='localhost',
-> MASTER_USER='root',
-> MASTER_PASSWORD='root',
-> MASTER_LOG_FILE='mariadb_bin.000001', #这里最后填的两个就是第1步看到的File和position。
-> MASTER_LOG_POS=245;
(2.)开始同步
mysql> START SLAVE;
(3.)查看Slave的运行状态
mysql> SHOW SLAVE STATUS\G
(4.)结果主要关注以下几个:
这几句表示slave的I/O和SQL线程都已经开始运行。每次日志的位置(Exec_Master_Log_Pos)增加了,意味着一些事件被获取并执行了。
如果你在master上进行修改,可以在slave上看到各种日志文件的位置的变化,同样,也可以看到数据库中数据的变化。
Slave_IO_Running=Yes
Slave_SQL_Running=Yes
……
Exec_Master_Log_Pos: 537