Ubuntu下Mysql安装日志及用户配置等教程

一、安装MySQL

1. 删除Mysql 数据库

sudo apt autoremove --purge mysql-server-*
sudo apt remove mysql-server
sudo apt autoremove mysql-server
sudo apt remove mysql-common

2. 安装Mysql 

sudo apt-get update #更新包
sudo apt install mysql-server-5.7
查看 MySQL 版本
sudo mysql -V
查看MySQL默认账号和密码
sudo cat /etc/mysql/debian.cnf

二、配置MySQL

输入下列配置命令

sudo mysql_secure_installation

根据下面界面进行配置

#1
VALIDATE PASSWORD PLUGIN can be used to test passwords...
Press y|Y for Yes, any other key for No: N 
 
#2
 Please set the password for root here...
 New password: (输入密码)
Re-enter new password: (重复输入)
 
#3
 By default, a MySQL installation has an anonymous user,
 allowing anyone to log into MySQL without having to have
 a user account created for them...
 Remove anonymous users? (Press y|Y for Yes, any other key for No) : N 
 
#4
 Normally, root should only be allowed to connect from
 'localhost'. This ensures that someone cannot guess at
 the root password from the network...
 Disallow root login remotely? (Press y|Y for Yes, any other key for No) : N 
 
#5
 By default, MySQL comes with a database named 'test' that
 anyone can access...
 Remove test database and access to it? (Press y|Y for Yes, any other key for No) : N 
 
#6
 Reloading the privilege tables will ensure that all changes
 made so far will take effect immediately.
 Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y

三、查看mysql服务状态

systemctl status mysql.service

四、修改root账户认证方式，并配置远程访问

mysql -u debian-sys-maint -p    #进入 mysql 命令模式
mysql> use mysql;
mysql> select user,host,authentication_string from user;
mysql> update mysql.user set authentication_string=password('密码') where user='root' ;
mysql> update user set plugin='mysql_native_password'; //错了客户端工具就连不上了
mysql> update user set host='%' where user = 'root';
mysql> select Host, User, plugin from user;
mysql> flush privileges;
mysql> quit;

五、配置远程访问mysql

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

修改配置文件，注释掉bind-address = 127.0.0.1

六、重启mysql服务
 

#重启
sudo service mysql restart 
#查看状态
systemctl status mysql.service

 七、增加用户并授权数据库

若只需要安装mysql的话，上述步骤已完成。为了使得数据库安全，通常增加一个用户作为开发用户，并授权DDL增删改查权限。

新建用户

create user zhangsan identified by 'zhangsan';

create user 'happytest'@'localhost' identified by 'happytest'; 如果mysql版本为8.0的话，执行此语句创建用户

授权

grant all privileges on zhangsanDb.* to zhangsan@'%' identified by 'zhangsan';
grant select,insert,update,delete on zhangsanDb.* to zhangsan@'%' identified by 'zhangsan';
flush privileges;

priv代表权限select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限

若出现创建用户，本地无法登录的情况，可能是由于空用户导致的，需要删除mysql的空用户。

八、开启数据库日志

开发过程中可以开启general_log用于记录每一次sql执行情况，正式部署环境可以开启bin_log用于记录数据库变更情况

1、开启general_log

show global variables like '%general%';
set global general_log=on;
show variables like 'general_log_file';

根据日志位置去查看日志

2、开启bin_log

进入配置文件，打开如下配置

 日志在对应的目录下，每天会产生一个文件，此处设置为文件最大500m，90天过期。

九、手动清理binlog日志

按照名称清理

## 将mysql-bin.000010之前的日志清理掉
mysql> purge binary logs to 'mysql-bin.000010';
Query OK, 0 rows affected (0.01 sec)

## 清理前
[root@zxy_slave1 mysql]# ls
mysql-bin.000007  mysql-bin.000012  mysql-bin.000017   mysql-bin.000008  mysql-bin.000013  mysql-bin.index mysql-bin.000009  mysql-bin.000014  mysql-bin.000005  mysql-bin.000010  mysql-bin.000015   mysql-bin.000006  mysql-bin.000011  mysql-bin.000016
## 清理后
[root@zxy_slave1 mysql]# ls
mysql-bin.000012  mysql-bin.000017    mysql-bin.000013  mysql-bin.index     mysql-bin.000014  mysql-bin.000010  mysql-bin.000015   mysql-bin.000011  mysql-bin.000016  

按照时间清理

## 删除2022-09-21 18:09:00之前的binlog日志
mysql> purge binary logs before '2022-09-21 18:09:00';
Query OK, 0 rows affected, 1 warning (0.02 sec)

## 删除前
[root@zxy_slave1 mysql]# vim mysql-bin.
mysql-bin.000010  mysql-bin.000012  mysql-bin.000014  mysql-bin.000016  mysql-bin.index
mysql-bin.000011  mysql-bin.000013  mysql-bin.000015  mysql-bin.000017
[root@zxy_slave1 mysql]# ls

## 删除后
[root@zxy_slave1 mysql]# ls
mysql-bin.000017  mysql-bin.index

 十一、Nginx配置转发mysql服务

若mysql在外部无法访问，需要外部访问可通过http转发方式进行访问。

编辑nginx.conf文件

stream {
    upstream mysql{
	server 127.18.33.14:3306;
   }
    server {
       listen 3306;
       proxy_connect_timeout 5s;
       proxy_timeout 5s;
       proxy_pass mysql;
    }
}