上篇文章介绍了MariaDB数据库的备份工具及实现的几种方法,这次介绍MariaDB数据库的复制,首选介绍什么是mysql的复制?
一、复制概述
1.1、复制可以解决什么问题?
数据复制技术有以下一些特点:
(1) 数据分布
(2) 负载平衡(load balancing),只针对读操作
(3) 数据库的备份
(4) 高可用性(high availability)和容错
1.2、复制如何进行
(1) master将数据库的改变记录到二进制日志(binary log)中(这些记录叫做二进制日志事件);
(2) slave通过I/O线程将master的binary log events拷贝到它的中继日志(relay log);
(3) slave再通过SQL线程replay中继日志中的事件,将改变保存至自己的数据文件中。
二、通过以下几个模型进行介绍:
2.1、主从
2.2、主主
2.3、半同步复制
2.4、基于ssl的复制
测试环境: centos 7自带的mariadb-server;
master:172.16.116.231 ;slave:172.16.116.232
2.1、主从复制
①主从配置过程: master: (1) 启动二进制日志; [mysqld] log_bin=mysql-bin 2) 为当前节点设置一个全局唯一的server_id号; [mysqld] server_id=1 (3) 创建有复制权限的用户账号; REPLICATION SLAVE, REPLICATION CLIENT |
从节点: (1) 启动中继日志; [mysqld] relay_log=relay-log (2) 为当前节点设置一个全局惟的ID号; [mysqld] server_id=11 (3) 使用有复制权限的用户账号连接至主服务器,并启动复制线程; mysql> CHANGE MASTER TO ~~ mysql> START SLAVE [IO_THREAD|SQL_THREAD]; |
②实现过程如下:
master:172.16.11.231
# yum install -y mariadb-server //master/slave节点安装MySQL数据库 [root@node1 ~]# vim /etc/my.cnf log_bin=mysql-bin //开启二进制 server_id=1 innodb_file_per_table=1 skip_name_resolve=1 ssl MariaDB [(none)]> grant replication slave,replication client on *.* to 'copy'@'172.16.116.232' identified by 'passwd'; //用户授权 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master status; //查看使用二进制日志文件的位置 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 494 | | | +------------------+----------+--------------+------------------+ MariaDB [(none)]> create database testdb; //测试从服务器是否可以复制 Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 581 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
slave:172.16.11.232
# yum install -y mariadb-server //master/slave节点安装MySQL数据库 [root@node2 ~]# vim /etc/my.cnf relay_log=relay-log //开启中继日志 server_id=11 //id号 innodb_file_per_table=1 //innodb存储引擎每表一个表空间 skip_name_resolve=1 //跳过名称反解 ssl //启用ssl MariaDB [(none)]> change master to master_host='172.16.116.231',master_user='copy',master_password='passwd',master_log_file='mysql-bin.000003',master_log_pos=494; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; //启动复制线程 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 172.16.116.231 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 494 Relay_Log_File: relay-log.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 494 Relay_Log_Space: 245 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 1 row in set (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.116.231 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000003 Read_Master_Log_Pos: 581 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 616 Relay_Master_Log_File: mysql-bin.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 581 Relay_Log_Space: 904 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) MariaDB [(none)]> show databases; //复制没问题 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 5 rows in set (0.01 sec)
2.2、主主复制;互为主从
① 配置过程 |
(1)各节点设置一个全局唯一的server_id号 (2)各节点都启用log_bin 和relay_log (3)创建有复制权限的用户账号 REPLICATION SLAVE ; REPLICATION CLIENT (4)定义自动增长id字段的数值范围为奇偶 配置一个节点使用奇数id auto_increment_offset=1 auto_increment_increment=2 另一个节点使用偶数id auto_increment_offset=2 auto_increment_increment=2 (5)均把对方指定为主节点,并启动复制线程 |
② 实现过程如下
master:172.16.116.231
[root@node1 ~]# vim /etc/my.cnf log_bin=mysql-bin //开启二进制日志 relay_log=relay-log //开启中继日志 server_id=1 //id号 innodb_file_per_table=1 //innodb存储引擎每表一个表空间 skip_name_resolve=1 //跳过名称反解 ssl auto_increment_offset=1 //自动增长id段使用奇数 auto_increment_increment=2 MariaDB [(none)]> grant replication slave,replication client on *.* to 'copy'@'172.16.116.232' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 504 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> change master to master_host='172.16.116.232',master_user='copy',master_password='passwd',master_log_file='mysql-bin.000003',master_log_pos=504; Query OK, 0 rows affected (0.38 sec) MariaDB [(none)]> start slave; //启动复制线程 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> create database testdb; //测试,创建新的数据库 Query OK, 1 row affected (0.00 sec) //在172.16.116.231的MySQL数据库中查看是否存在表及其表中数据 MariaDB [(none)]> select * from testdb.student; +----+-------+ | id | name | +----+-------+ | 2 | tom | | 4 | jerry | +----+-------+ 2 rows in set (0.00 sec) //存在,同样新增2条数据 MariaDB [(none)]> insert into testdb.student(name) value ('blue'),('red'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0
slave:172.16.116.232
[root@node2 ~]# vim /etc/my.cnf log_bin=mysql-bin relay_log=relay-log server_id=11 innodb_file_per_table=1 skip_name_resolve=1 ssl auto_increment_offset=2 auto_increment_increment=2 MariaDB [(none)]> grant replication slave,replication client on *.* to 'copy'@'172.16.116.231' identified by 'passwd'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000003 | 504 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) MariaDB [(none)]> change master to master_host='172.16.116.231',master_user='copy',master_password='passwd',master_log_file='mysql-bin.000003',master_log_pos=504; Query OK, 0 rows affected (0.04 sec) MariaDB [(none)]> start slave; //启动复制线程 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show databases; //查看是否同步成功,可以看到有testdb数据库 +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 5 rows in set (0.00 sec) //在172.16.116.232的MySQL数据库中创建表student并插入2条数据 MariaDB [(none)]> use testdb; Database changed MariaDB [testdb]> create table student(id int unsigned not null auto_increment primary key,name char(20) not null); Query OK, 0 rows affected (0.37 sec) MariaDB [testdb]> insert into student(name) value ('tom'),('jerry'); Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 //可以看到下表中的数据,同步没问题!! MariaDB [testdb]> select * from testdb.student; +----+-------+ | id | name | +----+-------+ | 2 | tom | | 4 | jerry | | 5 | blue | | 7 | red | +----+-------+ 4 rows in set (0.00 sec)
2.3、半同步复制
① 配置过程:
实现半同步复制的功能很简单,只需在mysql的主服务器和从服务器上安装个google提供的插件即可实现,master上使用semisync_master.so,slave上使用semisync_slave.so插件即可实现,插件在mysql通用二进制的mysql/lib/plugin目录内;rpm包安装的 /usr/lib64/mysql/plugin/目录内。
注:在从服务器开启半同步的时候,建立数据库很快;关闭从服务器的I/O线程的时候,主服务器创建数据库的时候会等待,当超过超时时间的时候才会创建;一旦超时,会自动降级为异步,再次执行操作不会等待
// 主服务器上需要安装的插件 MariaDB [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like '%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)]> install plugin rpl_semi_sync_slave soname 'semisync_slave.so'; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show global variables like '%semi%';
②其配置步骤如下;主从复制模型测试~~
master:
[root@node1 ~]# vim /etc/my.cnf log_bin=mysql-bin //开启二进制日志 server_id=1 innodb_file_per_table=1 skip_name_resolve=1 rpl_semi_sync_master_enabled=1 //开启半同步复制; 配置完成重启mariadb服务 rpl_semi_sync_master_timeout=5000 //设置复制的超时时间,单位为毫秒,超过这个时长,会自动降级为异步复制 MariaDB [(none)]> show global variables like '%semi%'; +------------------------------------+-------+ | Variable_name | Value | +------------------------------------+-------+ | rpl_semi_sync_master_enabled | ON | | rpl_semi_sync_master_timeout | 5000 | | rpl_semi_sync_master_trace_level | 32 | | rpl_semi_sync_master_wait_no_slave | ON | +------------------------------------+-------+ 4 rows in set (0.00 sec) MariaDB [(none)]> grant replication slave,replication client on *.* to 'copy'@'172.16.116.232' identified by 'passwd'; //授权 Query OK, 0 rows affected (5.01 sec) MariaDB [(none)]> show master status; //查看二进制日志的事件位置 +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 419 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
slave:
[root@node2 ~]# vim /etc/my.cnf relay_log=relay-log server_id=11 innodb_file_per_table=1 skip_name_resolve=1 rpl_semi_sync_slave_enabled=1 //开启半同步复制 ; 配置完成重启mariadb服务 MariaDB [(none)]> show global variables like '%semi%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | rpl_semi_sync_slave_enabled | ON | | rpl_semi_sync_slave_trace_level | 32 | +---------------------------------+-------+ 2 rows in set (0.00 sec) MariaDB [(none)]> change master to master_host='172.16.116.231',master_user='copy',master_password='passwd',master_log_file='mysql-bin.000004',master_log_pos=419; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave;//启动复制线程,看到上图显示Rpl_semi_sync_master_clients=1 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.116.231 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 419 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 419 Relay_Log_Space: 817 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
测试过程如下~~~
master上创建新的数据库;创建test1的时候很快的速度就完成了;在创建test2之前,关闭slave的io_thread,可以看到创建test2会有延迟,到达设定的值5m的时候(rpl_semi_sync_master_timeout=5000)才会创建完成;但是再创建test3的时候会很快完成,说明确实降级为异步复制了 MariaDB [(none)]> create database test1; Query OK, 1 row affected (0.00 sec) //在slave上执行 stop slave io_thread; MariaDB [(none)]> create database test2; Query OK, 1 row affected (5.00 sec) //看到延迟为5m MariaDB [(none)]> create database test3; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 0 | //客户端为0 | Rpl_semi_sync_master_net_avg_wait_time | 17088 | | Rpl_semi_sync_master_net_wait_time | 85444 | | Rpl_semi_sync_master_net_waits | 5 | | Rpl_semi_sync_master_no_times | 3 | | Rpl_semi_sync_master_no_tx | 4 | | Rpl_semi_sync_master_status | OFF | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 697 | | Rpl_semi_sync_master_tx_wait_time | 1394 | | Rpl_semi_sync_master_tx_waits | 2 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 2 | +--------------------------------------------+-------+ 14 rows in set (0.00 sec)
2.4、基于ssl实现主从复制
mariadb数据复制是明文的,为了加强数据内容安全性,基于ssl实现对复制内容的加密。
实现过程:
2.4.1、创建CA,并自签证书
# echo 01 > /etc/pki/CA/serial # touch /etc/pki/CA/index.txt # (umask 077;openssl genrsa -out /etc/pki/CA/private/cakey.pem 2048) # openssl req -new -x509 -key /etc/pki/CA/private/cakey.pem -out /etc/pki/CA/cacert.pem -days 3650 Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:Beijing Locality Name (eg, city) [Default City]:Beijing Organization Name (eg, company) [Default Company Ltd]:mariadb Organizational Unit Name (eg, section) []:it Common Name (eg, your name or your server's hostname) []:CA Email Address []:ca@haha.com //为主服务器(172.16.116.231)颁发证书 #openssl ca -in master.csr -out master.crt -days 365 //为从服务器(172.16.116.232)颁发证书; #openssl ca -in slave.csr -out slave.crt -days 365
2.4.2、主服务器(172.16.116.231)申请证书;
# hostname master [root@master ~]# mkdir /etc/mysql/ssl [root@master ~]# (umask 077;openssl genrsa -out /etc/mysql/ssl/master.key 2048) Generating RSA private key, 2048 bit long modulus .....................................................................+++ ...................+++ e is 65537 (0x10001) [root@master ~]# openssl req -new -key /etc/mysql/ssl/master.key -out /etc/mysql/ssl/master.csr Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:Beijing Locality Name (eg, city) [Default City]:Beijing Organization Name (eg, company) [Default Company Ltd]:mariadb Organizational Unit Name (eg, section) []:it Common Name (eg, your name or your server's hostname) []:master Email Address []:master@haha.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
2.4.3、为从服务器(172.16.116.232)颁发证书;
# hostname slave [root@slave ~]# mkdir /etc/mysql/ssl [root@slave ~]# (umask 077;openssl genrsa -out /etc/mysql/ssl/slave.key 2048) Generating RSA private key, 2048 bit long modulus .+++ ...+++ e is 65537 (0x10001) [root@slave ~]# openssl req -new -key /etc/mysql/ssl/slave.key -out /etc/mysql/ssl/slave.csr Country Name (2 letter code) [XX]:CN State or Province Name (full name) []:Beijing Locality Name (eg, city) [Default City]:Beijing Organization Name (eg, company) [Default Company Ltd]:mariadb Organizational Unit Name (eg, section) []:it Common Name (eg, your name or your server's hostname) []:slave Email Address []:slave@haha.com Please enter the following 'extra' attributes to be sent with your certificate request A challenge password []: An optional company name []:
2.4.4、修改CA证书权限及mysql的配置文件;将CA证书的公钥cacert.pem复制到主从服务器中
master
[root@master ~]# cp cacert.pem /etc/mysql/ssl/ [root@master ~]# cd /etc/mysql/ssl/ [root@master ssl]# chown -R mysql.mysql ./* [root@master ssl]# chmod 600 ./* [root@master ~]# vim /etc/my.cnf log_bin=mysql-log server_id=1 innodb_file_per_table=1 skip_name_resolve=1 ssl ssl_ca=/etc/mysql/ssl/cacert.pem ssl_cert=/etc/mysql/ssl/master.crt ssl_key=/etc/mysql/ssl/master.key
slave
[root@slave ~]# cp cacert.pem /etc/mysql/ssl/ [root@slave ~]# cd /etc/mysql/ssl/ [root@slave ssl]# chown -R mysql.mysql ./* [root@slave ssl]# chmod 600 ./* [root@slave ssl]# ll total 16 -rw------- 1 mysql mysql 1367 Nov 7 20:19 cacert.pem -rw------- 1 mysql mysql 4544 Nov 7 20:17 slave.crt -rw------- 1 mysql mysql 1675 Nov 7 20:14 slave.key [root@slave ssl]# vim /etc/my.cnf relay_log=relay-log server_id=11 innodb_file_per_table=1 skip_name_resolve=1 ssl ssl_ca=/etc/mysql/ssl/cacert.pem ssl_cert=/etc/mysql/ssl/slave.crt ssl_key=/etc/mysql/ssl/slave.key
2.4.5、启动mysql服务;授权用户;测试复制是否加密
master
MariaDB [(none)]> show global variables like '%ssl%'; +---------------+------------------------------+ | Variable_name | Value | +---------------+------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/ssl/master.crt | | ssl_cipher | | | ssl_key | /etc/mysql/ssl/master.key | +---------------+------------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> grant replication slave,replication client on *.* to 'copy'@'172.16.116.232' identified by 'passwd' require ssl; //授权 Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> flush privileges; Query OK, 0 rows affected (0.01 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-log.000003 | 506 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) 测试~~~ MariaDB [(none)]> create database testdb; Query OK, 1 row affected (0.00 sec) MariaDB [(none)]> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-log.000003 | 779 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
slave
MariaDB [(none)]> show global variables like '%ssl%'; +---------------+---------------------------+ | Variable_name | Value | +---------------+---------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | /etc/mysql/ssl/cacert.pem | | ssl_capath | | | ssl_cert | /etc/mysql/ssl/slave.crt | | ssl_cipher | | | ssl_key | /etc/mysql/ssl/slave.key | +---------------+---------------------------+ 7 rows in set (0.00 sec) MariaDB [(none)]> change master to master_host='172.16.116.231',master_user='copy',master_password='sql-log.000003',master_log_pos=506,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl.crt',master_ssl_key='/etc/mysql/ssl/slave.key'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> change master to master_host='172.16.116.231',master_user='copy',master_password='sql-log.000003',master_log_pos=506,master_ssl=1,master_ssl_ca='/etc/mysql/ssl/cacert.pem',master_ssl.crt',master_ssl_key='/etc/mysql/ssl/slave.key'; Query OK, 0 rows affected (0.02 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.116.231 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-log.000003 Read_Master_Log_Pos: 506 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 529 Relay_Master_Log_File: mysql-log.000003 Slave_IO_Running: Yes //准备就绪 Slave_SQL_Running: Yes //准备就绪 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 506 Relay_Log_Space: 817 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/slave.key Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec) //查看测试的结果;复制成功!~~ MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | | testdb | +--------------------+ 5 rows in set (0.00 sec) MariaDB [(none)]> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.16.116.231 Master_User: copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-log.000003 Read_Master_Log_Pos: 779 //复制的位置是779 Relay_Log_File: relay-log.000002 Relay_Log_Pos: 802 Relay_Master_Log_File: mysql-log.000003 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 779 Relay_Log_Space: 1090 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/mysql/ssl/cacert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/mysql/ssl/slave.crt Master_SSL_Cipher: Master_SSL_Key: /etc/mysql/ssl/slave.key Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 1 row in set (0.00 sec)
至此,MariaDB数据库的复制的几种模型介绍完毕! O(∩_∩)O 谢谢~~~
转载于:https://blog.51cto.com/bengbengtu/1710645