当前位置:Gxlcms > mysql > MySQL/MariaDB数据库主从复制

MySQL/MariaDB数据库主从复制

时间:2021-07-01 10:21:17 帮助过:41人阅读

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;),如果服务器已经运行了一段时间,可以先对主服务器进行备份(备份至某个位置),在从服务器上恢复,再从该位置进行同步。

MySQL/MariaDB数据库主从复制

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

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

人气教程排行