本文详细介绍了如何在Linux环境中通过tar.gz包和yum安装方式部署MySQL单节点，包括安装依赖、配置初始化、权限设置以及用户密码修改等步骤。此外，还讲解了多实例配置的方法，通过配置不同data目录和端口实现多个数据库实例的运行。最后，提供了从MySQL5.5升级到5.7的数据迁移方案，涉及全备份、增量备份和主从复制策略。
1 单节点部署
1.1 tar.gz包安装方式
1）安装依赖
yum -y install gcc glibc libaio libstdc++ libstdc libncurses ld-linux
2）准备软件包，解压
tar -xvf Percona-Server-5.7.19-17-Linux.x86_64.ssl101.tar.gz -C /usr/local/
3）建立软链接
ln -s /usr/local/Percona-Server-5.7.19-17-Linux.x86_64.ssl101 /usr/local/mysql
4）添加mysql 用户与用户组
groupadd mysql
useradd -r -g mysql mysql
5）修改目录属性权限
chown -R mysql:mysql /usr/local/mysql
6）添加环境变量
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile
7）创建data和binlog 目录，并修改权限
mkdir -p /mysql/{data,binlog}
chown -R mysql:mysql /mysql/
8）创建配置文件my.cnf
[mysql]
#CLIENT #
port = 3306
socket = /db2data/mysql/data/mysql.sock
[mysqld]
#thread_pool_stall_limit=100
# GENERAL #
user = mysql
port = 3306
default_storage_engine = InnoDB
basedir = /usr/local/mysql
socket = /db2data/mysql/data/mysql.sock
pid_file = /db2data/mysql/data/mysql.pid
character_set_server = utf8mb4
skip_name_resolve = ON
lower_case_table_names = 1
back_log = 50
read_buffer_size = 1M
read_rnd_buffer_size=17825792
sort_buffer_size = 16M
join_buffer_size = 16M
explicit_defaults_for_timestamp = TRUE
server_id = 183750931
ignore_db_dir = lost+found
default_time_zone = '+8:00'
core_file
local_infile = 1
log_timestamps = system
thread_stack = 512K
#log_warnings = 2
performance_schema = 0
# MyISAM #
key_buffer_size = 8M
myisam_recover_options = FORCE,BACKUP
keep_files_on_create = ON
# SAFETY #
max_allowed_packet = 64M
max_connect_errors = 300
sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sysdate_is_now = 1
#innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /db2data/mysql/data/
# BINARY LOGGING #
log_bin = /db2data/mysql/binlog/mysql-bin
binlog_format = row
expire_logs_days = 14
sync_binlog = 1
binlog_cache_size = 1M
max_binlog_cache_size = 2G
# CACHES AND LIMITS #
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections=1000
#max_connections=1000
thread_cache_size = 50
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 4096
secure_file_priv=''
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 8
transaction_isolation = READ-COMMITTED
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 32
innodb_max_dirty_pages_pct = 75
innodb_lock_wait_timeout = 120
#innodb_rollback_on_timeout = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 6000
innodb_lru_scan_depth = 2000
innodb_flush_neighbors = 0
innodb_print_all_deadlocks = 1
innodb_purge_threads = 4
#innodb_undo_tablespaces = 3
innodb_sort_buffer_size = 16M
innodb_online_alter_log_max_size = 1G
innodb_disable_sort_file_cache = ON
innodb_sync_array_size = 16
innodb_page_cleaners = 8
internal_tmp_disk_storage_engine = INNODB
innodb_checksum_algorithm = crc32
#innodb_file_format = Barracuda
#innodb_file_format_max = Barracuda
innodb_stats_on_metadata = 0
innodb_sync_spin_loops = 100
innodb_spin_wait_delay = 30
# LOGGING #
log_error_verbosity = 2
log_error = /db2data/mysql/data/mysql_error.log
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 20
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1
slow_query_log = 1
long_query_time = 0.5
slow_query_log_file = /db2data/mysql/data/mysql_slow.log
#relay_log = /db2data/mysql/log/relay_log/mysql-relay-bin
#general_log_file = /db2data/mysql/log/general.log
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 4G
# REPLICATION
#plugin-load = #"rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
log_slave_updates=1
gtid_mode = ON
#gtid_mode = OFF
#rpl_semi_sync_master_enabled = 0
#rpl_semi_sync_master_timeout = 25920000000
#rpl_semi_sync_slave_enabled = 1
#rpl_semi_sync_master_wait_no_slave = 1
#rpl_semi_sync_master_wait_for_slave_count = 1
#rpl_semi_sync_master_wait_point = AFTER_SYNC
#super_read_only=1
enforce_gtid_consistency = ON
binlog_gtid_simple_recovery = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
slave_net_timeout = 60
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 50
slave_exec_mode = STRICT
slave_rows_search_algorithms = 'TABLE_SCAN,INDEX_SCAN'
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 16
slave_preserve_commit_order = 1
slave_transaction_retries = 128
#slave_compressed_protocol = 0
#THREAD_POOL
#thread_handling = pool-of-threads
#thread_pool_oversubscribe = 10
#thread_pool_size = 36
9）初始化
/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/etc/my.cnf --basedir=/usr/local/mysql --datadir=/mysql/data/ --user=mysql --initialize
/usr/local/mysql/bin/mysqld --defaults-file=/home/mysql/etc/myy.cnf --basedir=/usr/local/mysql --datadir=/mysql/data1/ --user=mysql --initialize
10）获取初始密码
cat /mysql/data/mysql_error.log |grep 'password'
2022-04-22T07:25:36.535421-08:00 1 [Note] A temporary password is generated for root@localhost: xtq3vbrbux#T
11）开启mysql实例
#开启实例
mysqld_safe --defaults-file=/home/mysql/etc/my.cnf &
#关闭实例
mysqladmin -uroot -p -S /mysql/data/mysql.sock shutdown
12）修改数据库密码
alter user user() identified by '123456';
13）查看myql.user表的情况，确认新的MySQL用户添加成功
select host,user,authentication_string from mysql.user;
14）给root授权
#运行root在localhost访问
mysql> grant all privileges on *.* to 'root'@'127.0.0.1' identified by '123456' with grant option;
mysql> flush privileges;
15）查看用户权限表
mysql> select host, user, authentication_string from mysql.user;
16）连接测试
mysql -uroot -h127.0.0.1 -p'123456';
1.2 yum安装方式
1）添加公网yum源或本地yum地址，vi /etc/yum.repo.d/mysql.repo
[mysql57-community-dmr]
name=MySQL 5.7 Community Server Development Milestone Release
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
2）使用yum安装软件包
yum makecache
yum install mysql-community-server -y
3）数据库初始化
#查看初始密码
cat /var/log/mysqld.log | grep password
#登录MySQL
mysql -uroot -hlocalhost -p '使用grep出的密码'
#修改密码
mysql> alter user user() identified by '123456';
#给root用户授权
mysql> grant all privileges on *.* to 'root'@'127.0.0.1' identified by '123456' with grant option;
#刷新权限
mysql> flush privileges;
2 多实例配置
通过配置多个data目录与配置文件，实现同时启动多个数据库实例
配置两个cnf配置文件，分别指定3306,3307端口，并设置不同的data目录，可在系统查看到多实例进程
3 版本升级
背景：业务方需求从MySQL5.5升级到5.7
数据迁移方案：
1、拷贝前一天全备份+增备至新服务器
2、当前时间全备+binlog恢复
3、主从架构复制
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
