培训第二十二天（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      //重新加载防火墙successmysql> 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.cnfactivate_all_roles_on_login=on       #在登录时激活所有的角色[root@mysql1 ~]# service mysql8 restartShutting down MySQL... SUCCESS! Starting MySQL........ SUCCESS!

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

 #!/bin/bashyum list installed |grep libaio     #在已安装的软件包列表中搜索包含 libaio 字样的软件包if [ $? -ne 0 ]; thenyum -y install libaio      #如果没有安装这个软件包就安装这个软件fiecho libaio yesrm -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 yestar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz       #解压软件包echo tar xz yescp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql      #将root家目录下的已经解压过的软件包下的内容复制到/usr/local/mysql目录下echo copy file to /usr/local/mysql   yesmkdir /usr/local/mysql/mysql-files       #在/usr/local/mysql目录下创建mysql-files目录echo mysql-files yesgrep 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目录的所属主和所属组都为mysqlchmod 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     #修改环境变量，方便后续进入mysqlsource /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.cn6 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.cn6 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/datasocket=/tmp/mysql.sockactivate_all_roles_on_login=onport=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 restartShutting 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/bashyum list installed |grep libaioif [ $? -ne 0 ]; thenyum -y install libaiofiecho libaio yesrm -rf /etc/my.cnfecho remo my.cnf yes#tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar#echo tar yestar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xzecho tar xz yescp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysqlecho copy file to /usr/local/mysql   yesmkdir /usr/local/mysql/mysql-filesecho mysql-files yesgrep mysql /etc/passwduseradd -r -s /sbin/nologin mysqlchown mysql:mysql /usr/local/mysql/mysql-fileschmod 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/mysql8sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profilesource /etc/profile[root@mysql-slave ~]# source mysql.sh[root@mysql-slave ~]# vim /usr/local/mysql/my.cnf[mysqld]basedir=/usr/local/mysqldatadir=/usr/local/mysql/datasocket=/tmp/mysql.sockport=3310                 #指定了 MySQL 服务器监听的端口为 3310log-error=/usr/local/mysql/data/db01-slave.errrelay-log=/usr/local/mysql/data/relaylogserver-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.pembinlog.000002    db01-master.err    #innodb_temp        server-cert.pembinlog.index     #ib_16384_0.dblwr  mysql               server-key.pemca-key.pem       #ib_16384_1.dblwr  mysql.ibd           sysca.pem           ib_buffer_pool     performance_schema  undo_001client-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登录到该主机上的mysqlQuery 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 lockmysql> 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;   //创建数据库testQuery OK, 1 row affected (0.02 sec)mysql> use test;     //使用test数据库Database changedmysql> 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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)