mysql

Posted on In Views: Views:

mysql运维

装卸

1
2
3
4
5
6
sudo apt-get install mysql-server-5.7
service mysql start
service mysql stop
sudo apt-get remove mysql-server-5.7
# 数据存放目录 /var/lib/mysql
# 配置存放目录 /etc/mysql

用户管理

1
2
3
# root用户登录
mysql -u root -p
# 切勿修改root用户和使用root用户进行生产

Adding User Accounts

1
2
3
4
5
6
7
8
9
10
11
12
# e.g.1
CREATE USER 'finley'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'finley'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON database.table TO 'finley'@'localhost' WITH GRANT OPTION;
# e.g.2
CREATE USER 'finley'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'finley'@'%' WITH GRANT OPTION;
# e.g.3
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';
GRANT RELOAD,PROCESS ON *.* TO 'admin'@'localhost';
# e.g.4
CREATE USER 'dummy'@'localhost';

Removing User Accounts

1
DROP USER 'jeffrey'@'localhost';

授权管理

The MySQL Access Privilege System

When Privilege Changes Take Effect

1
2
3
4
# e.g.1
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
# e.g.2
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';

授权用户远程连接

1
2
3
4
5
6
7
8
9
10
# 检查一下3个配置文件是否有host绑定
/etc/mysql/mysql.cnf
/etc/mysql/conf.d/mysql.cnf
/etc/mysql/mysql.conf.d/mysql.cnf
# 注释本地host绑定
bind-address = 127.0.0.1
# 授权用户可连接的host
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
# 刷新授权
FLUSH PRIVILEGES;

数据编码

Character Set Configuration

1
2
# 查看编码设置
show variables like '%character%';

DDL、DML导入脚本

1
2
3
mysql -u user -p
use database
source ~/script.sql

数据备份与恢复

Chapter 7 Backup and Recovery

分为物理备份和逻辑备份两种

物理备份是备份的数据元文件，备份恢复快，适合数据量大，压缩率低，占用空间多，需要快速恢复的场景。具体可参考 MySQL Enterprise Backup Overview

逻辑备份是备份DDL,DML语句，备份恢复慢，适合数据量小，压缩率高，占用空间少，恢复速度要求低的场景

具体选择的策略可参考MySQL Backup in Facebook

逻辑备份具体可选用主从复制的方式，从slave节点上进行备份.

贴个逻辑备份脚本

主从复制

Replication