Centos7安装MySQL8.0
请到这个地址看:https://www.cnblogs.com/kevingrace/p/10482469.html Centos7安装MySQL8.0 - 操作手册
一、yum安装方式:
- 卸载之前版本的mysql:
1 2 | [root@DB-node01 ~] # for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done [root@DB-node01 ~] # rm -rf /var/lib/mysql && rm -rf /etc/my.cnf |
下载地址: https:
//pan
.baidu.com
/s/1QzYaSnzAQeTqAmk8FE9doA
提取密码: 2maw
- 安装、启动、第一次登录
1 2 3 4 5 6 7 | [root@DB-node01 ~] # yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm #安装yum源文件 [root@DB-node01 ~] # yum install mysql-community-server [root@DB-node01 ~] # systemctl start mysqld [root@DB-node01 ~] # systemctl enable mysqld [root@DB-node01 ~] # grep 'temporary password' /var/log/mysqld.log 2019-03-06T01:53:19.897262Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: e53xDalx.*dE [root@DB-node01 ~] # mysql -p'e53xDalx.*dE' |
- 修改密码、密码复杂性要求
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements mysql> set global validate_password.policy=0; --Mysql5.7有点不一样了. Query OK, 0 rows affected (0.00 sec) mysql> set global validate_password.length=1; Query OK, 0 rows affected (0.00 sec) mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ; --接着再修改密码 Query OK, 0 rows affected (0.05 sec) mysql> flush privileges ; Query OK, 0 rows affected (0.03 sec) |
- 查看基本信息:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> select version(); --查看版本 mysql> show global variables like 'port' ; --查看服务端口 mysql> select host, user ,authentication_string from mysql. user ; --查看mysql连接的授权信息 + -----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | + -----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005${7J0=4Dc7Jym8eI/FU4jimKWFvkD9XmoAkF1ca5.Un0bc6zgmPtU.0 | + -----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec) |
#######
mysql5.7之后的数据库里mysql.user表里已经没有password这个字段了,password字段改成了authentication_string。
1 2 | mysql> select host, user , password from mysql. user ; --这是mysql5.6及以下版本的查看命令, ERROR 1054 (42S22): Unknown column 'password' in 'field list' |
============================= mysql8.0修改用户密码命令 ================================
mysql> use mysql;
mysql> ALTER USER
'root'
@
'localhost'
IDENTIFIED BY
'123456'
;
mysql> flush privileges;
二、二进制安装
下载Mysql8.0.12地址: https:
//pan
.baidu.com
/s/1LyXrkrCPP7QKLrWYbLRBlw
提取密码: emmf
删除系统默认或之前可能安装的其他版本的mysql
1 2 | [root@mysql8-node ~] # for i in $(rpm -qa|grep mysql);do rpm -e $i --nodeps;done [root@mysql8-node ~] # rm -rf /var/lib/mysql && rm -rf /etc/my.cnf |
安装依赖包,创建用户、解压安装包
1 2 3 4 5 6 7 8 | [root@mysql8-node ~] # yum -y install libaio [root@mysql8-node ~] # yum -y install net-tools [root@mysql8-node ~] # groupadd mysql [root@mysql8-node ~] # useradd -g mysql mysql [root@mysql8-node ~] # cd /usr/src/ [root@mysql-node src] # tar -zvxf mysql8.0.12_bin_centos7.tar.gz [root@mysql-node src] # mv mysql /usr/local/ [root@mysql-node src] # chown -R mysql.mysql /usr/local/mysql |
- 将命令加入环境变量
1 2 3 4 5 | [root@mysql-node src] # vim /home/mysql/.bash_profile export PATH= /usr/local/mysql/bin : /usr/local/mysql/lib :$PATH [root@mysql-node src] # source /home/mysql/.bash_profile [root@mysql-node src] # echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile [root@mysql-node src] # source /etc/profile |
创建数据目录
1 2 | [root@mysql-node src] # mkdir -p /data/mysql/{data,log,binlog,conf,tmp} [root@mysql-node src] # chown -R mysql.mysql /data/mysql |
配置mysql
[root@mysql-node src]
# su - mysql
[mysql@mysql-node ~]$ vim
/data/mysql/conf/my
.cnf
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | [mysqld] lower_case_table_names = 1 user = mysql server_id = 1 port = 3306 default- time -zone = '+08:00' enforce_gtid_consistency = ON gtid_mode = ON binlog_checksum = none default_authentication_plugin = mysql_native_password datadir = /data/mysql/data pid- file = /data/mysql/tmp/mysqld .pid socket = /data/mysql/tmp/mysqld .sock tmpdir = /data/mysql/tmp/ skip-name-resolve = ON open_files_limit = 65535 table_open_cache = 2000 ################# innodb ######################## innodb_data_home_dir = /data/mysql/data innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend innodb_buffer_pool_size = 12000M innodb_flush_log_at_trx_commit = 1 innodb_io_capacity = 600 innodb_lock_wait_timeout = 120 innodb_log_buffer_size = 8M innodb_log_file_size = 200M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 85 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 32 innodb_file_per_table innodb_rollback_on_timeout innodb_undo_directory = /data/mysql/data innodb_log_group_home_dir = /data/mysql/data ################### session ########################### join_buffer_size = 8M key_buffer_size = 256M bulk_insert_buffer_size = 8M max_heap_table_size = 96M tmp_table_size = 96M read_buffer_size = 8M sort_buffer_size = 2M max_allowed_packet = 64M read_rnd_buffer_size = 32M ############ log set ################### log-error = /data/mysql/log/mysqld .err log-bin = /data/mysql/binlog/binlog log_bin_index = /data/mysql/binlog/binlog .index max_binlog_size = 500M slow_query_log_file = /data/mysql/log/slow .log slow_query_log = 1 long_query_time = 10 log_queries_not_using_indexes = ON log_throttle_queries_not_using_indexes = 10 log_slow_admin_statements = ON log_output = FILE,TABLE master_info_file = /data/mysql/binlog/master .info |
- 初始化、启动、登录、重置初始密码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 | 6) 初始化 (稍等一会儿, 可以到 /data/mysql/log/mysqld .err日子里查看初始化过程, 看看有没有error信息) [mysql@mysql-node ~]$ mysqld --defaults- file = /data/mysql/conf/my .cnf --initialize-insecure --user=mysql 7) 启动mysqld [mysql@mysql-node ~]$ mysqld_safe --defaults- file = /data/mysql/conf/my .cnf & [mysql@mysql-node ~]$ lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE /OFF NODE NAME mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql (LISTEN) 8) 登录mysql, 重置密码 本地首次使用sock文件登录mysql是不需要密码的 [mysql@mysql-node ~] # mysql -S /data/mysql/tmp/mysqld.sock ............. mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ; Query OK, 0 rows affected (0.07 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-----------+------------------+------------------------------------------------------------------------+ 4 rows in set (0.00 sec) 退出, 此时密码重置后, 就不能使用sock文件无密码登录了 [root@mysql-node ~] # mysql -S /data/mysql/tmp/mysqld.sock ERROR 1045 (28000): Access denied for user 'root' @ 'localhost' (using password: NO) [root@mysql-node ~] # mysql -p123456 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2002 (HY000): Can 't connect to local MySQL server through socket ' /tmp/mysql .sock' (2) 做sock文件的软链接 [root@mysql-node ~] # ln -s /data/mysql/tmp/mysqld.sock /tmp/mysql.sock [root@mysql-node ~] # mysql -p123456 或者 [root@mysql-node ~] # mysql -uroot -S /data/mysql/tmp/mysqld.sock -p123456 ............. mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.12 | +-----------+ 1 row in set (0.00 sec) |
- 授权
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 | #授予用户权限. 必须先要创建用户, 才能授权!! (创建用户时要带@并指定地址, 则 grant 授权时的地址就是这个@后面指定的!, 否则 grant 授权就会报错!) mysql> create user 'kevin' @ '%' identified by '123456' ; Query OK, 0 rows affected (0.11 sec) mysql> grant all privileges on *.* to 'kevin' @ '%' with grant option ; Query OK, 0 rows affected (0.21 sec) mysql> select host, user ,authentication_string from mysql. user ; + -----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | + -----------+------------------+------------------------------------------------------------------------+ | % | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | + -----------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> update mysql. user set host= '172.16.60.%' where user = "kevin" ; Query OK, 1 row affected (0.16 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges ; Query OK, 0 rows affected (0.05 sec) mysql> select host, user ,authentication_string from mysql. user ; + -------------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | + -------------+------------------+------------------------------------------------------------------------+ | 172.16.60.% | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | + -------------+------------------+------------------------------------------------------------------------+ 5 rows in set (0.00 sec) mysql> create user 'bobo' @ '172.16.60.%' identified by '123456' ; Query OK, 0 rows affected (0.09 sec) mysql> grant all privileges on *.* to 'bobo' @ '172.16.60.%' ; Query OK, 0 rows affected (0.17 sec) mysql> flush privileges ; Query OK, 0 rows affected (0.04 sec) mysql> select host, user ,authentication_string from mysql. user ; + -------------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | + -------------+------------------+------------------------------------------------------------------------+ | 172.16.60.% | bobo | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | 172.16.60.% | kevin | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | + -------------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec) mysql> show grants for kevin@ '172.16.60.%' ; + -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for kevin@172.16.60.% | + -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT , INSERT , UPDATE , DELETE , CREATE , DROP , RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES , INDEX , ALTER , SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE , REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW , SHOW VIEW , CREATE ROUTINE, ALTER ROUTINE, CREATE USER , EVENT, TRIGGER , CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `kevin`@`172.16.60.%` WITH GRANT OPTION | + -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
MySQL单机多实例安装配置
上面已经有了一个3306端口的MySQL实例, 现在需要再起来两个实例, 分别为3307, 3308. 操作如下:
- 创建数据目录、生成和修改mysql配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | #创建实例的数据目录 [root@mysql-node ~] # mkdir -p /data/mysql3307/{data,log,binlog,conf,tmp} [root@mysql-node ~] # mkdir -p /data/mysql3308/{data,log,binlog,conf,tmp} [root@mysql-node ~] # chown -R mysql.mysql /data/mysql3307 [root@mysql-node ~] # chown -R mysql.mysql /data/mysql3308 #配置mysql [root@mysql-node ~] # cp -r /data/mysql/conf/my.cnf /data/mysql3307/conf/ [root@mysql-node ~] # cp -r /data/mysql/conf/my.cnf /data/mysql3308/conf/ [root@mysql-node ~] # sed -i 's#/data/mysql/#/data/mysql3307/#g' /data/mysql3307/conf/my.cnf [root@mysql-node ~] # sed -i 's#/data/mysql/#/data/mysql3308/#g' /data/mysql3308/conf/my.cnf [root@mysql-node ~] # sed -i 's/3306/3307/g' /data/mysql3307/conf/my.cnf [root@mysql-node ~] # sed -i 's/3306/3308/g' /data/mysql3308/conf/my.cnf [root@mysql-node ~] # chown -R mysql.mysql /data/mysql* |
- 初始化实例、启动mysql、设置密码
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | 进行初始化两个实例 [root@mysql-node ~] # mysqld --defaults-file=/data/mysql3307/conf/my.cnf --initialize-insecure --user=mysql [root@mysql-node ~] # mysqld --defaults-file=/data/mysql3308/conf/my.cnf --initialize-insecure --user=mysql 接着启动mysqld [root@mysql-node ~] # mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf & [root@mysql-node ~] # mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf & [root@mysql-node ~] # ps -ef|grep mysql mysql 23996 1 0 14:37 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults- file = /data/mysql/conf/my .cnf mysql 24743 23996 0 14:38 ? 00:00:17 /usr/local/mysql/bin/mysqld --defaults- file = /data/mysql/conf/my .cnf --basedir= /usr/local/mysql --datadir= /data/mysql/data --plugin- dir = /usr/local/mysql/lib/plugin --log-error= /data/mysql/log/mysqld .err -- open -files-limit=65535 --pid- file = /data/mysql/tmp/mysqld .pid --socket= /data/mysql/tmp/mysqld .sock --port=3306 root 30473 23727 0 15:33 pts /0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults- file = /data/mysql3307/conf/my .cnf mysql 31191 30473 17 15:33 pts /0 00:00:02 /usr/local/mysql/bin/mysqld --defaults- file = /data/mysql3307/conf/my .cnf --basedir= /usr/local/mysql --datadir= /data/mysql3307/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql --log-error= /data/mysql3307/log/mysqld .err -- open -files-limit=65535 --pid- file = /data/mysql3307/tmp/mysqld .pid --socket= /data/mysql3307/tmp/mysqld .sock --port=3307 root 31254 23727 0 15:33 pts /0 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults- file = /data/mysql3308/conf/my .cnf mysql 31977 31254 39 15:33 pts /0 00:00:02 /usr/local/mysql/bin/mysqld --defaults- file = /data/mysql3308/conf/my .cnf --basedir= /usr/local/mysql --datadir= /data/mysql3308/data --plugin- dir = /usr/local/mysql/lib/plugin --user=mysql --log-error= /data/mysql3308/log/mysqld .err -- open -files-limit=65535 --pid- file = /data/mysql3308/tmp/mysqld .pid --socket= /data/mysql3308/tmp/mysqld .sock --port=3308 root 32044 23727 0 15:34 pts /0 00:00:00 grep --color=auto mysql [root@mysql-node ~] # lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE /OFF NODE NAME mysqld 31191 mysql 22u IPv6 23144844 0t0 TCP *:opsession-prxy (LISTEN) [root@mysql-node ~] # lsof -i:3308 COMMAND PID USER FD TYPE DEVICE SIZE /OFF NODE NAME mysqld 31977 mysql 22u IPv6 23145727 0t0 TCP *:tns-server (LISTEN) [root@mysql-node ~] # lsof -i:3306 COMMAND PID USER FD TYPE DEVICE SIZE /OFF NODE NAME mysqld 24743 mysql 23u IPv6 23132988 0t0 TCP *:mysql (LISTEN) 登录3307端口实例, 并设置密码 [root@mysql-node ~] # mysql -S /data/mysql3307/tmp/mysqld.sock ............ mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ; Query OK, 0 rows affected (0.11 sec) mysql> flush privileges; Query OK, 0 rows affected (0.11 sec) 退出, 使用新密码登录 [root@mysql-node ~] # mysql -uroot -S /data/mysql3307/tmp/mysqld.sock -p123456 ............. mysql> 同理, 登录3308端口实例, 并设置密码 [root@mysql-node ~] # mysql -S /data/mysql3308/tmp/mysqld.sock ........... mysql> ALTER USER 'root' @ 'localhost' IDENTIFIED BY '123456' ; Query OK, 0 rows affected (0.13 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec) 退出, 使用新密码登录 [root@mysql-node ~] # mysql -uroot -S /data/mysql3308/tmp/mysqld.sock -p123456 .................... mysql> ========================================== 3306, 3307, 3308三个端口实例的启动命令分别为: mysqld_safe --defaults- file = /data/mysql/conf/my .cnf & mysqld_safe --defaults- file = /data/mysql3307/conf/my .cnf & mysqld_safe --defaults- file = /data/mysql3308/conf/my .cnf & 登录命令分别为: mysql -uroot -S /data/mysql/tmp/mysqld .sock -p123456 mysql -uroot -S /data/mysql3307/tmp/mysqld .sock -p123456 mysql -uroot -S /data/mysql3308/tmp/mysqld .sock -p123456 |
编写mysql多端口实例的登录脚本
1 | 上面的多端口实例配置之后, 查看一个登录脚本:myin (注意将脚本中的密码改为自己的密码) |
- 编写登录脚本并授权(此处故障解决方法在下方)
1 2 3 4 5 6 7 8 | [root@mysql-node ~] # ll /usr/local/mysql/bin/myin -rwxrwxr-x 1 mysql mysql 161 Aug 22 2018 /usr/local/mysql/bin/myin [root@mysql-node ~] # cat /usr/local/mysql/bin/myin #!/bin/bash p=$1 shift mysql -h "127.0.0.1" -P "$p" --default-character- set =utf8mb4 --show-warnings -uroot -p '123456' -A --prompt= "(\u@\p)[\d]> " "$@" |
- 尝试登录
1 2 3 | [mysql@mysql-node ~] # myin 3306 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server |
- 解决方法
这是因为各个端口实例中的root用户授权的是localhost, 而不是127.0.0.1, 则可以再次添加一个针对127.0.0.1的root用户授权或者将myin脚本中的127.0.0.1 修改为 localhost
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | [root@mysql-node ~] # sed -i 's/127.0.0.1/localhost/g' /usr/local/mysql/bin/myin [root@mysql-node ~] # cat /usr/local/mysql/bin/myin #!/bin/bash p=$1 shift mysql -h "localhost" -P "$p" --default-character- set =utf8mb4 --show-warnings -uroot -p '123456' -A --prompt= "(\u@\p)[\d]> " "$@" 则现在可以登录各个实例了, 登录命令为 "myin port" 登录3306端口实例 [root@mysql-node ~] # myin 3306 > 登录3307端口实例 [root@mysql-node ~] # myin 3307 > 登录3308端口实例 [root@mysql-node ~] # myin 3308 > |
生产环境下的思路: 同一个业务库单独放在一个实例下, 不要混用数据库. 单机多实例间可以做主从或主主同步, 也可以在多机多实例之间坐主从或主主同步.
比如:
A机器有三个实例: 3310, 3311, 3312
B机器有三个实例: 3310, 3311, 3312
C机器有三个实例: 3310, 3311, 3312
则三台机器的每个对应的端口实例之间做主从或主主同步(GTID, MGR), 一主两从, 或三主模式. (多实例环境最好不要用3306端口, 否则经常会出现一些误操作)
三、Mysql8.0使用过程中踩过的一些坑
1) 创建用户和授权
在mysql8.0创建用户和授权和之前不太一样了,其实严格上来讲,也不能说是不一样, 只能说是更严格, mysql8.0需要先创建用户(创建用户时要带@并指定地址, 则grant授权时的地址就是这个@后面指定的!, 否则grant授权就会报错!)和设置密码,然后才能授权。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql> create user 'kevin' @ '%' identified by '123456' ; Query OK, 0 rows affected (0.04 sec) mysql> grant all privileges on *.* to 'kevin' @ '%' with grant option; Query OK, 0 rows affected (0.04 sec) mysql> create user 'bobo' @ '%' identified by '123456' ; Query OK, 0 rows affected (0.06 sec) mysql> grant all privileges on *.* to 'bobo' @ '%' with grant option; Query OK, 0 rows affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.04 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB #R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$ /VO_y ^7,]6;2qxggBLmJzhA0Qylu5 /AHuRScZ/ykKedgZKh/6krOIzPs2 | +-----------+------------------+------------------------------------------------------------------------+ |
如果还是用Mysql5.7及之前版本的直接授权的方法, 会有报错:
1
2
|
mysql> grant all privileges on *.* to 'shibo' @ '%' identified by '123456' ; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by ' 123456 '' at line 1 |
2) Mysql8.0默认是不能使用root账号进行远程登录的! root账号只能本地登录!
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
|
mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB #R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | root | $A$005$ /VO_y ^7,]6;2qxggBLmJzhA0Qylu5 /AHuRScZ/ykKedgZKh/6krOIzPs2 | +-----------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec) 如果想要远程登录, 则需要进行update更新下root账号的权限 mysql> update mysql.user set host= '%' where user= "root" ; Query OK, 1 row affected (0.10 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> flush privileges; Query OK, 0 rows affected (0.14 sec) mysql> select host,user,authentication_string from mysql.user; +-----------+------------------+------------------------------------------------------------------------+ | host | user | authentication_string | +-----------+------------------+------------------------------------------------------------------------+ | % | bobo | $A$005$1VY")q?G6<^X@-6LsXrPt5C0TwlTuvHbaOa3sYF0DKViIGoRPuCF8AzwiFcim1 | | % | kevin | $A$005$hy`U}ZB #R::rA8W0y2rmwgySqzv0rmR1eTeNDSaXfQPWIsrh7ytbVdi85 | | % | root | $A$005$ /VO_y ^7,]6;2qxggBLmJzhA0Qylu5 /AHuRScZ/ykKedgZKh/6krOIzPs2 | | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | +-----------+------------------+------------------------------------------------------------------------+ 6 rows in set (0.00 sec) |
这样就能在远程使用root账号登录该mysql8.0的数据库了
3) 修改root账号权限, 允许root账号远程登录后, 用navicat进行mysql的远程连接时,出现了弹窗报错:
出现这个原因是mysql8 之前的版本中加密规则是mysql_native_password, 而在mysql8之后,加密规则是caching_sha2_password, 解决问题方法有两种:
1) 一种是升级navicat驱动;
2) 一种是把mysql用户登录密码加密规则还原成mysql_native_password;
这里选择第二种方法来解决:
1
2
3
4
5
6
7
8
9
10
11
|
#修改加密规则 mysql> ALTER USER 'root' @ '%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER; Query OK, 0 rows affected (0.16 sec) #更新一下用户的密码 mysql> ALTER USER 'root' @ '%' IDENTIFIED WITH mysql_native_password BY '123456' ; Query OK, 0 rows affected (0.08 sec) #刷新权限 mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.03 sec) |
这样问题就解决了。
4) sqlyog链接时出现2058异常
1
2
3
4
5
6
|
1) 使用sqlyog链接时会出现2058的异常,此时我们需要修改mysql,命令行登录mysql(与修改密码中登录相同,使用修改后的密码),然后执行下面的命令: mysql > ALTER USER 'root' @ 'localhost' IDENTIFIED WITH mysql_native_password BY 'password' ; 其中password为自己修改的密码。然后SQLyog中重新连接,则可连接成功,OK。 2) 如果报错:ERROR 1396 (HY000): Operation ALTER USER failed for 'root' @ 'localhost' 则使用下面命令: mysql > ALTER USER 'root' @ '%' IDENTIFIED WITH mysql_native_password BY 'password' ; |
5) 修改默认编码方式
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
mysql8.0默认编码方式为utf8mb4,因此使用时不需要修改,可使用如下命令查看: mysql > SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%' ; 如果需要修改其他编码方式,比如需要修改为utf8mb4,可以使用如下方式: 修改mysql配置文件my.cnf, 找到后请在以下三部分里添加如下内容: [client] default-character- set = utf8mb4 [mysql] default-character- set = utf8mb4 [mysqld] character- set -client-handshake = FALSE character- set -server = utf8mb4 collation-server = utf8mb4_unicode_ci init_connect= 'SET NAMES utf8mb4' 然后重启mysqld服务即可, 其中: character_set_client (客户端来源数据使用的字符集) character_set_connection (连接层字符集) character_set_database (当前选中数据库的默认字符集) character_set_results (查询结果字符集) character_set_server (默认的内部操作字符集) ======================================== 数据库连接参数中: characterEncoding=utf8 会被自动识别为utf8mb4,也可以不加这个参数,会自动检测。 而autoReconnect= true 是必须加上的。 |
6) 部分参数配置查询命令
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
#查询mysql最大连接数设置 mysql> show global variables like 'max_conn%' ; mysql> SELECT @@MAX_CONNECTIONS AS 'Max Connections' ; # 查看最大链接数 mysql> show global status like 'Max_used_connections' ; # 查看慢查询日志是否开启以及日志位置 mysql> show variables like 'slow_query%' ; # 查看慢查询日志超时记录时间 mysql> show variables like 'long_query_time' ; # 查看链接创建以及现在正在链接数 mysql> show status like 'Threads%' ; # 查看数据库当前链接 mysql> show processlist; # 查看数据库配置 mysql> show variables like '%quer%' ; |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 25岁的心里话
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 按钮权限的设计及实现