目录
创建mysql用户及组
root@localhost:~$ groupadd mysql
root@localhost:~$ useradd -g mysql mysql
root@localhost:~$ passwd mysql
Changing password for user mysql.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
root@localhost:~$ su - mysql
mysql@localhost:~$ pwd
/home/mysql
mysql@localhost:~$
多版本多实例
以tar包方式为例,首先上传MySQL的压缩包格式安装包
mysql@localhost:~$ ls
mysql-8.0.22-el7-x86_64.tar.gz mysql-5.7.26-el7-x86_64.tar.gz
mysql@localhost:~$ tar -xvf mysql-8.0.22-el7-x86_64.tar.gz
mysql@localhost:~$ tar -xvf mysql-5.7.26-el7-x86_64.tar.gz
mysql@localhost:~$ mv mysql-5.7.26-el7-x86_64 mysql-5.7.26
mysql@localhost:~$ mv mysql-5.7.26-el7-x86_64
mysql@localhost:~$ mv mysql-8.0.22-el7-x86_64 mysql-8.0.22
mysql@localhost:~$ ls
mysql-5.7.26 mysql-8.0.22
mysql@localhost:~$ mkdir -p /home/mysql/mysql-8.0.22/data
mysql@localhost:~$ mkdir -p /home/mysql/mysql-5.7.26/data
重复以下步骤分别配置MySQL8.0和MySQL5.7
配置my.cnf
mysql@localhost:~$ vi /home/mysql/mysql-{8.0.22}/my.cnf
设置如下参数
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
# 命令自动补全
no-auto-rehash
[mysqld]
#serverID,统一集群或同时启动的实例不能重复
server_id=2
#设置3388端口
port = 3388
# 设置mysql的安装目录
basedir=/home/mysql/mysql-{8.0.22}
# 设置mysql数据库的数据的存放目录
datadir=/home/mysql/mysql-{8.0.22}/data
# 错误日志目录
log-error=/home/mysql/mysql-{8.0.22}/log.err
# 单表单文件模式
innodb_file_per_table=1
# 表名区分大小写,=1时新建表表名均为小写
lower_case_table_names=1
# 禁用dns解析
skip-name-resolve
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为utf8字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_allowed_packet=16M
socket=/home/mysql/mysql-{8.0.22}/mysql3388.sock
#慢日志位置
slow_query_log_file=/home/mysql/mysql-{8.0.22}/slow_query.log
#慢日志时间
long_query_time=1
#开启慢日志
slow_query_log=TRUE
[mysqld_safe]
open-files-limit = 8192
pid-file=/home/mysql/mysql-{8.0.22}/mysqld.pid
初始化数据库
mysql@localhost:~$ /home/mysql/mysql-8.0.22/bin/mysqld --defaults-file=/home/mysql/mysql-8.0.22/my.cnf --initialize
mysql@localhost:~$
获取root用户临时密码
mysql@localhost:~$ vi /home/mysql/mysql-8.0.22/log.err
2021-01-04T04:32:46.833374Z 0 [Warning] [MY-010139] [Server] Changed limits: max_open_files: 1024 (requested 9010)
2021-01-04T04:32:46.833381Z 0 [Warning] [MY-010141] [Server] Changed limits: max_connections: 214 (requested 1000)
2021-01-04T04:32:46.833384Z 0 [Warning] [MY-010142] [Server] Changed limits: table_open_cache: 400 (requested 4000)
2021-01-04T04:32:46.833609Z 0 [System] [MY-013169] [Server] /home/mysql/mysql-8.0.22/bin/mysqld (mysqld 8.0.22) initializing of server in progress as process 18982
2021-01-04T04:32:46.837506Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-01-04T04:32:47.147928Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-01-04T04:32:47.828680Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 5_(YLYnb7%y/
启动数据库
mysql@localhost:~$ /home/mysql/mysql-8.0.22/bin/mysqld_safe --defaults-file=/home/mysql/mysql-8.0.22/my.cnf
修改root用户密码
mysql@localhost:~/mysql-8.0.22/bin$ ./mysql -uroot -p"5_(YLYnb7%y/" -S /home/mysql/mysql-8.0.22/mysql3388.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 10
Server version: 8.0.22
Copyright (c) 2000, 2020, 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> alter user 'root'@'localhost' identified with mysql_native_password by 'schina';
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set Host='%' where User='root';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql>
关闭数据库
mysql@localhost:~/mysql-8.0.22/bin$ /home/mysql/mysql-8.0.22/bin/mysqladmin -uroot -p -S /home/mysql/mysql-8.0.22/mysql3388.sock shutdown
Enter password:
mysql@localhost:~/mysql-8.0.22/bin$
设置启停命令别名以方便启停
编辑mysql用户的.bash_profile
[mysql@orcl11g ~]$ su - mysql
密码:
上一次登录:六 2月 20 17:06:04 CST 2021pts/1 上
[mysql@orcl11g ~]$ cd
[mysql@orcl11g ~]$ vi .bash_profile
添加如下命令【以实际路径为准】
alias stopmysql57='/home/mysql/mysql-5.7.26/bin/mysqladmin -uroot -p -S /home/mysql/mysql-5.7.26/mysql3377.sock shutdown'
alias startmysql57='/home/mysql/mysql-5.7.26/bin/mysqld_safe --defaults-file=/home/mysql/mysql-5.7.26/my.cnf --user=mysql &'
alias stopmysql8='/home/mysql/mysql-8.0.22/bin/mysqladmin -uroot -p -S /home/mysql/mysql-8.0.22/mysql3388.sock shutdown'
alias startmysql8='/home/mysql/mysql-8.0.22/bin/mysqld_safe --defaults-file=/home/mysql/mysql-8.0.22/my.cnf --user=mysql &'
使修改生效
[mysql@orcl11g ~]$ source .bash_profile
[mysql@orcl11g ~]$
测试命令
[mysql@orcl11g ~]$ stopmysql57
Enter password:
2021-02-20T13:37:46.921296Z mysqld_safe mysqld from pid file /home/mysql/mysql57/mysqld.pid ended
[mysql@orcl11g ~]$ stopmysql8
Enter password:
2021-02-20T13:37:54.040713Z mysqld_safe mysqld from pid file /home/mysql/mysql8/mysqld.pid ended
[mysql@orcl11g ~]$ startmysql57
[1] 29560
[mysql@orcl11g ~]$ 2021-02-20T13:38:00.385876Z mysqld_safe Logging to '/home/mysql/mysql57/log.err'.
2021-02-20T13:38:00.421868Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql57/data
[mysql@orcl11g ~]$ startmysql8
[2] 29892
[mysql@orcl11g ~]$ 2021-02-20T13:38:04.650851Z mysqld_safe Logging to '/home/mysql/mysql8/log.err'.
2021-02-20T13:38:04.688043Z mysqld_safe Starting mysqld daemon with databases from /home/mysql/mysql8/data
[mysql@orcl11g ~]$
单版本多实例
准备工作
mysql@localhost:~$ ls
mysql-5.7.26 mysql-8.0.22
mysql@localhost:~$ cp mysql-5.7.26 mysql-multi
mysql@localhost:~$ ls
mysql-5.7.26 mysql-8.0.22 mysql-multi
mysql@localhost:~$ cd mysql-multi/data/
mysql@localhost:~/mysql-multi/data$ mkdir inst1 inst2
mysql@localhost:~/mysql-multi/data$ touch log_inst1.err log_inst2.err multi.log
mysql@localhost:~/mysql-multi/data$ ls
inst1 inst2 log_inst1.err log_inst2.err multi.log
mysql@localhost:~/mysql-multi/data$ cd ../bin
mysql@localhost:~/mysql-multi/bin$
mysql@localhost:~/mysql-multi/bin$ su - root
root@localhost:~$ cp /home/mysql/mysql-multi/bin/my_print_defaults /usr/bin/
root@localhost:~$ cp /home/mysql/mysql-multi/bin/mysqld_multi /usr/bin/
修改my.cnf
[mysqld_multi]
mysqld = /home/mysql/mysql-multi/bin/mysqld_safe
mysqladmin = /home/mysql/mysql-multi/bin/mysqladmin
log = /home/mysql/mysql-multi/data/multi.log
user = root
password = schina
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
# 命令自动补全
no-auto-rehash
[mysqld1]
#serverID,统一集群或同时启动的实例不能重复
server_id=1
#设置3355端口
port = 3355
# 设置mysql的安装目录
basedir=/home/mysql/mysql-multi
# 设置mysql数据库的数据的存放目录
datadir=/home/mysql/mysql-multi/data/inst1
# 错误日志目录
log-error=/home/mysql/mysql-multi/data/log_inst1.err
pid-file=/home/mysql/mysql-multi/data/inst1/mysqld.pid
# 单表单文件模式
innodb_file_per_table=1
# 表名区分大小写,=1时新建表表名均为小写
lower_case_table_names=1
# 禁用dns解析
skip-name-resolve
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为utf8字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_allowed_packet=16M
socket=/tmp/inst1.sock
#慢日志位置
slow_query_log_file=/home/mysql/mysql-multi/data/inst1/slow_query.log
#慢日志时间
long_query_time=1
#开启慢日志
slow_query_log=TRUE
[mysqld2]
#serverID,统一集群或同时启动的实例不能重复
server_id=2
#设置3366端口
port = 3366
# 设置mysql的安装目录
basedir=/home/mysql/mysql-multi
# 设置mysql数据库的数据的存放目录
datadir=/home/mysql/mysql-multi/data/inst2
# 错误日志目录
log-error=/home/mysql/mysql-multi/data/log_inst2.err
pid-file=/home/mysql/mysql-multi/data/inst2/mysqld.pid
# 单表单文件模式
innodb_file_per_table=1
# 表名区分大小写,=1时新建表表名均为小写
lower_case_table_names=1
# 禁用dns解析
skip-name-resolve
# 允许最大连接数
max_connections=1000
# 服务端使用的字符集默认为utf8字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
max_allowed_packet=16M
socket=/tmp/inst2.sock
#慢日志位置
slow_query_log_file=/home/mysql/mysql-multi/data/inst2/slow_query.log
#慢日志时间
long_query_time=1
#开启慢日志
slow_query_log=TRUE
初始化数据库
root@localhost:~$ /home/mysql/mysql-multi/bin/mysqld --basedir=/home/mysql/mysql-multi --datadir=/home/mysql/mysql-multi/data/inst1 --user=mysql --initialize
2021-01-04T05:54:22.817202Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-04T05:54:23.106954Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-04T05:54:23.176126Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-04T05:54:23.240367Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 4b967d03-4e51-11eb-beb9-000c296899bb.
2021-01-04T05:54:23.241424Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-04T05:54:23.245024Z 1 [Note] A temporary password is generated for root@localhost: B/gTydh<s8Q4
root@localhost:~$
root@localhost:~$ /home/mysql/mysql-multi/bin/mysqld --basedir=/home/mysql/mysql-multi --datadir=/home/mysql/mysql-multi/data/inst2 --user=mysql --initialize
2021-01-04T05:55:53.327748Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-01-04T05:55:53.469315Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-01-04T05:55:53.494994Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-01-04T05:55:53.502785Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 816371fa-4e51-11eb-832d-000c296899bb.
2021-01-04T05:55:53.503740Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-01-04T05:55:53.504420Z 1 [Note] A temporary password is generated for root@localhost: UNn<k7t,z?r=
root@localhost:~$
实例操作
--启动实例1
/home/mysql/mysql-multi/bin/mysqld_multi --defaults-extra-file=/home/mysql/mysql-multi/my.cnf start/stop 1
--启动实例1和2
/home/mysql/mysql-multi/bin/mysqld_multi --defaults-extra-file=/home/mysql/mysql-multi/my.cnf start/stop 1,2
--启动实例1到2
/home/mysql/mysql-multi/bin/mysqld_multi --defaults-extra-file=/home/mysql/mysql-multi/my.cnf start/stop 1-2
--查看实例运行情况
mysql@localhost:~$ /home/mysql/mysql-multi/bin/mysqld_multi --defaults-extra-file=/home/mysql/mysql-multi/my.cnf report
mysqld_multi log file version 2.16; run: Mon Jan 4 07:03:31 2021
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is not running
mysql@localhost:~$
问题解决
1. mysqld_multi启动数据库时报错:“Can't find command 'my_print_defaults'”
2. mysqld_multi停止数据库时报错:“'Access denied for user 'root'@'localhost'”