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

民工心事  金牌会员 | 2024-10-24 16:47:22 | 显示全部楼层 | 阅读模式
打印 上一主题 下一主题
楼主

主题 884|帖子 884|积分 2652

一、设置mysql开机自启

1.添加开机自启

  1. #添加开机启动
  2. [root@004 mysql]# #systemctl enable mysqld
  3. [root@004 mysql]# chkconfig --list
  5. 注：该输出结果只显示 SysV 服务，并不包含
  6. 原生 systemd 服务。SysV 配置数据
  7. 可能被原生 systemd 配置覆盖。
  9. ```
  10.   要列出 systemd 服务，请执行 'systemctl list-unit-files'。
  11.   查看在具体 target 启用的服务请执行
  12.   'systemctl list-dependencies [target]'。
  13. ```
  15. netconsole             0:关        1:关        2:关        3:关        4:关        5:关        6:关
  16. network                0:关        1:关        2:开        3:开        4:开        5:开        6:关
  17. [root@004 mysql]# chkconfig --add mysql8
  18. [root@004 mysql]# chkconfig --list
  20. 注：该输出结果只显示 SysV 服务，并不包含
  21. 原生 systemd 服务。SysV 配置数据
  22. 可能被原生 systemd 配置覆盖。
  24. ```
  25.   要列出 systemd 服务，请执行 'systemctl list-unit-files'。
  26.   查看在具体 target 启用的服务请执行
  27.   'systemctl list-dependencies [target]'。
  28. ```
  30. mysql8                 0:关        1:关        2:开        3:开        4:开        5:开        6:关
  31. netconsole             0:关        1:关        2:关        3:关        4:关        5:关        6:关
  32. network                0:关        1:关        2:开        3:开        4:开        5:开        6:关
复制代码
2.修改配置文件

  1. [root@004 mysql]# vim /usr/local/mysql/my.cnf
  2. [mysqld]
  3. basedir=/usr/local/mysql
  4. datadir=/usr/local/mysql/data
  5. socket=/tmp/mysql.sock
复制代码
3.重启服务

  1. [root@004 mysql]# service mysql8 restart
  2. Shutting down MySQL.. SUCCESS!
  3. Starting MySQL.. SUCCESS!
复制代码
4.mysql安全设置

  1. [root@004 mysql]# /usr/local/mysql/bin/mysql_secure_installation
  3. Securing the MySQL server deployment.
  5. Enter password for user root:
  7. VALIDATE PASSWORD COMPONENT can be used to test passwords
  8. and improve security. It checks the strength of password
  9. and allows the users to set only those passwords which are
  10. secure enough. Would you like to setup VALIDATE PASSWORD component?
  12. Press y|Y for Yes, any other key for No: n
  13. Using existing password for root.
  14. Change the password for root ? ((Press y|Y for Yes, any other key for No) : n
  16. ... skipping.
  17. By default, a MySQL installation has an anonymous user,
  18. allowing anyone to log into MySQL without having to have
  19. a user account created for them. This is intended only for
  20. testing, and to make the installation go a bit smoother.
  21. You should remove them before moving into a production
  22. environment.
  24. Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
  25. Success.
  27. Normally, root should only be allowed to connect from
  28. 'localhost'. This ensures that someone cannot guess at
  29. the root password from the network.
  31. Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
  32. Success.
  34. By default, MySQL comes with a database named 'test' that
  35. anyone can access. This is also intended only for testing,
  36. and should be removed before moving into a production
  37. environment.
  39. Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
  41. - Dropping test database...
  42.   Success.
  43. - Removing privileges on test database...
  44.   Success.
  46. Reloading the privilege tables will ensure that all changes
  47. made so far will take effect immediately.
  49. Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
  50. Success.
  52. All done!
复制代码
5.配置/etc/profile文件

  1. # 将mysql的bin也添加到
  2. [root@004 mysql]# #$PATH
  3. [root@004 mysql]# #/etc/profile
  4. [root@004 mysql]# sed -i '$aexport PATH=/usr/local/mysql/bin/:$PATH' /etc/profile
  5. [root@004 mysql]# sed -n '$p' /etc/profile
  6. export PATH=/usr/local/mysql/bin/:$PATH
  7. [root@004 mysql]# source /etc/profile
  8. [root@004 mysql]# mysql -pHui@2003
复制代码
5.创建aaa账号

  1. mysql> select host,user from mysql.user;
  2. +-----------+------------------+
  3. | host      | user             |
  4. +-----------+------------------+
  5. | localhost | mysql.infoschema |
  6. | localhost | mysql.session    |
  7. | localhost | mysql.sys        |
  8. | localhost | root             |
  9. +-----------+------------------+
  10. 4 rows in set (0.00 sec)
  12. mysql> create user 'aaa'@'%' identified by 'aaaa';
  13. Query OK, 0 rows affected (0.02 sec)
  15. mysql> select host,user from mysql.user;
  16. +-----------+------------------+
  17. | host      | user             |
  18. +-----------+------------------+
  19. | %         | aaa              |
  20. | localhost | mysql.infoschema |
  21. | localhost | mysql.session    |
  22. | localhost | mysql.sys        |
  23. | localhost | root             |
  24. +-----------+------------------+
  25. 5 rows in set (0.00 sec)
  27. mysql> quit
  28. Bye
复制代码
6.打开端口

  1. [root@004 mysql]# #打开防火墙或者端口
  2. [root@004 mysql]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
  3. success
  4. [root@004 mysql]# firewall-cmd --reload
  5. success
复制代码
7.创建脚色a

  1. [root@004 mysql]# mysql -pHui@2003
  2. mysql: [Warning] Using a password on the command line interface can be insecure.
  3. Welcome to the MySQL monitor.  Commands end with ; or \g.
  4. Your MySQL connection id is 12
  5. Server version: 8.0.33 MySQL Community Server - GPL
  7. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
  9. Oracle is a registered trademark of Oracle Corporation and/or its
  10. affiliates. Other names may be trademarks of their respective
  11. owners.
  13. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  15. mysql> create role a;
  16. Query OK, 0 rows affected (0.00 sec)
  18. mysql> select host,user from mysql.user;
  19. +-----------+------------------+
  20. | host      | user             |
  21. +-----------+------------------+
  22. | %         | a                |
  23. | %         | aaa              |
  24. | localhost | mysql.infoschema |
  25. | localhost | mysql.session    |
  26. | localhost | mysql.sys        |
  27. | localhost | root             |
  28. +-----------+------------------+
  29. 6 rows in set (0.00 sec)
复制代码
8.查询脚色a的权限

  1. mysql> show grants for a;
  2. +-------------------------------+
  3. | Grants for a@%                |
  4. +-------------------------------+
  5. | GRANT USAGE ON *.* TO `a`@`%` |
  6. +-------------------------------+
  7. 1 row in set (0.00 sec)
复制代码
9.给脚色a添加全部的权限

  1. mysql> grant all on *.* to a;
  2. Query OK, 0 rows affected (0.01 sec)
  4. mysql> show grants for a;
  5. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | Grants for a@%                                                                                                                                                                                                                                                  |
  7. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  8. | 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`@`%`                                                                                     |
  9. | 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`@`%` |
  10. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 2 rows in set (0.00 sec)
复制代码
10.将脚色a授权给账号aaa

  1. mysql> grant a to aaa;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> show grants for aaa;
  5. +---------------------------------+
  6. | Grants for aaa@%                |
  7. +---------------------------------+
  8. | GRANT USAGE ON *.* TO `aaa`@`%` |
  9. | GRANT `a`@`%` TO `aaa`@`%`      |
  10. +---------------------------------+
  11. 2 rows in set (0.00 sec)
  13. mysql> quit
  14. Bye
复制代码
11.长途登录查看库，发现脚色不生效


12.脚色不生效的处置惩罚

  1. #在配置文件中添加activate_all_roles_on_login=on
  2. [root@004 mysql]# vim /usr/local/mysql/my.cnf
  3. [mysqld]
  4. basedir=/usr/local/mysql
  5. datadir=/usr/local/mysql/data
  6. socket=/tmp/mysql.sock
  7. activate_all_roles_on_login=on
复制代码
glibc安装，my.cnf在项目目次之下；
rpm安装，my.cnf文件在/etc/my.cnf下；
13.长途登录工具刷新，就能看到库了


二、主从数据库

1.环境预备

预备两台机器
编号主机名主机IP1master192.168.2.382slave192.168.2.39 主服务器配置

（1）关闭防火墙
  1. [root@master ~]# systemctl stop firewalld
  2. [root@master ~]# systemctl disable firewalld
  3. Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
  4. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
复制代码
（2）关闭selinux
  1. [root@master ~]# setenforce 0
  2. [root@master ~]# vim /etc/selinux/config
复制代码
（3）安装ntpdate
  1. [root@master ~]# yum -y install ntpdate.x86_64
复制代码
（4）同步时间
  1. [root@master ~]# ntpdate cn.ntp.org.cn
  2. 6 Aug 11:40:58 ntpdate[1764]: adjust time server 203.107.6.88 offset 0.012037 sec
复制代码
从服务器配置

（1）关闭防火墙
  1. [root@slave ~]# systemctl stop firewalld
  2. [root@slave ~]# systemctl disable firewalld
  3. Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.
  4. Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
复制代码
（2）关闭selinux
  1. [root@slave ~]# setenforce 0
  2. [root@slave ~]# vim /etc/selinux/config
复制代码
（3）安装ntpdate
  1. [root@slave ~]# yum -y install ntpdate.x86_64
复制代码
（4）同步时间
  1. [root@slave ~]# ntpdate cn.ntp.org.cn
  2. 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
  1. # !/bin/bash
  3. yum list installed |grep libaio
  4. if [ $? ne 0 ]; then
  5.     yum -y install libaio
  6. fi
  7. echo libaio yes
  8. rm -rf /etc/my.cnf
  9. echo remo my.cnf yes
  11. tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
  12. echo tar zx yes
  14. cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
  15. echo copy file to /usr/local/mysql   yes
  17. mkdir /usr/local/mysql/mysql-files
  18. echo mysql-files yes
  20. grep mysql /etc/passwd
  23. useradd -r -s /sbin/nologin mysql
  26. chown mysql:mysql /usr/local/mysql/mysql-files
  27. chmod 750 /usr/local/mysql/mysql-files
  29. /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
  31. /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
  33. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8
  35. sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
  36. source /etc/profile
复制代码
运行脚本
  1. [root@master ~]# source mysql.sh
复制代码
启动服务
  1. [root@master ~]# service mysql8 start
复制代码
给root修改暗码
  1. mysql> alter user 'root'@'localhost' identified by 'Hui@2003';
复制代码
修改配置文件
[root@master ~]# vim /usr/local/mysql/my.cnf
  1. [mysqld]
  2. basedir=/usr/local/mysql
  3. datadir=/usr/local/mysql/data
  4. socket=/tmp/mysql.sock
  5. port=3306
  6. log-error=/usr/local/mysql/data/db01-master.err
  7. log-bin=/usr/local/mysql/data/binlog
  8. server-id=10
  9. character_set_server=utf8mb4
复制代码
开机自启：
  1. [root@master ~]#chkconfig --add mysql8
  3. [root@master ~]#chkconfig mysql8 on
  5. [root@master ~]#chkconfig --list
复制代码
（2）从数据库

  1. [root@slave ~]# tar -xvf mysql-8.0.33-linux-glibc2.12-x86_64.tar
复制代码
用脚本安装mysql
  1. [root@slave ~]# vim mysql.sh
  2. # !/bin/bash
  4. yum list installed |grep libaio
  5. if [ $? ne 0 ]; then
  6.         yum -y install libaio
  7. fi
  8. echo libaio yes
  9. rm -rf /etc/my.cnf
  10. echo remo my.cnf yes
  12. tar -xf mysql-8.0.33-linux-glibc2.12-x86_64.tar.xz
  13. echo tar zx yes
  15. cp -r ~/mysql-8.0.33-linux-glibc2.12-x86_64 /usr/local/mysql
  16. echo copy file to /usr/local/mysql   yes
  18. mkdir /usr/local/mysql/mysql-files
  19. echo mysql-files yes
  21. grep mysql /etc/passwd
  24. useradd -r -s /sbin/nologin mysql
  27. chown mysql:mysql /usr/local/mysql/mysql-files
  28. chmod 750 /usr/local/mysql/mysql-files
  30. # /usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql/
  32. # /usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/usr/local/mysql/data
  34. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql8
  36. sed -i '$aexport PATH=/usr/local/mysql/bin:$PATH' /etc/profile
  37. source /etc/profile
复制代码
运行脚本
  1. [root@slave ~]# source mysql.sh
复制代码
修改配置文件
[root@slave ~]# vim /usr/local/mysql/my.cnf
  1. [mysqld]
  2. basedir=/usr/local/mysql
  3. datadir=/usr/local/mysql/data
  4. socket=/tmp/mysql.sock
  5. port=3310
  6. log-error=/usr/local/mysql/data/db01-slave.err
  7. relay-log=/usr/local/mysql/data/relaylog
  8. server-id=11
  9. character_set_server=utf8mb4
复制代码
3.删除/usr/local/mysql/data中的auto.cnf

主服务器：
  1. #停止服务
  2. [root@master ~]# service mysql8 stop
  3. Shutting down MySQL.. SUCCESS!
  4. #查看ls /usr/local/mysql/data
  5. [root@master ~]# ls /usr/local/mysql/data
  6. auto.cnf       client-cert.pem    ibdata1       mysql.ibd           server-key.pem
  7. binlog.000001  client-key.pem     #innodb_redo  performance_schema  sys
  8. binlog.index   #ib_16384_0.dblwr  #innodb_temp  private_key.pem     undo_001
  9. ca-key.pem     #ib_16384_1.dblwr  master.err    public_key.pem      undo_002
  10. ca.pem         ib_buffer_pool     mysql         server-cert.pem
  11. #删除/usr/local/mysql/data中的auto.cnf  
  12. [root@master ~]# rm -rf /usr/local/mysql/data/auto.cnf
  13. [root@master ~]# yum -y install rsync
复制代码
4.配置从数据库

  1. #安装rsync
  2. [root@slave ~]# yum -y install rsync
复制代码
5.举行同步

  1. #将主的/usr/local/mysql/data文件同步到从服务器中
  2. [root@master ~]# rsync -av /usr/local/mysql/data root@192.168.2.39:/usr/local/mysql/
  4. #去从服务器上，就发现将data同步过来了
  5. [root@slave ~]# ls /usr/local/mysql/data/
  6. binlog.000001    client-key.pem     #innodb_redo  performance_schema  sys
  7. binlog.index     #ib_16384_0.dblwr  #innodb_temp  private_key.pem     undo_001
  8. ca-key.pem       #ib_16384_1.dblwr  master.err    public_key.pem      undo_002
  9. ca.pem           ib_buffer_pool     mysql         server-cert.pem
  10. client-cert.pem  ibdata1            mysql.ibd     server-key.pem
复制代码
6.在主数据库中创建账号

  1. [root@master ~]# service mysql8 start
  2. Starting MySQL.Logging to '/usr/local/mysql/data/db01-master.err'.
  3. . SUCCESS!
  5. #在主服务器里创建用户
  7. [root@master ~]# mysql -P3306 -p'Hui@2003'
  8. mysql> create user 'hui'@'%' identified by 'Hui@2003'；
  10. #给权限
  12. mysql> grant replication slave on * .* to 'hui'@'%';
  14. #锁表
  16. mysql> flush tables with read lock;
  17. Query OK, 0 rows affected (0.00 sec)
  19. #因为锁表，所以创建不了
  21. mysql> create database if not exists abc charset utf8;
  22. ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
  24. #查看二进制文件
  26. mysql> show master status;
  27. +---------------+----------+--------------+------------------+-------------------+
  28. | File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
  29. +---------------+----------+--------------+------------------+-------------------+
  30. | binlog.000002 |     1074 |              |                  |                   |
  31. +---------------+----------+--------------+------------------+-------------------+
  32. 1 row in set (0.01 sec)
复制代码
7.查看从数据库的server_id

  1. #从的server-id不能和主的一样
  3. [root@slave ~]# mysql -pHui@2003
  5. mysql> show variables like 'server_id';
  6. +---------------+-------+
  7. | Variable_name | Value |
  8. +---------------+-------+
  9. | server_id     | 11    |
  10. +---------------+-------+
  11. 1 row in set (0.02 sec)
复制代码
8.获得长途主机master主机的公钥

  1. [root@slave ~]# mysql -uhui -p'Hui@2003' -h192.168.2.38 -P3306 --get-server-public-key
  3. mysql> quit
  4. Bye
复制代码
9.登录本地的slave服务器数据库

  1. [root@slave ~]# mysql -P3310 -pHui@2003
  3. mysql> change master to
  4.     -> master_host='192.168.2.38',
  5.     -> master_user='hui',
  6.     -> master_port=3306,
  7.     -> master_log_file='binlog.000002',
  8.     -> master_log_pos=1074;
  9. Query OK, 0 rows affected, 8 warnings (0.02 sec)
复制代码
10.启动slave服务

  1. mysql> start slave;
  2. Query OK, 0 rows affected, 1 warning (0.02 sec)
复制代码
11.查看从服务器的状态信息

  1. mysql> show slave status\G
复制代码

三、测试

  1. #因为锁表，所以创建不了
  2. mysql> create database if not exists test charset utf8mb4;
  3. ERROR 1223 (HY000): Can't execute the query because you have a conflicting read lock
  5. #解锁
  6. mysql> unlock tables;
  7. Query OK, 0 rows affected (0.00 sec)
  9. #可以创建表了
  10. mysql> create database if not exists test charset utf8mb4;
  11. Query OK, 1 row affected (0.00 sec)
  13. #使用数据库
  14. mysql> use test;
  15. Database changed
  17. #创建表
  18. mysql> create table user(id int primary key,username varchar(45) not null,password varchar(45) not null);
  19. Query OK, 0 rows affected (0.03 sec)
  21. #插入数据
  22. mysql> insert into user values(1,'zhangsan','abc');
  23. Query OK, 1 row affected (0.02 sec)
  25. #查看
  26. mysql> select * from user;
  27. +----+----------+----------+
  28. | id | username | password |
  29. +----+----------+----------+
  30. |  1 | zhangsan | abc      |
  31. +----+----------+----------+
  32. 1 row in set (0.01 sec)
  34. #查看数据库
  35. mysql> show databases;
  36. +--------------------+
  37. | Database           |
  38. +--------------------+
  39. | information_schema |
  40. | mysql              |
  41. | performance_schema |
  42. | sys                |
  43. | test               |
  44. +--------------------+
  45. 5 rows in set (0.00 sec)
复制代码
从数据库就会同步



免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
回复

使用道具 举报

0 个回复

倒序浏览
返回列表

快速回复

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 or 立即注册

本版积分规则

民工心事

金牌会员
这个人很懒什么都没写!

楼主热帖

标签云

挺好的 服务器
微信订阅号
微信服务号
微信客服
小程序
H5
关于我们 商务合作 网站地图
©Copyright 2022-2024 杭州祥升科技有限公司 版权所有 浙ICP备20004199号
快速回复 返回顶部 返回列表