资讯详情

深入了解 · 专业解读

Linux服务器安装第二个MySQL5.7

发布时间：2026/9/12 19:33:57
Linux服务器安装第二个MySQL5.7 背景服务器上有固态硬盘和机械硬盘mysql数据库都在SSD上一个实例业务主库和历史库分了两个库。需求将历史库迁移到机械硬盘。操作再创建一个mysql数据库用于保存历史数据端口号3307。一、准备工作看一级磁盘目录大小du-sh/*看mysql数据目录大小du-sh/var/lib/mysql查询mysql主库的安装路径mysql-uroot-p-eshow variables where Variable_name in (datadir,basedir);rootjy-serv:/tmp# mysql -uroot -p -e show variables where Variable_name in (datadir,basedir);Enter password: --------------------------------|Variable_name|Value|--------------------------------|basedir|/usr/||datadir|/var/lib/mysql/|--------------------------------basedir程序安装目录rpm/yum 安装的 mysql5.7该值经常为空属于正常现象。datadir数据目录所有库物理文件位置。查询mysql的版本mysql-uroot-prootjy-serv:~# mysql -uroot -pEnter password: Welcome to the MySQL monitor. Commands end with;or\g. Your MySQL connectionidis318518Server version:5.7.17 MySQL Community Server(GPL)Copyright(c)2000,2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Typehelp;or\hforhelp. Type\ctoclearthe current input statement. mysqlselectversion();-----------|version()|-----------|5.7.17|-----------1rowinset(0.00sec)mysqlexit下载安装包主库是mysql5.7备库也选择5.7mysql官方下载路径解压、重命名为独立目录cd/usr/localtar-zxvfmysql-5.7.44-linux-glibc2.12-x86_64.tar.gzmvmysql-5.7.44-linux-glibc2.12-x86_64 mysql3307二、操作查看服务器磁盘目录df-hrootubuntu:~# df -hFilesystem Size Used Avail Use% Mounted on tmpfs 26G 31M 26G1% /run /dev/sda4 377G 244G 115G69% / tmpfs 126G0126G0% /dev/shm tmpfs5.0M05.0M0% /run/lock /dev/sda22.0G 125M1.7G7% /boot /dev/sda11.1G5.3M1.1G1% /boot/efi /dev/sdb1 15T1.2T 13T9% /data tmpfs 26G4.0K 26G1% /run/user/0预先创建数据目录mkdir-p/data/hdd_mysql/mysql3307chown-Rmysql:mysql /data/hdd_mysql/mysql3307chmod700/data/hdd_mysql/mysql3307配置文件 /etc/my3307.cnfrootjy-serv:/etc# cat my3307.cnf[mysqld]port3307socket/data/hdd_mysql/mysql3307/mysql3307.sock pid-file/data/hdd_mysql/mysql3307/mysql3307.piddatadir/data/hdd_mysql/mysql3307 lc-messages-dir/usr/local/mysql3307/shareusermysql sql-modeSTRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONmax_connections200max_allowed_packet16M skip-name-resolveinnodb_file_per_table1lower_case_table_names1innodb_flush_log_at_trx_commit1# 内存测试机器按实际比如服务器16G内存设置4Ginnodb_buffer_pool_size4G# 日志general_log0slow_query_log1slow_query_log_file/data/hdd_mysql/mysql3307/slow.loglong_query_time5# 历史库不需要binlog就注释#log-binmysql-binserver-id1007table_open_cache2000tmp_table_size32Mthread_cache_size10myisam_max_sort_file_size100Gmyisam_sort_buffer_size56Mkey_buffer_size8Mread_buffer_size64Kread_rnd_buffer_size256Kjoin_buffer_size256Ksort_buffer_size256Ktable_definition_cache1400open_files_limit4161配置文件 systemd 服务单元 /etc/systemd/system/mysql3307.servicerootjy-serv:/etc# cat /etc/systemd/system/mysql3307.service[Unit]DescriptionMySQL5.7Instance3307HistoryAfternetwork.target[Service]TypesimpleUsermysqlGroupmysqlExecStart/usr/local/mysql3307/bin/mysqld --defaults-file/etc/my3307.cnfExecStop/usr/local/mysql3307/bin/mysqladmin-uroot -pnew_Password-S/data/hdd_mysql/mysql3307/mysql3307.sockshutdownOOMScoreAdjust-500[Install]WantedBymulti-user.target配置文件如果是windows环境创建后上传到linux的需要修改格式、设置权限sed-is/\r$///etc/my3307.cnfchmod644/etc/my3307.cnfchownroot:root /etc/my3307.cnfsed-is/\r$///etc/systemd/system/mysql3307.servicechmod644/etc/systemd/system/mysql3307.servicechownroot:root /etc/systemd/system/mysql3307.service# 改完service文件一定要重载systemdsystemctl daemon-reload初始化 3307 实例# 二选一 通过配置文件启动/usr/local/mysql3307/bin/mysqld --defaults-file/etc/my3307.cnf--initialize--usermysql# 指定路径/usr/local/mysql3307/bin/mysqld--initialize--usermysql--datadir/data/hdd_mysql/mysql3307执行后获取临时密码2026-09-09T02:47:37.591362Z1[Note]A temporary password is generatedforrootlocalhost: lT?I((tt0uy加载 systemd 配置、启动systemctl daemon-reload systemctl start mysql3307 systemctlenablemysql3307 systemctl status mysql3307执行成功后会显示rootjy-serv:~# systemctl daemon-reloadrootjy-serv:~# systemctl start mysql3307rootjy-serv:~# systemctl status mysql3307● mysql3307.service - MySQL5.7Instance3307History Loaded: loaded(/etc/systemd/system/mysql3307.service;disabled;vendor preset: enabled)Active: active(running)since Wed2026-09-0910:59:12 CST;7s ago Main PID:203794(mysqld_safe)Tasks:31(limit:38239)Memory:326.6M CPU: 705ms CGroup: /system.slice/mysql3307.service ├─203794 /bin/sh /usr/local/mysql3307/bin/mysqld_safe --defaults-file/etc/my3307.cnf └─204054 /usr/local/mysql3307/bin/mysqld --defaults-file/etc/my3307.cnf--basedir/usr/Sep 0910:59:12 jy-serv systemd[1]: Started MySQL5.7Instance3307History. Sep 0910:59:12 jy-serv mysqld_safe[203794]:2026-09-09T02:59:12.421659Z mysqld_safe Logging to /datSep 0910:59:12 jy-serv mysqld_safe[203794]:2026-09-09T02:59:12.463605Z mysqld_safe Starting mysqld登录 3307 修改 root 密码rootjy-serv:~# mysql -uroot -p -h127.0.0.1 -P3307Enter password:# sql中执行ALTERUSERrootlocalhostIDENTIFIEDBYnew_Password;GRANTALLPRIVILEGESON*.*TOroot127.0.0.1IDENTIFIEDBYnew_Password;FLUSHPRIVILEGES;exit;三、验证rootjy-serv:~# mysql -uroot -pnew_Password -h127.0.0.1 -P3307 -e select version(),port;mysql:[Warning]Using a password on thecommandline interface can be insecure. --------------------|version()|port|--------------------|5.7.44-log|3307|-------------------- rootjy-serv:~#服务器能直连但是本地navicat连接失败提示 not allowedMySQL 账号权限限制当前 root 只允许127.0.0.1访问# 登录数据库mysql-uroot-pnew_Password-h127.0.0.1-P3307# 允许局域网IP访问CREATEUSERroot%IDENTIFIED BYnew_Password;# 以下二选一指定IP或者局域网所有机器GRANT ALL PRIVILEGES ON *.* TOroot192.168.100.xxx;GRANT ALL PRIVILEGES ON *.* TOroot%;FLUSH PRIVILEGES;exit;

相关新闻