第五周
1、如果主节点已经运行了一段时间，且有大量数据时，如何配置并启动slave节点（写出操作步骤）
环境准备：两台centos8
10.0.0.8
10.0.0.18
#主节点下载mariadb 设为开机自启
[root@master ~]#[root@master ~]# yum -y install mariadb-server;systemctl enable --now mariadb
[root@master ~]#vim /etc/my.cnf
[mysqld]
log-bin
[root@master ~]# systemctl restart mysqld
#[root@master ~]# mysql
MariaDB [(none)]>
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
3 rows in set (0.000 sec)
#模拟数据库已经运行了一段时间，上面进行了一些操作
MariaDB [(none)]> create database db1;
MariaDB [(none)]> use db1;
Database changed
MariaDB [db1]> create table test(id int auto_increment primary key ,name char(5));
Query OK, 0 rows affected (0.008 sec)
MariaDB [db1]> insert test (id,name) values(1,'yang');
Query OK, 1 row affected (0.002 sec)
MariaDB [db1]> insert test(id,name)values(2,'wang');
MariaDB [db1]> select *from test;
+----+------+
| id | name |
+----+------+
| 1 | yang |
| 2 | wang |
+----+------+
2 rows in set (0.000 sec)
#此时的二进制位置是
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 550|
1 row in set (0.000 sec)
#创建授权账号
MariaDB [db1]> grant replication slave on *.* to repluser@'10.0.0.%' identified by 'magedu'；
#在主服务器完全备份
[root@master ~]#mkdir /backup
[root@master ~]#mysqldump -A -F --single-transaction --master-data=1 > /backup/fullbackup_`date +%F_%T`.sql
[root@master ~]# ll /backup
total 472
-rw-r--r-- 1 root root 480092 Oct 16 19:47 fullbackup_2020-10-16_19:47:34.sql
#把完全备份的传给从节点18
[root@master ~]#scp /backup/fullbackup_2020-10-16_19:47:34.sql 10.0.0.18:/root
#配置从节点
#下载数据库修改配置文件
[root@slave ~]#dnf -y install mariadb-server
root@slave~]#systemctl enable --now mariadb
[root@slave~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
read-only
[root@slave ~]#systemctl restart mariadb
#将完全备份还原到新的从节点
#配置从节点，从完全备份的位置之后开始复制
[root@slave ~]#grep '^CHANGE MASTER' /data/fullbackup_2020-10-16_19:47:34.sql
CHANGE MASTER TO MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
#注意语法，在备份文件中添加主节点的服务器 账号和密码端口号
[root@slave ~]#vim /data/fullbackup_2020-10-16_19:47:34.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
#把备份导入到数据库中
[root@slave ~]#mysql < /data/fullbackup_2020-10-16_19:47:34.sql
[root@slave ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.11-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
#开启线程
MariaDB [(none)]> start slave;
#开启之后线程连接成功
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#主节点的数据已经完全同步了过来
MariaDB [db1]> select *from db1.test;
+----+------+
| id | name |
+----+------+
| 1 | yang |
| 2 | wang |
+----+------+
2 rows in set (0.000 sec)
#主节点上出现binlog dump 连接
MariaDB [(none)]> show processlist;
2、当master服务器宕机，提升一个slave成为新的master（写出操作步骤）
在第一个道题实验主从架构的基础上再加一个从节点达到1主两从
#增加一个从节点10.0.0.28
#把完全备份的传给从节点18
[root@master ~]#scp /backup/fullbackup_2020-10-16_19:47:34.sql 10.0.0.18:/root
[root@slave2 ~]#dnf -y install mariadb-server;systemctl enable --now mariadb
[root@slave2 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=28
read-only
#
[root@slave2 ~]#systemctl restart mariadb
#修改文件同步位置
[root@slave2 ~]#vim fullbackup_2020-10-16_19:47:34.sql
MCHANGE MASTER TO
MASTER_HOST='10.0.0.8',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000003', MASTER_LOG_POS=389;
#导入主节点的数据，同步成功
[root@slave2 ~]#mysql <fullbackup_2020-10-16_19:47:34.sql
[root@slave2 ~]#mysql
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.8
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000003
Read_Master_Log_Pos: 389
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 557
Relay_Master_Log_File: mariadb-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
#模拟主节点宕机，先导入testlog.log 执行十万条记录，看哪个节点同步的数据多 选谁等新主节点
[root@master ~]# mysql
MariaDB [(none)]> use db1
#执行存储过程的时候 停掉服务
MariaDB [db1]> call sp_testlog;
ERROR 1053 (08S01): Server shutdown in progress
[root@master ~]# systemctl stop mariadb
#比较两个从节点的同步情况，选择slave1作为新主
[root@slave1~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000002
4172783
mariadb-bin.000003
4172615
0
[root@slave2 ~]#cat /var/lib/mysql/relay-log.info
5
./mariadb-relay-bin.000002
4160557
mariadb-bin.000003
4103890
0
#在18机器上修改作为新主
#新master修改配置文件，关闭read-only配置
[root@slave1 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=18
log-bin
[root@slave1 ~]#mysql
MariaDB [hellodb]>stop slave;
MariaDB [hellodb]>reset slave all;
[root@centos8 ~]#systemctl restart mariadb
[root@slave1 ~]#mysql
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 330 |
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| db1 |
| db2 |
| information_schema |
| mysql |
| performance_schema |
+--------------------+
5 rows in set (0.001 sec)
#18上完全备份并拷贝给28
[root@slave1~]#mysqldump -A -F --single-transaction --master-data=1 > backup.sql
[root@slave1~]#scp backup.sql 10.0.0.28:/root
#在28上修改备份文件指向18
[root@slave2 ~]#vim backup.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.18',
MASTER_USER='repluser',
MASTER_PASSWORD='magedu',
MASTER_PORT=3306,
MASTER_LOG_FILE='mariadb-bin.000002', MASTER_LOG_POS=375;
#导入slave1的数据，在从节点上重新指向18，作为18的从节点
[root@slave2 ~]#mysql <backup.sql
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
***************************