开源大数据集群部署(三)集群mysql数据库部署
1、mysql部署
在hd1.dtstack.com主机root权限下安装配置
Ø 在安装目录/root/bigdata目录下解压包
tar -xvJf mysql-8.0.31-linux-glibc2.12-x86_64.tar.xz -C /opt/ ln -s /opt/mysql-8.0.31-linux-glibc2.12-x86_64/ /opt/mysql
Ø mysql配置文件/etc/my.cnf内容如下
[client] port = 3306 socket = /data/mysql8/data/mysql.sock [mysql] prompt = "\u@mysql \R:\m:\s [\d]>" port = 3306 socket = /data/mysql8/data/mysql.sock [mysqld] # 用户 user = mysql # # 端口 port = 3306 # # 安装目录 basedir = /opt/mysql # # 数据目录 datadir = /data/mysql57/data # # sock 文件目录 socket = /data/mysql57/data/mysql.sock # # 临时文件目录 tmpdir = /data/mysql57/tmp # # 错误日志目录 log-error = /data/mysql57/logs/error.log # # Server id server-id = 1 # # 二进制日志 log-bin = /data/mysql8/logs/mysql-bin # # binlog 清理机制 expire_logs_days = 10 # # 日志格式 推荐为 row binlog_format = ROW # # 双一参数 # sync_binlog = 1 # # binlog 缓存 如果有大事务 可以提升 binlog 写入效率 # binlog_cache_size = 4M # # binlog 使用最大内存 max_binlog_cache_size = 4G # # 单 binlog 文件大小 max_binlog_size = 500M # # 中继日志目录 relay_log = /data/mysql57/logs/mysql-relay # # GTID # gtid_mode = on # # 强制GTID的一致性 # enforce_gtid_consistency = 1 # # 隔离级别 transaction_isolation = READ-COMMITTED # # innodb 最大缓存区 操作系统内存的 70%-80% 最佳 # innodb_buffer_pool_size = 2048M # # 独立的缓冲池实例,建议每个实例大小至少为 1 G 参考 buffer pool 大小设置 # innodb_buffer_pool_instances = 2 # # 默认为关闭OFF。如果开启该参数,启动MySQL服务时,MySQL将本地热数据加载到InnoDB缓冲池中 # innodb_buffer_pool_load_at_startup = 1 # # 默认为关闭OFF。如果开启该参数,停止MySQL服务时,InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘 # innodb_buffer_pool_dump_at_shutdown = 1 # # 共享表空间大小 # innodb_data_file_path = ibdata1:500M:autoextend # # 临时表空间 # innodb_temp_data_file_path = ibtmp1:200M:autoextend # # redo 刷写机制 双一参数之一 # innodb_flush_log_at_trx_commit = 1 # # redo buffer size # innodb_log_buffer_size = 32M # # redo 文件大小 # innodb_log_file_size = 128MB # # redo 日志组数 默认为 2 # innodb_log_files_in_group = 2 # # 排序缓存大小 # innodb_sort_buffer_size = 1048576 # # doublewrite 两次写机制 # innodb_doublewrite = 1 # # 开启慢日志组 # slow_query_log = 1 # # 慢日志目录 # slow_query_log_file = /data/mysql57/logs/slow.log # # 查询大于多少秒会记录到慢日志中 # long_query_time = 5 # # 是否记录未使用索引的 SQL # log_queries_not_using_indexes = 0 # # MySQL 允许的最大连接 max_connections = 1000 lower-case-table-names = 1 #跳过ssl skip_ssl # # 单个用户限制的最大连接数,默认是不限制 # max_user_connections = 64 # #最大连接错误数,防止密码暴力破解 # max_connect_errors = 10000 # # PID 文件 pid-file = mysql.pid # # Server 编码 character-set-server = utf8mb4 # # Server 比较规则 # collation_server = utf8mb4_general_ci # # 禁用DNS主机名查找 # skip_name_resolve = 1 # # 打开文件最大数量 open_files_limit = 65535 # # 自动提交 autocommit = 1 # # timestamp 列如果没有显式定义为 null # explicit_defaults_for_timestamp = 1
说明:
ü server-id= 1服务的唯一编号,master为1
Ø 创建对应数据和日志目录
mkdir -p /data/mysql8/data/ mkdir -p /data/mysql8/logs/ mkdir -p /data/mysql8/tmp chown mysql:mysql -R /data/mysql8
Ø 初始化mysql数据
cd /opt/mysql bin/mysqld --initialize --user=mysql --basedir=/opt/mysql --datadir=/data/mysql8/data/ --lower-case-table-names=1
Ø 启动服务和检查服务(mysql权限启动mysql数据库)
vi /etc/systemd/system/mysqld.service [Unit] Description=MySQL Server 8.0.31 Documentation=man:mysqld(8) Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html After=network.target After=syslog.target [Install] WantedBy=multi-user.target [Service] User=mysql Group=mysql ExecStart=/opt/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf LimitNOFILE = 5000
systemctl start mysqld
状态为running表示mysql服务已经起来
修改默认密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Ø 创建用户
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456'; mysql> flush privileges; [mysql@hd3.dtstack.com ~]$ systemctl stop mysqld [mysql@hd3.dtstack.com ~]$ systemctl start mysqld