2024.8.06（mysql主从）

一、glibc安装（回顾）

mysql

清空/etc/目录下的my.cnf

ls -l /etc/my.cnf

rm -rf /etc/my.cnf

yum -y remove mariadb

find / -name "*mysql*" -exec rm -rf {} \;

1、安装mysql软件包

wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.33-li

nux-glibc2.12-x86_64.tar

2、解压

[root@Mysql-001 ~]# ls
mysql-8.0.33-linux-glibc2.12-x86_64.tar
[root@Mysql-001 ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar 
[root@Mysql-001 ~]# tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz 
[root@Mysql-001 ~]# ls mysql-8.0.33-linux-glibc2.12-x86_64
bin  docs  include  lib  LICENSE  man  README  share  support-files

3、将项目文件移动到/usr/local/mysql/

cp -r mysql-8.0.33-linux-glibc2.12-x86_64/ /usr/local/mysql/
yum list installed | grep libaio   #检查是否有libaio
libaio.x86_64                         0.3.109-13.el7                   @anaconda
[root@Mysql-001 ~]# useradd -r -s /sbin/nologin mysql
[root@Mysql-001 ~]# id mysql
uid=997(mysql) gid=995(mysql) 组=995(mysql)

4、在/usr/local/mysql/目录下创建mysql-files目录

mkdir /usr/local/mysql/mysql-files

5、修改mysql-files的权限为750 所属的组和属主都是mysql

chown mysql:mysql /usr/local/mysql/mysql-files/
chmod 750 /usr/local/mysql/mysql-files/
ll /usr/local/mysql/
总用量 292
drwxr-xr-x  2 root  root    4096 8月   5 09:48 bin
drwxr-xr-x  2 root  root      38 8月   5 09:48 docs
drwxr-xr-x  3 root  root     282 8月   5 09:48 include
drwxr-xr-x  6 root  root     201 8月   5 09:48 lib
-rw-r--r--  1 root  root  284945 8月   5 09:48 LICENSE
drwxr-xr-x  4 root  root      30 8月   5 09:48 man
drwxr-x---  2 mysql mysql      6 8月   5 09:55 mysql-files
-rw-r--r--  1 root  root     666 8月   5 09:48 README
drwxr-xr-x 28 root  root    4096 8月   5 09:48 share
drwxr-xr-x  2 root  root      77 8月   5 09:48 support-files

6、初始化数据库

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
2024-08-05T02:02:01.330394Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.33) initializing of server in progress as process 2825
2024-08-05T02:02:01.345707Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2024-08-05T02:02:02.337448Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2024-08-05T02:02:04.033580Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ax8c5SwT(p/u（mysql密码）

7、判断是否生成了data目录

ls /usr/local/mysql     #有data目录就是初始化成功了

把mysql.server文件放到/etc/init.d/目录下(方便启动mysql服务 service mysql start)

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

service mysql8 start

#修改密码

usr/local/mysql/bin/mysql -uroot -p

alter usr 'root@localhost' identified with mysql_native_password BY 'Zhang@2002';

#添加开机启动

systemctl enable mysqld

chkconfig --list

chkconfig --add mysql8

chkconfig --list

8、将mysql的bin也添加到$PATH

sed -i  '$aexport PATH=/usr/local/mysql/bin/:$PATH' /etc/profile

sed -n '$aexport PATH=/usr/local/mysql/bin/:$PATH' /etc/profile

source /etc/profile

mysql

select host,user from mysql.user;
create user 'aaa'@'%' identified by 'aaaa';
select host,user from mysql.user;
quit

9、打开防火墙或者端口

firewall-cmd --zone=public --add-port=3306/tcp --permanent

firewall-cmd --reload

10、打开navicat远程登陆mysql

create role a;
select host,user from mysql.user;
#角色保存在user表里面
grant all on *.* to a;
#给a所有的权限
show grants for a;
grant a to aaa;
#把a的权限全部给aaa
show grants for aaa;
quit
vim /usr/local/mysql/mysql.cnf

在配置文件中添加activate_all_roles_on_login=on

glibc安装，my.cnf在项目目录之下；

rpm安装，my.cnf文件在/etc/my.cnf

service mysql8 restart

二、主从复制

1、备份的三种类型

1. 热备份

2. 逻辑备份

3. 物理备份（最烧钱）

2、技术

1. 熟悉mysql数据库常见的主从架构

2. 理解mysql主从架构实现原理

3. 掌握mysql主从架构的搭建（主要）

3、集群

1. 集群主要类型

1.1 高可用集群 High Avaible Cluster HA cluster

1.2 高可用集群是指通过特殊软件，把独立的服务器连接起来，组成一个能够提供故障切换(Fail Over)功能的集群

2. 高可用标准

3. 常用的集群架构

1. mysql replication
2. mysql cluster
3. mysql group replication MGR

4. Maradb Galera CLuster

5.MHAlkeepalived HeatBeatLvs，Haproxy等技术构建高可用集群

4. 复制原理

1. replication,可以实现将数据从一台数据库服务器(mster)复制到多台数据库服务器slave

2. 默认情况下，replication属于异步复制，所以无需长连接
3. 工作原理
（1）主服务器master
（2）从服务器 slave
（3）描述

（从服务器不能修改，只能修改主服务器）

（1）slave端的io线程发送请求给master端的binlog dump线程
（2）master端的binlog dump线程获取二进制日志信息(文件名和位置信息)发送给slave端的io线程
（3）slave端的io线程获取到内容，依次写到slave端relaylog(中继日志)并把master端的binlog文件名和位置记录到master。info里头，
（4）slave端的sql线程检测到relaylog中的内容更新，就会解析relaylog中的更新内容，并执行这些操作，从而达到和master端数据

5. 复制架构体系

5.1 双机热备 主从复制

（默认情况下master接收读写，从服务器只接受读） 

5.2 级联（串联）复制

  （可以分担读的压力；中间服务器出现故障就瘫痪了）              

5.3 并联复制 一主多从

（解决单点故障；承担更多读的压力；从服务器都从主服务器读取数据，master服务器压力大） 

4、主从同步实际操作

创建两台虚拟机（master-mysql    slave-mysql） 

master-mysql：

[root@master-mysql ~]#  yum -y install ntpdate.x86_64
[root@master-mysql ~]# ntpdate cn.ntp.org.cn
 6 Aug 11:21:00 ntpdate[1204]: adjust time server 182.92.12.11 offset 0.007708 sec
[root@master-mysql ~]# yum -y install rsync

安装mysql-8.0.33-linux-glibc2.12-x86_64.tar包

[root@master-mysql ~]# vim mysql.sh

#!/bin/bash
yum list installed |grep libaio
if [ $? ne 0 ]; then
        yum -y install libaio
fi
echo libaio yes
rm -rf /etc/my.cnf
echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar zx yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql   yes

mkdir /usr/local/mysql/mysql-files
echo mysql-files yes

grep mysql /etc/passwd

 useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files

/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
source /etc/profile

[root@master-mysql ~]# source mysql.sh

[root@master-mysql ~]# service mysql8 start
[root@master-mysql ~]# /usr/local/mysql/bin/mysql -uroot -p

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Zhang@2002';
quit
[root@master-mysql ~]# vim /usr/local/mysql/my.cnf

[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3306
log-error=/usr/local/mysql/data/db01-master.err
log-bin=/usr/local/mysql/data/binlog
server-id=10
character_set_server=utf8mb4

设置开机自启动

[root@master-mysql ~]# systemctl enable mysql8

[root@master-mysql ~]# chkconfig --add mysql8
[root@master-mysql ~]# chkconfig mysql8 on
[root@master-mysql ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysql8             0:关    1:关    2:开    3:开    4:开    5:开    6:关
netconsole         0:关    1:关    2:关    3:关    4:关    5:关    6:关
network            0:关    1:关    2:开    3:开    4:开    5:开    6:关

[root@master-mysql ~]# service mysql8 restart

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[root@master-mysql ~]# service mysql8 stop

[root@master-mysql ~]# rm -rf /usr/local/mysql/data/auto.cnf

#删除auto.cnf,否则会是主从失败

[root@master-mysql ~]# rsync -av /usr/local/mysql/data [email protected]:/usr/local/mysql

[root@master-mysql ~]# service mysql8 start

[root@master-mysql ~]# mysql -pZhang@2002

mysql> create user 'slave'@'%' identified by 'Zhang@2002';
Query OK, 0 rows affected (0.01 sec)
 
mysql> grant replication slave on *.* to 'xiaojiang'@'%';
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+
1 row in set (0.01 sec)
 
mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
 
mysql> create database if not exists abc charset utf8;
ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |     1183 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
#File（当前文件的文件名）  Position（当前位置）

slave-mysql： 

[root@slave-mysql ~]#  yum -y install ntpdate.x86_64

[root@master-mysql ~]# ntpdate cn.ntp.org.cn
 6 Aug 11:21:00 ntpdate[1204]: adjust time server 182.92.12.11 offset 0.007708 sec
[root@slave-mysql ~]# yum -y install rsync

安装mysql-8.0.33-linux-glibc2.12-x86_64.tar包

[root@slave-mysql ~]# vim mysql.sh

#!/bin/bash
yum list installed |grep libaio
if [ $? ne 0 ]; then
        yum -y install libaio
fi
echo libaio yes
rm -rf /etc/my.cnf
echo remo my.cnf yes

tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar zx yes

cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql   yes

mkdir /usr/local/mysql/mysql-files
echo mysql-files yes

grep mysql /etc/passwd

        useradd -r -s /sbin/nologin mysql

chown mysql:mysql /usr/local/mysql/mysql-files
chmod 750 /usr/local/mysql/mysql-files

注释掉或者删掉

#/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/

#/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data

cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8

sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
source /etc/profile

[root@slave-mysql ~]# source mysql.sh

[root@slave-mysql ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
port=3310
log-error=/usr/local/mysql/data/db01-slave.err
relay-log=/usr/local/mysql/data/relaylog
server-id=11
character_set_server=utf8mb4
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

查看/usr/local/mysql目录下有没有data，有的话就可以开启服务器

[root@slave-mysql ~]# service mysql8 start

[root@slave-mysql ~]# mysql -pZhang@2002;

mysql> change master to
    -> master_host='192.168.1.21',
    -> master_user='xiaojiang',
    -> master_password='Zhang@2002',
    -> master_port=3306,
    -> master_log_file='binlog.000003',
    -> master_log_pos=1183;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
 
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
 
mysql> show slave status\G;
需要ssl非对称加密

[root@slave-mysql ~]# mysql -uxiaojiang -pZhang@2002 -h192.168.1.21 -P3306 --get-server-public-key          #获得公钥

登陆slave服务器本地的数据库

[root@slave-mysql ~]# mysql -pZhang@2002 -P3306

停用slave服务，重新配置slave服务

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
 
mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.03 sec)
 
mysql> change master to
    -> master_host='192.168.1.21',
    -> master_user='xiaojiang',
    -> master_password='Zhang@2002',
    -> master_port=3306,
    -> master_log_file='binlog.000003',
    -> master_log_pos=1183;
Query OK, 0 rows affected, 9 warnings (0.01 sec)

 启动slave服务

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
 
mysql> show slave status\G;
 
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 192.168.1.21
                  Master_User: xiaojiang
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 1183
               Relay_Log_File: relaylog.000002
                Relay_Log_Pos: 323
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@slave-mysql ~]# mysql -pZhang@2002 -P3306