基于日志点的主从复制
在主服务器上创建用户
CREATE USER ‘repl’ @ ‘IP段’ IDENTIFIED BY ‘pass’;
GRANT REPLICATION SLAVE ON *.* TO ‘repl’ @ ‘IP段’
如本地测试网段为192.168.0.*时
|
1 2 |
CREATE USER repl@'192.168.0.%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.0.%'; |
配置主从数据库服务器
配置主数据库服务器(my.cnf)
bin_log = mysql-bin
server_id = 1 #值在集群中唯一
配置从服务器服务器
bin_log = mysql-bin
server_id = 2
relay_log = mysql-relay-bin
log_slave_update = on #可选
read_only = on #可选
具体配置如下（从库将server-id修改为2即可），配置完成重启MySQL服务
|
1 2 3 4 5 6 7 8 9 10 |
#binary log log-bin=mysql-bin max_binlog_size = 1000M binlog_format =row # row, statement, mixed，其中 row 产生的数据量最大，但不会导致主从数据不一致的问题 expire_logs_days = 7 sync_binlog = 1 #Replicate server-id=1 relay_log=/var/lib/mysql/mysqld-relay-bin |
谨慎设置binlog_format=MIXED：如果你采用默认隔离级别REPEATABLE-READ，那么建议binlog_format=ROW。如果你是READ-COMMITTED隔离级别，binlog_format=MIXED和binlog_format=ROW效果是一样的，binlog记录的格式都是ROW，对主从复制来说是很安全的参数。
初始化从服务器数据
备份方法一：mysqldump –master-data=2 -single-transaction
备份方法二：xtrabackup –slave-info #对纯innodb更推荐
在主服务上进行备份
|
1 |
mysqldump --single-transaction --master-data --triggers --routines --all-databases -uroot -p > all.sql |
若出现类似如下报错，需将socket=/var/lib/mysql/mysql.sock的配置移到[client]中：
|
1 |
mysqldump: Got error: 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) when trying to connect |
然后通过scp命令将all.sql文件传到从库上
scp all.sql root@192.168.0.7:/root
若scp命令不可用，则需安装yum install openssh-clients，也可通过ftp等方式复制该sql文件
在从服务器上导入数据
mysql -uroot -p < all.sql
启动复制链路
CHANGE MASTER TO MASTER_HOST = ‘master_host_ip’,
MASTER_USER = ‘repl’,
MASTER_PASSWORD = ‘pass’,
MASTER_LOG_FILE = ‘mysql_log_file_name’,
MASTER_LOG_POS = 4;
如MySQL端口并非默认的3306，需同时添加MASTER_PORT＝***的设置，其中后面两段可能过more all.sql查看CHANGE MASTER TO…后面的内容，本例如下：
|
1 2 3 4 5 |
CHANGE MASTER TO MASTER_HOST = '192.168.0.131', MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=106; |
在从服务器MySQL命令行上通过先执行start slave再通过SHOW SLAVE STATUS可查看当前状态，还可在主从服务器上使用show processlist来查看进程
在执行show slave status时发现Got fatal error 1236…的报错，打开错误日志：
|
1 2 3 |
60716 18:14:52 [ERROR] Error reading packet from server: Misconfigured master - server id was not set ( server_errno=1236) 160716 18:14:52 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Misconfigured master - server id was not set', Error_code: 1236 160716 18:14:52 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000004', position 106 |
回到主服务器的MySQL命令行执行如下命令发现server_id为1，并没有问题
|
1 |
SHOW GLOBAL VARIABLES like 'server\_id'; |
最终发现由于在主服务器的my.cnf中将server-id=1配到[mysqld_safe]中去了，移入[mysqld]中重启MySQL服务错误消失。两条服务器中执行show processlist会发现都多出一有关主从交互的记录。
|
1 2 3 4 |
# 跳过指定数量的事务： mysql>slave stop; mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1 #跳过一个事务 mysql>slave start |
常用的一些配置
|
1 2 3 4 5 6 7 8 9 10 11 12 |
# 主库 binlog-do-db binlog-ignore-db # 从库 replicate_do_db replicte_do_table repicate_ingore_db replicate_ignore_table replicate_write_db replicate_wild_do_table # 可使用通配符 replicate_wild_ignore_table # 可使用通配符 |
基于GTID的主从复制
GTID=source_id:transaction_id
基于GTID的主从复制仅从MySQL5.6开始进行支持（可使用SELECT VERSION()进行查询）。
在主数据库服务器上建立复制账号，方法与基于日志的方式相同
|
1 2 |
CREATE USER repl@'192.168.0.%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.0.%'; |
配置主从数据库服务器
配置主数据库服务器
log-bin=mysql-bin
server-id = 1
gtid_mode=on
enforce-gtid-consistency = on
log-slave-updates=on #5.7中无需启用
|
1 2 3 4 5 6 7 |
log-bin=mysql-bin relay_log=/var/lib/mysql/mysqld-relay-bin master_info_repository = TABLE relay_log_info_repository = TABLE server-id = 1 #从服务器上修改为2 gtid_mode=on enforce_gtid_consistency = on |
配置从数据库服务器
server-id = 2
relay_log=/var/lib/mysql/mysqld-relay-bin
gtid_mode=on
enforce-gtid-consistency
其它建议配置参数
read_only = on #从数据库
master_info_repository = TABLE
relay_log_info_repository = TABLE
初始化从服务器数据
备份方法一：mysqldump –master-data=2 -single-transaction
备份方法二：xtrabackup –slave-info #对纯innodb更推荐
|
1 2 |
mysqldump --single-transaction --master-data=2 --triggers --routines --events --all-databases -uroot -p &gt; all2.sql #主库服务器导出 mysql -uroot -p > all2.sql #从库服务器导入 |
启动基于GTID的复制
CHANGE MASTER TO MASTER_HOST = ‘master_host_ip’,
MASTER_USER = ‘repl’,
MASTER_PASSWORD = ‘pass’,
MASTER_AUTO_POSITION=1;
|
1 2 3 4 |
CHANGE MASTER TO MASTER_HOST = '192.168.0.131', MASTER_USER = 'repl', MASTER_PASSWORD = '123456', MASTER_AUTO_POSITION=1; |
配置多线程主从复制
在从数据库中进行如下操作
|
1 2 3 4 5 6 7 8 9 |
stop slave; show slave status; #Slave_IO_State为空 show variables like 'slave_parallel_type'; #默认为DATABASE set global slave_parallel_type='logical_clock'; show variables like 'slave_parallel_workers'; set global slave_parallel_workers=4; start slave; show slave status; #Slave_IO_State: Waiting for master to send event show processlist\G; #4组线程同时运行 |
备注：
1.如果MySQL版本太低，需删除重新安装高版本来进行基于GTID的主从复制的测试
|
1 2 3 4 5 6 7 |
yum remove mysql mysql-server rpm -qa | grep mysql #yum remove 加结果中出的相关包 yum clean all wget https://dev.mysql.com/get/mysql57-community-release-el6-8.noarch.rpm yum localinstall mysql57-community-release-el6-8.noarch.rpm #yum --disablerepo=\* --enablerepo='mysql*-community*' list available yum install mysql-community-server |
2.在从服务器上导入sql时出现报错
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.
执行reset master然后在主服务器上重新dump一份sql文件
3.执行start slave时出现如下报错
ERROR 1872 (HY000): Slave failed to initialize relay log info structure from the repository
执行reset slave再启动start slave
4.启动MySQL时出现如下报错
|
1 2 |
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). [ERROR] --initialize specified but the data directory has files in it. Aborting. |
其中第一个是Warning,可以忽略，也可在/etc/my.cnf中加入explicit_defaults_for_timestamp = 1来进行解决，第二个是ERROR，通知删除命令可以解决（虽然只是一些日志文件但也可以暂移动到其它文件夹下防止后面还需要查看）。
5.MASTER_AUTO_POSITION错误
|
1 |
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active. |
执行change master to master_auto_position=0;即可
5.Error_code: 2003
a.查看密码是否正确
b.使用mysql -h 192.168.0.3 -u repl -p进行连接看是否可连接，若不可连接尝试关闭iptables(service iptables stop)再次连接，如可连接在/etc/sysconfig/iptables中添加一行
|
1 |
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT |
日志报错示例：
|
1 |
[ERROR] Slave I/O for channel '': error connecting to master 'repl@192.168.0.3:3306' - retry-time: 60 retries: 8, Error_code: 2003 |
6.ERROR 1794 (HY000)
未有机会复制这一错误，无法明确解决的方法是什么，解决时采取的措施有:
在从服务器上reset master
yum remove mysql-mmm* -y
由于进行过主主的配置，所以主服务器做过从服务器，所以执行了
delete from mysql.slave_master_info;
reset master;
reset slave;
重新导出数据库，然后在从服务器上重启mysql服务
|
1 |
ERROR 1794 (HY000): Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log. |
7.Nasty PTR record
这个是由于配置了DHCP动态分配IP的方式，只需将/etc/sysconfig/network-scripts/ifcfg-eth0中的配置修改为静态重启网络服务，然后删除.ssh/known_hosts中对应行再次连接即可
|
1 |
Nasty PTR record "192.168.0.7" is set up for 192.168.0.7, ignoring |
