一、设置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.sock3.重启服务
[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@20035.创建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
Bye6.打开端口
[root@004 mysql]# #打开防火墙或者端口
[root@004 mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
[root@004 mysql]# firewall-cmd --reload
success7.创建角色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 - GPLCopyright (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
Bye11.远程登录查看库，发现角色不生效
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=onglibc安装，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.cn6 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.cn6 Aug 11:39:58 ntpdate[1851]: adjust time server 182.92.12.11 offset 0.012761 sec2.安装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/bashyum list installed |grep libaio
if [ $? ne 0 ]; thenyum -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/passwduseradd -r -s /sbin/nologin mysqlchown 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/datacp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8sed -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/bashyum list installed |grep libaio
if [ $? ne 0 ]; thenyum -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/passwduseradd -r -s /sbin/nologin mysqlchown 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/datacp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8sed -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=utf8mb43.删除/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 rsync4.配置从数据库
#安装rsync
[root@slave ~]# yum -y install rsync5.进行同步
#将主的/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.pem6.在主数据库中创建账号
[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@2003mysql> 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-keymysql> quit
Bye9.登录本地的slave服务器数据库
[root@slave ~]# mysql -P3310 -pHui@2003mysql> 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)从数据库就会同步
