MariaDB数据库主从复制

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 = 2datadir = /data/mydatarelay-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_THREADMariaDB [（none）]> start slave SQL_THREAD从服务器上查看运行状况：相关线程已经启动，其中Seconds_Behind_Master显示了从服务器落后主服务器的时间，这个变量是通过当前服务器时间戳和二进制日志中事件的时间戳对比得到的（null为从库没有处于运行状态，0也有可能是复制中断了，这个参数经供参考）在主服务器上可同过show slave hosts查看与自己同步的从服务器：实现半同步复制半同步就是主服务器上数据发生了更改，需要等待至少一个从节点把数据完整地复制过去，才能继续后续的操作。半同步功能是以插件的方式提供能，要启用此功能需要先安装插件。该插件位于mariadb安装目录下的lib/plugin目录中，登入数据库后直接安装即可。主节点上安装插件MariaDB [（none）]> install plugin rpl_semi_sync_master soname "semisync_master.so";在配置文件中添加如下几项：rpl_semi_sync_master_enabled = 1rpl_semi_sync_master_timeout = 1000
重启服务后查看相关变量：MariaDB [（none）]> show global variables like "%semi%";+------------------------------------+-------+| Variable_name | Value |+------------------------------------+-------+| rpl_semi_sync_master_enabled | ON || rpl_semi_sync_master_timeout | 1000 || rpl_semi_sync_master_trace_level | 32 || rpl_semi_sync_master_wait_no_slave | ON |+------------------------------------+-------+从节点上安装插件MariaDB [（none）]> install plugin rpl_semi_sync_slave soname "semisync_slave.so";
在配置文件中添加：rpl_semi_sync_slave_enabled = 1
重启服务后查看相关变量：MariaDB [（none）]> show global variables like "%semi%";+---------------------------------+-------+| Variable_name | Value |+---------------------------------+-------+| rpl_semi_sync_slave_enabled | ON || rpl_semi_sync_slave_trace_level | 32 |+---------------------------------+-------+该功能已启用.................^_^双主复制架构实验环境：node1（192.168.1.106）,node2（192.168.1.126）配置双主模型是需要注意几点：
（1）双方节点都得创建具有复制权限用户；
（2）双节点都得启用中继日志和二进制日志；（3）为保证具有自动增长功能的字段能正确生成ID，需要配置两个节点分别使用偶数或奇数ID号；（4）都要把对方配置为自己的主节点；node1上配置文件：[root@node1 ~]# vim /etc/mysql/my.cnflog-bin = /data/binlog/master-binbinlog_format = rowdatadir = /data/mydatasync_binlog = 1autocommit = offrelay-log = /data/relaylog/relay-log #中继日志存放位置innodb_support_xa = 1sync_master_info = 1sync_relay_log = 1sync_relay_log_info = 1auto-increment-increment = 2 #自增长的间隔auto-increment-offset = 1 #自增长起始值slave_parallel_threads = 3log_slave_updates = 1 #开启从库更新操作写入二进制日志功能server-id = 1
node2上配置文件（除了下面3项其余的与node1上的配置一致）：[root@node1 ~]# vim /etc/mysql/my.cnf..............auto-increment-increment = 2auto-increment-offset = 2server-id = 2在node1和node2上都创建具有复制权限的用户：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;查看node1和node2上当前数据库二进制日志文件所处的位置：MariaDB [（none）]> show master status;在node1上主服务器指向node2：MariaDB [（none）]> change master to master_host="192.168.1.126",master_user="repuser",master_password="repuser",master_log_file="master-bin.000004",master_log_pos=657;在node2上主服务器指向node1：MariaDB [（none）]> change master to master_host="192.168.1.106",master_user="repuser",master_password="repuser",master_log_file="master-bin.000004",master_log_pos=657;最后启动I/O线程和SQL线程：MariaDB [（none）]> start slave;node1（192.168.1.106）上查看从节点状态：node2（192.168.1.126）上查看从节点状态：在双主模型中应用半同步功能，需要在两个节点上都安装插件，并配置主从相关的参数。配置完成.................^_^--------------------------------------分割线 --------------------------------------Ubuntu下Nginx做负载实现高性能WEB服务器5---MySQL主主同步 http://www.linuxidc.com/Linux/2012-06/61687p5.htm生产环境MySQL主主同步主键冲突处理 http://www.linuxidc.com/Linux/2013-07/86890.htmMySQL主从失败错误Got fatal error 1236 http://www.linuxidc.com/Linux/2012-02/54729.htmMySQL主从复制，单台服务器上实施 http://www.linuxidc.com/Linux/2013-03/81913.htm搭建MySQL代理服务器实现读写分离+主从同步 http://www.linuxidc.com/Linux/2014-05/102265.htmMySQL 5.5 主从双向同步 http://www.linuxidc.com/Linux/2012-12/75973.htmMySQL 5.5主从同步排错 http://www.linuxidc.com/Linux/2014-08/105416.htmMySQL主从复制异步半同步实例 http://www.linuxidc.com/Linux/2014-10/107608.htm--------------------------------------分割线 --------------------------------------本文永久更新链接地址