目 录CONTENT

文章目录

MySQL5.7安装与基础管理

简中仙
2020-08-28 / 0 评论 / 0 点赞 / 64 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2024-01-17,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一、MySQL 5.7.26 二进制版本安装

1、下载软件至存放目录

# mkdir -p /server/soft
# wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
# mv mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz /server/soft/

2、解压软件

# yum -y remove mariadb-libs.x86_64 
# yum -y install libaio-devel
# cd /server/soft/
# tar zxf mysql-5.7.41-linux-glibc2.12-x86_64.tar.gz
# ln -s /server/soft/mysql-5.7.41-linux-glibc2.12-x86_64 /usr/local/mysql

配置基础环境

cat >>/etc/security/limits.conf << EOF
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft nofile 65536
mysql hard nofile 65536
EOF
cat >>/etc/security/limits.d/20-nproc.conf<<EOF
mysql       soft    nproc     unlimited
EOF
cat >>/etc/security/limits.d/90-nproc.conf<<EOF
mysql       soft    nproc     unlimited
EOF
sed -i "s/${fs_file}/65535/g" /etc/sysctl.conf
/usr/sbin/sysctl -p 

3、创建用户及设置环境变量

# useradd -s /sbin/nologin mysql
# echo "export PATH=/usr/local/mysql/bin:$PATH" >> /etc/profile
# source /etc/profile
# mysql -V
mysql  Ver 14.14 Distrib 5.7.41, for linux-glibc2.12 (x86_64) using  EditLine wrapper

4、创建数据路径并授权

# mkdir -p /data/mysql/{data,logs,tmp}
# mkdir -p /var/lib/mysql/
# chown -R mysql.mysql /usr/local/mysql/
# chown -R mysql.mysql /data/mysql
# chown -R mysql:mysql /var/lib/mysql/

5、添加配置文件

# cat > /etc/my.cnf <<-EOF
[mysql]
socket                          = /var/lib/mysql/mysql.sock
prompt                          = "[\\u@db160] [\\d]>"	
no_auto_rehash

[mysqld]
# === Required Settings ===
server_id                       = 6
basedir                         = /usr/local/mysql
datadir                         = /data/mysql/data
max_allowed_packet              = 1G
max_connect_errors              = 1000000
pid_file                        = /data/mysql/mysqld.pid
port                            = 3306
skip_external_locking
skip_name_resolve               = 1
default_time_zone               = "+8:00"	
socket                          = /var/lib/mysql/mysql.sock
tmpdir                          = /data/mysql/tmp
user                            = mysql
secure_file_priv                = /data/mysql/tmp
symbolic-links                  = 0

character-set-server            = utf8mb4 
collation-server                = utf8mb4_general_ci
init_connect                    = 'SET NAMES utf8mb4'
lower_case_table_names          = 1
max_heap_table_size             = 128M

# === SQL Compatibility Mode ===
# Enable for b/c with databases created in older MySQL/MariaDB versions
# (e.g. when using null dates)
sql_mode                       = NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

# === InnoDB Settings ===
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 4     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 1638M    # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 1G
innodb_stats_on_metadata        = 0
innodb_lock_wait_timeout        = 50
innodb_page_size                = 16k
innodb_lru_scan_depth           = 4096
innodb_flush_neighbors          = 1
innodb_purge_threads            = 4
innodb_print_all_deadlocks      = 1
innodb_strict_mode              = 1
innodb_stats_persistent_sample_pages = 500
innodb_autoinc_lock_mode        = 2
innodb_online_alter_log_max_size= 4G
innodb_open_files               = 4096

#innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
#innodb_thread_concurrency      = 4     # Optional: Set to the number of CPUs on your system (minus 1 or 2) to better
                                        # contain CPU usage. E.g. if your system has 8 CPUs, try 6 or 7 and check
                                        # the overall load produced by MySQL/MariaDB.
innodb_read_io_threads          = 1
innodb_write_io_threads         = 1
#innodb_io_capacity             = 1000  # Max is 2000

# === MyISAM Settings ===
# The following 3 options are ONLY supported by MariaDB & up to MySQL 5.7
# Do NOT un-comment on MySQL 8.x+
#query_cache_limit              = 4M    # UPD
#query_cache_size               = 64M   # UPD
#query_cache_type               = 1     # Enabled by default

key_buffer_size                 = 32M   # UPD

low_priority_updates            = 1
concurrent_insert               = 2

# === Connection Settings ===
max_connections                 = 100   # UPD - Important: high no. of connections = high RAM consumption

back_log                        = 512
thread_cache_size               = 48
thread_stack                    = 512K

interactive_timeout             = 7200
wait_timeout                    = 3600

# For MySQL 5.7+ only (disabled by default)
max_execution_time             = 30000 # Set a timeout limit for SELECT statements (value in milliseconds).
                                        # This option may be useful to address aggressive crawling on large sites,
                                        # but it can also cause issues (e.g. with backups). So use with extreme caution and test!
                                        # More info at: https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_execution_time

# === Buffer Settings ===
innodb_sort_buffer_size         = 6710864    # UPD
join_buffer_size                = 16M    # UPD
read_buffer_size                = 8M    # UPD
read_rnd_buffer_size            = 16M    # UPD
sort_buffer_size                = 16M    # UPD

# === Table Settings ===
# In systemd managed systems like Ubuntu 16.04+ or CentOS 7+, you need to perform an extra action for table_open_cache & open_files_limit
# to be overriden (also see comment next to open_files_limit).
# E.g. for MySQL 5.7, please check: https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html
# and for MariaDB check: https://mariadb.com/kb/en/library/systemd/
table_definition_cache          = 40000 # UPD
table_open_cache                = 40000 # UPD
open_files_limit                = 65535 # UPD - This can be 2x to 3x the table_open_cache value or match the system's
                                        # open files limit usually set in /etc/sysctl.conf or /etc/security/limits.conf
                                        # In systemd managed systems this limit must also be set in:
                                        # /etc/systemd/system/mysqld.service.d/override.conf (for MySQL 5.7+) and
                                        # /etc/systemd/system/mariadb.service.d/override.conf (for MariaDB)

max_heap_table_size             = 128M
tmp_table_size                  = 128M

# === Search Settings ===
ft_min_word_len                 = 3     # Minimum length of words to be indexed for search results

# === Logging ===
log_timestamps                  = SYSTEM
log_error                       = /data/mysql/logs/error.log
#log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /data/mysql/slow.log
min_examined_row_limit          = 100
log_bin                         = /data/mysql/logs/binlog.log
sync_binlog                     = 1
binlog_cache_size               = 4M
max_binlog_cache_size           = 2G
expire_logs_days                = 7
innodb_log_files_in_group       = 3

relay_log                       = /data/mysql/logs/relay.log
master_info_repository          = TABLE
relay_log_info_repository       = TABLE

gtid_mode                       = ON
enforce_gtid_consistency        = TRUE

log-slave-updates               = 1
relay_log_recovery              = 1
relay_log_purge                 = 1

lock_wait_timeout               = 3600
explicit_defaults_for_timestamp = 1
binlog_format                   = row
log_slave_updates               = ON
slave_skip_errors               = ddl_exist_errors
plugin_load                     = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
slave_rows_search_algorithms    = 'INDEX_SCAN,HASH_SCAN'

validate_password_policy        = 1
validate_password_length        = 12
validate_password_number_count  = 1
validate_password_mixed_case_count  = 1
validate_password_special_char_count    = 1
validate-password               = FORCE_PLUS_PERMANENT

[mysqldump]
# Variable reference
# For MySQL 5.7+:  https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
# For MariaDB:     https://mariadb.com/kb/en/library/mysqldump/
quick
quote_names
max_allowed_packet              = 1G
wait_timeout                    = 180

[client]
port	                        = 3306
socket	                        = /var/lib/mysql/mysql.sock
EOF

6、初始化数据

# mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/data
# cat /data/mysql/logs/error.log | grep password | echo ${passwd:0-12}
%0BfuSP76ai0
参数说明
--initialize对于密码复杂度进行定制;密码过期时间:180;给root@localhost用户设置临时密码

7、添加系统服务

# cat >/etc/systemd/system/mysqld.service <<EOF
[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
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF
# systemctl start mysqld
# systemctl enable mysqld
# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/etc/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 六 2023-02-11 14:21:43 CST; 27s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 2279 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─2279 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf

2月 11 14:21:43 localhost.localdomain systemd[1]: Stopped MySQL Server.
2月 11 14:21:43 localhost.localdomain systemd[1]: Started MySQL Server.
# ps -ef | grep mysql
mysql      2279      1  7 14:21 ?        00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root       2307   1830  0 14:21 pts/0    00:00:00 grep --color=auto mysql

二、管理数据库密码

::: warning
数据库密码不要明文登录
:::

1、初次设置管理员密码

上文已设置密码策略

参数策略
validate_password_policy= 1符合长度,且必须含有数字,小写或大写字母,特殊字符,0为禁用该策略
validate_password_length= 12设置密码的最小长度,默认8
validate_password_number_count= 1密码中至少拥有的数字的个数,默认1最小是0
validate_password_mixed_case_count= 1密码中至少同时拥有的小写和大写字母的数量,默认是1最小是0
validate_password_special_char_count= 1密码中至少拥有的特殊字符的个数,默认1最小是0
validate-password= FORCE_PLUS_PERMANENT服务器在启动时加载插件,并防止在服务器运行时删除插件

其他策略

参数策略
default_password_lifetime=180设置密码有效期为180天
validate_password.check_user_name =ON设置为ON的时候表示能将密码设置成当前用户名

请注意密码需求,示例:!AqKYBgY7rWE9F

# mysql_secure_installation
mysql_secure_installation作用
为root用户设置密码、删除匿名账号、取消root用户远程登录、删除test库和对test库的访问权限、刷新授权表使修改生效

2、更改管理员密码

# mysqladmin -uroot -p password
Enter password:          //输入旧密码,如果没有直接回车
New password:           //新密码
Confirm new password:           //重复输入
# mysql -uroot -p123456
mysql> exit
Bye

3、忘记管理员用户密码(仅限数据库本机操作)

# systemctl stop mysqld
# mysqld_safe --skip-grant-tables --skip-networking &            //启动数据库到维护模式
[1] 68049
# Logging to '/data/mysql/data/mysql-1.err'.
2020-08-27T16:21:06.730348Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/data

# mysql
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;            //查看密码
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> flush privileges;            //启用授权表
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to root@'localhost' identified by '1';            //密码更改为1
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select user,host,authentication_string from mysql.user;            //密码已改变
+---------------+-----------+-------------------------------------------+
| user          | host      | authentication_string                     |
+---------------+-----------+-------------------------------------------+
| root          | localhost | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys     | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

mysql> exit
Bye
# mysql -uroot -p1
mysql> 

三、用户管理

1、创建用户

mysql> create user zhangsan@'192.168.1.%' identified by '123456';

说明:8.0以前,可以自动创建用户并授权

mysql> grant all on *.* to lisi@'192.168.1.%' identified by '123456';

2、查询用户

mysql> select user,host from mysql.user;
+---------------+-------------+
| user          | host        |
+---------------+-------------+
| lisi          | 192.168.1.% |
| zhangsan      | 192.168.1.% |
| mysql.session | localhost   |
| mysql.sys     | localhost   |
| root          | localhost   |
+---------------+-------------+

3、修改用户密码

1、alter user命令

mysql> alter user zhangsan@'192.168.1.%' identified by '111111';

2、set password命令

mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('***');

3、mysqladmin

mysqladmin -uroot -p*** password 1234abcd  
格式:mysqladmin -u用户名 -p旧密码 password 新密码 

4、update更新user表

mysql> use mysql 
mysql> update user set authentication_string = PASSWORD('1234abcd') where user = 'root';

4、删除用户

mysql> drop user lisi@'192.168.1.%';
mysql> DELETE FROM mydb.user WHERE Host = '% AND User = 'admin';

5、重命名用户

mysql> rename user 'jack'@'%' to 'jim'@'%'; 

四、权限管理

1、用户权限验证过程

  • 第一阶段:服务器首先会检查你是否允许连接。因为创建用户的时候会加上主机限制,可以限制成本地、某个IP、某个IP段、以及任何地方等,只允许你从配置的指定地方登陆。
  • 第二阶段:如果你能连接,Mysql会检查你发出的每个请求,看你是否有足够的权限实施它。比如你要更新某个表、或者查询某个表,Mysql会查看你对哪个表或者某个列是否有权限。

2、用户授权原则

1、只授予能满足需要的最小权限,防止用户干坏事。比如用户只是需要查询,那就只给select权限就可以了,不要给用户赋予update、insert或者delete权限。

2、创建用户的时候限制用户的登录主机,一般是限制成指定IP或者内网IP段。

3、初始化数据库的时候删除没有密码的用户。安装完数据库的时候会自动创建一些用户,这些用户默认没有密码。

4、为每个用户设置满足密码复杂度的密码。

5、定期清理不需要的用户。回收权限或者删除用户。

3、权限说明

MySQL 5.7的权限列表

官方文档:https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

权限说明权限说明
ALL全局的所有权限或者表级的所有权限SELECT查询权限
INSERT允许插入数据行UPDATE更新权限
DELETE删除数据行CREATE允许创建数据库或表
DROP允许删除对象(权限大)FILE允许对服务器主机上文件的访问
RELOAD允许执行flush操作SHUTDOWN允许执行mysqladmin shutdown
PROCESS允许查看线程运行信息REFERENCES在一个表创建外键时,需要有父表的references权限
INDEX允许创建或删除索引ALTER允许执行alter table更改表结构
SHOW DATABASES允许执行show databases查看所有数据库名SUPER允许执行一系列数据库管理命令,包括kill强制关闭某个连接命令,change master to创建复制关系命令,以及create/alter/drop server等命令
CREATE TEMPORARY TABLES允许使用create temporary table创建临时表,创建临时表以后,该会话对该临时表拥有所有权限,不再做权限检查LOCK TABLES允许执行lock tables语句
EXECUTE允许执行存储过程或函数REPLICATION SLAVE复制用户所需权限,否则无法请求主库变更推送
REPLICATION CLIENT允许执行show master status,show slave status,show binary logsCREATE VIEW允许创建视图
SHOW VIEW允许执行show create viewCREATE ROUTINE允许创建存储过程或函数
ALTER ROUTINE允许对存储过程或函数执行alter或drop操作CREATE USER可以创建,修改和删除用户
EVENT允许create,alter,drop,see eventTRIGGER允许create,drop,execute或查看trigger,触发器的创建用户,在执行触发器时仍然要求该用户拥有trigger权限
CREATE TABLESPACE允许create,alter,drop tablespaces或log file group

4、授权命令格式

格式:grant 权限  on 作用目标  to 用户  identified by 密码 with grant option;

Mysql权限层级

  • user表:全局层级

    • 存储用户记录的表。关键字段有Host、User、Password。

    • 创建对所有表有SELECT操作权限的用户

GRANT SELECT ON *.* TO name@'1.1.1.1' IDENTIFIED BY 'pwd';
  • db表:数据库层级

    • 存储该用户对一个数据库所有的操作权限。关键字段有Host、User、Db。

    • 授予所有权限

    GRANT ALL ON mydb.* TO name@'1.1.1.1' IDENTIFIED BY 'pwd';
  • tables_priv表:表层级
    记录了对一个表的单独授权记录.关键字段有Host、User、Db、Table_name、Table_priv、Column_priv。

    • 当授权all在某张表的时候,Table_priv会有如下授权记录:

      • Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。
    • 单独授权表的某一列,会记录在此表的Column_priv里

      • GRANT UPDATE(age) ON mydb.user TO name@'1.1.1.1';
      • GRANT SELECT(birthday) ON mydb.user TO name@'1.1.1.1';
      • 此时会在另一张表columns_priv表中留下单独授权记录
  • columns_priv表:列层级
    记录对表的某一列的授权记录。关键字段Host、User、Db、Table_name、Column_name。

  • procs_priv表:子程序层级
    可以对存储过程和存储函数进行权限设置。关键字段Host、User、proc_priv

5、授权需求

1、创建一个管理员用户root,可以通过192.168.1.0网段,管理数据库

mysql> grant all on *.* to root@'192.168.1.%' identified by '123456' with grant option;

2、创建一个应用用户wangwu,可以通过192.168.1.0网段,对blog库下的所有表进行增删改查

mysql> grant SELECT,INSERT, UPDATE, DELETE on blog.* to wangwu@'192.168.1.%' identified by '123456';

6、查看用户权限

mysql> show grants for wangwu@'192.168.1.%';
+----------------------------------------------------------------------------+
| Grants for wangwu@192.168.1.%                                              |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wangwu'@'192.168.1.%'                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `blog`.* TO 'wangwu'@'192.168.1.%' |
+----------------------------------------------------------------------------+

7、回收权限

mysql> revoke delete on blog.* from 'wangwu'@'192.168.1.%';          //回收删除权限
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for wangwu@'192.168.1.%';
+--------------------------------------------------------------------+
| Grants for wangwu@192.168.1.%                                      |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wangwu'@'192.168.1.%'                       |
| GRANT SELECT, INSERT, UPDATE ON `blog`.* TO 'wangwu'@'192.168.1.%' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

五、启动管理

1、日常启动

# systemctl start mysqld
# systemctl stop mysqld

2、维护启动

例如忘记管理员密码↑

# mysqld_safe &
[2] 70379
# 2020-08-29T15:38:35.623046Z mysqld_safe Logging to '/data/mysql/data/mysql-1.err'.
2020-08-29T15:38:35.663314Z mysqld_safe A mysqld process already exists

[2]+  退出 1                mysqld_safe
# mysqladmin -uroot -p shutdown
2020-08-29T15:39:08.019176Z mysqld_safe mysqld from pid file /data/mysql/data/mysql-1.pid ended
[1]+  完成                  mysqld_safe --skip-grant-tables --skip-networking

六、连接管理

1、本地连接

# mysql -uroot -p

或者

# mysql -uroot -p -S /var/lib/mysql/mysql.sock

2、远程连接

# mysql -uroot -p123456
mysql> grant all on *.* to root@'192.168.1.%' identified by '123456';
# mysql -uroot -p -h 192.168.1.11 -P3306

七、多实例管理

1、准备数据目录

# mkdir -p /data/330{7,8,9}/data

2、准备配置文件

# cat > /data/3307/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
log_error=/data/3307/mysql.log
port=3307
server_id=7
log_bin=/data/3307/mysql-bin
EOF
# cat > /data/3308/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
socket=/data/3308/mysql.sock
log_error=/data/3308/mysql.log
port=3308
server_id=8
log_bin=/data/3308/mysql-bin
EOF
# cat > /data/3309/my.cnf <<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
log_error=/data/3309/mysql.log
port=3309
server_id=9
log_bin=/data/3309/mysql-bin
EOF

3、初始化数据

# mv /etc/my.cnf /etc/my.cnf.bak
# mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
# mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql
# mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql

4、systemd管理多实例

# cd /etc/systemd/system
# cp mysqld.service mysqld3307.service
# cp mysqld.service mysqld3308.service
# cp mysqld.service mysqld3309.service
# vim mysqld3307.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3307/my.cnf
# vim mysqld3308.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3308/my.cnf
# vim mysqld3309.service
ExecStart=/usr/local/mysql/bin/mysqld  --defaults-file=/data/3309/my.cnf

5、授权并启动

# chown -R mysql.mysql /data/*
# systemctl start mysqld3307.service
# systemctl start mysqld3308.service
# systemctl start mysqld3309.service
# netstat -anpt | grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      70847/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      71447/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      71487/mysqld        
tcp6       0      0 :::3309                 :::*                    LISTEN      71527/mysqld        
# mysql -S /data/3307/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
# mysql -S /data/3308/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
# mysql -S /data/3309/mysql.sock -e "select @@server_id"
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
# mysql -S /data/3307/mysql.sock
mysql> 
0

评论区