N46030-段鹏-第十四周作业
1 MariaDB主从复制原理
2 MariaDB一主一从架构构建
准备两台纯新的CentOS7.6服务器，其中主机称为node1的服务器地址为192.168.130.132，主机称为node2的服务器地址为192.168.130.133
主从部署 master 192.168.130.132 vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] log-bin server-id=132 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid 从节点是否需要建立账号 MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.130.%' identified by 'centos'; Query OK, 0 rows affected (0.002 sec) MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 527 | +--------------------+-----------+ 1 row in set (0.000 sec) slave 192.168.130.133 vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=133 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mariadb/mariadb.log pid-file=/run/mariadb/mariadb.pid MariaDB [(none)]> help change master to CHANGE MASTER TO MASTER_HOST='192.168.130.132', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000001', MASTER_LOG_POS=527; MariaDB [(none)]> CHANGE MASTER TO -> MASTER_HOST='10.0.0.8', -> MASTER_USER='repluser', -> MASTER_PASSWORD='centos', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mariadb-bin.000001', -> MASTER_LOG_POS=527; Query OK, 0 rows affected (0.016 sec) MariaDB [(none)]> start slave; Query OK, 0 rows affected (0.003 sec) MariaDB [(none)]> show slave status\G
3 MariaDB级联复制
上一节中我们完成了一主一从的MariaDB的主从复制架构，但是有的时候，我们期望有一个后备的MariaDB的节点，用只
动词}备份存储数据，不需要对外提供服务。为了实现该功能，我们可以在之前的一主一从架构上进行调整，即将从节点的二进
制文件打开，然后给他配置一个远程同步数据用户，接着使用一台新的服务器做为从服务器的从属，同步从服务器数据的即
柯林斯，下面我们在原来的架构上添加一台新的CentOS7.6，作为节点3，IP地址为192.168.132.134
master 10.0.0.8 级联slave 10.0.0.18 slave 10.0.0.28 master yum install -y mariadb-server master 10.0.0.8 vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] log-bin server-id=8 systemctl restart mariadb MariaDB [(none)]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28228 | | mariadb-bin.000002 | 344 | +--------------------+-----------+ 2 rows in set (0.000 sec) MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'centos'; Query OK, 0 rows affected (0.002 sec) [root@localhost ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/all.sql [root@localhost ~]# scp /data/all.sql 10.0.0.18:/data [root@localhost ~]# scp /data/all.sql 10.0.0.28:/data 级联slave 10.0.0.18 vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 log-bin read-only log_slave_updates systemctl restart mariadb MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'centos'; vim /data/all.sql CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344; MariaDB [(none)]> set sql_log_bin=0; Query OK, 0 rows affected (0.000 sec) MariaDB [(none)]> source /data/all.sql Query OK, 0 rows affected (0.000 sec) Query OK, 0 rows affected (0.001 sec) MariaDB [mysql]> show master logs; +--------------------+-----------+ | Log_name | File_size | +--------------------+-----------+ | mariadb-bin.000001 | 28228 | | mariadb-bin.000002 | 344 | +--------------------+-----------+ 2 rows in set (0.000 sec) MariaDB [mysql]>start slave; 第三个slave节点 vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 read-only vim /data/all.sql CHANGE MASTER TO MASTER_HOST='10.0.0.18', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=344; [root@localhost ~]# mysql < /data/all.sql [root@localhost ~]# mysql -e 'start slave;'
4 MariaDB半同步复制
默认情况下mysql复制功能是异步的，异步复制可以提供最佳性能，主库吧binlog日志发送给从库就结束
，并不验证从库
半同步复制 master 10.0.0.8 slave1 10.0.0.18 slave2 10.0.0.28 master [root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=8 log-bin plugin-load-add=semisync_master rpl_semi_sync_master_enabled=ON rpl_semi_sync_master_timeout=3000 systemctl restart mariadb slave1 [root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=18 plugin_load_add=semisync_slave rpl_semi_sync_slave_enabled=ON systemctl restart mariadb CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=344; slave2 [root@localhost ~]# vim /etc/my.cnf.d/mariadb-server.cnf [mysqld] server-id=28 plugin_load_add=semisync_slave rpl_semi_sync_slave_enabled=ON systemctl restart mariadb CHANGE MASTER TO MASTER_HOST='10.0.0.8', MASTER_USER='repluser', MASTER_PASSWORD='centos', MASTER_PORT=3306, MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=344; master MariaDB [(none)]> show global status like '%semi%'; +--------------------------------------------+-------+ | Variable_name | Value | +--------------------------------------------+-------+ | Rpl_semi_sync_master_clients | 2 | | Rpl_semi_sync_master_get_ack | 1 | | Rpl_semi_sync_master_net_avg_wait_time | 0 | | Rpl_semi_sync_master_net_wait_time | 0 | | Rpl_semi_sync_master_net_waits | 1 | | Rpl_semi_sync_master_no_times | 1 | | Rpl_semi_sync_master_no_tx | 1 | | Rpl_semi_sync_master_request_ack | 1 | | Rpl_semi_sync_master_status | ON | | Rpl_semi_sync_master_timefunc_failures | 0 | | Rpl_semi_sync_master_tx_avg_wait_time | 0 | | Rpl_semi_sync_master_tx_wait_time | 0 | | Rpl_semi_sync_master_tx_waits | 0 | | Rpl_semi_sync_master_wait_pos_backtraverse | 0 | | Rpl_semi_sync_master_wait_sessions | 0 | | Rpl_semi_sync_master_yes_tx | 0 | | Rpl_semi_sync_slave_send_ack | 0 | | Rpl_semi_sync_slave_status | OFF | +--------------------------------------------+-------+ 18 rows in set (0.002 sec) 关闭俩个slave节点 mysql -e 'stop slave;' master上创建数据库 MariaDB [(none)]> create database test2; Query OK, 1 row affected (0.004 sec) MariaDB [(none)]> create database test3; Query OK, 1 row affected (3.002 sec)
5 MariaDB高可用方案MHA
准备三台新安装的CentOS7.6服务器，并使用yum安装MariaDB10.2.23，主机名分别为node1，node2，node3，
IP地址分别为192.168.130.132-134
浙公网安备 33010602011771号