CentOS7.3Mariadb5.5.56双主互备

说明： 主要记录思路，所有软件直接通过yum安装，Mariadb为5.5.56版，本次模拟为双主没有从。 如果是多主多从，且所有库之间数据要一至，那么就需要用到log_slave_updates=1 参数，将该参数加到[mysqld]下面，重启服务即可。举个简单的例子，有4台mysql服务器，分别为1、2、3、4 。1、2为主主互备，3、4分别做1、2的从备，如果没有log_slave_updates=1这个参数，在1号主服务器上写入的数据会同步到2、3，但不会同步到4。2号主服务器写的数据会同步到1、4，但不会同步到3。该参数的作用是将其它主服务的操作也写入当前主服务器的binary log，当前主服务器的从备就会将其它主服务器的操作写入当前的从备。这样就会实现所有的数据库之间数据一至。

1、安装Mariadb，两台服务器做同样操作

yum install -y mariadb mariadb-server

2、修改配置，两台服务器除server_id和auto-increment-offset 值外，其它配置一样 通过 mysqld --verbose --help |grep -A 1 'Default options' 命令来查看mysql使用的配置文件默认路径

vi /etc/my.cnf 在[mysqld]下面加入如下内容；

log-bin=mysql-bin	#启用二进制日志
binlog-format=ROW	#二进制日志格式
server_id=1		#服务ID，不同服务器不可相同
binlog-do-db=appdb      #配置需要同步的数据库，留空则同步所有，如果有多个，则一行一个，不要直接用逗号分隔写成 db1,db2这种格式，可能会被识别成一个库而导致失败
binlog-ignore-db=mysql       #配置不需要同步的数据库，同上，一行一个
auto-increment-increment = 10	#字段变化增量值，该值必须等于或大于主服务器数量，
auto-increment-offset = 1		#服务器1值为1，则服务器2值为2（不能相同）
slave-skip-errors = all			#忽略所有复制产生的错误，不建议开启
skip-name-resolve			#解决 Name or service not known 错误
log_slave_updates = 1			#多主多从，用于将其它主数据库的操作写入binary log
sync_binlog = 1		# 确保binlog日志写入后与硬盘同步

因为每台数据库服务器都可能在同一个表中插入数据，如果表有一个自动增长的主键，那么就会在多服务器上出现主键冲突。 解决这个问题的办法就是让每个数据库的自增主键不连续。 我假设需要将来可能需要10台服务器做备份， 所以auto-increment-increment 设为10. 而 auto-increment-offset=1 表示这台服务器的序号。 从1开始， 不超过auto-increment-increment。这样做之后， 我在这台服务器上插入的第一个id就是 1， 第二行的id就是 11了， 而不是2.（同理，在第二台服务器上插入的第一个id就是2， 第二行就是12， 这个后面再介绍） 这样就不会出现主键冲突了。

在MySQL中系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。而当设置为“1”的时候，是最安全但是性能损耗最大的设置。因为当设置为1的时候，即使系统Crash，也最多丢失binlog_cache中未完成的一个事务，对实际数据没有任何实质性影响。对于高并发事务的系统来说，“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。

3、启动服务，修改root密码、可访问来源，两个服务器做同样操作

systemctl start mariadb
mysql -u root -e "GRANT ALL PRIVILEGES ON *.* to 'root'@'localhost' IDENTIFIED BY 'Password' WITH GRANT OPTION;"	#这种格式可以不登录数据库进行命令操作，设置root用户密码且root只能在localhost环境下登录。
mysql -u root -pPassword -e "GRANT ALL PRIVILEGES ON *.* to 'root'@'%' IDENTIFIED BY 'Password' WITH GRANT OPTION;"	#设置root用户在任何环境下都能登录。

4、新建同步用户，两个服务器做同样操作

mysql -u root -pPassword -e "GRANT REPLICATION SLAVE ON *.* to 'mysync'@'%' identified by 'password';"

5、开始配置同步；配置同步前需要确保端口开放 这里假设服务器1的IP为1.1.1.1 ，服务器2IP为2.2.2.2 服务器1复制服务器2数据库的配置 登录服务器2mysql

mysql -u root -p
>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |   245    | appdb  |    mysql,infomation_schema,test,performance_schema    |
+------------------+----------+--------------+------------------+

登录服务器1mysql 如果mysql使用的是默认端口，连接主数据库时可以不用加master_port这一项

mysql -u root -p
>change master to master_host='2.2.2.2',master_port=3306,master_user='mysync',master_password='password',master_log_file='mysql-bin.000002',master_log_pos=245;
>start slave;
>show slave status\G
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 2.2.2.2
				  Master_User: mysync
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000003
		  Read_Master_Log_Pos: 245
			   Relay_Log_File: mariadb-relay-bin.000004
				Relay_Log_Pos: 529
		Relay_Master_Log_File: mysql-bin.000003
			 Slave_IO_Running: Yes
			Slave_SQL_Running: Yes
			  Replicate_Do_DB:
		  Replicate_Ignore_DB:
		   Replicate_Do_Table:
	   Replicate_Ignore_Table:
	  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
				   Last_Errno: 0
				   Last_Error:
				 Skip_Counter: 0
		  Exec_Master_Log_Pos: 245
			  Relay_Log_Space: 1109
			  Until_Condition: None
			   Until_Log_File:
				Until_Log_Pos: 0
		   Master_SSL_Allowed: No
		   Master_SSL_CA_File:
		   Master_SSL_CA_Path:
			  Master_SSL_Cert:
			Master_SSL_Cipher:
			   Master_SSL_Key:
		Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
				Last_IO_Errno: 0
				Last_IO_Error:
			   Last_SQL_Errno: 0
			   Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
			 Master_Server_Id: 1
1 row in set (0.00 sec)

看到两个YES表示成功。

服务器2复制服务器1数据库的配置，把上面的操作反过来操作一遍即可。 登录服务器1mysql

mysql -u root -p
>show master status;


+------------------+----------+--------------+-------------------------------------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                                                                                                                                                                                             +------------------+----------+--------------+-------------------------------------------------+
| mysql-bin.000002 |   245    | appdb    | mysql,infomation_schema,test,performance_schema |
+------------------+----------+--------------+-------------------------------------------------+

登录服务器2mysql 如果mysql使用的是默认端口，连接主数据库时可以不用加master_port这一项

mysql -u root -p
>change master to master_host='1.1.1.1',master_port=3306,master_user='mysync',master_password='password',master_log_file='mysql-bin.000002',master_log_pos=245;
>start slave;
>show slave status\G
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 1.1.1.1
				  Master_User: mysync
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000003
		  Read_Master_Log_Pos: 245
			   Relay_Log_File: mariadb-relay-bin.000004
				Relay_Log_Pos: 529
		Relay_Master_Log_File: mysql-bin.000003
			 Slave_IO_Running: Yes
			Slave_SQL_Running: Yes
			  Replicate_Do_DB:
		  Replicate_Ignore_DB:
		   Replicate_Do_Table:
	   Replicate_Ignore_Table:
	  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
				   Last_Errno: 0
				   Last_Error:
				 Skip_Counter: 0
		  Exec_Master_Log_Pos: 245
			  Relay_Log_Space: 1109
			  Until_Condition: None
			   Until_Log_File:
				Until_Log_Pos: 0
		   Master_SSL_Allowed: No
		   Master_SSL_CA_File:
		   Master_SSL_CA_Path:
			  Master_SSL_Cert:
			Master_SSL_Cipher:
			   Master_SSL_Key:
		Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
				Last_IO_Errno: 0
				Last_IO_Error:
			   Last_SQL_Errno: 0
			   Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
			 Master_Server_Id: 1
1 row in set (0.00 sec)

6、配置semi_sync Mysql的replication协议是异步的，虽然异步效率、性能很好，但是无法保证主从数据一致性，从5.5之后，Mysql为了保证主从库数据的一致性，引进了semi_sync功能，开启该功能后，只要master接收到其中一台SLAVE的返回信息，就会commit；否则需等待直至切换成异步再提交。 优点： 当事务返回客户端成功后，则日志一定在至少两台主机上存在。 MySQL的Semi-sync适合小事务，且两台主机的延迟又较小，则Semi-sync可以实现在性能很小损失的情况下的零数据丢失。 缺点： 完成单个事务增加了额外的等待延迟，延迟的大小取决于网络的好坏。 配置方法： 在主库安装semisync_master插件：

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';	#linux
mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.dll';	#windows

在备库安装semisync_slave插件

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';	#linux
mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';	#windows

临时启动：

mysql>set global rpl_semi_sync_master_enabled=on ;	#主库
mysql>set global rpl_semi_sync_slave_enabled=on ;	#从库

配置完成后重启slave才能生效

mysql>stop slave;
mysql>start slave;

每次自动启动需要修改My.cnf文件： 主库上，新增如下参数：

vi /etc/my.cnf
	[mysqld]
	...
	rpl_semi_sync_master_enabled=1
	rpl_semi_sync_master_timeout=1000
	...

备库上新增：

vi /etc/my.cnf
	[mysqld]
	...
	rpl_semi_sync_slave_enabled=1

重启master和slave，semi_sync插件会自动加载

重启后，master用如下命令查看：

mysql> show variables like '%rpl_semi%';

---

错误1：

/usr/sbin/mysqld: File '/var/lib/mysql/binlogs/mysql-bin.index' not found (Errcode: 13) 120719 5:50:55 [ERROR] Aborting 原来是因为文件夹权限的问题。刚才新建的目录binlogs是在root下面的。 修改文件夹权限，OK

错误2：

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

新安装没有初始化导致，执行mysql_install_db 命令，指定datadir即可 mysql_install_db --user=mysql --datadir=/data/mysql/data

###使用Navicat for MySQL复制数据到其它服务器流程： 先同步结构，再同步数据 如果同步结构中遇到提示部分视图、不存在之类的，先单独把缺失的同步完再全部同步。 一般需要同步的帐户对对应的库有对应的权限。如果权限不够，可以先赋予所有权限，同步完之后再改回来。 要保证新库有旧库对应的操作帐号，不然同步过程也会报错。

服务器断电，导致从库复制失败，报如下错误###

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

查看slave 状态

>show slave status\G
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 192.168.1.153
				  Master_User: root
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000016
		  Read_Master_Log_Pos: 548
			   Relay_Log_File: in-ser-12-relay-bin.000009
				Relay_Log_Pos: 9572368
		Relay_Master_Log_File: mysql-bin.000014		#这个binlog文件名记录下来，后面重新连接需要用到
			 Slave_IO_Running: Yes
			Slave_SQL_Running: No
			  Replicate_Do_DB:
		  Replicate_Ignore_DB:
		   Replicate_Do_Table:
	   Replicate_Ignore_Table:
	  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
				   Last_Errno: 1594
				   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
				 Skip_Counter: 0
		  Exec_Master_Log_Pos: 101422689			#同步失败的pos值，也需要记录下来，
			  Relay_Log_Space: 9728728
			  Until_Condition: None
			   Until_Log_File:
				Until_Log_Pos: 0
		   Master_SSL_Allowed: No
		   Master_SSL_CA_File:
		   Master_SSL_CA_Path:
			  Master_SSL_Cert:
			Master_SSL_Cipher:
			   Master_SSL_Key:
		Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
				Last_IO_Errno: 0
				Last_IO_Error:
			   Last_SQL_Errno: 1594
			   Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
  Replicate_Ignore_Server_Ids:
			 Master_Server_Id: 1
				  Master_UUID: 093d63ce-026d-11e9-91fa-54bf64719b1f
			 Master_Info_File: /mydata/mysql/data/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State:
		   Master_Retry_Count: 86400
				  Master_Bind:
	  Last_IO_Error_Timestamp:
	 Last_SQL_Error_Timestamp: 190305 09:28:40
			   Master_SSL_Crl:
		   Master_SSL_Crlpath:
		   Retrieved_Gtid_Set:
			Executed_Gtid_Set:
				Auto_Position: 0
		 Replicate_Rewrite_DB:
				 Channel_Name:
		   Master_TLS_Version:
1 row in set (0.00 sec)

既然是同步失败，那只需要从失败的位置开始重新同步即可，不过同步前要先用mysqlbinlog检查一下主库的binlog是否损坏

mysqlbinlog  --base64-output=decode-rows -vvv mysql-bin.000014 > test.sql		#这里就是用的上面检查slave状态中显示的binlog文件，如果没保错，说明主库的binlog文件未损坏，就可以继续下面的操作了

停止原有的同步，重置从库同步配置，重新配置主库信息，参考报错时slave status中的Relay_Master_Log_File和Exec_Master_Log_Pos这两个值。

mysql stop slave;
reset slave all;
change master to master_host='192.168.1.153',master_port=3306,master_user='root',master_password='123456',master_log_file='mysql-bin.000014',master_log_pos=101422689;
start slave;

解决binlog文件过多，导致占用大量磁盘空间

1、修改my.cnf文件，添加binlog过期时间；需要重启服务

expire_logs_days = x	#x表示过期天数；

2、直接通过命令修改expire_logs_days 值；

show variables like '%expire_logs_days%';	#查看一下默认过期时间，默认一般是为0 ，表示永不过期
set global expire_logs_days=10;				#设置过期时间为10天，自动删除超过10天的binlog
flush logs	# 触发expire_logs_days配置，会自动重新生成一个新的binlog文件；

3、已经占用了大量磁盘空间，需要及时手动释放磁盘空间，相比flush logs，可以逐步删除，例如有上百个binlog文件，每个1GB，目前正在使用第100个文件，直接进行flush logs操作有可能会导致io占用，可以用purge每次删除一部分，

purge master logs to 'mysql-bin.000010'
purge master logs to 'mysql-bin.000020'
purge master logs to 'mysql-bin.000030'
purge master logs to 'mysql-bin.000040'

操作前先在从库上使用show slave status\G 查看一下从库目前使用的是哪一个binlog文件，然后使用purge命令在主库上来删除多余的binlog文件 例如目前正在使用的binlog文件为 mysql-bin.000010,则直接执行如下命令，删除mysql-bin.000010之前的所有binlog

PURGE MASTER logs to 'mysql-bin.000010'

---

硬盘空间不足，导致bin-log文件被截断。从库同步中断，提示如下错误

Got fatal error 1236 from master when reading data from binary log: 'binlog truncated in the middle of event; consider out of disk space on master; the first event 'mysql-bin.001266' at 1769121, the last event read from './mysql-bin.001266' at 1769200, the last byte read from './mysql-bin.001266' at 1769472.'

到备库执行如下操作

stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001266',MASTER_LOG_POS=1769472;  # 根据错误提示更正binlog和log_pos的值
start slave;
show slave status\G

---

mysql federated 引用其它库的表时，做主从同步时，只能在源库更新这张表，不能在引用的位置更新；因为在引用位置更新时就已经对源库进行了操作，从库去同步时源库已经被修改，再做同样的操作会导致从库崩溃，陷入无限重启失败的情况。

临时解决办法，在主库备份该表完整数据，在从库使用 replicate_ignore_db=DBNAME临时忽略对于该引用库的同步操作。等从库可以正常启动后再去修改配置文件去掉该配置并重启从库，然后在主库删除引用库的整个库（为了保证数据的完整），再导入之前备份的数据。