MySQL 配置

一、进入MySQL
二、创建用户
- 创建备份数据库用户
三、初始化数据库
四、定时备份数据库
- Docker Mysql 备份
五、使用ssh来连接远程服务器的mysql
- 连接自动断开解决
六、wait_time 的坑
客户端工具

一、进入MySQL

基本命令 mysql -h[host] -u[用户] [数据库名称] -p

# 若 -h localhost -u root ，可以忽略
mysql -p
# 指定数据库名称
mysql mydb -p
# 用 liruwei 登录
mysql -hlocalhost -uliruwei mydb -p

二、创建用户

# 创建用户，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
CREATE USER 'username'@'%' IDENTIFIED BY 'password';

# 远程登录方式
ALTER USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'yourpassword';

# 数据库授权
# 授权数据库 `dbname` 全部权限
grant all privileges on dbname.* to username@'%';
# 授权数据库 `dbname` 某些权限
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON `dbname`.* TO `username`@`%`;
flush privileges;

创建备份数据库用户

# 创建 backup 用户，只能 localhost 访问，密码是 backup
$ CREATE USER 'backup'@'localhost' IDENTIFIED BY 'backup';

# 只需要添加 reload,REPLICATION CLIENT，select 权限即可，也有需要 lock tables 权限的
$ GRANT reload, select, REPLICATION CLIENT, lock tables ON *.* TO 'backup'@'localhost' WITH GRANT OPTION;

# 执行生效
flush privileges;

三、初始化数据库

方法一：使用三方工具，链接数据库，导入sql文件

若使用 Visual Studio Code ，安装 MySQL 插件即可

方法二：用 mysql 命令初始化数据库. mysql -h[host] -u[用户] -p [数据库名称] < init.sql

$ mysql -hlocalhost -uroot -p mydb < /root/mysql/init_db.sql

四、定时备份数据库

$ mysqldump -u <username> -p <password>  td_shop > /root/backup_mysql/$(date +%Y_%m_%d)_td_shop.sql

可以利用 crontab 定时备份
0 1 * * * /root/sh/td_shop_brake_up.sh

Docker Mysql 备份

创建一个只有访问权限的账号，防止数据库被删改。

$ docker exec -i ${ID} mysqldump -uroot -p数据库密码  dbname > /root/backup_mysql/$(date +%Y_%m_%d)_td_shop.sql

参考

五、使用ssh来连接远程服务器的mysql

$ ssh -L [local port]:[database host]:[remote port] [username]@[remote host]
 
$ ssh -L [本地端口]:[数据库所在地址]:[数据库远程端口] [远程服务器用户名]@[远程服务器地址]

示例: 下面的命令会将远程服务器监听3306端口的MySQL服务，映射到本地的3310端口上，之后可以直接使用MySQL客户端在命令行里登录，或者使用pma等数据库操作工具

$ ssh -N -L 3310:127.0.0.1:3306 root@MyRemote

连接自动断开解决

可以修改 sshd 配置，客户端自动发送心跳包，维持链接

# 第一步
$ vi /etc/ssh/sshd_config

# 第二步找到，并修改
# ClientAliveInterval 0 客户端每隔多少秒向服务器发送心跳包
# ClientAliveCountMax 3 客户端多少秒没响应，服务器自动断开

ClientAliveInterval 30
ClientAliveCountMax 86400

# 第三步重启sshd服务 (mac版本)
# 先暂停
$ sudo launchctl unload -w /System/Library/LaunchDaemons/ssh.plist
# 再启动
$ sudo launchctl load -w /System/Library/LaunchDaemons/ssh.plist
# 查看状态
$ sudo launchctl list | grep ssh 

六、wait_time 的坑

wait_timeout — 指的是mysql在关闭一个非交互的连接之前所要等待的秒数，其取值范围为1-2147483(Windows)，1-31536000(linux)，默认值28800。

interactive_time — 指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接)，其取值范围随wait_timeout变动，默认值28800。

所谓的交互式连接，即在mysql_real_connect()函数中使用了CLIENT_INTERACTIVE选项。说得直白一点，通过mysql客户端连接数据库是交互式连接，通过jdbc连接数据库是非交互式连接。

MySQL 的默认设置下，当一个连接的空闲时间超过8小时后，一到高峰期肯定会造成，会有太多的TCP连接没关闭，数据库连接数肯定是不够。从而会产生CPU占用过高，服务器告警等问题。因EPG的一个访问一次对数据库操作量不大，查询完数据就完成ok了，wait_timeout 设置在120s内就行了

修改

# 查看具体的配置
mysql> show variables like '%timeout';

# 修改
mysql> set global interactive_timeout=100;

   Query OK, 0 rows affected (0.00 sec)

如果修改interactive_timeout的话wait_timeout也会跟着变，而只修改wait_timeout是不生效的。

客户端工具

DBeave