培训第二十二天（mysql数据库主从搭建）

上午

1、为mysql添加开机启动chkconfig

 [root@mysql1 ~]# chkconfig --list     //列出系统服务在不同运行级别下的启动状态
 注：该输出结果只显示 SysV 服务，并不包含
 原生 systemd 服务。SysV 配置数据
 可能被原生 systemd 配置覆盖。 
       要列出 systemd 服务，请执行 'systemctl list-unit-files'。
       查看在具体 target 启用的服务请执行
       'systemctl list-dependencies [target]'。
 netconsole      0:关 1:关 2:关 3:关 4:关 5:关 6:关
 network         0:关 1:关 2:开 3:开 4:开 5:开 6:关
 [root@mysql1 ~]# chkconfig --add mysql8   //将名为 mysql8 的服务添加到系统的服务管理列表中，以便通过 chkconfig 工具来管理它在不同运行级别的启动状态
 [root@mysql ~]# chkconfig mysql8 on   //设置 mysql8 服务在特定运行级别（或多个运行级别）自动启动

2、编辑配置文件my.cnf

 [root@mysql1 ~]# vim /usr/local/mysql/my.cnf
 [mysqld]
 basedir=/usr/local/mysql/      #指明了 MySQL 的安装根目录
 datadir=/usr/local/mysql/data  #指明了数据文件的存储目录
 socket=/tmp/mysql.sock         #定义了 MySQL 服务器用于本地连接的套接字文件路径
 [root@mysql1 ~]# service mysql8 restart
 [root@mysql1 ~]# /usr/local/mysql/bin/mysql_secure_installation   //增强 MySQL 服务器安全性

3、修改环境变量

 将mysql的bin也添加到$PATH
 [root@mysql1 ~]# sed -i '$a export PATH=/usr/local/mysql/bin/:$PATH' /etc/profile
 [root@mysql1 ~]# source /etc/profile

4、mysql角色授权

 [root@mysql1 ~]# mysql -uroot -p'Root@123456'
 mysql> select host,user from mysql.user;
 +-----------+------------------+
 | host      | user             |
 +-----------+------------------+
 | localhost | mysql.infoschema |
 | localhost | mysql.session    |
 | localhost | mysql.sys        |
 | localhost | root             |
 +-----------+------------------+
 4 rows in set (0.01 sec)
 mysql> create user 'aaa'@'%' identified by 'aaa';
 Query OK, 0 rows affected (0.11 sec)
 [root@mysql1 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent     //防火墙打开3306端口
 success
 [root@mysql1 ~]# firewall-cmd --reload      //重新加载防火墙
 success
 mysql> create role a;
 Query OK, 0 rows affected (0.06 sec)
 mysql> select host,user from mysql.user;
 +-----------+------------------+
 | host      | user             |
 +-----------+------------------+
 | %         | a                |
 | %         | aaa              |
 | localhost | mysql.infoschema |
 | localhost | mysql.session    |
 | localhost | mysql.sys        |
 | localhost | root             |
 +-----------+------------------+
 6 rows in set (0.00 sec)
 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.02 sec)
 mysql> grant a to aaa;
 Query OK, 0 rows affected (0.01 sec)
 mysql> show grants for aaa;
 +---------------------------------+
 | Grants for aaa@%                |
 +---------------------------------+
 | GRANT USAGE ON *.* TO `aaa`@`%` |
 | GRANT `a`@`%` TO `aaa`@`%`      |
 +---------------------------------+
 2 rows in set (0.00 sec)

角色不生效

在配置文件中不添加activate_all_roles_on_login=on

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

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

 [root@mysql1 ~]# vim /usr/local/mysql/my.cnf
 activate_all_roles_on_login=on       #在登录时激活所有的角色
 [root@mysql1 ~]# service mysql8 restart
 Shutting down MySQL... SUCCESS! 
 Starting MySQL........ SUCCESS! 

5、自动安装mysql脚本（主）

 #!/bin/bash
 yum list installed |grep libaio     #在已安装的软件包列表中搜索包含 libaio 字样的软件包
 if [ $? -ne 0 ]; then
     yum -y install libaio      #如果没有安装这个软件包就安装这个软件
 fi
 echo libaio yes
 rm -rf /etc/my.cnf        #删除/etc/my.cnf文件，后续要创建到项目中（/usr/local/mysql/）
 echo remo my.cnf yes
 ​
 #tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar
 #echo tar yes
 tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz       #解压软件包
 echo tar xz yes
 ​
 cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql      #将root家目录下的已经解压过的软件包下的内容复制到/usr/local/mysql目录下
 echo copy file to /usr/local/mysql   yes
 ​
 mkdir /usr/local/mysql/mysql-files       #在/usr/local/mysql目录下创建mysql-files目录
 echo mysql-files yes
 ​
 grep mysql /etc/passwd      #查看是否有mysql用户       
 useradd -r -s /sbin/nologin mysql     #创建mysql用户（-r 选项表示创建一个系统用户（通常系统用户的 UID 范围在 1 到 499 之间））
     
 chown mysql:mysql /usr/local/mysql/mysql-files     #使mysql-files目录的所属主和所属组都为mysql
 chmod 750 /usr/local/mysql/mysql-files     #设置mysql-files目录的权限为750
 ​
 /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/       #初始化 MySQL 数据库服务器
 /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data      #为 MySQL 服务器设置 SSL（Secure Sockets Layer，安全套接层）和 RSA（Rivest–Shamir–Adleman，一种非对称加密算法）相关的配置
 cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8    #将/usr/local/mysql/support-files/mysql.server文件复制到/etc/init.d/目录下并改名为mysql8，方便后续启服务
 ​
 sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile     #修改环境变量，方便后续进入mysql
 source /etc/profile    #激活配置

--initialize 选项会执行初始化操作，包括生成随机的初始密码、创建必要的数据库结构等。

--user=mysql 指定以 mysql 用户来运行数据库服务。

--basedir=/usr/local/mysql/ 指明了 MySQL 的安装根目录。

执行此命令后，您可以在输出中找到生成的初始密码，请注意记录好这个密码，以便后续登录和修改密码使用。

--datadir=/usr/local/mysql/data 选项指定了 MySQL 数据文件的存储目录。

执行此命令可以帮助确保 MySQL 服务器在通信过程中的安全性，通过使用 SSL 加密连接来保护数据传输。

6、数据库时间同步

 [root@slave-mysql ~]# yum -y install ntpdate.x86_64 
 [root@slave-mysql ~]# ntpdate cn.ntp.org.cn
  6 Aug 11:21:15 ntpdate[1165]: adjust time server 203.107.6.88 offset 0.000143 sec
 [root@mysql ~]# yum -y install ntpdate.x86_64 
 [root@mysql ~]# ntpdate cn.ntp.org.cn
  6 Aug 11:21:15 ntpdate[1165]: adjust time server 203.107.6.88 offset 0.000143 sec

下午

环境准备：主从服务器关闭防火墙和selinux，时间同步

1、使用脚本重新搭建主服务器

 [root@mysql ~]# sh mysql.sh
 [root@mysql ~]# vim /usr/local/mysql/my.cnf 
 [mysqld]
 basedir=/usr/local/mysql/
 datadir=/usr/local/mysql/data
 socket=/tmp/mysql.sock
 activate_all_roles_on_login=on
 port=3306                          #指定了 MySQL 服务器监听的端口为 3306，这是 MySQL 的默认端口，客户端连接时将使用这个端口进行通信
 log-error=/usr/local/mysql/data/db01-master.err  #定义了错误日志的存储路径为 /usr/local/mysql/data/db01-master.err，服务器运行过程中的错误信息将被记录到这个文件中
 log-bin=/usr/local/mysql/data/binlog  #启用了二进制日志，并指定其存储位置为 /usr/local/mysql/data/binlog，二进制日志可用于数据备份、主从复制等场景
 server-id=10        #为服务器分配了一个唯一的标识符 10，在主从复制架构中用于区分不同的服务器
 character_set_server=utf8mb4    #设置了服务器的默认字符集为 utf8mb4，确保在处理数据时使用这种更广泛支持 Unicode 字符的字符集
 [root@mysql ~]# service mysql8 restart
 Shutting down MySQL. SUCCESS! 
 Starting MySQL... SUCCESS! 
 [root@mysql-zhu ~]# mysql -uroot -p'y-Hqum4J)X!='
 mysql> alter user 'root'@'localhost' identified by '123';    //修改root密码
 Query OK, 0 rows affected (0.07 sec)

2、使用脚本重新搭建从服务器

 [root@mysql-slave ~]# 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
 #echo tar yes
 tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
 echo tar xz 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/     从服务器不初始化mysql
 #/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data              也就没有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@mysql-slave ~]# source mysql.sh
 [root@mysql-slave ~]# vim /usr/local/mysql/my.cnf
 [mysqld]
 basedir=/usr/local/mysql
 datadir=/usr/local/mysql/data
 socket=/tmp/mysql.sock
 port=3310                 #指定了 MySQL 服务器监听的端口为 3310
 log-error=/usr/local/mysql/data/db01-slave.err
 relay-log=/usr/local/mysql/data/relaylog
 server-id=11               #为服务器分配了一个唯一的标识符 11，在主从复制架构中用于区分不同的服务器
 character_set_server=utf8mb4

3、数据同步

 [root@mysql-zhu ~]# service mysql8 stop    //停止主mysql服务
 Shutting down MySQL. SUCCESS! 
 [root@mysql-zhu ~]# ls /usr/local/mysql/data/
 auto.cnf     //主要用于存储一些服务器自动生成的配置信息，其中可能包括服务器的 UUID（Universally Unique Identifier，通用唯一标识符）等重要的标识信息
 [root@mysql-zhu ~]# rm -rf /usr/local/mysql/data/auto.cnf
 [root@mysql-zhu ~]# yum -y install rsync    //下载同步软件
 [root@mysql-slave ~]# yum -y install rsync    //下载同步软件
 [root@mysql-zhu ~]# rsync -av /usr/local/mysql/data root@10.0.0.60:/usr/local/mysql/  //将主中的data目录同步到从中
 [root@mysql-slave ~]# ls /usr/local/mysql/data/
 binlog.000001    client-key.pem     #innodb_redo        public_key.pem
 binlog.000002    db01-master.err    #innodb_temp        server-cert.pem
 binlog.index     #ib_16384_0.dblwr  mysql               server-key.pem
 ca-key.pem       #ib_16384_1.dblwr  mysql.ibd           sys
 ca.pem           ib_buffer_pool     performance_schema  undo_001
 client-cert.pem  ibdata1            private_key.pem     undo_002
 [root@mysql-slave ~]# service mysql8 start   //启动从的mysql服务
 Starting MySQL.... SUCCESS! 
 [root@mysql-slave ~]# mysql -P3310 -p123     //可以登录到从的mysql中了

4、主从复制实现

replication slave：指定授予的权限类型为复制从服务器的权限

 [root@mysql-zhu ~]# service mysql8 start   //启动主的mysql服务
 Starting MySQL.... SUCCESS! 
 [root@mysql-zhu ~]# mysql -uroot -p'123'      //登录到主的mysql中
 mysql> create user 'slave'@'%' identified by '123';     //创建一个slave用户，%表示在每个主机上都可以使用slave登录到该主机上的mysql
 Query OK, 0 rows affected (0.06 sec)
 mysql> grant replication slave on *.* to 'slave'@'%';    //将所有的数据库的所有表的replication slave操作权限给到slave用户
 Query OK, 0 rows affected (0.01 sec)
 mysql> flush privileges;     //重新加载权限表
 Query OK, 0 rows affected (0.00 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 |     1067 |              |                  |                   |
 +---------------+----------+--------------+------------------+-------------------+
 1 row in set (0.01 sec)
 File：当前正在写入的二进制日志文件的名称。
 Position：当前写入的位置（偏移量）在二进制日志文件中的位置。
 [root@mysql-slave ~]# mysql -uslave -p123 -h10.0.0.51 -P3306 --get-server-public-key   //用于以指定的用户 slave 和密码 123 连接到主机地址为 10.0.0.51 、端口为 3306 的 MySQL 服务器，并获取服务器的公共密钥
 [root@mysql-slave ~]# mysql -P3310 -p123    //登录到从的数据库中
 mysql> change master to
     -> master_host='10.0.0.51',      //指定主服务器的主机地址
     -> master_user='slave',          //指定用于连接主服务器进行复制的用户名
     -> master_password='123',        //指定对应的密码
     -> master_port=3306,             //指定主服务器的端口
     -> master_log_file='binlog.000003',   //指定从哪个二进制日志文件开始复制
     -> master_log_pos=1067;          //指定在指定的二进制日志文件中的起始位置
 Query OK, 0 rows affected, 9 warnings (0.01 sec)
 mysql> start slave;      //启动从服务器的复制进程
 Query OK, 0 rows affected, 1 warning (0.03 sec)
 mysql> show slave status\G    //查看从服务器的复制状态详细信息

5、同步测试

主服务器

 mysql> unlock tables;    //释放之前通过 LOCK TABLES 命令施加的表锁
 Query OK, 0 rows affected (0.00 sec)
 mysql> create database if not exists test charset utf8mb4;   //创建数据库test
 Query OK, 1 row affected (0.02 sec)
 mysql> use test;     //使用test数据库
 Database changed
 mysql> create table user(id int primary key,username varchar(45) not null,passwordvarchar(45) not null);    //创建user表
 Query OK, 0 rows affected (0.03 sec)
 mysql> insert into user values(1,'zhangsan','abc');   //向user表中插入数据
 Query OK, 1 row affected (0.03 sec)

从服务器

 mysql> show databases;      //查看数据库
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | test               |
 +--------------------+
 5 rows in set (0.02 sec)
 mysql> use test;           //使用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> show tables;      //查看所有表
 +----------------+
 | Tables_in_test |
 +----------------+
 | user           |
 +----------------+
 1 row in set (0.00 sec)
 mysql> select * from user;    //查看user表的内容
 +----+----------+----------+
 | id | username | password |
 +----+----------+----------+
 |  1 | zhangsan | abc      |
 +----+----------+----------+
 1 row in set (0.00 sec)