一、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 {} \;
安装mysql软件包
wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.33-li
nux-glibc2.12-x86_64.tar
解压
[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将项目文件移动到/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)在/usr/local/mysql/目录下创建mysql-files目录
mkdir /usr/local/mysql/mysql-files
修改mysql-files的权限为750 所属的组和属主都是mysqlchown 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
初始化数据库/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密码)
判断是否生成了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
将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
打开防火墙或者端口
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
打开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主从架构的搭建(主要)
(一)集群
1、集群主要类型
(1)高可用集群 High Avaible Cluster HA cluster
(2)高可用集群是指通过特殊软件,把独立的服务器连接起来,组成一个能够提供故障切换(Fail Over)功能的集群
2、高可用标准
可用性级别指标 年宕机时间 描述 叫法 99% 3.65天 基本可用系统 2个9 99.9% 8.76小时 可用系统 3个9 99.99% 52.6分钟 高可用系统 4个9 99.999% 5.3分钟 扛故障系统 5个9 99.9999% 32秒 容错系统 6个9
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、复制架构体系
(1)双机热备 主从复制
(默认情况下master接收读写,从服务器只接受读)
(2)级联(串联)复制
(可以分担读的压力;中间服务器出现故障就瘫痪了)
(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 yestar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar zx yescp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql yesmkdir /usr/local/mysql/mysql-files
echo mysql-files yesgrep 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 -pALTER 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 root@192.168.1.22:/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 yestar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
echo tar zx yescp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
echo copy file to /usr/local/mysql yesmkdir /usr/local/mysql/mysql-files
echo mysql-files yesgrep 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
测试是否同步:
master-mysql:
mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)
mysql> create database if not exists test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> use test;
Database changed
mysql> create table student(id int primary key,name varchar(45) not null,gender varchar(4) not null);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values(1,'张三','男');
Query OK, 1 row affected (0.02 sec)
mysql> insert into student values(2,'凤凰','女');
Query OK, 1 row affected (0.01 sec)
mysql> insert into student values(3,'张三','男');
Query OK, 1 row affected (0.00 sec)
slave-mysql:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from student;
+----+--------+--------+
| id | name | gender |
+----+--------+--------+
| 1 | 张三 | 男 |
| 2 | 凤凰 | 女 |
| 3 | 张三 | 男 |
+----+--------+--------+
3 rows in set (0.00 sec)