安全配置,生产环境
[root@mysql01 ~]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
[root@mysql01 ~]# service mysql8 restart
Shutting down MySQL... SUCCESS!
Starting MySQL.. SUCCESS!
[root@mysql01 ~]# /usr/local/mysql/bin/mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
# 输入2次n,然后一路y下去
mysql> show grants for a;
+-------------------------------+
| Grants for a@% |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)
mysql> grant all on *.* to a;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for aaa;
+----------------------------------------------------+
| Grants for aaa@% |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `aaa`@`%` |
| GRANT SELECT, INSERT ON `test`.`user` TO `aaa`@`%` |
| GRANT `a`@`%` TO `aaa`@`%` |
+----------------------------------------------------+
3 rows in set (0.01 sec)
注意:角色不生效的解决方法
在/usr/local/mysql/my.cnf下添加一行
activate_all_roles_on_login=on # 激活所有角色 在配置文件中添加一行
glibc安装,my.cnf 在项目目录之下;
主从复制
=======概述=======
一、备份的三种类型
1. 热备份
2. 逻辑备份
3. 物理备份
二、应用场景:
⼊职企业,发现企业架构为一主多从,但是两台从服务器和主库不同步,但是每天会全库北方主服务器上的数据到从服务器,由于数据量不是很大,所以没有人处理主从不同步的问题。
业对数据安全性越来越重视,由于常规备份不能实时记录数据库的所有状态,为了能够保障数据库实时备份冗余,希望将现有的单机数据库变成双机热备。
- 集群概述:
1、集群主要类型
(1)高可用集群 High Avaible Cluster,即HA cluster
(2)高可用集群是指通过特殊软件,把独⽴的服务器连接起来,组成一个能够提供故障切换(Fail Over)功能的集群。
2、高可用标准
3、常⽤的集群架构
(1)mysql replication
(2)mysql cluster
(3)mysql group replication MGR
(4)Maradb Galera CLuster
(5)MHA|keepalived|HeatBeat|Lvs,Haproxy等技术构建高可用集群
4、复制原理:
(1)replication,可以实现将数据从一台数据库服务器(mster)复制到多台数据库服务器slave
(2)默认情况下,replication属于异步复制,所以无需长连接。
(3)工作原理:
slave端的io线程发送请求给master端的binlog dump线程
master端的binlog dump线程获取⼆进制⽇志信息(文件名和位置信息)发送给slave端的io线程。
slave端的io线程获取到内容,依次写到slave端relay log(中继日志)并把master端的binlog文件名和位置记录到master。
slave端的sql线程检测到relaylog中的内容更新,就会解析relaylog中的更新内容,并执行这些操作,从而达到和master端数据一致。
5、复制架构体系:
(1)双机热备,主从复制:默认情况下 master接收读写,从服务器只接受读。
(2)级联复制:可以分担读的压力;中间服务器出现故障,就瘫痪了。
(3)并联复制一主多从:解决单点故障;承担更多的读的压力;从服务器都从主服务器读取数据,master服务器压力大。
(4)双主复制:看起来可以同时接收读写,实际运作中只有⼀台服务器在工作,另外⼀台只接受读。
=========环境准备==========
两者数据库的的版本必须一致;从数据库不能做任何增删改查操作;master端和slave端的server-id不能一致;保持master和slave端初始数据一致;master开启二进制日志,slave必须开始relay日志。
主机:master-mysql(192.168.2.45) slave-mysql(192.168.2.43)
关闭防火墙和selinux
[root@master-mysql ~]# setenforce 0
[root@master-mysql ~]# vim /etc/selinux/config
SELINUX=permissive
[root@slave-mysql ~]# setenforce 0
[root@slave-mysql ~]# vim /etc/selinux/config
SELINUX=permissive
[root@master-mysql ~]# yum -y install ntpdate.x86_64
[root@slave-mysql ~]# yum -y install ntpdate.x86_64
[root@slave-mysql ~]# ntpdate cn.ntp.org.cn
6 Aug 11:37:31 ntpdate[2328]: adjust time server 182.92.12.11 offset 0.005835 sec
[root@master-mysql ~]# ntpdate cn.ntp.org.cn
[root@slave-mysql ~]# yum -y install rsync
[root@master-mysql ~]# yum -y install rsync
========基础配置(master-mysql )======
1)上传mysql安装包,保证目录下含.xz的压缩包
2)编写脚本
[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 ~]# ls
mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
[root@master-mysql ~]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/master-mysql.err'.
. SUCCESS!
[root@master-mysql ~]# mysql -uroot -p
Enter password:z,MSirzEZ5Ng # 旧密码
mysql> set password='Juan@1888$'; # 更改密码
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
3)编写配置文件
[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 # server-id不能相同
character_set_server=utf8mb4
4)重启服务
[root@master-mysql ~]# service mysql8 restart
ERROR! MySQL server PID file could not be found!
Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
. SUCCESS!
5)设置开机自启
[root@mysql001 ~]# chkconfig --add mysql8
[root@mysql001 ~]# chkconfig mysql8 on
[root@master-mysql ~]# chkconfig --list
……
mysql8 0:关 1:关 2:开 3:开 4:开 5:开 6:关
netconsole 0:关 1:关 2:关 3:关 4:关 5:关 6:关
network 0:关 1:关 2:开 3:开 4:开 5:开 6:关
=======基础配置(slave-mysql)=======
1)上传压缩文件
[root@slave-mysql ~]# ls
anaconda-ks.cfg
mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
mysql.sh
soft
2)上传脚本,修改脚本(注释掉初始化部分和生成data目录的指令,其数据都来自主服务器)
[root@slave-mysql ~]# vim mysql.sh
#/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
[root@slave-mysql ~]# source mysql.sh
# 未初始化,不能启动也无法安装,只能通过tab键测试
3)修改配置文件
[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
======数据同步(master服务器同步到slave服务器)========
1)停用master服务
[root@master-mysql ~]# service mysql8 stop
Shutting down MySQL.. SUCCESS!
2)aster删除/usr/local/mysql/data/auto.cnf 每安装⼀个mysql软件,data数据⽬录都会产⼀个auto.cnf文件,里面是⼀个唯⼀性编号,相当于每个⼈的身份证编号。
[root@master-mysql ~]# ls /usr/local/mysql/data/
auto.cnf # 保证当前的mysql唯一
[root@master-mysql ~]# cat /usr/local/mysql/data/
[auto]
server-uuid=fe96b926-53bc-11ef-8049-000c2959dd04
[root@master-mysql ~]# rm -rf /usr/local/mysql/data/auto.cnf
3)同步data文件
[root@master-mysql ~]# yum list installed|grep rsync
rsync.x86_64 3.1.2-12.el7_9 @updates
[root@slave-mysql ~]# yum list installed|grep rsync
rsync.x86_64 3.1.2-12.el7_9
[root@master-mysql ~]# rsync -av /usr/local/mysql/data root@192.168.2.43:/usr/local/mysql/
[root@slave-mysql ~]# ls /usr/local/mysql/
bin include man README
data lib my.cnf share
docs LICENSE mysql-files support-files
4)启动主从服务器
[root@master-mysql ~]# service mysql8 start
[root@slave-mysql ~]# service mysql8 start
5)使用主服务器的账号密码测试登录从服务器,但不允许从服务器远程登录
[root@slave-mysql ~]# mysql -P3310 -pJuan@1888$
mysql>
无法启动的几种情况!!!
1. /etc/init.d/mysql8不存在,可能没有复制
2. mysql没有安装在/usr/local/mysql
3. my.cnf导致无法启动,格式:
1)安装路径
2)数据目录
3)套接字
4)错误日志到底放在data里面
5) mysql账户无法写⼊/usr/local/mysql/
6)将错误日志放在data就没有权限文件
7)直接修改/usr/local/mysql的权限
========主从复制实现=========
# master创建授权账号
[root@master-mysql ~]#mysql -uroot -p
mysql> create user 'huajuan'@'%' identified by 'abc_123';
Query OK, 0 rows affected (0.01 sec)
mysql> grant replication slave on *.* to 'huajuan'@'%';
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 privileges;
Query OK, 0 rows affected (0.00 sec)
# 在master服务器中锁表,然后查看二进制文件binlog的位置
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.000004 | 1029 | | | |
+---------------+----------+--------------+------------------+-------------------+
binlog.当前文件名 位置
1 row in set (0.00 sec)
# 在slave服务器上获得master主机的公钥
[root@slave-mysql~]# mysql -uhuajuan -pabc_123 -h 192.168.2.45 -P3306 --get-server-public-key
mysql> exit
Bye
# slave连接到本地的数据库
[root@slave-mysql ~]# mysql -P3310 -pJuan@1888$
mysql>
# 停用并重新设置slave服务
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> reset slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> change master to master_host='192.168.2.45', master_user='huajuan', master_password='abc_123', master_port=3306, master_log_file='binlog.000004', master_log_pos=1029;
Query OK, 0 rows affected, 9 warnings (0.02 sec)
# 再次启动slave服务并查看主从同状态
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show slave status\G,同步成功
========测试==========
# 在master服务器上创建表,插入数据
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> unlock tables; # 先解锁
Query OK, 0 rows affected (0.00 sec)
mysql> create database if not exists abc charset utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| abc |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.01 sec)
mysql> create database test;
Query OK, 1 row affected (0.01 sec)
mysql> use test;
Database changed
mysql> create table user(id int primary key,username varchar(45) not null,password varchar(45) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> insert into user values(1, 'tangpin', 'efg');
Query OK, 1 row affected (0.03 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | tangpin | efg |
+----+----------+----------+
1 row in set (0.00 sec)
# slave从服务区进行查看,发现数据表已经同步
mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| abc |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.01 sec)
mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
1 row in set (0.01 sec)
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
| 1 | tangpin | efg |
+----+----------+----------+
1 row in set (0.00 sec)