1、MySQL单实例部署
版本：8.0.28
1. 卸载自带数据库
# 查看是否安装过mysql和Mariadb
rpm -qa | grep mysql
rpm -qa | grep mariadb
# 如果存在安装卸载
yum -y remove mariadb-*
# 或者使用rpm卸载，需要指定完整的名称
rpm -e --nodeps <上面查询到的完整名称>
2. 创建用户和用户组
groupadd mysql
useradd -g mysql mysql -s /sbin/nologin
# 或者创建可登陆用户
useradd -r -g mysql mysql
3. 目录创建以及解压文件
tar xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz -C /usr/local
mv mysql-8.0.19-linux-glibc2.12-x86_64/ mysql
mkdir /usr/local/mysql/data
4. 配置文件/etc/my.cnf
改配置文件中存在mysql5.7版本不支持的参数
[client]
port = 3406
socket = /tmp/mysql.sock
[mysql]
prompt="\u@db \R:\m:\s [\d]> "
no-auto-rehash
[mysqld]
server-id = 3406100
user = mysql
port = 3406
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data/
socket = /tmp/mysql.sock
pid-file = db.pid
character-set-server = utf8mb4
skip_name_resolve = 1
open_files_limit = 65535
back_log = 1024
max_connections = 512
max_connect_errors = 1000000
table_open_cache = 1024
table_definition_cache = 1024
table_open_cache_instances = 64
thread_stack = 512K
external-locking = FALSE
max_allowed_packet = 32M
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 768
interactive_timeout = 600
wait_timeout = 600
tmp_table_size = 32M
max_heap_table_size = 32M
slow_query_log = 1
slow_query_log_file = /usr/local/mysql/data/slow.log
log-error = /usr/local/mysql/data/error.log
long_query_time = 0.1
log-bin = /usr/local/mysql/data/mysql-binlog
sync_binlog = 1
binlog_cache_size = 4M
max_binlog_cache_size = 1G
max_binlog_size = 1G
gtid_mode = on
enforce_gtid_consistency = 1
log_slave_updates
binlog_format = row
relay_log_recovery = 1
relay-log-purge = 1
key_buffer_size = 32M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
bulk_insert_buffer_size = 64M
#myisam_sort_buffer_size = 128M
#myisam_max_sort_file_size = 10G
#myisam_repair_threads = 1
lock_wait_timeout = 3600
explicit_defaults_for_timestamp = 1
innodb_thread_concurrency = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
master_info_repository = TABLE
relay_log_info_repository = TABLE
slave_parallel_type=LOGICAL_CLOCK
transaction_isolation = REPEATABLE-READ
#innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1024M
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 32M
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
#innodb_max_undo_log_size = 1G
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
innodb_flush_neighbors = 0
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_open_files = 65535
innodb_max_dirty_pages_pct = 50
innodb_flush_method = O_DIRECT
innodb_lru_scan_depth = 4000
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = 1
innodb_print_all_deadlocks = 1
innodb_file_per_table = 1
innodb_online_alter_log_max_size = 4G
innodb_stats_on_metadata = 0
innodb_status_file = 1
# 注意: 开启 innodb_status_output & innodb_status_output_locks 后, 可能会导致log-error文件增长较快
innodb_status_output = 0
innodb_status_output_locks = 0
#performance_schema
performance_schema = 1
performance_schema_instrument = '%=on'
#innodb monitor
innodb_monitor_enable="module_innodb"
innodb_monitor_enable="module_server"
innodb_monitor_enable="module_dml"
innodb_monitor_enable="module_ddl"
innodb_monitor_enable="module_trx"
innodb_monitor_enable="module_os"
innodb_monitor_enable="module_purge"
innodb_monitor_enable="module_log"
innodb_monitor_enable="module_lock"
innodb_monitor_enable="module_buffer"
innodb_monitor_enable="module_index"
innodb_monitor_enable="module_ibuf_system"
innodb_monitor_enable="module_buffer_page"
innodb_monitor_enable="module_adaptive_hash"
# Group Replication
#server_id = 1003306
#gtid_mode = ON
#enforce_gtid_consistency = ON
#master_info_repository = TABLE
#relay_log_info_repository = TABLE
binlog_checksum = NONE
#log_slave_updates = ON
#log_bin = binlog
#binlog_format= ROW
transaction_write_set_extraction = XXHASH64
# 未来可能被弃用的变量，会出现告警信息，binlog_expire_logs_seconds用来代替
# expire_logs_days = 7
binlog_expire_logs_seconds = 7
# 8版本弃用的变量
# loose-group_replication_group_name = 'e842862c-9b12-11e8-8131-080027f1fd08'
# internal_tmp_disk_storage_engine = InnoDB # 8版本不再支持，默认引擎
# query_cache_size = 0 # 8版本不再支持这两个参数
# query_cache_type = 0
# loose-group_replication_start_on_boot = off
# loose-group_replication_local_address = 'enmoedu:33066'
# loose-group_replication_group_seeds ='enmoedu1:33067,enmoedu2:33068,enmoedu:33066'
# loose-group_replication_bootstrap_group = off
# loose-group_replication_single_primary_mode=off
# loose-group_replication_enforce_update_everywhere_checks=true
[mysqldump]
quick
max_allowed_packet = 32M
5. 创建目录并改变目录文件的拥有者
# 创建数据目录和配置文件目录
mkdir -p /usr/local/mysql/data
chown mysql:mysql -R /usr/local/mysql/
6. 初始化服务
cd /usr/local/mysql/bin
./mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql
记住日志中的临时密码或者使用命令进行查看：
grep -i "temporary password" /usr/local/mysql/data/error.log
7. 添加到服务管理并设置自启动
# 使用systemd来管理mysql
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# 添加mysql到服务
chkconfig --add mysqld
# 设置开机自启动
chkconfig mysqld on
8. 使用systemd服务启停mysql
# 设置mysql环境变量或者将mysqld启动脚本软连接到/usr/local/bin
# 设置环境变量
sudo vim /etc/profile.d/my_env.sh
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:${MYSQL_HOME}/bin
source /etc/profile
# 启动mysql服务
systemctl start mysqld
# 查看mysql服务状态
systemctl status mysqld
# 停止服务
systemctl stop mysqld
9. 安全启动数据库并修改密码
如果添加到了systemd服务，也可以使用systectl进行管理启动
# 安全启动
./mysqld_safe --defaults-file=/etc/my.cnf &
mysql -uroot -p
# 修改root用户密码
alter user 'root'@'localhost' identified by '密码';
flush privileges;
# 修改root用户权限（用于其他服务器访问 - 8版本之前好像是这样）
grant all privileges on *.* to root@'%' identified by '密码';
flush privileges;
# 关闭数据库
./mysqladmin shutdown -uroot -p'password'
8.0之后授权
mysql -uroot -p
use mysql;
# 查看user表中的数据（8版本密码字段已修改为authentication_string）
select Host, User,authentication_string from user;
# 修改user表中的Host:（可能会报错误：ERROR 1062 (23000): Duplicate entry '%-root' for key 'PRIMARY'，查看如果修改可忽略）
update user set Host='%' where User='root';
# 刷新
flush privileges;
# 再次查看
select Host, User,authentication_string from user;
连接测试
2、多实例部署两种方式
一台主机多个mysql实例
单机多实例有两种创建方式：
- 笨办法：解压多份修改配置文件，指定不同的端口以及socket和目录位置等信息即可
- 部署一个服务后多次初始化，简要操作如下：
- 创建datadir数据目录，并赋权（所属用户和组）
- 使用原来的服务进行多次初始化，初始化时指定新创建的目录
- 复制my.cnf配置文件，修改端口和sock和目录位置
- 启动服务并修改密码