mysql技巧

ubuntu22.04安装配置优化mysql8

参考链接:https://www.cyberciti.biz/faq/installing-mysql-server-on-ubuntu-22-04-lts-linux/ 精简版:

一、安装:

sudo apt update
sudo apt list --upgradable # get a list of upgrades
sudo apt upgrade

apt-cache search mysql-server

mysql-server - MySQL database server (metapackage depending on the latest version)
mysql-server-8.0 - MySQL database server binaries and system database setup
mysql-server-core-8.0 - MySQL database server binaries
default-mysql-server - MySQL database server binaries and system database setup (metapackage)
default-mysql-server-core - MySQL database server binaries (metapackage)
mariadb-server-10.6 - MariaDB database server binaries
mariadb-server-core-10.6 - MariaDB database core server files

apt info -a mysql-server-8.0

mysql-server-8.0 vs mysql-server-core-8.0 package:

mysql-server-8.0 – In almost all cases, you need this package. It contains MySQL database server binaries, clients and system database setup.
mysql-server-core-8.0(更适合容器) – This package includes the server binaries but doesn’t contain all the infrastructure needed to set up system databases. So this one is more useful for those setting up Linux containers (Docker, LXD and co) and don’t need all the stuff like mysql clients.

apt install mysql-server-8.0

二、配置和版本查看:

敲mysql进入数据库,添加用户:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'My7Pass@Word_9_8A_zE';

sudo systemctl start mysql.service
sudo systemctl stop mysql.service
sudo systemctl restart mysql.service
sudo systemctl status mysql.service

配置文件:
/etc/mysql/conf.d/
/etc/mysql/mysql.conf.d/
/etc/mysql/mysql.conf.d/mysqld.cnf

安全
mysql_secure_installation

启动停止状态:
systemctl is-enabled mysql.service
systemctl enable mysql.service
systemctl status mysql.service
systemctl start mysql.service
systemctl stop mysql.service
systemctl restart mysql.service

查看进程启动日志:
journalctl -u mysql.service -xe
查看mysql日常日志:
tail -f /var/log/mysql/error.log

查看版本:
root@VM-8-2-ubuntu:~# mysql -e 'SHOW VARIABLES LIKE "%version%";'
+--------------------------+-------------------------+
| Variable_name            | Value                   |
+--------------------------+-------------------------+
| admin_tls_version        | TLSv1.2,TLSv1.3         |
| immediate_server_version | 999999                  |
| innodb_version           | 8.0.36                  |
| original_server_version  | 999999                  |
| protocol_version         | 10                      |
| replica_type_conversions |                         |
| slave_type_conversions   |                         |
| tls_version              | TLSv1.2,TLSv1.3         |
| version                  | 8.0.36-0ubuntu0.22.04.1 |
| version_comment          | (Ubuntu)                |
| version_compile_machine  | x86_64                  |
| version_compile_os       | Linux                   |
| version_compile_zlib     | 1.2.13                  |
+--------------------------+-------------------------+

root@VM-8-2-ubuntu:~# mysql -e 'SELECT VERSION();'
+-------------------------+
| VERSION()               |
+-------------------------+
| 8.0.36-0ubuntu0.22.04.1 |
+-------------------------+

三、创建用户

创建数据库:
CREATE DATABASE mydemodb;
创建用户:
CREATE USER 'vivekappusr'@'%' IDENTIFIED BY 'aa09dd995C72_5355a598fc7D8ab1230a';
给权限:
GRANT SELECT, INSERT, UPDATE, DELETE ON mydemodb.* TO 'vivekappusr'@'%';
收回权限:
GRANT ALL PRIVILEGES ON mydemodb.* TO 'vivekappusr'@'%';

mysql> SELECT USER,host FROM mysql.user;
+------------------+-----------+
| USER             | host      |
+------------------+-----------+
| abc              | %         |
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
6 rows in set (0.01 sec)

mysql> SHOW GRANTS FOR abc;
+--------------------------------------------------+
| Grants for abc@%                                 |
+--------------------------------------------------+
| GRANT USAGE ON *.* TO `abc`@`%`                  |
| GRANT ALL PRIVILEGES ON `abc`.* TO `abc`@`%`     |
+--------------------------------------------------+
3 rows in set (0.00 sec)

四、配置文件样例:

配置文件样例:/etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
datadir     = /var/lib/mysql
log-error   = /var/log/mysql/error.log
# server LAN/VLAN IP and port
bind_address = 10.147.164.6
port = 3306
skip_external_locking
skip_name_resolve
max_allowed_packet              = 256M
max_connect_errors              = 1000000
# InnoDB
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1
innodb_buffer_pool_size         = 512M
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 16M
innodb_log_file_size            = 512M
innodb_stats_on_metadata        = 0
innodb_read_io_threads          = 64
innodb_write_io_threads         = 64
 
# MyISAM Settings (set if you are using MyISAM)
key_buffer_size                 = 32M   
 
low_priority_updates            = 1
concurrent_insert               = 2
 
# Connection Settings
max_connections                 = 100   
 
back_log                        = 512
thread_cache_size               = 100
thread_stack                    = 192K
 
interactive_timeout             = 180
wait_timeout                    = 180
 
# Buffer Settings
join_buffer_size                = 4M    
read_buffer_size                = 3M    
read_rnd_buffer_size            = 4M    
sort_buffer_size                = 4M

# Table Settings (see below for open file limits)
table_definition_cache          = 40000 
table_open_cache                = 40000 
open_files_limit                = 60000 
 
max_heap_table_size             = 128M
tmp_table_size                  = 128M
 
# Search Settings
ft_min_word_len                 = 3

# Logging
log_error                       = /var/lib/mysql/mysql_error.log
log_queries_not_using_indexes   = 1
long_query_time                 = 5
slow_query_log                  = 0     # Disabled for production
slow_query_log_file             = /var/lib/mysql/mysql_slow.log

[mysqldump]
quick
quote_names
max_allowed_packet

五、高负载优化参数:

如果是一个负载高的mysql8,会碰到如下错误:
[Warning] Could not increase number of max_open_files to more than 102400 (request: 640147)
论坛的解决办法:
https://www.nixcraft.com/t/warning-could-not-increase-number-of-max-open-files-to-more-than-102400-request-640147/3849/3

具体操作如下,查看当前配置:
# mysql -u root -p -e 'SHOW GLOBAL VARIABLES LIKE "open_files_limit";'
Enter password: 
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 10000 |
+------------------+-------+

systemctl edit mysql.service
再下面两行之间进行插入配置(LimitNOFILE=infinity 改为 LimitNOFILE=1800000):
### Anything between here and the comment below will become the new contents of the file
[Service]
LimitNOFILE=1800000
### Lines below this comment will be discarded

创建新文件:/etc/sysctl.d/100-custom.conf
添加:
fs.nr_open=1800000

生效:
sysctl -p /etc/sysctl.d/100-custom.conf

重启服务:
sudo systemctl daemon-reload
sudo systemctl restart mysql

再次查看配置:
# mysql -u root -p -e 'SHOW GLOBAL VARIABLES LIKE "open_files_limit";'
Enter password: 
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| open_files_limit | 1800000 |
+------------------+---------+

查看current open files limit:
root@VM-8-2-ubuntu:/var/run/mysqld# ps -ef|grep mysql
mysql    2525213       1  1 Mar28 ?        00:22:06 /usr/sbin/mysqld
root     3244154 3238576  0 18:58 pts/1    00:00:00 grep --color=auto mysql
root@VM-8-2-ubuntu:~# cat /proc/2525213/limits|grep open
Max open files            1800000                1800000                files    
京ICP备11047313号-21