编译安装MySQL5.6(其他版本均可同)

1、软件准备

cd /opt/soft
wget http://172.16.2.73:10088/mysql/mysql-5.6.20.tar.gz
tar xf mysql-5.6.20.tar.gz

2、创建相关用户

groupadd -g 550 mysql
useradd -g mysql -u 550 mysql

3、安装相关依赖

yum install cmake autoconf wget gcc-c++ gcc ncurses-devel openssl openssl-devel -y
cd mysql-5.6.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/opt/mysql \
-DMYSQL_DATADIR=/opt/mysql/data \
-DSYSCONFDIR=/etc/my.cnf \
-DWITH_DEBUG=0 \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1  \
-DMYSQL_UNIX_ADDR=/opt/mysql/data/mysql.sock \
-DMYSQL_TCP_PORT=3306 \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DENABLE_DOWNLOADS=1 \
-DEXTRA_CHARSETS=all  \
-DDEFAULT_CHARSET=utf8  \
-DDEFAULT_COLLATION=utf8_general_ci

执行完 cmake 之后、执行 make 和 make install

4、数据库初始化

mkdir -p /opt/mysql/data
chown -R mysql:mysql /opt/mysql
[client]
port = 3306
socket = /opt/mysql/data/mysql.sock
 
[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 4
innodb_data_file_path = ibdata1:512M:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 20M
innodb_log_file_size=100M
innodb_log_files_in_group = 3
 
port = 3306
socket = /opt/mysql/data/mysql.sock
basedir = /opt/mysql
datadir = /opt/mysql/data
 
skip-name-resolve
skip_external_locking
local-infile = 0
character-set-server = utf8
default-storage-engine = innodb
log-error = /opt/mysql/data/error.log
#slow_query_log = on
#long_query_time = 2
#slow_query_log_file = /opt/mysql/data/slow.log
 
server-id = 102
auto_increment_increment=1
auto_increment_offset=1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-slave-updates
log-bin = mysql-bin
binlog_format = ROW
binlog_cache_size = 4M
sync_binlog = 1
expire_logs_days = 30
back_log = 500

max_connections = 1000
max_connect_errors = 1000
wait_timeout = 18000
interactive_timeout = 18000
connect_timeout = 20

max_allowed_packet = 32M
table_open_cache = 2000
read_buffer_size = 512K
read_rnd_buffer_size = 512K
sort_buffer_size = 4M
join_buffer_size = 4M
thread_cache_size = 64
explicit_defaults_for_timestamp = 1
query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 4M
transaction_isolation = REPEATABLE-READ
tmp_table_size = 32M
symbolic-links = 0
lower_case_table_names = 1

# 主从同步错误20230107
# slave-skip-errors=1146

#初始化
./scripts/mysql_install_db --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data --defaults-file=/etc/my.cnf --random-passwords
# 拷贝启动文件
cp ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
/etc/init.d/mysql start
#设置环境变量
vim /etc/profile
export PATH=/opt/mysql/bin:/opt/mysql/lib:$PATH 
source /etc/profile
#修改密码
cat /root/.mysql_secret
mysql -uroot -p
mysql> SET PASSWORD = PASSWORD('123456'); 
# 允许任意主机以用户 root 和密码连接到 mysql 服务器
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'passwd' WITH GRANT OPTION;
#特定 ip 的主机以特定用户和密码连接到 mysql 服务器
GRANT ALL PRIVILEGES ON *.* TO 'user'@'x.x.x.x' IDENTIFIED BY 'userpwd' WITH GRANT OPTION; 
#刷新 MySQL 的系统权限相关表
flush PRIVILEGES;