MySql单机版安装部署
Mysql单机版部署文档
-
下载 Mysql 社区版的 YUM 源
浏览器打开 https://dev.mysql.com/downloads/repo/yum/ 下载对应操作系统的 YUM 源
CentOS 8 下载 mysql80-community-release-el8-1.noarch.rpm
CentOS 7 下载 mysql80-community-release-el7-3.noarch.rpm
CentOS 6 下载 mysql80-community-release-el6-3.noarch.rpm
# 根据操作系统版本安装 YUM 源
rpm -ivh mysql80-community-release-el7-3.noarch.rpm
- 查询 YUM 源中的 Mysql
yum repolist enabled | grep "mysql.*-community.*"
- 安装 Mysql
yum install -y mysql-community-server
- 修改操作系统内核参数 sysctl.conf
vim /etc/sysctl.conf
# 添加以下配置项
* soft core unlimit
* hard core unlimit
* soft fsize unlimited
* hard fsize unlimited
* soft data unlimited
* hard data unlimited
* soft nproc 65535
* hard nproc 63535
* soft stack unlimited
* hard stack unlimited
* soft nofile 409600
* hard nofile 409600
- 配置 /etc/my.cnf 文件
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
# 配置 INNODB Buffer Pool, 配置成内存的 70% 即可
innodb_buffer_pool_size = 48G
datadir=/data/mysql/datadir
socket=/data/mysql/mysql.sock
tmpdir=/data/mysql/tmp
log-error=/data/mysql/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
# 开启 binlog 日志
log-bin=mysql-bin
# 日志使用 ROW 格式
binlog_format=ROW
# binlog最大大小
max_binlog_size=512M
# binlog缓存大小
binlog_cache_size=8m
# 最大binlog缓存大小
max_binlog_cache_size=256M
# binlog在30天自动删除
binlog_expire_logs_seconds=2592000
server-id=1
gtid_mode=ON
enforce_gtid_consistency=true
# 是否对sql语句大小写敏感，1表示不敏感, 要在安装完成后马上配置, 否则无法生效
lower_case_table_names=1
# 慢查询功能
slow_query_log=1
long_query_time=1
log-queries-not-using-indexes
slow_query_log_file = /data/mysql/mysql-slow.log
# mysql8 无效 log-slow-queries=/data/mysql/slow-query.log
sql_mode= STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
# Response Settings
back_log = 1024
max_connections = 20000
max_connect_errors = 10000
connect-timeout = 5
wait_timeout = 600
interactive_timeout = 3600
slave-net-timeout = 60
net_read_timeout = 30
net_write_timeout = 60
net_retry_count = 10
net_buffer_length = 16384
max_allowed_packet = 32M
tmp_table_size = 512M
max_heap_table_size = 512M
sort_buffer_size = 2M
read_buffer_size = 2M
join_buffer_size = 8M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
group_concat_max_len = 64K
#Cache Settings
table_open_cache = 512
thread_stack = 512K
thread_cache_size = 200
#query_cache_type = 0
#query_cache_size = 0
#query_cache_limit = 0
#query_cache_min_res_unit = 0
default-time-zone = system
character-set-server = utf8
default-storage-engine = InnoDB
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1
#plugin-load = thread_pool.so
#Log Settings
log-bin = mysql-bin
log-bin-index = mysql-bin.index
#relay-log = relay-log
#relay-log-index = relay-log.index
expire_logs_days = 7
relay-log-purge = 1
sync_binlog = 0
[mysqldump]$
quick = 1
# SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 32M
[mysql]
disable-auto-rehash = 1
default-character-set = utf8
connect-timeout = 3
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
- 启动 Mysql
service mysqld start
# 配置为开机启动
systemctl enable mysqld
- 登录及配置 Mysql
# 第一次登录时, 密码从 /data/mysql/log/mysqld.log 中查看
grep 'password' /data/mysql/log/mysqld.log
mysql -uroot -p
select host, user, max_user_connections, authentication_string from mysql.user;
# 修改root密码
alter user 'root'@'localhost' identified by 'Taiji1@3456';
# 如果上一条命令执行不成功, 则执行以下命令修改root密码
set password for root@localhost = password('m,[<}l;p1mysql');
# 创建数据库用户
create user 'bjparkcy'@'%' identified by 'A123@#$mysql';
# 创建数据库
create database t_product_center DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
# 删除数据库
drop database t_product_center;
# 为用户授权 - 部分权限
grant select, insert, delete, create, drop, update, alter, index on test.* to 'bjparkcy'@'%' identified by 'A123@#$mysql' with grant option;
# 为用户授权 - 对于数据库的所有权限
grant all privileges on t_product_center.* to 'bjparkcy'@'%' with grant option;
# 更新权限
flush privileges;
# 查看权限
show grants;
# 收回权限
revoke delete on test.* from 'bjparkcy'@'%';
# 修改用户名
rename user 'bjparkcy'@'%' to 'wyf'@'%';
# 修改密码
set password for 'bjparkcy'@'%'=password('111111');
# 如果上一条命令修改不成功，则执行下面的命令
mysqladmin -ubjparkcy -p111111 password '新密码';
# 备份数据库
mysqldump -uroot -pTaiji1@3456 --databases t_product_center > /data/mysql/bak/t_product_center.sql;
- Mysql数据库备份及还原
数据库备份脚本参考 备份脚本, 每晚执行
crontab 表达式
59 23 * * * /data/mysql/auto-mysql-fullbackup.sh
# 数据库备份
mysqldump -uroot -pTaiji1@3456 --databases t_product_center > /data/mysql/bak/t_product_center.sql;
# 数据库还原
source /data/mysql/bak/t_product_center.sql;
# 全量备份
0 1 * * 6 /data/mysql/autobackup.sh full
# 增量备份
0 1 * * * /data/mysql/autobackup.sh incremental
# 增量备份
0 */12 * * * /data/mysql/autobackup.sh incremental-with-redo-log-only
# 删除过期备份文件
0 2 * * 1 /data/mysql/autobackup.sh remove-old
感谢您的反馈。如果您有关于如何使用 KubeSphere 的具体问题，请在 Slack 上提问。如果您想报告问题或提出改进建议，请在 GitHub 存储库中打开问题。
上一篇
页面内容