mysql主从复制

earthchen

发布于 2020-09-24 15:59:09

9740

发布于 2020-09-24 15:59:09

文章被收录于专栏：earthchen的专栏

mysql主从复制

主从复制的形式

一主一从
主主复制
一主多从—扩展系统读取的性能，因为读是在从库读取的
多主一从—5.7开始支持
联级复制—

用途和条件

用途

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务

必要条件

主库开启binlog日志（设置log-bin参数）
主从server-id不同
从库服务器能连通主库

主从复制的原理

主要涉及三个线程：binlog 线程、I/O 线程和 SQL 线程。

binlog 线程 ：负责将主服务器上的数据更改写入二进制日志（Binary log）中。
I/O 线程 ：负责从主服务器上读取二进制日志，并写入从服务器的重放日志（Replay log）中。
SQL 线程 ：负责读取重放日志并重放其中的 SQL 语句。

从库生成两个线程，一个I/O线程，一个SQL线程；
主库使用binlog线程将更改写入日志

问题及解决方法

问题

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决办法

半同步复制—解决数据丢失的问题
并行复制—-解决从库复制延迟的问题

半同步复制的原理

事务在主库写完binlog后需要从库返回一个已接受，才放回给客户端
5.5集成到mysql，以插件的形式存在，需要单独安装
确保事务提交后binlog至少传输到一个从库
不保证从库应用完成这个事务的binlog
性能有一定的降低
网络异常或从库宕机，卡主库，直到超时或从库恢复

并行复制

从库多线程apply binlog
库级别并行应用binlog，同一个库数据更改还是串行的
在社区5.6中新增，5.7版本并行复制基于事务组

使用docker搭建mysql主从复制集群

创建主从配置文件

由于我这里使用docker搭建，所以需要把配置文件和数据文件映射到宿主机，让容器运行时挂载数据

所以在本地合适的地方创建目录，并添加配置文件

├── master
│   ├── conf
│   └── data
└── slave
    ├── conf
    └── data

master的配置文件为

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 1
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

# Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

slave的配置文件为

[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket    = /var/run/mysqld/mysqld.sock
datadir    = /var/lib/mysql

symbolic-links=0

character-set-server = utf8   
#skip-networking  
innodb_print_all_deadlocks = 1
max_connections = 2000  
max_connect_errors = 6000  
open_files_limit = 65535  
table_open_cache = 128   
max_allowed_packet = 4M  
binlog_cache_size = 1M  
max_heap_table_size = 8M  
tmp_table_size = 16M  
  
read_buffer_size = 2M  
read_rnd_buffer_size = 8M  
sort_buffer_size = 8M  
join_buffer_size = 28M  
key_buffer_size = 4M  
  
thread_cache_size = 8  
  
query_cache_type = 1  
query_cache_size = 8M  
query_cache_limit = 2M  
  
ft_min_word_len = 4  
  
log-bin = mysql-bin
server-id = 2
binlog_format = mixed  
 
performance_schema = 0  
explicit_defaults_for_timestamp  
  
#lower_case_table_names = 1  
  
interactive_timeout = 28800  
wait_timeout = 28800  

# Recommended in standard MySQL setup  
  
sql_mode=NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES   
  
[mysqldump]  
quick  
max_allowed_packet = 16M  
  
[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

启动master和slave的容器

docker run --name=master-mysql -p 3307:3306 -v /Users/earthchen/docker/data/cluster/mysql/master/conf:/etc/mysql/conf.d -v /Users/earthchen/docker/data/cluster/mysql/master/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci


docker run --name=slave-mysql -p 3308:3306 -v /Users/earthchen/docker/data/cluster/mysql/slave/conf:/etc/mysql/conf.d -v /Users/earthchen/docker/data/cluster/mysql/slave/data:/var/lib/mysql  -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.6 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

在master上进行配置

创建远程连接用户，并赋予查询数据库，以及查询的权限，可以用于读写分离

mysql -uroot -p

grant SHOW DATABASES,SELECT on *.* to 'slave'@'%' identified by '123456';

FLUSH PRIVILEGES;

最后查看一下master的状态，

show master status;

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 |      675 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

上面的mysql-bin.000004，要根据你自己的输出情况来看

在slave上进行配置

主要就是配置一下master的配置，然后开启集群配置

change master to master_host='192.168.1.140',master_user='reader',master_password='123456',master_log_file='mysql-bin.000004',master_log_pos=0;

start slave;

# 查看集群状态，看是否有错误，然后解决掉就可以了
show slave status\G