mysql8.4

• ubuntu24.04
• 初始化

常见安装问题

• 权限问题

超简安装·2

• docker部署·mysql
• 重安装·8.0
• docker部署·doris
• 内存设置
• 快速启动

mysql简易安装

https://www.mysql.com/downloads/
https://dev.mysql.com/downloads/mysql/

社区通用版下载
mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
582M,mysql server,企业级安装,用的就是这个,这个也够了

mysql-8.0.32-linux-glibc2.12-x86_64.tar
959M,包含以下三个
mysql-test-8.0.32-linux-glibc2.12-x86_64.tar.xz
mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mysql-router-8.0.32-linux-glibc2.12-x86_64.tar.xz
mysql-router是一个轻量级的故障切换路由器,相当于HA,切IP用的,是个路由,不涉及数据库内部操作
对于企业来说,IP的切换通常由网络部门处理,都是企业级的,所以mysql-router的应用场景较少 

本次安装环境为ubantu,linux安装与之几乎完全一致 

用户创建
----------------------------------------------------------------------------
root用户
这里采用root用户进行安装,服务的启动也使用root用户,
若是企业内首次安装mysql有限用root用户的场景,就解释然后申请开通

添加用户linux 
groupadd mysql
useradd -g mysql mysql

添加用户ubantu 
除了用户,还有一个组,在ubantu上这个命令会自动创建一个同名的mysql组
adduser mysql 

这里涉及用户是否有目录的问题,企业级创建的用户通常没有创建对应的home目录,
如果是集群服务器节点互信,用mysql可以,用root也可以,或者用已有的互信用户也行,
重点是不影响互信的功能


软件安装目录
----------------------------------------------------------------------------
此压缩包,是编辑好的,相关动态库都已经包含其中,解压就可直接使用
安装目录随意,企业级的关键在于不同服务器位置的统一,便于平台统一维护,尤其是后面要创建数据目录
tar -xvf mysql-8.0.32-linux-glibc2.12-x86_64.tar.xz
mv mysql-8.0.32-linux-glibc2.12-x86_64 /opt/app/mysql
chown -R mysql.mysql  /opt/app/mysql

环境变量配置,不是必需,配置是为了使用方便
export PATH=/opt/app/mysql/bin:$PATH

root@xt:~# vim /etc/profile
root@xt:~# source /etc/profile
root@xt:~# which mysql
/opt/app/mysql/bin/mysql


数据目录创建
----------------------------------------------------------------------------
要求统一,如果可能存在一个服务器多个实例的场景,也要统一规划
主要考虑的还是磁盘大小,要考虑业务未来3年的增长,备份存量,不可能一年玩一次数据库迁移

mkdir -p /opt/data/tmp
mkdir -p /opt/data/mysql_13301
mkdir -p /opt/data/logs
chown -R mysql.mysql /opt/data/logs
chown -R mysql.mysql /opt/data/mysql_13301/
chown -R mysql.mysql /opt/data/tmp/

第2个实例的数据目录
mkdir -p /opt/data/mysql_13302
chown -R mysql.mysql /opt/data/mysql_13302/
多个实例之间共用相同的日志目录,日志监控,报警,读取相同的目录
数据实例目录可以通过实例参数读取,也可以脚本匹配/opt/data/mysql_* 

mysql配置文件

[client]
port                  = 13301
socket                = /opt/app/mysql/data/logs/my_13301.sock
default-character-set = utf8

[mysql]
no-auto-rehash
prompt                 =\\u@\\h \\D \\R:\\m:\\s [\\d]>
default-character-set  =utf8
socket                 =/opt/app/mysql/data/logs/my_13301.sock

[mysqld]
user        = mysql
server-id   = 13301
port        = 13301
socket      = /opt/app/mysql/data/logs/my_13301.sock
pid-file    = /opt/app/mysql/data/logs/my_13301.pid

basedir                  = /opt/app/mysql
datadir                  = /opt/app/mysql/data/mysql_13301
tmpdir                   = /opt/app/mysql/data/tmp
replica_load_tmpdir      = /opt/app/mysql/data/tmp

character-set-filesystem = utf8mb4
character_set_server     = utf8mb4
collation_server         = utf8mb4_bin
lower_case_table_names   = 1


#skip_name_resolve
open_files_limit    = 65535
back_log            = 103
max_connections     = 500
max_connect_errors  = 10000

#----log settings----#
default-time_zone       = '+8:00'
log_timestamps          = SYSTEM
log_error_verbosity     = 2
init_connect            = 'SET NAMES utf8mb4'
#sql_mode                = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
log-error               = /opt/app/mysql/data/logs/my_13301_error.log
log-bin                 = /opt/app/mysql/data/mysql_13301/mysql-bin

binlog_format           = row
sync_binlog             = 0
binlog_cache_size       = 32M
max_binlog_cache_size   = 512M
max_binlog_size         = 1024M

binlog_expire_logs_seconds        = 7200
interactive_timeout     = 120
wait_timeout            = 120
transaction_isolation   = READ-COMMITTED

read_only=0                        #off

relay-log               = /opt/app/mysql/data/mysql_13301/relay-bin
replica_net_timeout     = 60
replica_skip_errors     = 1062
log_replica_updates     = 1  #


innodb_page_size               = 16384  #default value
innodb_data_file_path          = ibdata1:1024M:autoextend
innodb_temp_data_file_path     = ibtmp1:2048M:autoextend    
innodb_buffer_pool_size        = 300M
innodb_buffer_pool_instances   = 1
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency      = 0
innodb_file_per_table          = 1

max_allowed_packet        = 64M
max_heap_table_size       = 64M
sort_buffer_size          = 4M
join_buffer_size          = 4M
thread_cache_size         = 32
skip-name-resolve
ft_min_word_len           = 4
default-storage-engine    = InnoDB
thread_stack              = 192K
#tmp_table_size           = 64M
innodb_thread_concurrency = 0
innodb_lock_wait_timeout  = 180


[mysqldump]
quick
max_allowed_packet = 32M

各个操作都是以root用户操作的,添加文件需要重新授权
chown -R mysql.mysql /opt/data 

mysql创建实例

建库
----------------------------------------------------------------------------
这里要注意的是配置文件放哪里更合适,有人会放在/etc目录下,
但配置文件决定的是库的创建,也就是说配置文件与库紧密相关,
那么它与数据文件同级放更合适一些,如果你要COPY走一份数据文件,就顺便把与之相关的配置文件也COPY走 

mysql5.7或mysql8指定配置文件创建数据库
which mysqld 
mysqld --defaults-file=/opt/data/my_13301.cnf  --initialize --user=mysql 


安装过程中查看日志,如果报错可以查询具体错误内容,无错会有一个临时密码
tail -f logs/my_13301_error.log
[Server] A temporary password is generated for root@localhost: iE+jJlw!w9?,

mysql启动与关闭

启动
nohup mysqld_safe  --defaults-file=/opt/data/my_13301.cnf  --user=mysql &


关闭方式一
mysqladmin shutdown -uroot -p'rootroot' -S /opt/data/logs/my_13301.sock

关闭方式二
mysql> shutdown;
Query OK, 0 rows affected (3.43 sec)

定义别名，方便日常维度
alias mstart="nohup mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &"
alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock"

更新root密码

初始化密码修改
mysql -uroot -S /opt/data/logs/my_13301.sock -p 

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'rootroot';
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

mysqladmin -u用户名 -p旧密码 password 新密码 
mysqladmin -u root password 'otA8_b19H78rqu' -S /opt/data/logs/my_13301.sock


mysql_native_password
执行本地身份验证的插件；
基于本机密码哈希方法实现身份验证。

sha256_password
为用户帐户密码实现SHA-256哈希，比本地身份验证更强；
随着技术的发展，SHA1和其他哈希算法的前身（例如MD5）已被证明非常容易破解；
sha2更笼统地指SHA-2类加密算法，其中256位加密是其中的一种256长度的实例。

caching_sha2_password
sha256_password实现基本的SHA-256身份验证；
caching_sha2_password实现SHA-256身份验证（如sha256_password）,
但在服务器端使用高速缓存可提高性能，并具有其他功能可更广泛地应用。

mysql> show variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set (0.12 sec)

创建管理用户

创建管理用户 
drop user 'automng'@'127.0.0.1' ;
drop user 'automng'@'localhost' ;
drop user 'automng'@'%';

create user 'automng'@'127.0.0.1' ;
GRANT ALL PRIVILEGES ON *.* TO 'automng'@'127.0.0.1' WITH GRANT OPTION;
flush privileges;
ALTER USER 'automng'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'Automng_123';

create user 'automng'@'localhost' ;
GRANT ALL PRIVILEGES ON *.* TO 'automng'@'localhost'  WITH GRANT OPTION;
flush privileges;
ALTER USER 'automng'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Automng_123';

create user 'automng'@'%' identified by 'Automng_123';
GRANT ALL PRIVILEGES ON *.* TO 'automng'@'%'  WITH GRANT OPTION;
flush privileges;
ALTER USER 'automng'@'%' IDENTIFIED WITH mysql_native_password BY 'Automng_123';

mysql> select user,host,password_expired,password_last_changed,password_lifetime from mysql.user;
+------------------+-----------+------------------+-----------------------+-------------------+
| user             | host      | password_expired | password_last_changed | password_lifetime |
+------------------+-----------+------------------+-----------------------+-------------------+
| automng          | %         | N                | 2023-01-29 16:53:46   |              NULL |
| automng          | 127.0.0.1 | N                | 2023-01-29 16:53:46   |              NULL |
| automng          | localhost | N                | 2023-01-29 16:53:46   |              NULL |
| mysql.infoschema | localhost | N                | 2023-01-29 15:39:40   |              NULL |
| mysql.session    | localhost | N                | 2023-01-29 15:39:40   |              NULL |
| mysql.sys        | localhost | N                | 2023-01-29 15:39:40   |              NULL |
| root             | localhost | N                | 2023-01-29 16:51:59   |                 0 |
+------------------+-----------+------------------+-----------------------+-------------------+

刷新权限
flush privileges;

更改用户的加密方式
ALTER USER 'automng'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'Automng_123';
ALTER USER 'automng'@'%' IDENTIFIED WITH caching_sha2_password BY 'Automng_123';

mysql> select host,user,plugin from mysql.user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | automng          | caching_sha2_password |
| 127.0.0.1 | automng          | mysql_native_password |
| localhost | automng          | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | mysql_native_password |
+-----------+------------------+-----------------------+
7 rows in set (0.00 sec)

dbeaver数据库客户端

 
免费
这是一个数据库连接的客户端,使用Java编写,有windows,mac,ubantu多个系统的版本,需要先配置JDK环境
它连接数据库的驱动就是Java连接数据库的驱动,如果你是Java程序员,可直接将你代码中连接数据库的驱动拿过来用
如果不是联网环境,需要先下载相关Java连接数据库的驱动,点击编辑驱动
连接mysql数据库因版本不同驱动名称有细微差别,就是Java代码连接池配置的那一套 

驱动类型:MySQL 
类名:com.mysql.jdbc.Driver (这个Java类名因版本不同会有细微差异)
添加文件,添加java连接数据库的驱动,mysql-connector-java-5.1.49.jar 

com.mysql.jdbc.Driver，导入的是 mysql-connector-java 5
com.mysql.cj.jdbc.Driver，导入的是 mysql-connector-java 8

驱动这一部分剩下的部分不需要填写,就是导入驱动jar包,然后填写上类名

mysql超简安装

只想在linux上简单搭建个mysql，能用就行，不要求什么优化配置，能不能快速搞定？

答案是超简安装
超简安装就是将已经安装好的一个数据库+配置文件打包


ubantu上的一次操作记录,linux此方式及此包同样可行
sudo apt-get update  
sudo apt-get install libncurses5

不更换数据目录
---------------------------------------------------------------------------------
操作开始,进入root 用户：
系统上没有mysql用户,adduser提前创建

解压mysql_8.0.32.tar.gz压缩包 （文章结尾可以下载，在参考部分或点击直接下载）
cd /opt/app
root@ai1:/opt/app# tar -xvf mysql_8.0.32.tar.gz

chown -R mysql.mysql /opt/app/mysql

配置环境变量
root@ai1:~# vim /etc/profile
export PATH=/opt/app/mysql/bin:$PATH
root@ai1:~# source /etc/profile

以root用户启动
nohup mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &
或者 
nohup /opt/app/mysql/bin/mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &

访问
mysql -uroot -prootroot -S /opt/app/mysql/data/logs/my_13301.sock
mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock

这是一个空库，需要自己提前创建数据库
create database db1;

启动与关闭
alias mstart="nohup mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &"
alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock"

注意点：先创建mysql用户，然后在/opt/app下解压，这个目录是固定的，必须是这个目录，然后就可以用了
若要换软件安装目录，需要更新配置文件中的basedir目录，
换其他配置更新配置文件中对应的相关参数



更换数据目录
----------------------------------------------------------------
数据目录原来在/opt/data目录，现在换为/opt/app/mysql/data，换其他位置参考下面的操作
$ sudo su - root
# adduser mysql
rsync -rltDv mysql_8.0.32.tar.gz /opt/app/
cd /opt/app 
tar -xvf mysql_8.0.32.tar.gz 


修改配置文件相关路径
将/opt/data目录替换为实际上的/opt/app/mysql/data
root@ai1:/opt/app/mysql/data# cp my_13301.cnf my_13301.cnf.bak
root@ai1:/opt/app/mysql/data# sed -i 's/\/opt\/data/\/opt\/app\/mysql\/data/g' my_13301.cnf

cd /opt/app/mysql/data/mysql_13301
sed -i 's/\/opt\/data/\/opt\/app\/mysql\/data/g' mysql-bin.index
chown -R mysql.mysql /opt/app/mysql

启动
root@ai1:~# vim /etc/profile
export PATH=/opt/app/mysql/bin:$PATH
root@ai1:~# source /etc/profile

nohup mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &
root@ai1:~# mysql -uroot -prootroot -S /opt/app/mysql/data/logs/my_13301.sock
mysql> exit
Bye
root@ai1:~# 
root@ai1:~# mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock
mysql> shutdown;
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye

超简安装数据目录变更

 
验证mysql可用后，如果数据量大，可以换一个更大空间目录作为数据目录 

mkdir -p /wks/data/mysql
chown mysql.mysql /wks/data/mysql/

关闭mysql,然后同步文件 
rsync -rltDv /opt/app/mysql/data/ /wks/data/mysql/

[root@nd /]#ls /wks/data/mysql/
logs  my_13301.cnf  my_13301.cnf.bak  mysql_13301  tmp

用 go/python 实现配置文件的修改得了... 

mysql批量建库

mysql软件本身的安装实际上就是下载解压,
主要工作量在配置文件修改,数据库创建,root密码修改,用户创建等等 

方便的前提是提前创建好一个demo,相当于创建一个模板,后续批量建库就是把这个模板COPY过去,
授权 chown -R mysql.mysql /opt/data 
然后启动 

前面安装的时候有提到过统一规划,规划好了,后续维护就方便,遇到特殊情况,微调一下配置文件就可以了

安装并修改配置

 
添加用户mysql 
groupadd mysql
useradd -g mysql mysql


cp mysql_8.0.32.tar.gz /data/
tar -xvf mysql_8.0.32.tar.gz
cd /data/mysql/data

数据目录 
[root@nd data]# ls
logs  my_13301.cnf  my_13301.cnf.bak  mysql_13301  tmp

数据总目录：
下面放日志目录，tmp目录，各种实例的配置文件，实例目录
实例目录以端口区分

可通过下面的命令修改数据目录与基目录 
python create_conf.py -d /data/mysql/data -b /data/mysql

端口默认13301，如果更换端口要确保实例目录存在
datadir                  = /data/mysql/data/mysql_13301


这里修改了数据目录，基目录，buffer_pool_size
python mysql_create_conf.py -d /data/mysql/data -b /data/mysql -s 2G


alias mstart="nohup mysqld_safe  --defaults-file=/opt/app/mysql/data/my_13301.cnf  --user=mysql &"
alias min="mysql -uautomng -pAutomng_123 -S /opt/app/mysql/data/logs/my_13301.sock"


vim mysql_13301_start.sh 
nohup /data/mysql/bin/mysqld_safe  --defaults-file=/data/mysql/data/my_13301.cnf  --user=mysql &

sh mysql_13301_start.sh 


无法启动时查看错误日志
/data/mysql/data/logs/my_13301_error.log

进入mysql 
/data/mysql/bin/mysql -uroot -prootroot -S /data/mysql/data/logs/my_13301.sock
/data/mysql/bin/mysql -uautomng -pAutomng_123 -S /data/mysql/data/logs/my_13301.sock


更新root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'w42703_W42703';
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

create_conf.py脚本

python create_conf.py -b /data/mysql -d /data/mysql/data -s 2G -P 3307

 
进入datadir目录 
vim create_conf.py

[root@nd data]# python create_conf.py -b /data/mysql -d /data/mysql/data -s 2G -P 3307
cp -r /data/mysql/data/mysql_13301 /data/mysql/data/mysql_3307
nohup /data/mysql/bin/mysqld_safe  --defaults-file=/data/mysql/data/my_3307.cnf  --user=mysql &

[root@nd data]# ls
create_conf.py  logs  my_13301.cnf  my_13301.cnf.bak  my_3307.cnf  mysql_13301  mysql_3307  mysql_start_3307.sh  tmp

进入mysql 
/data/mysql/bin/mysql -uroot -prootroot -S /data/mysql/data/logs/my_3307.sock
/data/mysql/bin/mysql -uautomng -pAutomng_123 -S /data/mysql/data/logs/my_3307.sock

更新root密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Automng_123';
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;

脚本内容

 
#!/usr/bin/python
# -*- coding: UTF-8 -*-

import sys, getopt
import os


#读取当前目录下的配置文件
def get_conf_all(file_name = 'my_13301.cnf.bak'):
    """读取配置文件 
    """
    absolute_path = os.path.abspath(file_name)
    content= ''
    # 打开文件  
    with open(absolute_path, 'r') as file:  
        # 读取文件的所有内容  
        content = file.read()  
        
    return content

def change_bin_index(data_dir,file_name = 'mysql_13301/mysql-bin.index'):
    """读取配置文件 
    """
    absolute_path = os.path.abspath(file_name)
    content= ''
    if os.path.exists(absolute_path):
        # 打开文件  
        with open(absolute_path, 'r') as file:  
            # 读取文件的所有内容  
            content = file.read()  
    else:
        print(absolute_path+" does not exists")
        return 

    content = content.replace("/opt/app/mysql/data",data_dir)
    with open(file_name, "w") as file:  
        file.write(content)

    return content


def main(argv):
    base_dir = ''
    data_dir = ''
    port = 13301
    try:
        content = get_conf_all()
        opts, args = getopt.getopt(argv,"hb:d:P:s:",["bbase_dir=","ddata_dir=","Pport=","spool_size="])
    except getopt.GetoptError:
        print('create_conf.py -b base_dir -d data_dir -P port -s pool_size')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print('create_conf.py -b base_dir -d data_dir -P port -s pool_size')
            sys.exit()

        elif opt in ("-d", "--ddata_dir"):
            data_dir = arg
            # print('set data_dir=', data_dir)
                
            if data_dir[-1]=="/":
                data_dir = data_dir[:-1]
            content = content.replace("/opt/data",data_dir)
            bin_index_content = change_bin_index(data_dir)

        elif opt in ("-P", "--Pport"):
            port = arg
            
            content = content.replace("13301",port)
            datadir2 = data_dir+"/mysql_"+port
            copy_cmd = "cp -r "+data_dir+"/mysql_13301 "+datadir2
            print(copy_cmd)

            try:
                os.system(copy_cmd)

                bin_index_content = bin_index_content.replace("13301",port)
                file_name = datadir2+'/mysql-bin.index'
                absolute_path = os.path.abspath(file_name)

                if os.path.exists(absolute_path):
                    with open(absolute_path, "w") as file:  
                        file.write(bin_index_content)
            except Exception as e:
                print(e)

            if not os.path.exists(datadir2):
                print(datadir2,"The directory does not exist. Please copy the original data directory to this directory")
                print("check the mysql-bin.index in ",datadir2)

        elif opt in ("-s", "--spool_size"):
            pool_size = arg
            content = content.replace("300M",pool_size)

        elif opt in ("-b", "--bbase_dir"):
            base_dir = arg
            # print('set base_dir=', base_dir)
            if base_dir[-1]=="/":
                base_dir = base_dir[:-1]
            content = content.replace("/opt/app/mysql", base_dir)

    mysql_conf_path  = os.path.abspath("my_"+str(port)+".cnf")
    # print("mysql_conf_path:",mysql_conf_path)
    with open(mysql_conf_path, "w") as file:  
        file.write(content)

    start_msqyl = "nohup "+base_dir+"/bin/mysqld_safe  --defaults-file="+data_dir+"/my_"+port+".cnf  --user=mysql &"
    print(start_msqyl)
    mysql_start_shell = os.path.abspath("mysql_start_"+port+".sh")
    with open(mysql_start_shell, "w") as file:  
        file.write(start_msqyl)
    os.system("chown -R mysql.mysql "+datadir2)

    log_file = data_dir+"/logs/my_"+port+"_error.log"
    if not os.path.exists(log_file):
        os.system("touch "+log_file)
        os.system("chown -R mysql.mysql "+log_file)
            
if __name__ == "__main__":
    main(sys.argv[1:])
    

参考

    mysql_8.0.32.tar.gz 夸克网盘下载