MySQL Basic Configuration

安装

wget http://dev.mysql.com/get/mysql-apt-config_0.8.1-1_all.deb
sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb
sudo apt-get update
sudo apt-get install mysql-server

sudo mysql_secure_installation

变更存储位置

为 Azure VM 添加磁盘

https://docs.microsoft.com/en-us/azure/virtual-machines/virtual-machines-linux-classic-attach-disk

修改 Data 目录

https://www.digitalocean.com/community/tutorials/how-to-move-a-mysql-data-directory-to-a-new-location-on-ubuntu-16-04

数据库维护

# 创建库
CREATE DATABASE `dbname` CHARACTER SET utf8 COLLATE utf8_bin;

权限控制

添加用户

# 添加仅允许从 localhost 访问的帐户
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_pass';

# 允许 admin 用户从任何主机全操作权限访问所有库
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;

# 撤销 admin 用户从任何主机全操作权限访问所有库
REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';

# 查看用户列表
SELECT host, user FROM mysql.user;

# 查看用户权限
SHOW GRANTS FOR [user];

日志、备份与恢复

Binlog

向 /etc/mysql/mysql.conf.d/mysqld.cnf 中添加以下内容以启动 binlog:

log-bin          = /data/mysql/binlog/log
expire-logs-days = 30
max-binlog-size  = 1024M
server-id        = 1

mysqldump

# dump 
$ mysqldump -u root -p databasename > backupfilename.sql

# recover
$ mysql -u root -p databasename < backupfilename.sql