MySQL数据库管理-mysql8.0标准部署

部署思路

1 将basedir和datadir单独分开进行存储

2 设置单独的basedir类似/mysqlbase/mysql-8.0.x.和固定的mysqlhome  类似/home/mysql/mysqlbase

使用ln -s进行软链接，方便以后的升级直接修改/home/mysql/mysqlbase的链接即可。其他部署也类似。

ln -s 真实存在的文件 公共连接文件

ln -s $DATADIR/mysql.sockt  /tmp/mysql.socket

3 初始化

4 添加自启动。

1 删除原有的mysql类包 

[root@ecs-39787535 ~]# rpm -qa|grep mysql
[root@ecs-39787535 ~]# rpm -qa|grep mari
mariadb-libs-5.5.68-1.el7.x86_64
[root@ecs-39787535 ~]# rpm -e --nodeps mariadb-libs-5.5.68-1.el7.x86_64
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

2 创建用户

groupadd mysql
useradd -g mysql mysql

3  解压软件包

cd /usr/local & mkdir mysql
cd mysql    

# cp mysql-8.0.34-linux-glibc2.12-x86_64.tar.gz /home/mysql/

[root@ecs-39787535 mysql]# cd /home/mysql/

 chown mysql:mysql mysql-8.0.34-linux-glibc2.12-x86_64.tar.gz 
 chmod 775 mysql-8.0.34-linux-glibc2.12-x86_64.tar.gz 
 su - mysql

tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.gz 
mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql834
 

创建通用目录链接mysql8.0

ln -s  /home/mysql/mysqlbase  /usr/local/mysql

cd /usr/local/mysql/mysql834

[root@ecs-39787535 mysqlbase]# ls -lrt
total 308
-rw-r--r--  1 mysql mysql    666 Jun 22 19:07 README
-rw-r--r--  1 mysql mysql 279815 Jun 22 19:07 LICENSE
drwxr-xr-x  2 mysql mysql   4096 Jun 22 20:25 support-files
drwxr-xr-x 28 mysql mysql   4096 Jun 22 20:25 share
drwxr-xr-x  4 mysql mysql   4096 Jun 22 20:25 man
drwxr-xr-x  6 mysql mysql   4096 Jun 22 20:25 lib
drwxr-xr-x  3 mysql mysql   4096 Jun 22 20:25 include
drwxr-xr-x  2 mysql mysql   4096 Jun 22 20:25 docs
drwxr-xr-x  2 mysql mysql   4096 Jun 22 20:25 bin

# mkdir /data

# chown mysql:mysql /data
# chmod 775 /data

su - mysql

配置 root环境变量
vim /etc/profile
export PATH=$PATH:/usr/local/mysql/mysqlbase/bin:/usr/local/mysql/mysqlbase/lib
source /etc/profile

mysql: error while loading shared libraries: libncurses.so.6: cannot open shared object file: No such file or directory

4 编写配置文件

配置文件中加入误操作避免符

设置prompt主要是方便搞清楚生产系统中登录的是哪个库，哪个用户，防止误操作。

常用选项如下，其它选项详见官方文档：

Option    Description
\c    A counter that increments for each statement you issue
\D    The full current date
\d    The default database
\h    The server host
\m    Minutes of the current time
\R    The current time, in 24-hour military time (0–23)
\s    Seconds of the current time
\U    Your full user_name@host_name account name
\u    Your user name
\_    A space
\     A space (a space follows the backslash)
\'    Single quote
\"    Double quote
\\    A literal \ backslash character

官方给出了共4种方式去设置：

1).推荐，直接在mysql命令窗口中设置

mysql> prompt \R:\m:\s \U[\d]>
如果要取消提示，敲prompt命令即可
1
2
2).设置MYSQL_PS1环境变量

shell> export MYSQL_PS1="(\u@\h) [\d]> "
1
3).启动mysql时，使用–prompt参数

shell> mysql --prompt="(\u@\h) [\d]> "
1
4).prompt参数写入参数文件，比如/etc/my.cnf

[mysql]
prompt=(\\u@\\h) [\\d]>\\_
————————————————

[client]
port=3306
default-character-set = utf8mb4
socket = /tmp/mysql.sock
[mysql]
prompt="\R:\m:\s [\d]> "
no-auto-rehash
max_allowed_packet = 256M
[mysqld]
server-id=1
port=3306
basedir=/usr/local/mysql/mysqlbase
datadir=/data
socket=/tmp/mysql.sock
log-error=/data/mysqld.log
pid-file=/data/mysqld.pid
user=mysql
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
default-storage-engine = InnoDB
autocommit = on
event_scheduler = ON 
log_error = /home/mysql/error.log
lower_case_table_names = 1
back_log=2000
connect_timeout=15
skip_name_resolve=ON
max_connections=5000
table_definition_cache=2000
table_open_cache=10000
ssl=0
slow_query_log = ON
slow_query_log_file = /data/slow.log

# Innodb
innodb_buffer_pool_size = 10240M 
innodb_buffer_pool_instances=8
innodb_log_file_size = 1024M  --已经过期
innodb_log_buffer_size = 16M
innodb_lock_wait_timeout = 20
innodb_autoinc_lock_mode=2
innodb_read_io_threads = 5
innodb_write_io_threads = 5
innodb_thread_concurrency = 8
innodb_doublewrite=1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = 'O_DIRECT'
innodb-page-cleaners=8
innodb_purge_threads=4
innodb_lru_scan_depth=2048
innodb_io_capacity=8000
innodb_io_capacity_max=16000
innodb_adaptive_hash_index=OFF
innodb-change-buffering=none
innodb_flush_neighbors=0
innodb_max_dirty_pages_pct = 90
innodb_max_dirty_pages_pct_lwm = 10
innodb_file_per_table = ON
# Binlog
relay-log=relay-1
enforce-gtid-consistency
gtid-mode=on
binlog_cache_size = 1024M
#master-info-repository=TABLE
#relay-log-info-repository=TABLE
binlog-checksum=NONE
log-bin
sync_binlog=1
# Monitoring
innodb_monitor_enable='%'
performance_schema=ON
performance_schema_instrument='%synch%=on'

WINDOWS默认的ini文件配置

[mysql]
prompt="\R:\m:\s [\d]> "
no-auto-rehash
[mysqld]

# 设置3306端口

port=3306

# 设置mysql的安装目录

basedir=F:\mysql\MySQL
gtid_mode=ON
log_bin=ON
log-slave-updates=ON
enforce-gtid-consistency

# 设置mysql数据库的数据的存放目录

datadir=D:\mysql-8.0.24-winx64\data
basedir=D:\mysql-8.0.24-winx64
log-bin=D:\mysql-8.0.24-winx64\log

# 允许最大连接数

max_connections=200

# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统

max_connect_errors=10

# 服务端使用的字符集默认为UTF8MB4

character-set-server=UTF8MB4

# 创建新表时将使用的默认存储引擎

default-storage-engine=INNODB

# 默认使用“mysql_native_password”插件认证

default_authentication_plugin=mysql_native_password

[mysql]

# 设置mysql客户端默认字符集

default-character-set=UTF8MB4

[client]

# 设置mysql客户端连接服务端时默认使用的端口

port=3306

default-character-set=UTF8MB4

5 初始化数据库

 ./mysqld --user=mysql --basedir=/usr/local/mysql/mysqlbase --datadir=/data --initialize

./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.11' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.22' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by ./mysqld)
./mysqld: /lib64/libm.so.6: version `GLIBC_2.27' not found (required by ./mysqld)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.28' not found (required by ./mysqld)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by /home/mysql/mysql834/bin/../lib/private/libcrypto.so.3)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.28' not found (required by /home/mysql/mysql834/bin/../lib/private/libprotobuf-lite.so.3.19.4)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by /home/mysql/mysql834/bin/../lib/private/libprotobuf-lite.so.3.19.4)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by /home/mysql/mysql834/bin/../lib/private/libprotobuf-lite.so.3.19.4)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by /home/mysql/mysql834/bin/../lib/private/libprotobuf-lite.so.3.19.4)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by /home/mysql/mysql834/bin/../lib/private/libprotobuf-lite.so.3.19.4)
[root@ecs-39787535 bin]# 

yum install libstdc++.so.6 -y

---由于使用的包为 mysql 2.28 glibc包导致问题。

session.sql_log_bin inside a transaction 

经分析数据库autocommit参数设置为off。重新设置成on后执行正常无报错
[ERROR] [MY-000061] [Server] 1694  Cannot modify @@session.sql_log_bin inside a transaction.

6 配置自动启动

复制 mysql.server 文件，在/usr/local/mysql/mysqlbase目录下执行

cp -a ./support-files/mysql.server /etc/init.d/mysql 
cp -a ./support-files/mysql.server /etc/init.d/mysqld

赋权

chown 777 /etc/my.cnf
chmod +x /etc/init.d/mysql
chmod +x /etc/init.d/mysqld

service mysqld status

service mysqld start

[root@ecs-39787535 init.d]# service mysqld start
/etc/init.d/mysqld: line 239: my_print_defaults: command not found
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)
[root@ecs-39787535 init.d]# 

由于配置文件basedir错误导致 ，将配置文件basedir修改完成后 ，重启正常 

[root@ecs-39787535 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL........... SUCCESS! 
[root@ecs-39787535 ~]# 

使用mysqld safe启动数据库 

[root@ecs-39787535 bin]# ./mysqld_safe --user=mysql --basedir=/usr/local/mysql/mysqlbase --datadir=/data &
[1] 22115
[root@ecs-39787535 bin]# 2023-08-16T07:45:29.353388Z mysqld_safe Logging to '/home/mysql/error.log'.
2023-08-16T07:45:29.383878Z mysqld_safe Starting mysqld daemon with databases from /data

[root@ecs-39787535 bin]# 
[root@ecs-39787535 bin]# 
[root@ecs-39787535 bin]# ps -ef|grep mysq;
root     22115 13926  0 15:45 pts/0    00:00:00 /bin/sh ./mysqld_safe --user=mysql --basedir=/usr/local/mysql/mysql833 --datadir=/data
mysql    22943 22115 41 15:45 pts/0    00:00:03 /usr/local/mysql/mysql833/bin/mysqld --basedir=/usr/local/mysql/mysql833 --datadir=/data --plugin-dir=/usr/local/mysql/mysql833/lib/plugin --user=mysql --log-error=/home/mysql/error.log --pid-file=/data/mysqld.pid --socket=/tmp/mysql.sock --port=3306
root     23003 13926  0 15:45 pts/0    00:00:00 grep --color=auto mysq
[root@ecs-39787535 bin]# 

7 启动mysql，修改root密码

如果修改了soket地址需要使用ln -s进行调整

例如

ln -s /app/adm/great.sokt /tmp/mysql.sokt

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
flush privileges;
15:48:  [(none)]> alter user 'root'@localhost identified by "ora#123";
Query OK, 0 rows affected (0.01 sec)
修改远程登录连接

create user 'root'@'%' identified by  '远程连接密码';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;
————————————————

8 调整脚本

/data/mysql/bin/mysqld_safe --user=mysql --basedir=/data/mysql --datadir=/data/mysqldata &

/data/mysql/bin/mysql -uroot -p"Tjyd@_123$" << eof
shutdown;
eof

8 升级到 8.0.34

上传包 ，修改权限

[root@ecs-39787535 mysql]# chown mysql:mysql mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 
[root@ecs-39787535 mysql]# chmod 775 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 
[root@ecs-39787535 mysql]# su - mysql
Last login: Wed Aug 16 15:48:39 CST 2023 on pts/0
-bash: warning: setlocale: LC_TIME: cannot change locale (en_US.UTF-8)
[mysql@ecs-39787535 ~]$ tar -xvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
 

[root@ecs-39787535 ~]# service mysqld stop
Shutting down MySQL.... SUCCESS! 
[root@ecs-39787535 ~]# 

mv mysqlbase mysql833bak

mv mysql-8.0.34-linux-glibc2.12-x86_64 mysqlbase

[root@ecs-39787535 ~]# service mysqld start
Starting MySQL........................ SUCCESS! 
[root@ecs-39787535 ~]# mysql -uroot -pora#123
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.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

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.

16:43:  [(none)]> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.34    |
+-----------+
1 row in set (0.00 sec)

16:43:  [(none)]>