MySQL/MariaDB数据库主从复制

MySQL的主从复制是指从服务器向主服务器获取二进制日志文件，然后在从服务器上对这些日志重新执行，从而使从服务器和主服务器保持

MySQL数据库复制概述

MySQL的主从复制是指从服务器向主服务器获取二进制日志文件，然后在从服务器上对这些日志重新执行，从而使从服务器和主服务器保持同步。但由于是异步的复制，从服务器在一定程度上落后于主服务器，刚写入到主服务器上的数据可能服务在从服务器上查询得到。

MySQL的复制原理：

（1）从服务器创建I/O线程连接主数据库，向主数据库请求二进制日志文件。

（2）主库上启动Binlog Dump，将二进制日志文件发送给I/O线程，I/O线程获取数据后将数据写在从库的中继日志中（relay log）。

（3）SQL线程读取中继日志并执行。

在主服务器上，记录二进制日志的方式有3种：

statement：基于语句级别的binlog，记录的是修改数据的sql语句。

row：基于行级别的binlog逐行记录数据的变更。

mixed：由MySQL数据库自动选择哪种方式记录binlog。

statement格式仅仅是记录操作的SQL语句，row格式会将每行数据的变化都记录到binlog中，相对于statement要详细的多，更能够保证主从数据库的一致性。但是row格式下的二进制日志文件会显得非常庞大，会影响磁盘I/O，在传输过程中也会影响带宽，所以需要根据具体情况来设定。

在一个非常繁忙的数据库上，主库上往往有多个线程并发执行写操作，而这些事件记录到二进制日志中一定是线性的。从库上只有一个SQL线程执行写操作，长此以往，从库与主库的差距会越来越大。这种情况下在从库上可以启动多个SQL线程用于执行写操作，每个线程负责执行主库上某个数据库的所有相关事务。还有在主库上一个事务提交之后，相关的记录不会立刻同步到磁盘上，而是记录在缓冲上，每隔一段时间同步一次。这样也可能会导致主从不一致，可以在主服务器上设置参数sync_binlog=1，一但事务提交，就将二进制日志文件从内存缓冲同步至磁盘上。

数据库复制搭建过程

一主多从架构

实验环境：主服务器（192.168.1.106），从服务器（192.168.1.127）

1）首先确保各服务器时间同步

2）安装MariaDB数据库，这里使用的是mariadb-10，二进制安装方式，mariadb-10中GTID机制已成为标配，不需要再手动启动。

[root@www ansible]# ansible mysql -m shell -a 'tar xf /root/mariadb-10.0.10-linux-x86_64.tar.gz -C /usr/local/'

[root@www ansible]# ansible mysql -m shell -a 'groupadd -g 300 -r mysql'

[root@www ansible]# ansible mysql -m shell -a 'useradd -u 300 -g mysql -r  mysql'

[root@www ansible]# ansible mysql -m shell -a 'ln -sv /usr/local/mariadb-10.0.10-linux-x86_64/ /usr/local/mysql'

[root@www ansible]# ansible mysql -m shell -a 'chown -R root.mysql /usr/local/mysql/*'

[root@www ansible]# ansible mysql -m shell -a 'chown -R mysql.mysql /data'

[root@www ansible]# ansible mysql -m shell -a 'mkdir /etc/mysql'

[root@www ansible]# ansible mysql -m shell -a 'cp /usr/local/mysql/support-files/my-large.cnf /etc/mysql/my.cnf'

[root@www ansible]# ansible mysql -m shell -a 'cp /usr/local/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld'

[root@www ansible]# ansible mysql -m shell -a 'chkconfig --add mysqld'

在各个节点上/usr/local/mysql目录下执行初始化操作：

./scripts/mysql_install_db --user=mysql --datadir=/MySQL_DATA/data/

3）修改配置文件

主服务器上：

/etc/mysql/my.cnf：

datadir=/data/mydata                    #数据存放目录

binlog_format=mixed                      #记录二进制日志格式

sync_binlog = 1                          #事务一提交，就将二进制日志同步至磁盘上

autocommit = off                        #关闭自动提交

server-id      = 1                      #服务器ID号

innodb_support_xa      = 1              #支持分布式事务

从服务器上：

/etc/mysql/my.cnf：

server-id      = 2

datadir = /data/mydata

relay-log = /data/relaylog/relay-log  #中继日志存放目录

read_only      = 1                #从服务器为只读状态（确保数据的一致性）

slave_parallel_threads = 3        #启动的SQL线程数

sync_master_info = 1              #从服务器按需要同步master.info文件

sync_relay_log = 1                #按需要同步中继日志

sync_relay_log_info = 1            #按需要同步relay.info文件

4）启动服务

[root@www ~]# ansible mysql -m shell -a 'service mysqld start'

5）在主服务器上登录mariadb，创建授权账号

MariaDB [(none)]> grant replication slave,replication client on *.* to repuser@'192.168.1.%' identified by 'repuser';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

6）从服务器上，登录数据库使用有复制权限的账号连接master

首先查看主服务器二进制日志所处的位置（show master status;）,如果服务器已经运行了一段时间，可以先对主服务器进行备份（备份至某个位置），在从服务器上恢复，再从该位置进行同步。

MariaDB [(none)]> change master to master_host='192.168.1.106',master_user='repuser',master_password='repuser',master_log_file='master-bin.000007',master_log_pos=343;

从服务器上启动IO线程和sql线程

MariaDB [(none)]> start slave;

或者

MariaDB [(none)]> start slave IO_THREAD

MariaDB [(none)]> start slave SQL_THREAD

从服务器上查看运行状况：