mysql运维之-单机多实例(多进程的方式)
本文实践的是多进程的方式做MYSQL单机多实例，一个实例一个进程，各实例有自己的配置文件，总结起来有以下几点：
- 端口port分开
- 配置文件分开
- socket分开
- 日志分开
- 多serverid
- 都可以使用mysql用户
1.源代码编译安装&配置MYSQL环境变量（略）
2.配置多实例
MYSQL版本：5.7.28
2.1.创建多个数据目录
mkdir /data/{3307,3306,3308} -p
2.2.准备多个配置文件
root@lzr-K401LB:/etc/systemd/system# cat /data/3306/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/data/3306/data port=3306 socket=/data/3306/mysql.sock log-error=/data/3306/data/mysql.err log-bin=/data/3306/data/mysql-bin server_id=6 explicit_defaults_for_timestamp=true #skip-grant-tables root@lzr-K401LB:/etc/systemd/system#
root@lzr-K401LB:/etc/systemd/system# cat /data/3307/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/data/3307/data port=3307 socket=/data/3307/mysql.sock log-error=/data/3307/data/mysql.err log-bin=/data/3307/data/mysql-bin server_id=7 explicit_defaults_for_timestamp=true #skip-grant-tables root@lzr-K401LB:/etc/systemd/system#
root@lzr-K401LB:/etc/systemd/system# cat /data/3308/my.cnf [mysqld] basedir=/usr/local/mysql datadir=/data/3308/data port=3308 socket=/data/3308/mysql.sock log-error=/data/3308/data/mysql.err log-bin=/data/3308/data/mysql-bin server_id=8 explicit_defaults_for_timestamp=true #skip-grant-tables root@lzr-K401LB:/etc/systemd/system#
2.3 初始化多套数据目录
cd /usr/mysql/bin
./mysql_install_db --defaults-file=/data/3306/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3306/data
./mysql_install_db --defaults-file=/data/3307/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3307/data
./mysql_install_db --defaults-file=/data/3308/my.cnf --user=mysql --basedir=/usr/local/mysql --datadir=/data/3308/data
2.4 文件权限
chown -R mysql.mysql /data
2.4 启动&关闭MYSQL多实例
cd /usr/mysql/bin
启动
./mysqld_safe --defaults-file=/data/3307/my.cnf &
./mysqld_safe --defaults-file=/data/3306/my.cnf &
./mysqld_safe --defaults-file=/data/3308/my.cnf &
关闭(无密码)
./mysqladmin -S /data/3306/mysql.sock shutdown
./mysqladmin -S /data/3307/mysql.sock shutdown
./mysqladmin -S /data/3308/mysql.sock shutdown
关闭(有密码)
./mysqladmin -uroot -p123456 -S /data/3306/mysql.sock shutdown
./mysqladmin -uroot -p123456 -S /data/3307/mysql.sock shutdown
./mysqladmin -uroot -p123456 -S /data/3308/mysql.sock shutdown
2.5 查看端口占用
root@lzr-K401LB:/usr/local/mysql/bin# netstat -ln | grep 330 tcp6 0 0 :::3306 :::* LISTEN tcp6 0 0 :::3307 :::* LISTEN tcp6 0 0 :::3308 :::* LISTEN unix 2 [ ACC ] STREAM LISTENING 711534 /data/3306/mysql.sock unix 2 [ ACC ] STREAM LISTENING 713104 /data/3307/mysql.sock unix 2 [ ACC ] STREAM LISTENING 713814 /data/3308/mysql.sock root@lzr-K401LB:/usr/local/mysql/bin#
2.6 设置多实例密码
cd /usr/mysql/bin
./mysqladmin -uroot -S /data/3306/mysql.sock password '123456'
./mysqladmin -uroot -S /data/3307/mysql.sock password '123456'
./mysqladmin -uroot -S /data/3308/mysql.sock password '123456'
2.7 验证库连接
root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3306/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28-log Source distribution Copyright (c) 2000, 2019, 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 6 | +---------------+-------+ 1 row in set (0.01 sec) mysql>
root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3307/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28-log Source distribution Copyright (c) 2000, 2019, 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 7 | +---------------+-------+ 1 row in set (0.01 sec) mysql>
root@lzr-K401LB:/usr/local/mysql/bin# ./mysql -uroot -p123456 -S /data/3308/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.28-log Source distribution Copyright (c) 2000, 2019, 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. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show variables like 'server_id'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | server_id | 8 | +---------------+-------+ 1 row in set (0.00 sec) mysql>
2.8 忘记密码
修改my.cnf
[mysqld]
skip-grant-tables
启动mysql
修改密码
flush privileges;
alter user 'root'@'localhost' identified with mysql_native_password by '123456';
flush privileges;
2.9 配置后台服务+开机启动
配置3306-3308后台服务
root@lzr-K401LB:/etc/systemd/system# cat /etc/systemd/system/mysqld3306.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 ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf LimitNOFILE = 5000
添加执行权限
root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3306.service root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3307.service root@lzr-K401LB:/etc/systemd/system# chmod a+x mysqld3308.service root@lzr-K401LB:/etc/systemd/system#
配置开机启动
root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3306.service Created symlink /etc/systemd/system/multi-user.target.wants/mysqld3306.service → /etc/systemd/system/mysqld3306.service. root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3307.service Created symlink /etc/systemd/system/multi-user.target.wants/mysqld3307.service → /etc/systemd/system/mysqld3307.service. root@lzr-K401LB:/etc/systemd/system# systemctl enable mysqld3308.service Created symlink /etc/systemd/system/multi-user.target.wants/mysqld3308.service → /etc/systemd/system/mysqld3308.service.
启动服务
root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3306.service root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3307.service root@lzr-K401LB:/etc/systemd/system# systemctl start mysqld3308.service root@lzr-K401LB:/etc/systemd/system# ps aux|grep mysql mysql 14853 0.0 0.0 4624 1792 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3306/my.cnf mysql 15019 1.6 2.2 1321440 182324 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3306/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3306/mysql.sock --port=3306 mysql 15051 0.0 0.0 4624 1696 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3307/my.cnf mysql 15217 2.0 2.2 1321440 181872 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf --basedir=/usr/local/mysql --datadir=/data/3307/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3307/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3307/mysql.sock --port=3307 mysql 15250 0.0 0.0 4624 1760 ? Ss 13:24 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.cnf mysql 15416 2.3 2.2 1321440 182032 ? Sl 13:24 0:00 /usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf --basedir=/usr/local/mysql --datadir=/data/3308/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/data/3308/data/mysql.err --pid-file=lzr-K401LB.pid --socket=/data/3308/mysql.sock --port=3308 root 15455 0.0 0.0 16172 1004 pts/2 S+ 13:24 0:00 grep --color=auto mysql root@lzr-K401LB:/etc/systemd/system#
