mysql数据库主从复制软件_Mysql数据库的主从复制

Mysql数据库的主从复制

为什么要进行mysql的主从复制

冗余：promte(提升为主)，异地灾备

扩展：转移一部分“读”请求；

支援安全的备份操作；

测试；

...

mysql 的主从复制

主从复制

主：可读可写

从：只可度

主从结构

一主多从；

一主一从；

一从多主：每个主服务器提供不同的数据库；

异步复制：

级联复制；

循环复制；

双主复制；

半同步复制；

单个msql的问题分析

若后端mysql数据库只有一台时，会有以下问题：

单点故障，服务不可用，无法处理大量的并发数据请求，数据丢失将造成大灾难。

改造办法：

增加mysql数据库服务器，对数据进行备份，形成主备。

确保mysql数据库服务器是一样的。

主服务器宕机了，备份服务器继续工作，数据有保障。

mysql主从复制与读写分离是密切相关的

主从复制原理

mysql主从复制的类型：

基于语句的复制

基于行的复制

混合类型的复制

过程：

1》mysql从服务器开启I/O(io_thread)线程，向主服务器请求数据同步(获取二进制日志)。

2》mysql主服务器开启I/O(io_thread)线程回应从服务器

3》从服务器得到主的二进制日志写入中继日志

4》从服务器开启sql(sql_thread)线程将日志内容执行，实现数据同步

复制实现

清楚整个过程的所有需要注意的点儿后才能开始复制

1》复制前的配置

时间同步；

复制的开始位置：

从0开始；

从备份中恢复到从节点后启动的复制的起始点，备份操作时主节点所处的日志文件及其事件位置；

主从服务器mysqld程序版本不一致？

从服务器的版本号高于主服务器的版本号；

主从复制时应该注意的问题：

查看

select @@global.xxxxx;

设置

set @@global.xxxx={1|0};

1、从服务设定为“只读”；

在从服务器启动read_only，但仅对非super权限的用户有效；

阻止所有用户：

mysql> flush tables with read lock;

2、尽量确保复制时的事务安全，可以在配置文件中设置

在master节点启用参数：

sync_binlog = on

表示只要当前节点有事物提交时就立即从内存缓冲区保存到二进制日志中，避免从服务器复制时二进制日志中没有这个提交操作主服务器就坏了，这样从服务器就不知道这个事物该不该提交了。

如果用到的是innodb存储引擎：

innodb_flush_logs_at_trx_commit=on

每当事物提交时就同步到事物日志中

innodb_support_xa=on

让innodb支持分布式事物

3、从服务器意外中止时尽量避免自动启动复制线程

为了避免这种事情发生，我们要把网断掉，查看一下是否有复制到一半的事物，如果有手动删除，然后手动加change master to 指向意外终止时主服务器二进制日志的位置，或者重新备份恢复后启动复制功能。

4、从节点设置参数，可在配置文件中设置

sync_master_info=on

sync_relay_log_info=on

2》主从复制实现

如果主服务器已经运行一段时间了，要想实现主从复制，先对主服务器进行一个完全备份，然后在从服务器上进行恢复，使两者的数据达到一致后在开启主从复制功能。

主服务器：

配置文件/etc/my.cnf

server_id=# ##任意数，不要和从相同

log_bin=log-bin

重启服务

启动mysql

mysql -uroot -p

mysql> grant replication slave,replication client on *.* to 'username'@'host' identified by 'your_password';

mysql> flush privileges;

mysql>show master status; ##查看正在使用二进制日志

+----------------+----------+--------------+------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+----------------+----------+--------------+------------------+

| log-bin.000001 | 245 | | |

+----------------+----------+--------------+------------------+

从服务器：

配置文件/etc/my.cnf

server_id=# ##任意数，不要和其他相同

relay_log=relay-log

read_only=on

sync_master_info=on

sync_relay_log_info=on ##这些参数可以在数据库中设置不过是暂时的重启失效而已

重启服务

启动mysql

mysql>help change master to ##查看帮助

mysql> change master to master_host='host',master_user='username',master_password='your_password',master_log_file='log-bin.000001',master_log_pos=245;

mysql> start slave io_thread,sql_thread;

mysql> show slave status\G;

MariaDB [(none)]> show status\G; ##以下两个出现就算主从成功。

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

若主从服务器没有设置skip_name_resolve= on，则需要保证主机间可以相互解析主机

主从复制centos6.9的配置及注意事项

vim /etc/my.cnf

[mysqld]

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

user=mysql

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

skip_name_resolve = on

innodb_file_per_table = on

max_connections = 20000 ##以上三行不能写到server里，mysql服务启动不了

[server] ##注意在这里要加上server这一项不然不会生效

server_id = 1

log_bin = bin-log

relay_log = relay-log

auto_increment_offset = 1

auto_increment_increment = 2

启动slave时要用mysql> start slave io_thread,sql_thread; 不能简写成mysql> start slave，不然开启不了复制操作

3》主主复制实现：

主主复制实际也是主从复制，区别在于互为主从而已。

互为主从的配置要求：

1、server_id必须要使用不同值；

2、两个节点各自都要开启binlog和relay log；

3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

定义一个节点使用奇数id

auto_increment_offset=1

auto_increment_increment=2

另一个节点使用偶数id

auto_increment_offset=2

auto_increment_increment=2

4、服务启动后执行如下两步：

1都授权有复制权限的用户账号；

2各把对方指定为主节点；

在A服务器上配置

配置文件my.cnf

[server]

skip_name_resolve = on

innodb_file_per_table = on

max_connections = 20000

log_bin = bin-log ##开启二级制日志

relay_log = relay-log ##开启中继日志

server_id = 1

auto_increment_offset = 1 ##表示id号从1开始

auto_increment_increment = 2 ##表示以2为步进，即为奇数

登录服务器

MariaDB [(none)]> select user,password,host from mysql.user; ##查看一下是否有授权的用户用于主从复制

MariaDB [mydb]>grant replication slave,replication client on *.* to 'username'@'host' identified by 'your_password'; ##若无授权用户进程主从复制则进行授权

MariaDB [mydb]> change master to master_host='host',master_user='username',master_password='your_password',master_log_file='binlog',master_log_pos=#;

MariaDB [mydb]> start slave;

MariaDB [mydb]> show slave status \G

在B服务器上配置

配置文件my.cnf

[server]

skip_name_resolve = on

innodb_file_per_table = on

max_connections = 20000

server_id = 2

relay_log = relay-log

log_bin = bin-log

auto_increment_offset = 2

auto_increment_increment = 2

登录服务器

MariaDB [(none)]> select user,password,host from mysql.user; ##查看一下是否有授权的用户用于主从复制

MariaDB [mydb]>grant replication client,replication slave on *.* to 'username'@'host' identified by 'your_password'; ##若无授权用户进程主从复制则进行授权

MariaDB [mydb]> change master to master_host='host',master_user='username',master_password='your_password',master_log_file='binlog',master_log_pos=#;

MariaDB [mydb]> start slave;

MariaDB [mydb]> show slave status \G

检查主从同步，如果您看到Slave_IO_Running和Slave_SQL_Running均为Yes，则主从复制连接正常。

4)基于ssl的复制实现

查看所安装的数据库软件是否支持ssl，默认yum安装都是不支持的，需要编译安装的时候加上ssl功能

MariaDB [(none)]> show global variables like '%ssl%';

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| have_openssl | DISABLED |

| have_ssl | DISABLED |

| ssl_ca | |

| ssl_capath | |

| ssl_cert | |

| ssl_cipher | |

| ssl_key | |

+---------------+----------+

在主服务器上创建一个用于复制的账号，要求从服务器使用这个账号连接到主服务器时必须使用ssl连接

MariaDB [none]>grant replication client,replication slave on *.* to 'username'@'host' identified by 'your_password' require ssl;

在从服务器上创建证书和私钥文件

从服务器上使用change master to命令指明自己的ssl相关选项

help change master to查看使用的选项

change master to master_host='host',master_user='username',master_password='your_password',master_log_file='binlog',master_log_pos=#,MASTER_SSL_CERT='/etc/pki/tls/certs/mysql.crt',MASTER_SSL_KEY='/etc/pki/tls/certs/mysql.key';

这里的,MASTER_SSL_CERT='/etc/pki/tls/certs/mysql.crt',MASTER_SSL_KEY='/etc/pki/tls/certs/mysql.key'; 是从服务器上的证书和私钥文件路径

如果从服务器也需要主服务器提供ssl验证，就需要在主服务器上也创建证书和私钥文件

将证书和私钥文件在主服务器的配置文件中指定

一般情况下主服务器端不需要配置证书和私钥，因为是从服务器去连接主服务器进行复制，所以要求从服务器提供证书和私钥文件进行认证。

5》半同步复制

半同步复制指当一个主节点有多个从节点时，其中一个或几个从节点使用同步复制的方式，其他从节点使用异步复制的方式，称为半同步复制。

同步复制时客户端在访问mysql数据库并完成一个写操作后，从节点会立即将数据同步到本地并将结果反馈给主节点，证明自己已经同步完成，客户端才能进行第二次写操作。

如果主节点等待从节点反馈的时间超过了规定的超时时间，这个从节点会自动降级为异步复制模方式。

支持多种插件：

/usr/lib64/mysql/plugins/

需要安装才可使用：

mysql> install plugin plugin_name soname 'shared_library_name';

半同步复制插件：

semisync_master.so

semisync_slave.so

半同步复制的实现

在主上配置：

MariaDB [(none)]>install plugin rpl_semi_sync_master soname 'semisync_master.so';

MariaDB [(none)]>show global variables like 'rpl_semi%';

+------------------------------------+-------+

| variable_name | value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | off |

| rpl_semi_sync_master_timeout | 10000 |

| rpl_semi_sync_master_trace_level | 32 |

| rpl_semi_sync_master_wait_no_slave | on | ##没有从节点时是否开启同步复制

+------------------------------------+-------+

MariaDB [(none)]> set global rpl_semi_sync_master_enabled=on;

MariaDB [(none)]> show global status like 'rpl_semi%';

在从上配置：

MariaDB [(none)]>install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

MariaDB [(none)]> show global variables like 'rpl_semi%';

+---------------------------------+-------+

| variable_name | value |

+---------------------------------+-------+

| rpl_semi_sync_slave_enabled | off |

| rpl_semi_sync_slave_trace_level | 32 |

+---------------------------------+-------+

MariaDB [(none)]> set @@global.rpl_semi_sync_slave_enabled=on；

MariaDB [(none)]> stop slave io_thread;

MariaDB [(none)]> start slave io_thread;

MariaDB [(none)]> show global variables like 'rpl_semi%';

判断方法：

主节点：

mariadb [mydb]> select @@global.rpl_semi_sync_master_clients；

复制过滤器

仅复制有限一个或几个数据库相关的数据，而非所有，由复制过滤器进行；

有两种实现思路：使用set设置

(1) 主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；

问题：其它库的time-point recovery将无从实现，因为二进制日志不全，所以对于其他库无法进行重放二级制日志，一般这种方法不会使用。

binlog_do_db=

binlog_ignore_db=

(2) 从服务器

从服务器的sql thread仅重放关注的数据库或表相关的事件，并将其应用于本地；

问题：网络io和磁盘io，因为会将所有库的数据复制到从服务器的中继日志中，只是从中继日志中重放的时候只会挑选特定的数据库或表进行重放。

replicate_do_db=

replicate_ignore_db=

replicate_do_table=

replicate_ignore_table=

replicate_wild_do_table=

replicate_wild_ignore_table=

复制的监控和维护

(1) 清理日志：purge

MariaDB [(none)]> purge { binary | master } logs { to 'log_name' | before datetime_expr };

使用purge删除二进制日志后，mysql重新启动时不会读取删除的二进制日志文件，这样不会报错。

(2) 复制监控

master:

show master status;查看正在使用二进制日志

show binlog events;查看二进制日志中的事件

show binary logs;查看二进制日志列表

slave:

show slave status;查看从服务器的状态

seconds_behind_master: 0 ##判断从服务器是否落后于主服务器

(3) 如何确定主从节点数据是否一致？

通过表的checksum检查；

使用percona-toolkit中pt-table-checksum；

(4) 主从数据不一致时的修复方法？

备份恢复后重试设置主从复制；

maxscale配置示例：

[maxscale]

threads=auto

[server1]

type=server

address=172.18.0.67

port=3306

protocol=mysqlbackend

[server2]

type=server

address=172.18.0.68

port=3306

protocol=mysqlbackend

[server3]

type=server

address=172.18.0.69

port=3306

protocol=mysqlbackend

[mysql monitor]

type=monitor

module=mysqlmon

servers=server1,server2,server3

user=maxscale

passwd=201221dc8fc5a49ea50f417a939a1302

monitor_interval=1000

[read-only service]

type=service

router=readconnroute

servers=server2,server3

user=maxscale

passwd=201221dc8fc5a49ea50f417a939a1302

router_options=slave

[read-write service]

type=service

router=readwritesplit

servers=server1

user=maxscale

passwd=201221dc8fc5a49ea50f417a939a1302

max_slave_connections=100%

[maxadmin service]

type=service

router=cli

[read-only listener]

type=listener

service=read-only service

protocol=mysqlclient

port=4008

[read-write listener]

type=listener

service=read-write service

protocol=mysqlclient

port=4006

[maxadmin listener]

type=listener

service=maxadmin service

protocol=maxscaled

port=6602