Mysql主从复制

mysql高可用

mysql高可用

介绍

MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表

主从复制主要用途

读写分离

在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。

高可用

随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能.

MySQL 主从形式

一主多从，提高系统的读性能

多主一从

1. mysql安装过程
   1. 解压

tar -zxvf mysql-5.6.34-linux-glibc2.5-x86_64.tar.gz -C /usr/local

重命名

mv mysql-5.6.34-linux-glibc2.5-x86_64 mysql

新建mysql用户组合mysql用户

# groupadd mysql # useradd -g mysql mysql

etc下添加配置文件my.cnf

这里我们可以去/usr/local/mysql/support-files下拷贝一份my-default.cnf到/etc下并重命名 my.cnf

cp /mysql/support-files/my-default.cnf /etc/my.cnf

配置/etc目录下的my.cnf

编辑 vi /etc/my.cnf，把下面的代码全部复制到my.cnf文件下

[mysql] # 设置mysql客户端默认字符集 default-character-set=utf8 socket=/var/lib/mysql/mysql.sock [mysqld] skip-name-resolve #设置3306端口 port = 3306 socket=/var/lib/mysql/mysql.sock # 设置mysql的安装目录 basedir=/usr/local/mysql # 设置mysql数据库的数据的存放目录 datadir=/usr/local/mysql/data # 允许最大连接数 max_connections=200 # 服务端使用的字符集默认为8比特编码的latin1字符集 character-set-server=utf8 # 创建新表时将使用的默认存储引擎 default-storage-engine=INNODB lower_case_table_name=1 max_allowed_packet=16M

 修改当前目录拥有着为mysql用户

#cd /usr/local/mysql #chown -R mysql:mysql ./

安装autoconf库

yum -y install autoconf

安装数据库

#./scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ --datadir=/usr/local/mysql/data/

修改当前data目录的拥有者为mysql用户

#cd /usr/local/mysql # chown -R mysql:mysql data

配置MySQL

授予my.cnf最大权限

chmod 777 /etc/my.cnf

开机自启动服务控制脚本

cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld

增加mysqld服务控制脚本执行权限

# chmod +x /etc/rc.d/init.d/mysqld

​​​​​​​将mysqld服务加入到系统服务

# chkconfig --add mysqld

​​​​​​​用户可读写(其他用户不可写)

chmod 644 /etc/my.cnf

​​​​​​​​​​​​​​检查mysqld服务是否已经生效

chkconfig --list mysqld

表明mysqld服务已经生效，在2、3、4、5运行级别随系统启动而自动启动，以后可以使用service

命令控制mysql的启动和停止

​​​​​​​数据库启停命令

命令为:service mysqld start和service mysqld stop

​​​​​​​启动mysqld

service mysqld start

​​​​​​​将mysql的bin目录加入PATH环境变量

vi /etc/profile export PATH=$PATH:/usr/local/mysql/bin source /etc/profile

​​​​​​​以root账户登录mysql,默认是没有密码的

mysql -uroot -p 要输入密码的时候直接回车即可。

​​​​​​​设置root账户密码(修改成你要的密码)

mysql>use mysql; mysql>update user set password=password('123456') where user='root' and host= 'localhost'; mysql>flush privileges;

​​​​​​​设置远程主机登录

创建mysql新用户

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

授权与用户最大权限

GRANT privileges ON *.* TO 'username'@'%'

注意下面的your username 和 your password改成你需要设置的用户和密码

mysql>GRANT ALL PRIVILEGES ON *.* TO 'han'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;

​​​​​​​关闭防火墙

关闭防火墙

systemctl stop firewalld.service

开机进制启动

systemctl disable firewalld.service

​​​​​​​克隆虚拟机(克隆容易出问题,需要注意)

在这里我们再克隆2台虚拟机,供计三台虚拟机.

​​​​​​​mysql高可用（一主一从）

​​​​​​​master上开启binlog日志

在/etc/my.cnf文件，配置上如下内容

#启动二进制日志 log-bin=master-bin server-id=1

​​​​​​​​​​​​​​创建需要复制的数据库

如果需要复制多个数据库,那么这里可以重复添加binlog_do_db=数据库，也可以不写，表示所有数据库都进行同步

重启服务

service mysqld restart

如果重启不行,可以尝试重启系统,重新加载/etc/my.cnf文件

登录mysql检查日志是否开启

mysql> show variables like '%log_bin%';

已经成功开启

注意:这里提醒大家,如果 log_bin这项如果是OFF,主要是因为log-bin和server-id没有配置.

​​​​​​​master的数据库中建立备份账号

登录root账户

mysql -uroot -p

创建这个账号主要是给slave服务使用

hanguoqing为用户名，%表示任何远程地址，如下表示密码为123456的任何远程地址hanguoqing都可以连接master主机

grant replication slave on *.* to 'hanguoqing'@'%' identified by '123456';

​​​​​​​slave服务器设置

同样创建用户等等,设置权限等,和主服务一样,这里不写了.

​​​​​​​配置slave下my.cnf

 vi /etc/my.cnf

server-id=2 relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index #replicate-do-db=test(主配置,从不需要配置)

#备注：

#server-id 服务器唯一标识，如果有多个从服务器，每个服务器的server-id不能重复，跟IP一样是唯一标识，如果你没设置server-id或者设置为0，则从服务器不会连接到主服务器。

#relay-log 启动MySQL二进制日志，可以用来做数据备份和崩溃恢复，或主服务器挂掉了，将此从服务器作为其他从服务器的主服务器。

#replicate-do-db 指定同步的数据库，如果复制多个数据库，重复设置这个选项即可。若在master端不指定binlog-do-db，则在slave端可用replication-do-db来过滤。

#replicate-ignore-db 不需要同步的数据库，如果有多个数据库，重复设置这个选项即可。

#其中需要注意的是，replicate-do-db和replicate-ignore-db为互斥选项，一般只需要一个即可。

重启slave服务

service mysqld restart

​​​​​​​查看master服务状态

在master服务上登录mysql,以root用户登录

mysql -uroot -p

show master status;

必须获取master服务状态信息才可以

mysql-master-bin.000001: 当前正在写入的binlog文件

Position: 120 当前正在写入的位置。

Binlog_Do_DB: 只记录指定数据库的二进制文件。

Binlog_Ignore_DB:不记录指定数据库的二进制文件

Executed_Gtid_Set:5.7在mysql库下引入了新的表gtid_executed，会记录当前执行的GTID

​​​​​​​​​​​​​​slave连接master主服务

change master to master_host='192.168.234.129',master_port=3306,master_user='root',master_password='root',master_log_file='master-bin.000002',master_log_pos=338;

备注：

#master_host对应主服务器的IP地址。

#master_port对应主服务器的端口。

#master_log_file对应show master status显示的File列：master-bin.000001。

#master_log_pos对应show master status显示的Position列：154。

注意:如果之前运行过这段连接命令并且成功了,那么再次运行此连接命令会报错ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

,需要先停止(stop slave)之前的同步,再执行连接命令才可以.

在做mysql主从复制的时候，在从服务器上进行show slave status\G;查看时 Slave_IO_Running: No与Slave_SQL_Running: No，然后stop slave;后stat slave出现如下错误：

mysql> start slave;

ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log

解决方案：

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset slave;（reset重置）

Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_host='192.168.10.155',master_user='slave1',master_password='123456',master_log_file='mysql-bin.000023',master_log_pos=107;

Query OK, 0 rows affected (0.01 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

然后在执行show slave status\G;查看：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

这样就ok了，在网上查看出现这样的原始可能是以前的mysql做过主从！

​​​​​​​启动slave数据同步

#启动slave数据同步 mysql> start slave;

#停止slave数据同步（若有需要） mysql> stop slave;

​​​​​​​查看slave信息

mysql> show slave status\G;

Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功。

Slave_IO_Running：NO错误解决方案

因为这里是克隆出来的数据库，容易造成Slave_IO_Running的结果是NO，那么主要因为mysql服务都存在一个唯一的uuid，但是克隆出来的mysql服务uuid是相同的。需要修改slave节点下的mysql服务的uuid

uuid在auto.cnf文件下。

vi /usr/local/mysql/data/auto.cnf

任意更改下uuid即可

重启slave下mysql服务

service mysqld restart

#停止链路

stop slave;

#启动链路

start slave;

#再查看slave链路

show slave status \G

Slave_SQL_Running：NO解决方案

mysql> stop slave;                                                      

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;            

mysql> start slave;                                                      

mysql> show slave status\G  

​​​​​​​测试

在主服务器上登陆mysql，且进入test数据库，创建test表，且插入一条数据

提示：这里最好用数据库管理工具（如nacicat）来操作。

创建表

create table teachers(id int not null primary key,tname varchar(20),wechat varchar(20));

插入数据

insert into teachers(id,tname,wechat)values(1,'hanguoqing','18515413161');

提交

commit;

在从服务器上登陆mysql，且进入test数据库你会发现从数据库中，也出现了teachers表，且表中还有一条数据存在，证明同步数据成功。

​​​​​​​一主三从

按照上述讲解的方式，自己配置一主三从

​​​​​​​mysql双主双从

​​​​​​​设计思路

主节点101 备份节点103

主节点102 备份节点104

两个台主节点相互备份（即：master101也是master102的备份节点，反之同理）

​​​​​​​环境搭建

这里采用了4台节点，两个主节点和两个从节点。

注意:这里和上面一样，分别创建一个远程连接用户，比如也可以使用root用户作为远程连接。

​​​​​​​master节点配置 （/etc/my.cnf）

两个master节点配置相同

添加如下配置

server-id不能相同，4个节点不同

两个主节点配置信息

server-id=101  #每个节点不同 log-bin=master-bin auto_increment_increment=2 auto_increment_offset=1 #master2 应设置为2 log-slave-updates sync_binlog=1

配置解析：

auto_increment_increment=2 :主键自增长步长，这里存在两个主节点，步长设置为2为了避免主键冲突。节点1 ---》1,3,5,7,9.. 节点2 -à2,4,6,8,10..

auto_increment_offset=1：自增持主键开始值，master1应从1开始，master2应从2开始

log-slave-updates：matser1的从节点master2可以备份信息，那么master2的从节点slave2能备份到master1的信息吗？默认不行，这个配置主要是让另一个主节点从节点可以更新到另外一个主节点的数据的。

sync_binlog=1 ：提交事务同步数据，表示每提交一次事务即开启信息同步，这个效率是很低的，但是数据安全性比较高。

sync_binlog=n  表示每进行n次事务提交后，同步到其他节点和同步本地磁盘数据

重启两个master服务检测下

service mysqld restart

show variables like '%log_bin%';

表示成功

​​​​​​​slave节点配置

server-id=103 #节点4 需要修改 relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index

配置完成后，重新启动两个slave服务

service mysqld restart

检测master服务状态

在master服务上登录mysql,以root用户登录

show master status;

默认的都是从00001开始的，如果这里不是默认值，可以重置信息

reset master;

salve服务连接master服务

master101的slave分别是slave103和master102节点

分别在master102和slave103节点上执行如下操作

change master to master_host='192.168.234.129',master_port=3306,master_user='root',master_password='root',master_log_file='master-bin.000001',master_log_pos=120;

master102的slave分别是slave101和master104节点

分别在master101和slave104节点上执行如下操作

change master to master_host='192.168.234.131',master_port=3306,master_user='root',master_password='root',master_log_file='master-bin.000001',master_log_pos=120;

​​​​​​​启动slave数据同步

以上4个节点2个主节点，但是4个都是从节点，别分启动数据同步

start slave;

​​​​​​​查看slave信息

分别在4个节点上查看状态是否正常

show slave status\G;

如果出现slave_io_running:NO 或者 slave_sql_running:NO分别按照上述错误进行调试。

​​​​​​​测试

使用mysql视图化工具进行测试

测试1：在master1/master2创建表，即同步到masterX，slave1和slave2上

测试2：在master1/master2创建添加记录，即同步到masterX，slave1和slave2上，并且id主键自增会采用masterX自己步长和起始值

spring+mybatis

方式一 ：springboot+mybatis

application.properties

spring.application.name=mybatis # 主节点 master.dataSource.url=jdbc:mysql://192.168.172.101:3306/test master.dataSource.driver=com.mysql.jdbc.Driver master.dataSource.passowrd=root master.dataSource.username=123456 #从节点 slave.dataSource.url=jdbc:mysql://192.168.172.102:3306/test slave.dataSource.driver=com.mysql.jdbc.Driver slave.dataSource.passowrd=root slave.dataSource.username=123456

​​​​​​​添加mapper文件

这里添加一个master的mapper，和slave的mapper。master可以负责写数据，slave可以负责读数据。

​​​​​​​添加pojo对象

​​​​​​​配置sqlsesion和动态映射

@Configuration @MapperScan(basePackages = {"com.powernode.master.mapper"},sqlSessionFactoryRef = "masterSqlSessionFactoryBean") public class DataSourceConfig {     @Value("${master.dataSource.url}")     private String masterUrl;     @Value("${master.dataSource.driver}")     private String masterDriver;     @Value("${master.dataSource.passowrd}")     private String masterUsername;     @Value("${master.dataSource.username}")     private String masterPassword;     @Bean     public DruidDataSource masterDruidDataSource(){         DruidDataSource dataSource = new DruidDataSource();         dataSource.setDriverClassName(masterDriver);         dataSource.setUrl(masterUrl);         dataSource.setPassword(masterPassword);         dataSource.setUsername(masterUsername);         return dataSource;     }     @Bean     public SqlSessionFactoryBean masterSqlSessionFactoryBean(DruidDataSource masterDruidDataSource){         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();         sqlSessionFactoryBean.setDataSource(masterDruidDataSource);         return sqlSessionFactoryBean;     } }

​​​​​​​编写业务测试接口

​​​​​​​启动类

​​​​​​​读取信息slave

如果使用slave来读取数据，那么需要把DataSourceConfig中的master信息修改为slave信息。

​​​​​​​方式二 springboot+mybatis

通过方式一我们不难发现，在进行读写分离的时候，需要手动切换极为不方便，这样也不能投入开发使用。那么有没有一种方法可以动态切换主从呢？

多数据源动态配置

​​​​​​​application.properties

​​​​​​​MyThreadLocal

​​​​​​​MyDataSource

​​​​​​​DataSourceConfig

@Configuration @MapperScan(basePackages = {"com.powernode.mapper"},sqlSessionFactoryRef = "sqlSessionFactoryBean") public class DataSourceConfig {     /*主服务*/     @Value("${master.dataSource.url}")     private String masterUrl;     @Value("${master.dataSource.driver}")     private String masterDriver;     @Value("${master.dataSource.passowrd}")     private String masterUsername;     @Value("${master.dataSource.username}")     private String masterPassword;     /*从服务*/     @Value("${slave.dataSource.url}")     private String slaveUrl;     @Value("${slave.dataSource.driver}")     private String slaveDriver;     @Value("${slave.dataSource.passowrd}")     private String slaveUsername;     @Value("${slave.dataSource.username}")     private String slavePassword;     @Bean     public DruidDataSource masterDruidDataSource(){         DruidDataSource dataSource = new DruidDataSource();         dataSource.setDriverClassName(masterDriver);         dataSource.setUrl(masterUrl);         dataSource.setPassword(masterPassword);         dataSource.setUsername(masterUsername);         return dataSource;     }     @Bean     public DruidDataSource slaveDruidDataSource(){         DruidDataSource dataSource = new DruidDataSource();         dataSource.setDriverClassName(slaveDriver);         dataSource.setUrl(slaveUrl);         dataSource.setPassword(slavePassword);         dataSource.setUsername(slaveUsername);         return dataSource;     }     @Bean     public MyDataSource myDataSource(DruidDataSource slaveDruidDataSource,DruidDataSource masterDruidDataSource){         MyDataSource dataSource = new MyDataSource();         dataSource.setDefaultTargetDataSource(masterDruidDataSource);         Map map = new HashMap();         map.put("master",masterDruidDataSource);         map.put("slave",slaveDruidDataSource);         dataSource.setTargetDataSources(map);         return dataSource;     }     @Bean     public SqlSessionFactoryBean sqlSessionFactoryBean(MyDataSource myDataSource){         SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();         sqlSessionFactoryBean.setDataSource(myDataSource);         return sqlSessionFactoryBean;     } }

​​​​​​​Mapper

这里只要一个mapper即可，master和slave都使用同一个mapper

​​​​​​​pojo

​​​​​​​启动类

​​​​​​​​​​​​​​业务接口测试

在操作数据时，选择主从进行操作。