8.6-设置mysql开机自启+角色生效+主从数据库

一、设置mysql开机自启

1.添加开机自启

#添加开机启动
[root@004 mysql]# #systemctl enable mysqld
[root@004 mysql]# 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@004 mysql]# chkconfig --add mysql8
[root@004 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:关

2.修改配置文件

[root@004 mysql]# vim /usr/local/mysql/my.cnf
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock

3.重启服务

[root@004 mysql]# service mysql8 restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL.. SUCCESS! 

4.mysql安全设置

[root@004 mysql]# /usr/local/mysql/bin/mysql_secure_installation 

Securing the MySQL server deployment.

Enter password for user root: 

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: n
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.

Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

- Dropping test database...
  Success.
- Removing privileges on test database...
  Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done! 

5.配置/etc/profile文件

# 将mysql的bin也添加到
[root@004 mysql]# #$PATH
[root@004 mysql]# #/etc/profile
[root@004 mysql]# sed -i '$aexport PATH=/usr/local/mysql/bin/:$PATH' /etc/profile
[root@004 mysql]# sed -n '$p' /etc/profile
export PATH=/usr/local/mysql/bin/:$PATH
[root@004 mysql]# source /etc/profile
[root@004 mysql]# mysql -pHui@2003

5.创建aaa账号

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.00 sec)

mysql> create user 'aaa'@'%' identified by 'aaaa';
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | aaa              |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql> quit
Bye

6.打开端口

[root@004 mysql]# #打开防火墙或者端口
[root@004 mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent 
success
[root@004 mysql]# firewall-cmd --reload
success

7.创建角色a

[root@004 mysql]# mysql -pHui@2003
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.33 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create role a;
Query OK, 0 rows affected (0.00 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)

8.查询角色a的权限

mysql> show grants for a;
+-------------------------------+
| Grants for a@%                |
+-------------------------------+
| GRANT USAGE ON *.* TO `a`@`%` |
+-------------------------------+
1 row in set (0.00 sec)

9.给角色a添加所有的权限

mysql> grant all on *.* to a;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for a;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for a@%                                                                                                                                                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `a`@`%`                                                                                     |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON *.* TO `a`@`%` |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

10.将角色a授权给账号aaa

mysql> grant a to aaa;
Query OK, 0 rows affected (0.00 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)

mysql> quit
Bye

11.远程登录查看库，发现角色不生效

12.角色不生效的处理

#在配置文件中添加activate_all_roles_on_login=on
[root@004 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

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

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

13.远程登录工具刷新，就能看到库了

二、主从数据库

1.环境准备

准备两台机器

编号	主机名	主机IP
1	master	192.168.2.38
2	slave	192.168.2.39

主服务器配置

（1）关闭防火墙

[root@master ~]# systemctl stop firewalld
[root@master ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

（2）关闭selinux

[root@master ~]# setenforce 0
[root@master ~]# vim /etc/selinux/config 

（3）安装ntpdate

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

（4）同步时间

[root@master ~]# ntpdate cn.ntp.org.cn
 6 Aug 11:40:58 ntpdate[1764]: adjust time server 203.107.6.88 offset 0.012037 sec

从服务器配置

（1）关闭防火墙

[root@slave ~]# systemctl stop firewalld
[root@slave ~]# systemctl disable firewalld
Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

（2）关闭selinux

[root@slave ~]# setenforce 0
[root@slave ~]# vim /etc/selinux/config 

（3）安装ntpdate

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

（4）同步时间

[root@slave ~]# ntpdate cn.ntp.org.cn
 6 Aug 11:39:58 ntpdate[1851]: adjust time server 182.92.12.11 offset 0.012761 sec

2.安装mysql

（1）主数据库

写mysql.sh脚本，安装mysql

[root@master ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar

[root@master ~]# 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 ~]# source mysql.sh 

启动服务

[root@master ~]# service mysql8 start

给root修改密码

mysql> alter user 'root'@'localhost' identified by 'Hui@2003';

修改配置文件

[root@master ~]# 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 ~]#chkconfig --add mysql8

[root@master ~]#chkconfig mysql8 on

[root@master ~]#chkconfig --list

（2）从数据库

[root@slave ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar 

用脚本安装mysql

[root@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.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 ~]# source mysql.sh

修改配置文件

[root@slave ~]# 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

3.删除/usr/local/mysql/data中的auto.cnf

主服务器：

#停止服务
[root@master ~]# service mysql8 stop
Shutting down MySQL.. SUCCESS! 
#查看ls /usr/local/mysql/data
[root@master ~]# ls /usr/local/mysql/data
auto.cnf       client-cert.pem    ibdata1       mysql.ibd           server-key.pem
binlog.000001  client-key.pem     #innodb_redo  performance_schema  sys
binlog.index   #ib_16384_0.dblwr  #innodb_temp  private_key.pem     undo_001
ca-key.pem     #ib_16384_1.dblwr  master.err    public_key.pem      undo_002
ca.pem         ib_buffer_pool     mysql         server-cert.pem
#删除/usr/local/mysql/data中的auto.cnf  
[root@master ~]# rm -rf /usr/local/mysql/data/auto.cnf 
[root@master ~]# yum -y install rsync

4.配置从数据库

#安装rsync
[root@slave ~]# yum -y install rsync

5.进行同步

#将主的/usr/local/mysql/data文件同步到从服务器中
[root@master ~]# rsync -av /usr/local/mysql/data root@192.168.2.39:/usr/local/mysql/

#去从服务器上，就发现将data同步过来了
[root@slave ~]# ls /usr/local/mysql/data/
binlog.000001    client-key.pem     #innodb_redo  performance_schema  sys
binlog.index     #ib_16384_0.dblwr  #innodb_temp  private_key.pem     undo_001
ca-key.pem       #ib_16384_1.dblwr  master.err    public_key.pem      undo_002
ca.pem           ib_buffer_pool     mysql         server-cert.pem
client-cert.pem  ibdata1            mysql.ibd     server-key.pem

6.在主数据库中创建账号

[root@master ~]# service mysql8 start
Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
. SUCCESS! 

#在主服务器里创建用户

[root@master ~]# mysql -P3306 -p'Hui@2003' 
mysql> create user 'hui'@'%' identified by 'Hui@2003'；

#给权限

mysql> grant replication slave on * .* to 'hui'@'%';

#锁表

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.000002 |     1074 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)

7.查看从数据库的server_id

#从的server-id不能和主的一样

[root@slave ~]# mysql -pHui@2003

mysql> show variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 11    |
+---------------+-------+
1 row in set (0.02 sec)

8.获得远程主机master主机的公钥

[root@slave ~]# mysql -uhui -p'Hui@2003' -h192.168.2.38 -P3306 --get-server-public-key

mysql> quit
Bye

9.登录本地的slave服务器数据库

[root@slave ~]# mysql -P3310 -pHui@2003

mysql> change master to
    -> master_host='192.168.2.38',
    -> master_user='hui',
    -> master_port=3306,
    -> master_log_file='binlog.000002',
    -> master_log_pos=1074;
Query OK, 0 rows affected, 8 warnings (0.02 sec)

10.启动slave服务

mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.02 sec)

11.查看从服务器的状态信息

mysql> show slave status\G

三、测试

#因为锁表，所以创建不了
mysql> create database if not exists test charset utf8mb4;
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 test charset utf8mb4;
Query OK, 1 row affected (0.00 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,'zhangsan','abc');
Query OK, 1 row affected (0.02 sec)

#查看
mysql> select * from user;
+----+----------+----------+
| id | username | password |
+----+----------+----------+
|  1 | zhangsan | abc      |
+----+----------+----------+
1 row in set (0.01 sec)

#查看数据库
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

从数据库就会同步