linux 基于 my.cnf 安装mysql 5.7

爱码狮

已于 2023-03-29 09:45:07 修改

阅读量1.9k

点赞数

分类专栏：数据库文章标签： linux mysql navicat 服务器经验分享

于 2021-09-02 11:48:43 首次发布

本文链接：https://blog.csdn.net/qq_37928350/article/details/120053621

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

下载地址：https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

1. 首先需要创建安装的目录（解压mysql目录）：

[root@bogon mysql 5.7]# mkdir mysql_3306

2. 解压mysql 压缩文件到 mysql 路径下：

[root@bogon mysql 5.7]# tar -xvzf mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz -C /home/mysql_3306/ --strip-components=1

3. 打开创建解压的目录：

[root@bogon home]# cd mysql_3306/

4. 创建log 文件目录：

[root@bogon mysql_3306]# mkdir -p keyring data tmp log/{binlog,relaylog,redolog}

5. 创建 my.cnf 文件：

[root@bogon mysql_3306]#  vi my.cnf

my.cnf 文件内容：

[mysqld]
early-plugin-load="keyring_file.so"
keyring_file_data=/home/mysql_3306/keyring/keyring

# DO NOT MODIFY, Universe will generate this part
port = 3306
server_id = 1
basedir = /home/mysql_3306/
datadir = /home/mysql_3306/data/
log_bin = /home/mysql_3306/log/binlog/mysql-bin
tmpdir = /home/mysql_3306/tmp/
relay_log = /home/mysql_3306/log/relaylog/mysql-relay
innodb_log_group_home_dir = /home/mysql_3306/log/redolog/
log_error = /home/mysql_3306/data/mysql-error.log
report_host = 192.168.43.67

# BINLOG
binlog_error_action = ABORT_SERVER
binlog_format = row
binlog_rows_query_log_events = 1
log_slave_updates = 1
master_info_repository = TABLE
max_binlog_size = 256M
relay_log_info_repository = TABLE
relay_log_recovery = 1
sync_binlog = 1

# GTID #
gtid_mode = ON
enforce_gtid_consistency = 1

# ENGINE
default_storage_engine = InnoDB
innodb_buffer_pool_size = 128M
innodb_data_file_path = ibdata1:1G:autoextend
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 1000
innodb_log_buffer_size = 64M
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
innodb_max_dirty_pages_pct = 60
innodb_print_all_deadlocks=1
innodb_stats_on_metadata = 0
innodb_strict_mode = 1
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 8
innodb_buffer_pool_load_at_startup = 1
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_dump_pct=25
innodb_sort_buffer_size = 8M
#innodb_page_cleaners = 8
#innodb_buffer_pool_instances = 8

# CACHE
key_buffer_size = 32M
tmp_table_size = 32M
max_heap_table_size = 32M
table_open_cache = 1024
query_cache_type = 0
query_cache_size = 0
max_connections = 2000
thread_cache_size = 1024
open_files_limit = 65535
binlog_cache_size = 1M
join_buffer_size = 8M
sort_buffer_size = 8M

# SLOW LOG
slow_query_log = 1
slow_query_log_file = /home/mysql_3306/data/mysql-slow.log
log_slow_admin_statements = 1
log_slow_slave_statements = 1
long_query_time  = 1

# SEMISYNC #
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_slave_enabled = 0
rpl_semi_sync_master_wait_for_slave_count = 1
rpl_semi_sync_master_wait_no_slave = 0
rpl_semi_sync_master_timeout = 30000

# MISC
log_timestamps=SYSTEM
lower_case_table_names = 1
max_allowed_packet = 64M
read_only = 0
skip_external_locking = 1
skip_name_resolve = 1
skip_slave_start = 1
socket = /home/mysql_3306/data/mysqld.sock
pid_file = /home/mysql_3306/data/mysqld.pid
disabled_storage_engines = ARCHIVE,BLACKHOLE,EXAMPLE,FEDERATED,MEMORY,MERGE,NDB
log-output = TABLE,FILE
character_set_server = utf8mb4
secure_file_priv = ""
performance-schema-instrument='wait/lock/metadata/sql/mdl=ON'
expire_logs_days = 14

# MTS
slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=16
slave_preserve_commit_order=1

6. mysql_3306 授权限：

[root@bogon mysql_3306]#  chown -R mysql:mysql /home/mysql_3306

7. 重新加载配置文件：

[root@bogon mysql_3306]# systemctl daemon-reload

8. 初始化：

[root@bogon mysql_3306]# /home/mysql_3306/bin/mysqld --defaults-file=/home/mysql_3306/my.cnf --initialize --user=mysql &

[root@bogon mysql_3306]# /home/mysql_3306/bin/mysql_ssl_rsa_setup --datadir=/home/mysql_3306/data/

9. 打印查看log ，有无异常：

[root@bogon mysql_3306]# cat data/mysql-error.log

10. 创建mysql_3306.service 文件：

[root@bogon mysql_3306]# vi /etc/systemd/system/mysql_3306.service

mysql_3306.service 内容：

[Unit]
Description=MySQL Server
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

# 启动结束后会发出通知信号，systemd 接下来可以启动其他服务，在容器中可能需要设置为forking（启动一个子进程，并且启动后父进程会退出）
Type=forking
PIDFile=/home/mysql_3306/data/mysqld.pid

# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0

# Start main service
ExecStart=/home/mysql_3306/bin/mysqld --defaults-file=/home/mysql_3306/my.cnf --daemonize --pid-file=/home/mysql_3306/data/mysqld.pid --user=mysql --socket=/home/mysql_3306/data/mysqld.sock --port=3306

# Use this to switch malloc implementation
EnvironmentFile=-/etc/sysconfig/mysql

# Sets open_files_limit
LimitNOFILE = 65535

# 守护，意外的停止会被重启
Restart=on-failure

#设置安全退出码，不会被自动重启
RestartPreventExitStatus=1

PrivateTmp=false

11. 重新加载配置文件：

[root@bogon mysql_3306]# systemctl daemon-reload

12. 启动服务：

[root@bogon mysql_3306]# systemctl start mysql_3306.service

13. 查询服务状态：(正常启动)

[root@bogon mysql_3306]# systemctl status mysql_3306.service
● mysql_3306.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysql_3306.service; enabled; vendor preset: disabled)
   Active: active (running) since 四 2021-09-02 10:42:01 CST; 16s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 29329 (mysqld)
   CGroup: /system.slice/mysql_3306.service
           └─29329 /home/mysql_3306/bin/mysqld --defaults-file=/home/mysql_3306/my.cnf --daemonize --pid-file=/home/mysql_3306/data/mysqld.pid --user=mysql --socket=/home/mysql_3306/data/mysqld.sock --port=...

9月 02 10:42:01 bogon systemd[1]: Starting MySQL Server...
9月 02 10:42:01 bogon systemd[1]: Started MySQL Server.

14 .设置开机自启：

[root@bogon mysql_3306]# systemctl enable mysql_3306.service

15. 查看root 默认密码：

[root@bogon mysql_3306]# cat /home/mysql_3306/data/mysql-error.log |grep "temporary password"
2021-09-02T10:39:04.785752+08:00 1 [Note] A temporary password is generated for root@localhost: ******（默认密码）

16. 登录 mysql服务输入默认密码：

[root@bogon mysql_3306]# /home/mysql_3306/bin/mysql -uroot -p -S /home/mysql_3306/data/mysqld.sock
Enter password:

17. 登录成功：

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.34-log

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

18. 修改root 默认密码，重新加载权限表。

mysql>  alter user root@'localhost' identified by '123456'; 
mysql>  flush privileges;

19. 安装完毕发现远程无法登入服务，需要修改user 所对应的 host。

mysql> update user set host ='%' where user ='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql>  flush privileges;
mysql> select host,user from user ;
+-----------+---------------+
| host      | user          |
+-----------+---------------+
| %         | root          |
| localhost | mysql.session |
| localhost | mysql.sys     |
+-----------+---------------+
4 rows in set (0.00 sec)