文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

公司没有 DBA，Mysql 运维自己来

时间：2020-01-16 11:08:46   收藏：0   阅读：46

目录

  一、虚拟机部署
  二、基本运维
  三、配置
  四、常见问题
  五、脚本
  参考资料

如果你的公司有 DBA，那么我恭喜你，你可以无视 Mysql 运维。如果你的公司没有 DBA，那你就好好学两手 Mysql 基本运维操作，行走江湖，防身必备。

环境：CentOS7

版本：

一、虚拟机部署

本文仅介绍 rpm 安装方式

安装 mysql yum 源

官方下载地址：https://dev.mysql.com/downloads/repo/yum/

（1）下载 yum 源

$ wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

（2）安装 yum repo 文件并更新 yum 缓存

$ rpm -ivh mysql80-community-release-el7-1.noarch.rpm

执行结果：

会在 /etc/yum.repos.d/ 目录下生成两个 repo 文件

$ ls | grep mysql
mysql-community.repo
mysql-community-source.repo

更新 yum：

$ yum clean all
$ yum makecache

（3）查看 rpm 安装状态

$ yum search mysql | grep server
mysql-community-common.i686 : MySQL database common files for server and client
mysql-community-common.x86_64 : MySQL database common files for server and
mysql-community-test.x86_64 : Test suite for the MySQL database server
                       : administering MySQL servers
mysql-community-server.x86_64 : A very fast and reliable SQL database server

通过 yum 安装 mysql 有几个重要目录：

# 配置文件
/etc/my.cnf
# 数据库目录
/var/lib/mysql/
# 配置文件
/usr/share/mysql（mysql.server命令及配置文件）
# 相关命令
/usr/bin（mysqladmin mysqldump等命令）
# 启动脚本
/usr/lib/systemd/system/mysqld.service （注册为 systemd 服务）

（4）安装 mysql 服务器

$ yum install mysql-community-server

mysql 服务管理

通过 yum 方式安装 mysql 后，本地会有一个名为 mysqld 的 systemd 服务。

其服务管理十分简便：

# 查看状态
systemctl status mysqld
# 启用服务
systemctl enable mysqld
# 禁用服务
systemctl disable mysqld
# 启动服务
systemctl start mysqld
# 重启服务
systemctl restart mysqld
# 停止服务
systemctl stop mysqld

初始化数据库密码

查看一下初始密码

$ grep "password" /var/log/mysqld.log
2018-09-30T03:13:41.727736Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: %:lt+srWu4k1

执行命令：

mysql -uroot -p<临时密码>

输入临时密码，进入 mysql，如果要修改密码，执行以下指令：

ALTER user ‘root‘@‘localhost‘ IDENTIFIED BY ‘你的密码‘;

注：密码强度默认为中等，大小写字母、数字、特殊符号，只有修改成功后才能修改配置再设置更简单的密码

配置远程访问

CREATE USER ‘root‘@‘%‘ IDENTIFIED BY ‘你的密码‘;
GRANT ALL ON *.* TO ‘root‘@‘%‘;
ALTER USER ‘root‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘你的密码‘;
FLUSH PRIVILEGES;

跳过登录认证

vim /etc/my.cnf

在 [mysqld] 下面加上 skip-grant-tables

作用是登录时跳过登录认证，换句话说就是 root 什么密码都可以登录进去。

执行 systemctl restart mysqld，重启 mysql

二、基本运维

创建用户

CREATE USER ‘username‘@‘host‘ IDENTIFIED BY ‘password‘;

说明：

示例：

CREATE USER ‘dog‘@‘localhost‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘pig‘@‘192.168.1.101_‘ IDENDIFIED BY ‘123456‘;
CREATE USER ‘pig‘@‘%‘ IDENTIFIED BY ‘123456‘;
CREATE USER ‘pig‘@‘%‘ IDENTIFIED BY ‘‘;
CREATE USER ‘pig‘@‘%‘;

授权

命令：

GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘

说明：

示例：

GRANT SELECT, INSERT ON test.user TO ‘pig‘@‘%‘;
GRANT ALL ON *.* TO ‘pig‘@‘%‘;
GRANT ALL ON maindataplus.* TO ‘pig‘@‘%‘;

注意：

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:

GRANT privileges ON databasename.tablename TO ‘username‘@‘host‘ WITH GRANT OPTION;

撤销授权

命令:

REVOKE privilege ON databasename.tablename FROM ‘username‘@‘host‘;

说明:

privilege, databasename, tablename：同授权部分

例子:

REVOKE SELECT ON *.* FROM ‘pig‘@‘%‘;

注意:

假如你在给用户‘pig‘@‘%‘授权的时候是这样的（或类似的）：GRANT SELECT ON test.user TO ‘pig‘@‘%‘，则在使用REVOKE SELECT ON *.* FROM ‘pig‘@‘%‘;命令并不能撤销该用户对 test 数据库中 user 表的SELECT 操作。相反，如果授权使用的是GRANT SELECT ON *.* TO ‘pig‘@‘%‘;则REVOKE SELECT ON test.user FROM ‘pig‘@‘%‘;命令也不能撤销该用户对 test 数据库中 user 表的Select权限。

具体信息可以用命令SHOW GRANTS FOR ‘pig‘@‘%‘; 查看。

更改用户密码

SET PASSWORD FOR ‘username‘@‘host‘ = PASSWORD(‘newpassword‘);

如果是当前登陆用户用:

SET PASSWORD = PASSWORD("newpassword");

示例：

SET PASSWORD FOR ‘pig‘@‘%‘ = PASSWORD("123456");

备份与恢复

Mysql 备份数据使用 mysqldump 命令。

mysqldump 将数据库中的数据备份成一个文本文件，表的结构和表中的数据将存储在生成的文本文件中。

备份：

备份一个数据库

语法：

mysqldump -u <username> -p <database> [<table1> <table2> ...] > backup.sql

备份多个数据库

mysqldump -u <username> -p --databases <database1> <database2> ... > backup.sql

备份所有数据库

mysqldump -u <username> -p --all-databases > backup.sql

恢复一个数据库

Mysql 恢复数据使用 mysqldump 命令。

语法：

mysql -u <username> -p <database> < backup.sql

恢复所有数据库

mysql -u <username> -p --all-databases < backup.sql

卸载

（1）查看已安装的 mysql

$ rpm -qa | grep -i mysql
perl-DBD-MySQL-4.023-6.el7.x86_64
mysql80-community-release-el7-1.noarch
mysql-community-common-8.0.12-1.el7.x86_64
mysql-community-client-8.0.12-1.el7.x86_64
mysql-community-libs-compat-8.0.12-1.el7.x86_64
mysql-community-libs-8.0.12-1.el7.x86_64

（2）卸载 mysql

$ yum remove mysql-community-server.x86_64

主从节点部署

假设需要配置一个主从 Mysql 服务器环境

主节点上的操作

（1）修改配置并重启

执行 vi /etc/my.cnf ，添加如下配置：

[mysqld]
server-id=1
log_bin=/var/lib/mysql/binlog

修改后，重启 mysql 使配置生效：

$ systemctl restart mysql

（2）创建用于同步的用户

进入 mysql 命令控制台：

$ mysql -u root -p
Password:

执行以下 SQL：

-- 创建 slave1 用户，并指定该用户只能在主机 192.168.8.11 上登录
mysql> CREATE USER ‘slave1‘@‘192.168.8.11‘ IDENTIFIED WITH mysql_native_password BY ‘密码‘;
-- 为 slave1 赋予 REPLICATION SLAVE 权限
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘slave1‘@‘192.168.8.11‘;
-- 刷新授权表信息
mysql> FLUSH PRIVILEGES;

（3）加读锁

为了主库与从库的数据保持一致，我们先为 mysql 加入读锁，使其变为只读。

mysql> FLUSH TABLES WITH READ LOCK;

（4）查看主节点状态

mysql> show master status;
+------------------+----------+--------------+---------------------------------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            | Executed_Gtid_Set |
+------------------+----------+--------------+---------------------------------------------+-------------------+
| mysql-bin.000001 |     4202 |              | mysql,information_schema,performance_schema |                   |
+------------------+----------+--------------+---------------------------------------------+-------------------+
1 row in set (0.00 sec)

注意：需要记录下 File 和 Position，后面会用到。

（5）导出 sql

$ mysqldump -u root -p --all-databases --master-data > dbdump.sql

（6）解除读锁

mysql> UNLOCK TABLES;

（7）将 sql 远程传送到从节点上

$ scp dbdump.sql root@192.168.8.11:/home

从节点上的操作

（1）修改配置并重启

执行 vi /etc/my.cnf ，添加如下配置：

[mysqld]
server-id=2
log_bin=/var/lib/mysql/binlog

修改后，重启 mysql 使配置生效：

$ systemctl restart mysql

（2）导入 sql

$ mysql -u root -p < /home/dbdump.sql

（3）在从节点上建立与主节点的连接

进入 mysql 命令控制台：

$ mysql -u root -p
Password:

执行以下 SQL：

-- 停止从节点服务
mysql> STOP SLAVE;

mysql> CHANGE MASTER TO
    -> MASTER_HOST=‘192.168.8.10‘,
    -> MASTER_USER=‘slave1‘,
    -> MASTER_PASSWORD=‘密码6‘,
    -> MASTER_LOG_FILE=‘binlog.000001‘,
    -> MASTER_LOG_POS=4202;

MASTER_LOG_FILE 和 MASTER_LOG_POS 参数要分别与 show master status 指令获得的 File 和 Position 属性值对应。

（4）启动 slave 进程

mysql> start slave;

（5）查看主从同步状态

mysql> show slave status\G;

说明：如果以下两项参数均为 YES，说明配置正确。

（6）将从节点设为只读

mysql> set global read_only=1;
mysql> set global super_read_only=1;
mysql> show global variables like "%read_only%";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | ON    |
| super_read_only       | ON    |
| transaction_read_only | OFF   |
+-----------------------+-------+

注：设置 slave 服务器为只读，并不影响主从同步。

三、配置

大部分情况下，默认的基本配置已经足够应付大多数场景，不要轻易修改 Mysql 服务器配置，除非你明确知道修改项是有益的。

配置文件路径

配置 Mysql 首先要确定配置文件在哪儿。

不同 Linux 操作系统上，Mysql 配置文件路径可能不同。通常的路径为 /etc/my.cnf 或 /etc/mysql/my.cnf 。

如果不知道配置文件路径，可以尝试以下操作：

# which mysqld
/usr/sbin/mysqld
# /usr/sbin/mysqld --verbose --help | grep -A 1 ‘Default options‘
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

配置项语法

Mysql 配置项设置都使用小写，单词之间用下划线或横线隔开（二者是等价的）。

建议使用固定的风格，这样检索配置项时较为方便。

# 这两种格式等价
/usr/sbin/mysqld --auto-increment-offset=5
/usr/sbin/mysqld --auto_increment_offset=5

常用配置项说明

这里介绍比较常用的基本配置，更多配置项说明可以参考：Mysql 服务器配置说明

先给出一份常用配置模板，内容如下：

[mysqld]
# GENERAL
# -------------------------------------------------------------------------------
datadir = /var/lib/mysql
socket  = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306
default_storage_engine = InnoDB
default_time_zone = ‘+8:00‘
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

# LOG
# -------------------------------------------------------------------------------
log_error = /var/log/mysql/mysql-error.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

# InnoDB
# -------------------------------------------------------------------------------
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT

# MyIsam
# -------------------------------------------------------------------------------
key_buffer_size = <value>

# OTHER
# -------------------------------------------------------------------------------
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = <value>
thread_cache = <value>
open_files_limit = 65535

[client]
socket  = /var/lib/mysql/mysql.sock
port = 3306

四、常见问题

Too many connections

现象

尝试连接 Mysql 时，遇到 Too many connections 错误。

原因

数据库连接线程数超过最大值，访问被拒绝。

解决方案

如果实际连接线程数过大，可以考虑增加服务器节点来分流；如果实际线程数并不算过大，那么可以配置 max_connections 来增加允许的最大连接数。需要注意的是，连接数不宜过大，一般来说，单库每秒有 2000 个并发连接时，就可以考虑扩容了，健康的状态应该维持在每秒 1000 个并发连接左右。

（1）查看最大连接数

mysql> show variables like ‘%max_connections%‘;
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| max_connections        | 151   |
| mysqlx_max_connections | 100   |
+------------------------+-------+

（2）查看服务器响应的最大连接数

mysql> show global status like ‘Max_used_connections‘;
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 142   |
+----------------------+-------+
1 row in set (0.00 sec)

（3）临时设置最大连接数

set GLOBAL max_connections=256;

注意：当服务器重启时，最大连接数会被重置。

（4）永久设置最大连接数

修改 /etc/my.cnf 配置文件，在 [mysqld] 添加以下配置：

max_connections=256

重启 mysql 以生效

（5）修改 Linux 最大文件数限制

设置了最大连接数，如果还是没有生效，考虑检查一下 Linux 最大文件数

Mysql 最大连接数会受到最大文件数限制，vim /etc/security/limits.conf，添加 mysql 用户配置

mysql hard nofile 65535
mysql soft nofile 65535

（6）检查 LimitNOFILE

如果是使用 rpm 方式安装 mysql，检查 mysqld.service 文件中的 LimitNOFILE 是否配置的太小。

时区（time_zone）偏差

现象

数据库中存储的 Timestamp 字段值比真实值少了 13 个小时。

原因

查看时区方法：

通过 show variables like ‘%time_zone%‘; 命令查看 Mysql 时区配置：

mysql> show variables like ‘%time_zone%‘;
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | CST    |
| time_zone        | SYSTEM |
+------------------+--------+

解决方案

方案一

mysql> set global time_zone = ‘+08:00‘;
Query OK, 0 rows affected (0.00 sec)

mysql> set time_zone = ‘+08:00‘;
Query OK, 0 rows affected (0.00 sec)

方案二

修改 my.cnf 文件，在 [mysqld] 节下增加 default-time-zone=‘+08:00‘ ，然后重启。

五、脚本

这里推荐我写的几个一键运维脚本，非常方便，欢迎使用：

参考资料

原文：https://www.cnblogs.com/aimei/p/12199813.html

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！