通过官网查看
https://mariadb.org/
文档
https://downloads.mariadb.org/mariadb/repositories/
最新版为 10.5.x
fox.风
设置数据源-清华大学
sudo apt-get install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb [arch=amd64,arm64,ppc64el] https://mirrors.tuna.tsinghua.edu.cn/mariadb/repo/10.5/ubuntu focal main'
安装 mariadb
sudo apt update
sudo apt -y install mariadb-server
# 安装完成 默认自启动
# 如果没有 请用如下 查看
sudo systemctl status mariadb
# 开机自启动
sudo systemctl enable mariadb --now
设置密码
sudo mysql_secure_installation
设置管理用户
命令输入 mysql
# 设置权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY 'very_strong_password';
或
GRANT ALL PRIVILEGES on *.* TO 'admin'@'%' IDENTIFIED BY 'admin' with grant option;
# 设置 密码
SET PASSWORD FOR admin=PASSWORD('admin');
# 应用刷新
flush privileges;
# 退出
exit;
设置远程可以登录
如果不设置,只能本机内访问
sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
把 bind-address=XXX
修改为 如下,表允许所有地址
bind-address=0.0.0.0
最后重启
sudo systemctl restart mariadb
其他命令
# 重启
sudo systemctl restart mariadb
# 启动
sudo systemctl start mariadb
# 关闭
sudo systemctl stop mariadb
# 状态
sudo systemctl status mariadb
修改端口
如果需要修改端口号的同学可以按如下修改,如不需要修改,直接 PASS
sudo vim /etc/mysql/my.cnf
把
#port = 3306
修改为你想要设置的端口
port = 3306
查看mysql 进程
进入msyql命令行执行
show processlist;
修改存储目录位置
默认情况/var/lib/mysql
存储在系统盘内,那么要改为数据盘/www
# 先停止 mariadb
systemctl stop mariadb
# 移动,并做 映射
mv /var/lib/mysql /www/mysql-data && ln -s /www/mysql-data /var/lib/mysql
# 移动,并做 映射
mv /var/log/mysql /www/mysql-log && ln -s /www/mysql-log /var/log/mysql
# 最后启动 mariadb
systemctl start mariadb
相关优化
连接超时优化
查看数据库 当前 连接超时配置,在mysql 命令行中执行
show global variables like '%timeout%';
输出
+---------------------------------------+----------+
| Variable_name | Value |
+---------------------------------------+----------+
| connect_timeout | 10 |
| deadlock_timeout_long | 50000000 |
| deadlock_timeout_short | 10000 |
| delayed_insert_timeout | 300 |
| idle_readonly_transaction_timeout | 0 |
| idle_transaction_timeout | 0 |
| idle_write_transaction_timeout | 0 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 86400 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_semi_sync_master_timeout | 10000 |
| rpl_semi_sync_slave_kill_conn_timeout | 5 |
| slave_net_timeout | 60 |
| thread_pool_idle_timeout | 60 |
| wait_timeout | 28800 |
+---------------------------------------+----------+
19 rows in set (0.001 sec)
数据库默认配置超时时间是 28800秒,即 8小时,那么可以改成我们想要的时间
vim /etc/mysql/mariadb.conf.d/50-server.cnf
在[mysqld]
下,修改或增加如下
wait_timeout=86400
86400 为 24 小时
最后重启数据库
systemctl restart mariadb
连接数
vim /etc/mysql/mariadb.conf.d/50-server.cnf
在[mysqld]
下,修改或增加如下
max_connections = 3000
最后重启数据库
systemctl restart mariadb
慢查询日志
vim /etc/mysql/mariadb.conf.d/50-server.cnf
在[mysqld]
下,修改或增加如下
slow_query_log = on
slow_query_log_file = /www/mysql-data/mariadb-slow.log
long_query_time = 3
slow_query_log_file: 改为你的存储位置
最后重启数据库
systemctl restart mariadb
IP address ‘172.16.12.195’ could not be resolved: Temporary failure in name resolution
https://blog.csdn.net/lxpbs8851/article/details/7892256
vim /etc/mysql/mariadb.conf.d/50-server.cnf
在MYSQL的配置文件中[mysqld]中加入下面的参数:
[mysqld]
skip-host-cache
skip-name-resolve