开源数据库MySQL 主从复制

概述

	MySQL 复制技术

集群目的

			负载均衡
				解决高并发
			高可用HA
				服务可用性
			远程灾备
				数据有效性

类型

			M
			M-S
			M-S-S...
			M-M
			M-M-S-S

概念

				1. 在主库上把数据更改（DDL DML DCL）记录到二进制日志（Binary Log）中。
				2. 备库I/O线程将主库上的日志复制到自己的中继日志（Relay Log）中。
				3. 备库SQL线程读取中继日志中的事件，将其重放到备库数据库之上。

案例

环境

		master1
			10.18.41.54
				systemctl start mysqld
		master2
			10.18.41.66
				systemctl start mysqld
		slave 1
			10.18.41.56
				systemctl start mysqld
		slave 2
			10.18.41.69
				systemctl start mysqld
		建议
			可以使用host文件的形式，也可以使用DNS服务器解析。

一主一从（M-S）(1)

		主(master1)
			部署一台新mysql服务器。准备好域名解析。
			准备数据1（验证主从同步使用）
				master1
					create database master1db;
					create table master1db.master1tab(name char(50));
					insert into master1db.master1tab values (1111);
					insert into master1db.master1tab values (2222);
			开启二进制日志
				master1
					[root@localhost ~]# vim /etc/my.cnf
					log_bin
					server-id=1
					[root@localhost ~]# systemctl restart mysqld
						重启生效
			创建复制用户
				master1
					grant replication slave, replication
					 client on *.* to 'rep'@'192.168.2.%'  identified by 'QianFeng@123';
			备份master数据库的数据
				master1
					mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
					scp -r 2017-1-1-mysql-all.sql master2:/tmp
						发送给另一台主机
					观察二进制日志分割点
						CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000002', MASTER_LOG_POS=154;
			准备数据2（验证主从同步使用）
				master1
					insert into master1db.master1tab values (33333333);
					insert into master1db.master1tab values (44444);
		从(master2)
			测试rep用户是否可用
				master2
					部署数据库应用
					mysql -h master1 -urep -p'QianFeng@123'
						预防账户问题。
			启动服务器序号
				[root@localhost ~]#vim /etc/my.cnf
					不用在从设备上开启二进制日志，没有人向master2请求日志。
				server-id=2
					服务器ID是必须设置的。
				[root@localhost ~]# systemctl restart mysqld
				mysql -uroot -p'QianFeng@123'
					测试服务器是否修改正确。能否正常登陆。
			恢复手动同步数据
				mysql>  set sql_log_bin=0;
				mysql>  source /tmp/2017-1-1-mysql-full.sql
			设置主服务器
				mysql> change master to
					master_host='master1',
					master_user='rep',
					master_password='QianFeng@123',
					master_log_file='localhost-bin.000002',
					master_log_pos=154;
					注意，二进制日志的位置，应该参照主服务器备份时生成的新位置。
			启动从设备
				mysql> start slave;
			查看启动状态（IO-YES/SQL-YES）
				show slave status\G;
					mysql> show slave status\G;
					*************************** 1. row ***************************
           Slave_IO_State: Waiting for master to send event
              Master_Host: master1
              Master_User: rep
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: localhost-bin.000003
      Read_Master_Log_Pos: 154
           Relay_Log_File: localhost-relay-bin.000003
            Relay_Log_Pos: 375
    Relay_Master_Log_File: localhost-bin.000003
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes

			返回主服务器（master1）更新数据，在从服务器（master2）观察是否同步。

一主一从（M-S）(2)

		需求
			实验2与上一个实验需求基本相同经。master1 作为主mysql，master2 作为从mysql。

			不同之处，使用了
			“gtid_mode=ON
			enforce_gtid_consistency=1”
			该属性自动记录position位置。不需要手动指定了。
		环境
			因与实验1功能相同
			请重置master2数据库
				systemctl stop mysqld
				rm -rf /var/lib/mysql/*
				systemctl start mysqld
				grep password /var/log/mysqld.log
				 mysqladmin -p'VsudOt+g%5Nw' password 'QianFeng@123'
		主
			1 启动二进制日志，服务器ID，GTID
				vim /etc/my.cnf
				log_bin
				server-id=1
				gtid_mode=ON
				enforce_gtid_consistency=1
				systemctl restart mysqld
			2 授权复制用户rep（略）
				grant replication slave,replication client on *.* to 'rep'@'192.168.122.%' identified by 'QianFeng@123';
				flush privileges;
			3 备份数据
				mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2 --flush-logs > `date +%F`-mysql-all.sql
					master-data=2设置为2，已经不需要这个标记了。
				scp 2017-1-1-mysql-all.sql    master2:/tmp
			4 模拟数据变化
				insert into master1db.master1tab values (6666666666);
		从
			1 测试rep用户是否可用
				master2
					mysql -h master1 -urep -p'QianFeng@123'
						预防账户问题。
						注意防火墙应该关闭
			2 启动二进制日志，服务器ID，GTID
				vim /etc/my.cnf
				log_bin
				server-id=2
				gtid_mode=ON
				enforce_gtid_consistency=1
				systemctl restart mysqld
					测试配置是否有问题，如果启动失败。请检查配置。
			3 还恢复手动同步数据
				set sql_log_bin=0;
				source /tmp/2017-1-1-mysql-full.sql
				select * from master1db.master1tab;
					该实验的关键效果即将出现。
			4 设置主服务器
				mysql> change master to
				master_host='master1',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1;
					注意，和前一个实验比少了两行
						mysql> change master to
				master_host='master1',
				master_user='rep',
				master_password='QianFeng@123',
				master_log_file='localhost-bin.000002',
				master_log_pos=154;
				start slave;
				show slave status\G;
			5 返回主服务器（master1）更新数据，在从服务器（master2）观察是否同步。

双主双从（MM-SS）

		双主（MM）
			前言
				前面的实验，主服务器单节点设置。假如主服务器故障会影响全局的写入事件。
				故设置双主。
				目前：已经设置master1为master2的主服务器，
				只需设置master2为master1的主服务器，
			1  设置master2为master1的主服务器
				在master2 上进行授权
					mysql> grant replication slave, replication  client on *.* to 'rep'@'192.168.2.%'  identified by 'QianFeng@123';
					mysql> flush privileges;
				master1
					mysql> change master to
				master_host='master2',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1;
				start slave;
					show slave status\G;
						如果状态不佳，重启一下master2的mysqld
						再去master2上。启动和停止从属状态。
				start slave;
				stop slave;
					测试
						master1上插入数据，在master2上观察
						master2上插入数据，在master1上观察
						双方同步成功，双主设置完成。
		双从（SS）
			1 同步现有数据库
				master1
					mysqldump -p'QianFeng@123' --all-databases --single-transaction --master-data=2  --flush-logs > `date +%F`-mysql-all.sql
					scp -r 2017-1-1-mysql-all.sql slave1:/tmp
					scp -r 2017-1-1-mysql-all.sql slave2:/tmp
				slave1
					# mysql -p'QianFeng@123' < /tmp/2017-1-1-mysql-all.sql
				slave2
					mysql -p'QianFeng@123' < /tmp/2017-1-1-mysql-all.sql
			2 启动从服务器ID，gtid
				slave1
					vim /etc/my.cnf
				server-id=3
				gtid_mode=ON
				enforce_gtid_consistency=1
				master-info-repository=TABLE
				relay-log-info-repository=TABLE
						从机把主的信息存在主信息仓库里。主信息库可以是文件也可以上表，具体由—master-info-repository参数值决定。—master-info-repository＝				file时 会生成master.info 和 relay-log.info2个文件，如果—master-info-repository＝table，信息就会存在mysql.master_slave_info表中。
					systemctl restart mysqld
				slave2
					vim /etc/my.cnf
				server-id=4
				gtid_mode=ON
				enforce_gtid_consistency=1
				master-info-repository=TABLE
				relay-log-info-repository=TABLE
					systemctl restart mysqld
			3 设置主服务器
				slave1
				mysql> change master to
				master_host='master1',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1 for channel 'master1';
				mysql> change master to
				master_host='master2',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1 for channel 'master2';
				start slave;
				show slave status\G;
				slave2
				mysql> change master to
				master_host='master1',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1 for channel 'master1';
				mysql> change master to
				master_host='master2',
				master_user='rep',
				master_password='QianFeng@123',
				master_auto_position=1 for channel 'master2';
				start slave;
				show slave status\G
			4 测试
				master1 插入数据
				master2插入数据
				slave1查看
				slave2查看
				错误示例
					mysql数据库错误代码解决.docx