MySQL二进制安装1、概述此次二进制安装是针对Linux操作系统无法使用root权限，只能使用普通用户部署安装MySQL；本次MySQL二进制安装采用的版本：5.7.372、环境操作系统为centos7.x安装必要的依赖包，此项由管理员安装，提前让管理员安装以下依赖包$ yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel perl perl-Data-Dumper libaio -y3、登陆服务器使用普通账户 mysql所有数据操作都将存在/home/mysql下，或有mysql账户权限的目录；4、创建数据目录# 创建数据目录 $ pwd /home/mysql $ mkdir hdata/mysql_data/{data,logs} -p # 创建MySQL软件存放目录 $ pwd /home/mysql $ mkdir software/{src,mysql} -p5、下载MySQL二进制包登陆MySQL官网下载通用版mysql 5.7.37网址 https://downloads.mysql.com/archives/community/6、解压安装包# 解压 $ pwd /home/mysql $ cd software/src $ tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar $ mv mysql-5.7.37-linux-glibc2.12-x86_64 mysql $ mv mysql ../7、创建MySQL配置文件# 创建mysql配置文件 $ cd software/mysql $ mkdir conf $ vi my.cnf [client] port = 3308 socket = /tmp/mysql.sock [mysqld] port = 3308 user = mysql character-set-server = utf8 default_storage_engine = innodb log_timestamps = SYSTEM basedir = /home/mysql/software/mysql datadir=/home/mysql/hdata/mysql_data/data max_connections = 5000 max_connect_errors = 1000 table_open_cache = 1024 max_allowed_packet = 128M open_files_limit = 65535 socket=/tmp/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 binlog_format = MIXED query_cache_size = 128M query_cache_limit = 4M sort_buffer_size = 16M join_buffer_size = 16M wait_timeout=31536000 interactive_timeout=31536000 innodb_buffer_pool_size = 1024M innodb_file_per_table = 1 innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_purge_threads = 2 innodb_flush_log_at_trx_commit = 1 innodb_log_file_size = 512M innodb_log_files_in_group = 2 innodb_log_buffer_size = 16M innodb_max_dirty_pages_pct = 80 innodb_rollback_on_timeout = on innodb_lock_wait_timeout = 28800 innodb_data_file_path=ibdata1:1024M:autoextend innodb_undo_tablespaces=3 lower_case_table_names = 1 slow_query_log = 1 long_query_time = 3 log-queries-not-using-indexes slow_query_log_file =/home/mysql/hdata/mysql_data/logs/mysql-slow.log sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqld_safe] log-error=/home/mysql/hdata/mysql_data/logs/mysqld.log pid-file=/tmp/mysqld.pid8、初始化MySQL$ cd software/mysql $ ./bin/mysqld --defaults-file=/home/mysql/software/mysql/conf/my.cnf --initialize --user=mysql --basedir=/home/mysql/software/mysql --datadir=/home/mysql/hdata/mysql_data/data --innodb_undo_tablespaces=3 --explicit_defaults_for_timestamp初始化完后mysql密码记得保存9、启动MySQL因无root权限，用普通账户启动mysql$ cd software/mysql/bin $ nohup ./mysqld --defaults-file=/home/mysql/software/mysql/conf/my.cnf --user=mysql --basedir=/home/mysql/software/mysql --datadir=/home/mysql/hdata/mysql_data/data &10、登陆并设置MySQL# 登陆MySQL $ cd software/mysql/bin # 密码是刚才初始化记录的密码 $ ./mysql -uroot -p -P3308 # 更新MySQL账户密码 mysql> set password for 'root'@'localhost'=password('1qaz@WSX3edc'); Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '1qaz@WSX3edc' WITH GRANT OPTION; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) mysql> exit Bye此次MySQL二进制安装完成
[TOC]概述mysql5.7.32增加审计日志功能，记录数据库的操作获取审计功能插件下载5.5.68-MariaDB插件路径： /usr/lib64/mysql/plugin/server_audit.so 配置mysql5.7.321、获取mysql插件路径mysql> show global variables like '%plugin_dir%'; +---------------+--------------------------+ | Variable_name | Value | +---------------+--------------------------+ | plugin_dir | /usr/lib64/mysql/plugin/ | +---------------+--------------------------+ 1 row in set (0.01 sec)2、分发插件# 将插件放置此目录 $ cd /usr/lib64/mysql/plugin/ $ ll server_audit.so3、安装插件INSTALL PLUGIN server_audit SONAME 'server_audit.so';4、查看变量开启情况，默认都是关闭mysql> show variables like '%audit%';5、配置开启$ vim /etc/my.cnf # 新增以下配置 server_audit_logging=on server_audit_file_path =/hdata/mysql_data/log/server_audit.log server_audit_file_rotate_size=10000000 server_audit=FORCE_PLUS_PERMANEN6、重启mysql$ systemctl restart mysqld7、查看配置情况mysql> show variables like '%audit%'; +-------------------------------+----------------------------------------+ | Variable_name | Value | +-------------------------------+----------------------------------------+ | server_audit_events | | | server_audit_excl_users | | | server_audit_file_path | /hdata/mysql_data/log/server_audit.log | | server_audit_file_rotate_now | OFF | | server_audit_file_rotate_size | 10000000 | | server_audit_file_rotations | 9 | | server_audit_incl_users | | | server_audit_loc_info | | | server_audit_logging | ON | | server_audit_mode | 1 | | server_audit_output_type | file | | server_audit_query_log_limit | 1024 | | server_audit_syslog_facility | LOG_USER | | server_audit_syslog_ident | mysql-server_auditing | | server_audit_syslog_info | | | server_audit_syslog_priority | LOG_INFO | +-------------------------------+----------------------------------------+ 16 rows in set (0.00 sec)
1. 数据库安装1.1 mysql安装1.1.1 安装mysqld1.1.2 创建mysql主主复制1.1.3 安装keepalived1. 数据库安装1.1 mysql安装1.1.1 安装mysqldPS ：分别在docloud008和docloud012上按照以下步骤安装mysqld配置mysql yum源并安装 yum install http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm -y安装mysqlyum install mysql-community-server -y启动mysqlsystemctl start mysqld && systemctl enable mysqld修改mysql密码#获取默认密码 sudo grep 'temporary password' /var/log/mysqld.log # 登陆mysql mysql -uroot -p #修改mysql密码 set password for 'root'@'localhost'=password('MyNewPass4!'); # 修改远程登录密码 GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root用户的密码' WITH GRANT OPTION; # 刷新 flush privileges;配置mysql的配置文件#停止mysql服务 systemctl stop mysqld # 修改配置文件 vim /etc/my.cnf [mysqld] port=3308 #设置UTF-8编码 character_set_server=utf8 init_connect='SET NAMES utf8' #datadir=/var/lib/mysql #修改数据存储目录 datadir=/hdata/mysql_data/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted #security risks symbolic-links=0 #修改日志存储目录 log-error=/hdata/mysql_data/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #开启binlog #binlog日志，即binary log，是二进制日志文件。它有两个作用，一是增量备份，#即只备份新增的内容；二是用于主从复制等，即主节点维护了一个binlog日志文#件，从节点从binlog中同步数据。 #当数据被误操作删除时，我们可以通过binlog日志恢复数据。 server-id=1 # 注意，集群没唯一id，master01为1，master02为2 log-bin=/hdata/mysql_data/binlog/bin-log #修改最大写入包 max_allowed_packet=10485760 #最大连接数 max_connections=1000 #设置默认时区 default-time-zone = '+08:00'将/var/lib/mysql拷贝到/hdata/mysql_datacd /var/lib sudo cp -r mysql /hdata/mysql_data/创建存储目录mkdir -pv /hdata/mysql_data/{log,binlog}修改mysql数据目录和日志目录文件夹属性sudo chown -R mysql:mysql /hdata/mysql_data重启mysqlsystemctl start mysqld1.1.2 创建mysql主主复制在master01上增加以下配置[root@docloud008 ~]# vim /etc/my.cnf # 主主复制配置 binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto-increment-increment = 2 auto-increment-offset = 1在master02上增加以下配置[root@docloud012 ~]# vim /etc/my.cnf # 主主复制配置 binlog_format = mixed relay-log = relay-bin relay-log-index = slave-relay-bin.index auto-increment-increment = 2 auto-increment-offset = 2分别重启master01和master02的mysqld服务# master01 [root@docloud006 ~]# systemctl restart mysqld # master02 [root@docloud012 ~]# systemctl restart mysqld在master01上创建同步账户mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.20.0.44' IDENTIFIED BY '1qaz@WSX';在master01上查看binlog状态信息mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)在master02上将master01设置为主服务器并开启slavemysql> CHANGE MASTER TO -> MASTER_HOST='172.20.0.44', -> MASTER_USER='rep', -> MASTER_PASSWORD='1qaz@WSX', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)查看同步状态，都为yes为成功Slave_IO_Running和Slave_SQL_Running这两个值必须为Yes，代表从服务器能正常连接服务器在master02上创建授权账户mysql> GRANT REPLICATION SLAVE ON *.* TO 'rep'@'172.20.0.41' IDENTIFIED BY '1qaz@WSX'在master02上查看binlog状态mysql> show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000001 | 154 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)在master01上设置master02为主服务器并开启slavemysql> CHANGE MASTER TO -> MASTER_HOST='172.20.0.41', -> MASTER_USER='rep', -> MASTER_PASSWORD='1qaz@WSX', -> MASTER_PORT=3306, -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=154; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec)查看同步状态1.1.3 安装keepalived在master01上安装$ yum install keepalived -y # 修改配置文件 $ vim /etc/keepalived/keepalived.conf global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr # vrrp_strict # 关闭组播 vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_2 { state BACKUP # 服务状态；MASTER（工作状态）BACKUP（备用状态） interface eth0 # VIP绑定网卡 virtual_router_id 52 # 虚拟路由ID，主、备节点必须一致 mcast_src_ip 172.20.0.41 # 本机IP priority 100 # 优先级；取值范围：0~254；MASTER > BACKUP advert_int 1 # 组播信息发送间隔，主、备节点必须一致，默认1s nopreempt # 开启非抢占模式 unicast_src_ip 172.20.0.41 # 单播源地址 unicast_peer { 172.20.0.44 # 单播目的地址 } authentication { # 验证信息；主、备节点必须一致 auth_type PASS # VRRP验证类型，PASS、AH两种 auth_pass 1111 # VRRP验证密码，在同一个vrrp_instance下，主、从必须使用相同的密码才能正常通信 } virtual_ipaddress { # 虚拟IP池，主、备节点必须一致，可以定义多个VIP 172.20.0.102 # 虚拟IP } } 在master02上安装keepalived$ yum install keepalived -y # 修改配置文件 $ vim /etc/keepalived/keepalived.conf global_defs { notification_email { acassen@firewall.loc failover@firewall.loc sysadmin@firewall.loc } notification_email_from Alexandre.Cassen@firewall.loc smtp_server 192.168.200.1 smtp_connect_timeout 30 router_id LVS_DEVEL vrrp_skip_check_adv_addr # vrrp_strict # 关闭组播 vrrp_garp_interval 0 vrrp_gna_interval 0 } vrrp_instance VI_2 { state BACKUP # 服务状态；MASTER（工作状态）BACKUP（备用状态） interface eth0 # VIP绑定网卡 virtual_router_id 52 # 虚拟路由ID，主、备节点必须一致 mcast_src_ip 172.20.0.44 # 本机IP priority 100 # 优先级；取值范围：0~254；MASTER > BACKUP advert_int 1 # 组播信息发送间隔，主、备节点必须一致，默认1s nopreempt # 开启非抢占模式 unicast_src_ip 172.20.0.44 # 单播源地址 unicast_peer { 172.20.0.41 # 单播目的地址 } authentication { # 验证信息；主、备节点必须一致 auth_type PASS # VRRP验证类型，PASS、AH两种 auth_pass 1111 # VRRP验证密码，在同一个vrrp_instance下，主、从必须使用相同的密码才能正常通信 } virtual_ipaddress { # 虚拟IP池，主、备节点必须一致，可以定义多个VIP 172.20.0.102 # 虚拟IP } }分别重启master01和master02的keepalivedsystemctl restart keepalived # 查看ip,会显示172.20.0.102的ip地址 ip a