运维-07-数据库服务-mysql¶

常用命令¶

MySQL 服务常用命令¶

查看数据库服务状态¶

ps -ef | grep mysql
root     13059     1  0 May08 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql
mysql    13666 13059  1 May08 ?        1-09:14:12 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/galera/openstack_18913/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --wsrep_provider=/usr/lib64/galera/libgalera_smm.so --wsrep_on=on --log-error=/data/galera/openstack_18913/data/error.log --open-files-limit=65535 --pid-file=mysql.pid --socket=/tmp/mysql.sock --port=18913 --wsrep_start_position=99c8deb6-49e5-11e8-821c-3e99f2b60939:5220104

输出注解

mysqld_safe 进程	MySQL 守护进程
mysqld 进程	显示 mysql 基本信息	安装目录/usr/local/mysql/配置文件/etc/my.cnf数据目录/data/galera/openstack_18913/data错误日志/data/galera/openstack_18913/data/error.log集群状态wsrep_on=onsock 文件/tmp/mysql.sock

启动数据库服务¶

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

关闭数据库服务¶

/usr/local/mysql/bin/mysqladmin –uroot –pxxxxxx -S /tmp/mysql.sock shutdown

数据库客户端连接¶

Socket 连接（本地连接）

mysql -udba -p  -S /tmp/mysql.sock -P 18913
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9822100
Server version: 10.1.22-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
dba@localhost 16:28:  [(none)]>

TCP/IP 连接（远程连接）

mysql -udba -p -h 10.150.68.71 -P 18913
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9822100
Server version: 10.1.22-MariaDB MariaDB Server
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
dba@10.150.68.70 16:28:  [(none)]>

注意

不要将密码直接输入在命令行里，存在安全风险

数据库表及索引常用命令¶

查看所有数据库¶

dba@localhost 16:38:  [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| cinder             |
| glance             |
| information_schema |
| keystone           |
| mysql              |
| neutron            |
| nova               |
| nova_api           |
| nova_cell0         |
| octavia            |
| performance_schema |
+--------------------+
11 rows in set (0.00 sec)

切换数据库¶

dba@localhost 16:39:  [(none)]> USE databasename;
Database changed

创建数据库¶

mysql> CREATE DATABASE `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| databasename       |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

删除数据库¶

mysql> drop database databasename;
Query OK, 0 rows affected (0.01 sec)

创建表¶

mysql> CREATE TABLE IF NOT EXISTS `tablename`(
    -> `id` INT UNSIGNED AUTO_INCREMENT,
    -> `tablename_title` VARCHAR(100) NOT NULL,
    -> `tablename _author` VARCHAR(40) NOT NULL,
    -> `tablename _date` DATE,
    -> PRIMARY KEY ( `id` )
  -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.07 sec)

创建表原则¶

事项	注解
NOT NULL	最好设置字段的属性为 NOT NULL，但是在操作数据库时如果输入该字段的数据为NULL会报错
AUTO_INCREMENT	定义列自增属性，一般用于主键，数值会自动加1。
PRIMARY KEY	关键字用于定义列为主键,可以使用多列来定义主键，列间以逗号分隔。但是不建议使用多列主键，降低索引性能
ENGINE	设置存储引擎
CHARSET	设置编码
int(11) VS int(21)	两者在存储空间和范围上完全相同，只是显示时补全0的位数不一样
浮点型FLOAT、DOUBLE	会造成精度丢失，要求数据精准情况下避免使用
定点型DECIMAL	高精度的数据类型，常用来存储交易相关的数据
TINYINT或者ENUM	存储、年龄、性别、省份、类型等分类信息
CHAR	存储定长，最大为 255 字符
VARCHAR	存储变长，超过 255 字符后与 text 类似，大多存储格式为溢出页，效率不如 CHAR ，可以根据存储的数据灵活选择

DATE	三字节,如：2015-05-01
TIME	三字节，如：11:12:00
TIMESTAMP	存储范围：1970-01-01 00::00:01 to 2038-01-19 03:14:07
DATETIME	存储范围：1000-01-01 00:00:00 to 9999-12-31 23:59:59

注意

拆分大字段到单独表中，避免范围扫描代价大;
数据库中不推荐存储图片，性能太差;
TIMESTAMP 会根据系统时区进行转换，DATETIME 不会

删除表¶

DROP TABLE tablename;

注意

创建表时需要注意空格造成的语法错误。
安全执行更新：在执行更新前添加 set sql_safe_updates = 1。
谨慎执行 SQL 操作：确认执行环境和 SQL 语句是否正确。

查看所有数据表¶

dba@localhost 16:40:  [cinder]> SHOW TABLES;
+----------------------------+
| Tables_in_cinder           |
+----------------------------+
| attachment_specs           |
| backup_metadata            |
| backups                    |
| cgsnapshots                |
| clusters                   |
| consistencygroups          |
| driver_initiator_data      |
| encryption                 |
| group_snapshots            |
| group_type_projects        |
| group_type_specs           |
| group_types                |
| group_volume_type_mapping  |
| groups                     |
| image_volume_cache_entries |
| messages                   |
| migrate_version            |
| quality_of_service_specs   |
| quota_classes              |
| quota_usages               |
| quotas                     |
| reservations               |
| services                   |
| snapshot_metadata          |
| snapshots                  |
| transfers                  |
| volume_admin_metadata      |
| volume_attachment          |
| volume_glance_metadata     |
| volume_metadata            |
| volume_type_extra_specs    |
| volume_type_projects       |
| volume_types               |
| volumes                    |
| workers                    |
+----------------------------+
35 rows in set (0.00 sec)

查看表相关信息¶

dba@localhost 16:42:  [cinder]> SHOW TABLE STATUS LIKE 'backups'\G
*************************** 1. row ***************************
   Name: backups
   Engine: InnoDB
   Version: 10
   Row_format: Compact
   Rows: 318
   Avg_row_length: 515
   Data_length: 163840
   Max_data_length: 0
   Index_length: 0
   Data_free: 0
   Auto_increment: NULL
   Create_time: 2018-06-08 09:42:15
   Update_time: NULL
   Check_time: NULL
   Collation: utf8_general_ci
   Checksum: NULL
   Create_options:
   Comment:
   1 row in set (0.00 sec)

查看表索引¶

dba@localhost 16:42:  [cinder]> SHOW INDEX FROM backups\G
*************************** 1. row ***************************
Table: backups
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 318
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)

查看表字段¶

dba@localhost 16:44:  [cinder]> SHOW COLUMNS FROM backups;
+-----------------------+--------------+------+-----+---------+-------+
| Field                 | Type         | Null | Key | Default | Extra |
+-----------------------+--------------+------+-----+---------+-------+
| created_at            | datetime     | YES  |     | NULL    |       |
| updated_at            | datetime     | YES  |     | NULL    |       |
| deleted_at            | datetime     | YES  |     | NULL    |       |
| deleted               | tinyint(1)   | YES  |     | NULL    |       |
| id                    | varchar(36)  | NO   | PRI | NULL    |       |
| volume_id             | varchar(36)  | NO   |     | NULL    |       |
| user_id               | varchar(255) | YES  |     | NULL    |       |
| project_id            | varchar(255) | YES  |     | NULL    |       |
| host                  | varchar(255) | YES  |     | NULL    |       |
| availability_zone     | varchar(255) | YES  |     | NULL    |       |
| display_name          | varchar(255) | YES  |     | NULL    |       |
| display_description   | varchar(255) | YES  |     | NULL    |       |
+-----------------------+--------------+------+-----+---------+-------+

查看创建表语句¶

dba@localhost 16:45:  [cinder]> SHOW CREATE TABLE backups\G
*************************** 1. row ***************************
Table: backups
Create Table: CREATE TABLE `backups` (
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`deleted_at` datetime DEFAULT NULL,
`deleted` tinyint(1) DEFAULT NULL,
`id` varchar(36) NOT NULL,
`volume_id` varchar(36) NOT NULL,
`user_id` varchar(255) DEFAULT NULL,
`project_id` varchar(255) DEFAULT NULL,
`host` varchar(255) DEFAULT NULL,
`availability_zone` varchar(255) DEFAULT NULL,
`display_name` varchar(255) DEFAULT NULL,
`display_description` varchar(255) DEFAULT NULL,
`container` varchar(255) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
`fail_reason` varchar(255) DEFAULT NULL,
`service_metadata` varchar(255) DEFAULT NULL,
`service` varchar(255) DEFAULT NULL,
`size` int(11) DEFAULT NULL,
`object_count` int(11) DEFAULT NULL,
`parent_id` varchar(36) DEFAULT NULL,
`temp_volume_id` varchar(36) DEFAULT NULL,
`temp_snapshot_id` varchar(36) DEFAULT NULL,
`num_dependent_backups` int(11) DEFAULT NULL,
`snapshot_id` varchar(36) DEFAULT NULL,
`data_timestamp` datetime DEFAULT NULL,
`restore_volume_id` varchar(36) DEFAULT NULL,
`volume_type_id` varchar(255) DEFAULT NULL,
`real_size` bigint(20) DEFAULT NULL,
`encryption_key_id` varchar(36) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

添加主键¶

alter table backups  add primary key (id);

添加唯一索引¶

alter table backups  add unique key unq_idx_volume_type_id (volume_type_id);

创建单列索引¶

create index idx_volume_type_id on backups (volume_type_id);

创建联合索引¶

create index idx_real_parent on backups (real_size, parent_id);

删除索引¶

drop index idx_real_parent on backups;

小技巧

依据 WHERE 查询条件建立索引；排序 order by, group by, distinct 字段添加索引

注意

选择性很差的字段通常不适合创建单列索引；依据条件查询是需要注意类型匹配，避免索引失效

警告

索引越多，对表的插入和索引字段修改就越慢，事务越长，代价越高，需要合理添加索引，避免冗余

字符集常用命令¶

查看字符集¶

dba@localhost 13:53:  [(none)]> show variables like 'character%';
+--------------------------+-----------------------------------------------------------------+
| Variable_name            | Value                                                           |
+--------------------------+-----------------------------------------------------------------+
| character_set_client     | utf8                                                            |
| character_set_connection | utf8                                                            |
| character_set_database   | utf8                                                            |
| character_set_filesystem | binary                                                          |
| character_set_results    | utf8                                                            |
| character_set_server     | utf8                                                            |
| character_set_system     | utf8                                                            |
| character_sets_dir       | /usr/local/mariadb-10.1.22-linux-systemd-x86_64/share/charsets/ |
+--------------------------+-----------------------------------------------------------------+
8 rows in set (0.00 sec)

设置数据库字符集¶

create database mydb character set utf8;

修改单个表的字符集¶

alter database test default character set = utf8;

不同字符集占用存储比较¶

编码输入的字符串	中国	China
gbk(双字节)	varchar(2)/4 bytes	varchar(5)/5 bytes
utf8(三字节)	varchar(2)/6 bytes	varchar(5)/5 bytes
utf8mb4(四字节)	varchar(2) 不一定	varchar(5)/5 bytes

注解

对于 utf8mb4 占用四字节但是并不绝对。如果在 utf8 可以覆盖到的范围则仍然占用 3 字节。 utf8mb4 优势应用场景是存储 emoji 表情，要求 MySQL 版本 > 5.5.3，DBC驱动版本 > 5.1.13，库和表的编码设为 utf8mb4

事务常用命令¶

MySQL 事务处理主要有两种方法¶

1、用 BEGIN 或者 START TRANSACTION, ROLLBACK, COMMIT 来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

注意

autocommit 可以在 session 级别设置；
每个 DML 操作都自动提交；
DDL 永远都是自动提交，无法通过 rollback 回滚

事务示例¶

start transaction;   -- 开启一个事务
begin;   -- 或者使用(非标准sql)
insert into t values (1, 1, 1);
commit;   -- 事务结束，插入成功

begin;
insert into t values (2, 1, 1);
insert into t values (3, 1, 1);
insert into t values (4, 1, 1);
rollback;   -- 事务结束，没有插入数据

begin;
insert into t values (1, 1, 1);
savepoint a1;
insert into t values (2, 1, 1);
rollback to a1;    -- 回滚到指定的保存点
commit;

查看当前会话隔离级别¶

dba@localhost 14:53:  [(none)]> show variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.01 sec)

查看全局变量隔离级别¶

dba@localhost 14:53:  [(none)]> show global variables like 'tx_isolation';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

设置当前会话隔离级别¶

dba@localhost 14:54:  [(none)]> set tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

设置全局隔离级别¶

dba@localhost 14:55:  [(none)]> set  global tx_isolation = 'READ-COMMITTED';
Query OK, 0 rows affected (0.00 sec)

日志常用命令¶

二进制日志开启¶

vim /etc/my.cnf
[mysqld]
log_bin = /data/galera/openstack_18913/binlog/mysql-bin
binlog_format = {ROW|STATEMENT|MIXED}

查看二进制日志列表¶

交互式环境查看

dba@localhost 12:08:  [(none)]> show binary logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000008 |       366 |
| mysql-bin.000009 |       370 |
| mysql-bin.000010 |       385 |
| mysql-bin.000011 |       346 |
| mysql-bin.000012 |       370 |
| mysql-bin.000013 |       366 |
+------------------+-----------+
6 rows in set (0.00 sec)

通过物理文件查看

[root@10e150e68e71 binlog]# ll /data/galera/openstack_18913/binlog/
total 28
-rw-r----- 1 mysql mysql 366 Apr 27 14:40 mysql-bin.000008
-rw-rw---- 1 mysql mysql 370 Apr 27 14:40 mysql-bin.000009
-rw-rw---- 1 mysql mysql 385 May  8 14:38 mysql-bin.000010
-rw-rw---- 1 mysql mysql 346 May  8 15:23 mysql-bin.000011
-rw-rw---- 1 mysql mysql 370 May  8 22:01 mysql-bin.000012
-rw-rw---- 1 mysql mysql 366 May  8 22:01 mysql-bin.000013
-rw-rw---- 1 mysql mysql 318 May  8 22:01 mysql-bin.index

查看二进制日志内容¶

[root@10e150e68e71 binlog]#  mysqlbinlog -usys -p --base64-output=DECODE-ROWS /data/galera/openstack_18913/binlog/mysql-bin.000008 --start-datetime='2018-05-08 15:00:00' --stop-datetime='2018-05-08 15:30:00'
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#180427 14:40:05 server id 6918913  end_log_pos 249     Start: binlog v 4, server v 10.1.22-MariaDB created 180427 14:40:05
# Warning: this binlog is either in use or was not closed properly.
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

注解

binlog 为 row 格式时，添加参数 –base64-output=DECODE-ROWS，增加可读性

清除二进制日志¶

根据时间点清除，删除指定时间点之前的日志

[root@mysql-test-172-28-15-121 data]# ll bin.*
-rw-r----- 1 mysql mysql      195 Aug 22 18:58 bin.000001
-rw-r----- 1 mysql mysql     2143 Aug 28 18:01 bin.000002
-rw-r----- 1 mysql mysql      154 Aug 30 09:26 bin.000003
-rw-r----- 1 mysql mysql      154 Aug 30 09:31 bin.000004
-rw-r----- 1 mysql mysql 42127976 Aug 30 09:40 bin.000005
-rw-r----- 1 mysql mysql      303 Aug 30 09:46 bin.000006
-rw-r----- 1 mysql mysql      104 Aug 30 17:23 bin.index

mysql> PURGE BINARY LOGS BEFORE '2018-08-30 00:00:00';

[root@mysql-test-172-28-15-121 data]# ll bin.*
-rw-r----- 1 mysql mysql      154 Aug 30 09:26 bin.000003
-rw-r----- 1 mysql mysql      154 Aug 30 09:31 bin.000004
-rw-r----- 1 mysql mysql 42127976 Aug 30 09:40 bin.000005
-rw-r----- 1 mysql mysql      303 Aug 30 09:46 bin.000006
-rw-r----- 1 mysql mysql       78 Aug 30 17:26 bin.index

根据 binglog 文件清除，删除指定文件之前的日志文件

mysql> PURGE MASTER LOGS TO 'bin.000005';

[root@mysql-test-172-28-15-121 data]# ll bin.*
-rw-r----- 1 mysql mysql 42127976 Aug 30 09:40 bin.000005
-rw-r----- 1 mysql mysql      303 Aug 30 09:46 bin.000006
-rw-r----- 1 mysql mysql       52 Aug 30 17:28 bin.index

配置文件设置定期清除，如下只保留 7 天的日志文件

vim my.cnf
[mysqld]
expire_logs_days = 7

注意

使用 rm 直接删除物理文件可能导致空间不释放，最好使用上述安全模式清除过期 binlog。

刷新二进制文件¶

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000007 | 42153651 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+------------+----------+--------------+------------------+-------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000008 |      154 |              |                  |                   |
+------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置错误日志¶

vim /etc/my.cnf
[mysqld]
log_error = /data/galera/openstack_18913/data/error.log

查看错误日志¶

tail -f /data/galera/openstack_18913/data/error.log

慢日志开启¶

vim /etc/my.cnf
[mysqld]
slow_query_log = 1                 -------开启慢日志
slow_query_log_file = slow.log     -------慢日志名称，不指定绝对路径时默认在数据目录下
log_queries_not_using_indexes = 0  -------不记录记录没有使用索引的sql
long_query_time = 1                -------记录执行时间超过1s的sql

查看慢日志¶

less /data/galera/openstack_18913/data/slow.log
# Time: 180608 13:47:55
# User@Host: [] @  []
# Thread_id: 2  Schema: nova_api  QC_hit: No
# Query_time: 1.014054  Lock_time: 0.000292  Rows_sent: 0  Rows_examined: 0
# Rows_affected: 0
use nova_api;
SET timestamp=1528436875;
ALTER TABLE resource_providers ADD parent_provider_id INTEGER;

备份恢复常用命令¶

逻辑备份 mysqldump¶

--备份所有数据库
mysqldump -h IPaddress -uroot -ppassword --all-databases --single-transaction --master-data=2  --triggers --routines --events  > /backupfile_date.sql

--备份指定数据库
mysqldump -h IPaddress -uroot -ppassword --single-transaction --master-data=2  --triggers --routines --events  nova > /nova_date.sql

--备份单个表
mysqldump -h IPaddress -uroot -ppassword --single-transaction --master-data=2  --triggers --routines --events  nova cells > /nova_ cells_date.sql

参数项	含义
–all-databases	备份所有数据库
–single-transaction	一致性备份
–master-data=2	记录binlog file和pos点，备份文件中该行为注释状态
–triggers	备份触发器
–routines	备份存储过程
–events	备份事件

小技巧

如果运行 mysqldump 没有–quick 或–opt 选项，mysqldump 在转储结果前将整个结果集装入内存。如果转储大数据库可能会出现问题。该选项默认启用，可以用–skip-opt 禁用

逻辑备份恢复¶

方式一：source /backupfile_date.sql
方式二：mysql -h IPaddress -uroot -p databasename < /backupfile_date.sql

物理备份 Xtrabackup¶

--限流、并行备份所有数据库
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=xxxxxx --host=localhost --socket=/tmp/mysql.sock --parallel=8 --throttle=50 --slave-info --no-timestamp $FULLXTRA_BAKDIR  >> $PHYSICAL_XTRA_LOGDIR/xtrabak_$CURRENTTIME.log

--增量备份
/usr/bin/innobackupex --defaults-file=/etc/my.cnf --user=bkpuser --password=xxxxxx --host=localhost --socket=/tmp/mysql.sock --incremental --incremental-dir $INCREMENTAL_BAKDIR

--流方式备份
innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --stream=xbstream /dbbackup/ > /dbbackup/stream.bak

--压缩备份
innobackupex --user=root --password=123456 --defaults-file=/etc/mysql/my.cnf --compress --compress-thread 4 /dbbackup/

参数解释

参数项	含义
–parallel	并行备份
–throttle	限流备份
–incremental	增量备份
–stream	流方式备份

物理备份文件恢复¶

停止 mysql 服务
mv data data_old
mkdir data
chown –R mysql:mysql ./data
innobackupex --user=root --password=aaaaaa  --apply-log $FULLXTRA_BAKDIR
innobackupex --defaults-file=/etc/my.cnf --user=root --password=xx --copy-back $FULLXTRA_BAKDIR
启动 mysql 服务

binlog 日志备份¶

FIRST_BINLOG=`/usr/local/mysql/bin/mysql -h$REMOTE_HOSTIP -u$REMOTE_USER -p$REMOTE_PASS -P$REMOTE_PORT -e "show binary logs;" |grep -vE "(Log_name|File_size)"|awk '{print $1}' |head -n 1`
/usr/local/mysql/bin/mysqlbinlog --raw --read-from-remote-server --stop-never --host=${REMOTE_HOSTIP} --port=${REMOTE_PORT} --user=${REMOTE_USER} --password=${REMOTE_PASS} ${FIRST_BINLOG}

参数解释

参数	含义
$REMOTE_HOSTIP	节点IP
$REMOTE_USER	节点备份用户
$REMOTE_PASS	节点备份用户密码
$REMOTE_PORT	节点端口

权限管理常用命令¶

创建用户¶

方式一：
CREATE USER 'netease'@'localhost' IDENTIFIED BY 'xxxxxx';`
GRANT SELECT ON *.* TO 'netease'@'localhost';
方式二：
GRANT SELECT ON *.* TO 'netease'@'localhost' IDENTIFIED BY 'xxxxxx';`

删除用户¶

drop user 'netease'@'localhost';

查看用户权限¶

show grants;                              ----查看当前用户的权限
show grants for 'username'@'IPaddress';   ----查看其它用户的权限

更改用户的权限¶

revoke select on *.* from netease@'localhost';   ------回收不需要的权限
grant insert on *.* to netease@'localhost';      ------重新赋权

警告

With Grant Option：允许被授予权利的人把这个权利授予其他的人，普通用户不要设置 with grant option 属性 MySQL 默认的 test 库不受权限控制，存储数据存在安全风险，线上数据库不要留 test 库；权限相关的操作不要直接操作表，统一使用 MySQL 命令。禁止简单密码

修改帐号密码¶

修改普通帐号密码

方式一：使用新密码通过 grant 语句重新授权
grant select,insert,delete,update on databasename.* to username@'IPaddress' identified by 'newpassword';

方式二：更改数据库记录，Update User 表的 Password 字段
update user set password=PASSWORD('new_password') where user='username ';
flush privileges;

修改 root 密码

修改参数文件：添加 skip-grant-tables
重启数据库
登录 MySQL 修改密码
mysql>use mysql;
mysql>update user set password=PASSWORD('new_password') where user='root';
mysql>flush privileges;
mysql>quit;

提示

5.7版本以上 MySQL root 帐号密码修改语句：update user set authentication_string=PASSWORD(‘root’) where user=’root’;

帐号连接问题¶

can not connect to mysql server on 'xxxxx'
原因：网络不通
解决：防火墙需要开通端，mysql 服务状态要启动，selinux 设置为非强制

host 'xxxxx' is not allowed to connect to this mysql server
原因：权限表中没有帐号和地址
解决：创建用户并授权

Access denied
原因：权限表中有帐号没有对应地址，没有相应权限
解决：添加用户可访问地址，添加相应权限

权限统计¶

[root@mysql-test-172-28-15-121 data]# mysql -uroot -paaaaaa -NBe "select concat(\"'\",user,\"'\",'@',\"'\",host,\"'\") from mysql.user order by user desc;" |while read userhost ;do mysql -uroot -paaaaaa -NBe "show grants for ${userhost};";done >/tmp/pri.sql
[root@mysql-test-172-28-15-121 data]# less /tmp/pri.sql
GRANT ALL PRIVILEGES ON *.* TO 'zhaochunpu'@'172.%.%.%'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.28.15.%'
GRANT ALL PRIVILEGES ON *.* TO 'root'@'172.28.8.52'
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost'
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost'
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost'
GRANT SUPER ON *.* TO 'mysql.session'@'localhost'
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost'
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost'
GRANT USAGE ON *.* TO 'miaohh'@'172.28.15.%'
GRANT SELECT, INSERT ON `zhao`.* TO 'miaohh'@'172.28.15.%'
GRANT ALL PRIVILEGES ON *.* TO 'dba'@'172.28.15.%'
GRANT SELECT ON *.* TO 'dba'@'localhost'

性能测试常用命令¶

性能测试工具：sysbench
工具安装：yum install –y sysbench

性能测试衡量指标¶

服务吞吐量(TPS, QPS)
服务响应时间
服务并发性

测试步骤¶

---找到工具自带的压测脚本
find / -name oltp.lua

----准备数据
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua  --mysql-host=172.28.15.121 --mysql-port=3308 --mysql-user=dba --mysql-password=dba --db_driver=mysql --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 prepare

---进行压测
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=172.28.15.121 --mysql-port=3308 --mysql-user=dba --mysql-password=dba --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run > /tmp/sysbench121.log

----清理压测数据
sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=172.28.15.121 --mysql-port=3308 --mysql-user=dba --mysql-password=dba --db_driver=mysql cleanup

----查看压测结果
cat /tmp/sysbench121.log

参数解释

参数	含义
/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua	测试类型，openstack业务类型oltp
–oltp_tables_count	测试需要几张表
–oltp-table-size	每张表的大小
–mysql-host	MySQL Host
–mysql-port	MySQL Port
–db_driver	数据库类型
–mysql-user	MySQL User
–mysql-password	MySQL Password
–time	压测时间
–threads	并发连接数
–report-interval	结果输出时间间隔
prepare	准备压测数据
run	开始随机读写测试
cleanup	清理测试数据

Galera 常用命令¶

集群主节点启动¶

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql  --wsrep-new-cluster &

其他节点启动¶

/usr/local/mysql
/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

是否与集群的 wsrep_cluster_state_uuid 一致¶

root@[(none)]> show status like '%wsrep_%_state_uuid%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_local_state_uuid   | eebe303f-5262-11e8-9e80-8a8d79151541 |
| wsrep_cluster_state_uuid | eebe303f-5262-11e8-9e80-8a8d79151541 |
+--------------------------+--------------------------------------+

集群提交次数¶

root@[(none)]> show status like '%wsrep_last_committed%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| wsrep_last_committed | 115562 |
+----------------------+--------+

集群已经提交事务数目，是一个累计值，所有节点应该相等，如果出现不一致，说明事务有延迟，可以用来计算延迟。

节点本身写入数据量¶

root@[(none)]> show status like '%wsrep_replicated%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| wsrep_replicated       | 13579    |
| wsrep_replicated_bytes | 35585864 |
+------------------------+----------+

wsrep_replicated_bytes 为写集的总字节数，可以用于参考节点之间的负载均衡是否平衡，该值较大的节点较为繁忙。

节点复制数据量¶

root@[(none)]> show status like '%wsrep_received%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| wsrep_received       | 11036    |
| wsrep_received_bytes | 35576367 |
+----------------------+----------+

与 wsrep_replicated 对应，表示接收来自其他节点的写集（write set）数目。

队列中事务并发执行占比¶

root@[(none)]> show status like '%wsrep_apply_oooe%';
+------------------+----------+
| Variable_name    | Value    |
+------------------+----------+
| wsrep_apply_oooe | 0.006204 |
+------------------+----------+

值越高意味着效率越高。

节点复制状态¶

root@[(none)]> show status like '%wsrep_local_state%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| wsrep_local_state_uuid    | eebe303f-5262-11e8-9e80-8a8d79151541 |
| wsrep_local_state         | 4                                    |
| wsrep_local_state_comment | Synced                               |
+---------------------------+--------------------------------------+

节点的状态，取值 1-4。

表示正在请求加入集群，速度很快一般看不到这个状态；
表示正在同步数据；
表示当前节点已经加入集群；
表示当前节点与整个集群是完全相同的。

节点地址¶

root@[(none)]> show status like '%wsrep_incoming_addresses%';
+--------------------------+-------------------------- -+
| Variable_name            | Value                      |
+--------------------------+-------------------------- -+
| wsrep_incoming_addresses | IP1:3306,IP2:3306,IP3:3306 |
+--------------------------+----------------------------+

集群中其它节点的地址，多个地址之间用逗号分隔。

节点切换次数¶

root@[(none)]> show status like '%wsrep_cluster_conf_id%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| wsrep_cluster_conf_id | 53    |
+-----------------------+-------+

集群节点关系改变的次数（每次增加/删除都会 +1）。

当前集群节点数¶

root@[(none)]> show status like '%wsrep_cluster_size%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

集群当前状态¶

root@[(none)]> show status like '%wsrep_cluster_status';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+

集群的目前状态，取值：PRIMARY（正常）/NON_PRIMARY（不一致）。

节点状态¶

root@[(none)]> show status like 'wsrep_connected';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| wsrep_connected | ON    |
+-----------------+-------+

节点是否连接到集群，取值：ON/OFF。

节点可用性¶

root@[(none)]> show status like '%wsrep_ready%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+

数据库监控¶

监控项目¶

CPU 的监控¶

通过 iostat 命令进行查看 CPU 的使用率

[root@10e129e169e149 ~]# iostat
Linux 3.10.0-514.26.2.el7.x86_64 (10e129e169e149)       08/30/2018      _x86_64_        (40 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.88    0.00    0.11    0.01    0.00   99.00

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sdd               0.00         0.00         0.00       1836          0
sdc               0.00         0.00         0.00       1836          0
sde               0.00         0.00         0.00       1836          0
sdf               0.00         0.00         0.00       1836          0
sdg               0.00         0.00         0.00       1836          0
sdb              22.56        10.95       419.14   20437220  782021436
sda              21.80         0.16       124.81     293469  232867728

CPU 的性能指标

%user 代表用户态的 CPU 利用率，越低越好。
%idle 代表空闲 CPU 利用率越高越好。
%sys 代表内核态的 CPU 利用率越低越好。
%iowait 代表用来等待的 CPU 时间片，越低越好。

通常建议 %user 不超过 10，不用担心，超过 10 就要引起注意。 %idle 不低于 80%。 %sys 通常不高于 5%。 %iowait 通常不高于 5%。

内存的监控¶

通过 free 命令查看内存的使用情况

[root@10e129e169e149 ~]# free -g
              total        used        free      shared  buff/cache   available
Mem:            503          46         416           0          40         454
Swap:             0           0           0

total 代表机器的总内存大小， used 代表用掉的内存， free 表示空闲的物理内存。

虽然表面上系统是这么表示的，但是真正的已经用的内存数为： used - (buffers + cached) 的值，真正未用到的内存数： free + buffers + cached 的值。

I/O 的监控¶

通过 iostate 命令可以查看系统 I/O 的情况

[root@10e129e169e149 ~]# iostat
Linux 3.10.0-514.26.2.el7.x86_64 (10e129e169e149)       08/30/2018      _x86_64_        (40 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.88    0.00    0.11    0.01    0.00   99.00

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sdd               0.00         0.00         0.00       1836          0
sdc               0.00         0.00         0.00       1836          0
sde               0.00         0.00         0.00       1836          0
sdf               0.00         0.00         0.00       1836          0
sdg               0.00         0.00         0.00       1836          0
sdb              22.56        10.95       419.14   20437220  782021436
sda              21.80         0.16       124.81     293469  232867728

%user : 在用户级别运行所使用的 CPU 的百分比
%nice : nice 操作所使用的 CPU 的百分比
%sys : 在系统级别 (kernel) 运行所使用 CPU 的百分比
%iowait : CPU 等待硬件 I/O 时,所占用 CPU 百分比
%idle : CPU 空闲时间的百分比
tps : 每秒钟发送到的 I/O 请求数
Blk_read /s : 每秒读取的 block 数
Blk_wrtn/s : 每秒写入的 block 数
Blk_read : 读入的 block 总数
Blk_wrtn : 写入的 block 总数

MySQL 的查询吞吐率（QPS）¶

MySQL 的查询吞吐率（QPS）包括 Change DB、Select、Insert、Update、Delete

QPS （MyISAM 引擎）

mysql> SHOW GLOBAL STATUS LIKE 'Questions';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Questions     | 319031 |
+---------------+--------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 26914 |
+---------------+-------+
1 row in set (0.00 sec)

QPS = Questions / Uptime

MySQL 自启动以来的平均 QPS，如果要计算某一时间段内的 QPS，可在高峰期间获取间隔时间 t2 - t1，然后分别计算出 t2 和 t1 时刻的 q 值，QPS = (q2-q1) / (t2-t1)

QPS （InnoDB 引擎）

mysql> SHOW GLOBAL STATUS LIKE 'com_update';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_update    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'com_insert';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_insert    | 2     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'com_delete';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'com_select';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| Com_select    | 192241 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 27024 |
+---------------+-------+
1 row in set (0.00 sec)

QPS = (com_update + com_insert + com_delete + com_select) / uptime

上述方法为计算 InnoDB 的 QPS 方法，如需计算某一时间段内的 QPS，需要计算时间段内增删改查的差值然后除以对应时间段。

MySQL 的每秒事务量（TPS）¶

mysql> SHOW GLOBAL STATUS LIKE 'Com_commit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_commit    | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_rollback  | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Uptime';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Uptime        | 27107 |
+---------------+-------+
1 row in set (0.00 sec)

TPS = (Com_commit + Com_rollback) / Uptime

上述方法为计算 InnoDB 的 TPS 方法，如需计算某一时间段内的 TPS，需要计算时间段内 Com_commit + Com_rollback 的整体差值，然后除以对应时间段。

InnoDB 缓冲池命中率¶

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22870656 |
+----------------------------------+----------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_ahead';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_read_ahead | 0     |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 653   |
+--------------------------+-------+
1 row in set (0.00 sec)

InnoDB 缓冲池命中率 = (Innodb_buffer_pool_read_requests) / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads)

Innodb_buffer_pool_read_requests ：从缓冲池中读取的次数
Innodb_buffer_pool_read_ahead ：预读的页数
Innodb_buffer_pool_reads ：表示从物理磁盘读取的页数

通常 InnoDB 存储引擎的缓冲池的命中率不应该小于 99.9%。如果该值小于 99.9%，建议就应该增大 innodb_buffer_pool_size 的值了，该值一般设置为内存总大小的 75%-85%。

MySQL 查询缓存命中率¶

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_hits';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Qcache_hits   | 0     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_inserts';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Qcache_inserts | 0     |
+----------------+-------+
1 row in set (0.00 sec)

MySQL查询缓存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

Qcache_hits ：Query Cache 命中次数
Qcache_inserts ：向 Query Cache 中插入新的 Query Cache 的次数，也就是没有命中的次数

上述计算方法可以得到 MySQL 查询缓存的命中率，命中率越高，查询速率越高。

MySQL 查询缓存内存使用率¶

mysql> SHOW VARIABLES LIKE  "Query_cache_size";
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
+------------------+---------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Qcache_free_memory';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| Qcache_free_memory | 1031832 |
+--------------------+---------+
1 row in set (0.00 sec)

MySQL查询缓存内存使用率 = (Query_cache_size – Qcache_free_memory) / Query_cache_size * 100%

Query_cache_size ：配置的缓存内存大小
Qcache_free_memory ：目前还处于空闲状态的 Query Cache 内存总量

上述方法可以计算出 MySQL 查询缓存内存使用率

MySQL 连接缓存命中率¶

mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_created | 9     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections   | 2416  |
+---------------+-------+
1 row in set (0.00 sec)

线程缓存命中率 = 1 - Threads_created / Connections

Threads_created ：总共被创建的线程的个数
Connections ：试图连接到(不管是否成功) MySQL 服务器的连接数。

Binlog cache 命中率¶

mysql> SHOW GLOBAL STATUS LIKE 'Binlog_cache_use';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Binlog_cache_use | 3     |
+------------------+-------+
1 row in set (0.01 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Binlog_cache_disk_use';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Binlog_cache_disk_use | 1     |
+-----------------------+-------+
1 row in set (0.00 sec)

Binlog cache命中率 = (Binlog_cache_use) / ( Binlog_cache_use + Binlog_cache_disk_use) 这个值一般要大于 98%

Binlog_cache_disk_use ：binlog_cache_size 设计的内存不足导致缓存二进制日志用到了临时文件的次数。
Binlog_cache_use :用 binlog_cache_size 缓存的次数。

当对应的 Binlog_cache_disk_use 值比较大的时候我们可以考虑适当的调高 binlog_cache_size 对应的值。

Innodb_log_buffer_size 的大小¶

mysql> SHOW VARIABLES LIKE 'innodb_log_buffer_size';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+
1 row in set (0.00 sec)
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_log_waits';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 0     |
+------------------+-------+
1 row in set (0.00 sec)

Innodb_log_waits 表示因 log buffer 不足导致等待的次数，如果该值不为 0，可以适当增大 innodb_log_buffer_size 的值。

Innodb_buffer_pool_wait_free¶

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name                | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0     |
+------------------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.01 sec)

Innodb_buffer_pool_wait_free 值不为 0 表示 buffer pool 没有空闲的空间了，可能原因是 innodb_buffer_pool_size 设置太大，可以适当减少该值。

MySQL 表统计锁定¶

mysql> SHOW GLOBAL STATUS LIKE 'Table_locks_waited';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Table_locks_waited | 0     |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Table_locks_immediate';
+-----------------------+--------+
| Variable_name         | Value  |
+-----------------------+--------+
| Table_locks_immediate | 103393 |
+-----------------------+--------+
1 row in set (0.01 sec)

MySQL 表统计锁定 = Table_locks_waited / Table_locks_immediate

Table_locks_waited ：不能马上获得锁的数据
Table_locks_immediate ：马上获的锁的查询数目

MySQL 表统计锁定趋向于 0，如果值比较大则表示系统的锁阻塞情况比较严重。

Slow_queries 慢查询数¶

mysql> SHOW GLOBAL STATUS LIKE 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

Slow_queries 该值表示 MySQL 启动以来的慢查询个数，即执行时间超过 long_query_time 的次数，可根据 Slow_queries / uptime 的比值判断单位时间内的慢查询个数，进而判断系统的性能。

MySQL 并发连接数¶

mysql> SHOW GLOBAL STATUS LIKE 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 3     |
| Threads_connected | 6     |
| Threads_created   | 9     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

max_connections :数据库当前设置的最大连接数。
Threads_connected :当前连接数，跟 show processlist 结果相同。
Threads_running :当前活跃的连接数。

Galera 监控¶

Galera 集群主要有以下参数需要监控

集群完整性检查¶

监控节点是否与集群的 wsrep_cluster_state_uuid 一致

root@[(none)]> show status like '%wsrep_%_state_uuid%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_local_state_uuid   | eebe303f-5262-11e8-9e80-8a8d79151541 |
| wsrep_cluster_state_uuid | eebe303f-5262-11e8-9e80-8a8d79151541 |
+--------------------------+--------------------------------------+

监控节点切换次数

root@[(none)]> show status like '%wsrep_cluster_conf_id%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| wsrep_cluster_conf_id | 53    |
+-----------------------+-------+

集群节点关系改变的次数（每次增加/删除都会 +1）。

监控当前集群节点数

root@[(none)]> show status like '%wsrep_cluster_size%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| wsrep_cluster_size | 3     |
+--------------------+-------+

监控集群当前状态

root@[(none)]> show status like '%wsrep_cluster_status';
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| wsrep_cluster_status | Primary |
+----------------------+---------+

集群的目前状态，取值：PRIMARY（正常）/NON_PRIMARY（不一致）。

节点状态检查¶

监控节点可用性

root@[(none)]> show status like '%wsrep_ready%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wsrep_ready   | ON    |
+---------------+-------+

节点是否可用，取值：ON/OFF。

监控节点状态

root@[(none)]> show status like 'wsrep_connected';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| wsrep_connected | ON    |
+-----------------+-------+

节点是否连接到集群，取值：ON/OFF。

监控节点复制状态

root@[(none)]> show status like '%wsrep_local_state%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| wsrep_local_state_uuid    | eebe303f-5262-11e8-9e80-8a8d79151541 |
| wsrep_local_state         | 4                                    |
| wsrep_local_state_comment | Synced                               |
+---------------------------+--------------------------------------+

节点的状态，取值 1-4。

表示正在请求加入集群，速度很快一般看不到这个状态；
表示正在同步数据；
表示当前节点已经加入集群；
表示当前节点与整个集群是完全相同的。

复制健康检查¶

复制停止时间

root@[(none)]> show status like "wsrep_flow_control_paused";
+---------------------------+----------+
| Variable_name             | Value    |
+---------------------------+----------+
| wsrep_flow_control_paused | 0.000248 |
+---------------------------+----------+

有多少事务可以并行应用处理

root@[(none)]> show status like "wsrep_cert_deps_distance";
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| wsrep_cert_deps_distance | 93.898534 |
+--------------------------+-----------+

该节点已经停止复制的次数

root@[(none)]> show status like "wsrep_flow_control_sent";
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| wsrep_flow_control_sent | 0     |
+-------------------------+-------+

slave 事务队列的平均长度

root@[(none)]> show status like "wsrep_local_recv_queue_avg";
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| wsrep_local_recv_queue_avg | 0.000358 |
+----------------------------+----------+

检测慢网络问题¶

网络平均延迟

root@[(none)]> show status like "wsrep_local_send_queue_avg";
+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| wsrep_local_send_queue_avg | 0.010515 |
+----------------------------+----------+

冲突或死锁的数目¶

监控集群提交次数

root@[(none)]> show status like '%wsrep_last_committed%';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| wsrep_last_committed | 115562 |
+----------------------+--------+

集群已经提交事务数目，是一个累计值，所有节点应该相等，如果出现不一致，说明事务有延迟。

同步过程中节点认证失败计数

root@[(none)]> show status like "wsrep_local_cert_failures";
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| wsrep_local_cert_failures | 5397  |
+---------------------------+-------+

强制放弃的事务数量

root@[(none)]> show status like "wsrep_local_bf_aborts";
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| wsrep_local_bf_aborts | 1     |
+-----------------------+-------+

监控节点本身写入数据量

root@[(none)]> show status like '%wsrep_replicated%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| wsrep_replicated       | 13579    |
| wsrep_replicated_bytes | 35585864 |
+------------------------+----------+

wsrep_replicated_bytes 为写集的总字节数，可以用于参考节点之间的负载均衡是否平衡，该值较大的节点较为繁忙。

MySQL 主从复制监控¶

查看主从同步状态¶

主从监控核心信息主要根据如下命令取出来相应状态

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 10.10.11.119
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000009
          Read_Master_Log_Pos: 741
               Relay_Log_File: mysql-test-172-28-15-120-relay-bin.000019
                Relay_Log_Pos: 946
        Relay_Master_Log_File: mysql-bin.000009
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 741
              Relay_Log_Space: 1247
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3306
                  Master_UUID: 69c24acd-7384-11e8-a97c-fa163e1eb670
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: 69c24acd-7384-11e8-a97c-fa163e1eb670:1-39
            Executed_Gtid_Set: 6242edfb-738d-11e8-9943-fa163e69926b:1-4,
69c24acd-7384-11e8-a97c-fa163e1eb670:1-39
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)


mysql> SHOW VARIABLES LIKE 'READ_ONLY';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

主从监控主要监控参数有 Slave_IO_Running、Slave_SQL_Running、Seconds_Behind_Master、read_only

Slave_IO_Running 的状态值为 Yes，说明从库的 IO 线程没有问题，为 No，说明从库已经不再取主库的日志。
Slave_SQL_Running 的状态值为 Yes，说明从库的 SQL 线程没有问题，为 No，说明从库执行 SQL 语句的时候有错误。
Seconds_Behind_Master 的状态值为 0，说明主从复制没有延迟，出现大于 0 的值，说明从库执行的 SQL 落后于主库的时间差。
从库的 read_only 状态值为 ON，说明从库支支持查询，不支持普通账号的变更数据操作，为 OFF，普通账号即刻变更从库数据。

故障处理¶

MySQL 故障处理¶

问题1：MySQL 节点异常 down 机，在机器重启之后，MySQL 服务没办法自动重启¶

问题现象¶

在 MySQL 节点发生异常 down 机，导致 MySQL 服务不是正常关闭，当节点重启以后，MySQL 的服务没办法自动拉起。

处理过程¶

拉起 down 的虚拟机或者恢复 MySQL 所在的物理机。
查看 MySQL 服务，发现未自动启动，使用下面命令启动。

# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf

注解

现有的 OpenStack 环境，MySQL 都是使用二进制方式安装，所以服务启动需要执行如上命令。

后续查看 MySQL 的服务，发现服务已经正常启动。

问题原因¶

节点 down 机，导致 MySQL 服务异常退出，不能自动启动。

问题2：用户 userA 访问 localhost 被拒绝（没有允许通过）¶

问题现象¶

登陆数据库的时候报如下错误

[root@10e129e169e148 ~]# mysql -uroot -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

处理过程¶

输入正确的用户名和密码或者正确的 MySQL 服务器的 IP/主机名。

问题原因¶

造成这个错误一般数据库用户名和密码错误或者 MySQL 服务器不正确。

问题3：达到最大连接数¶

问题现象¶

连接数据库的时候报如下错误

[root@mysql]# mysql -udba -p
Enter password:
ERROR 1040 (HY000): Too many connections

处理过程¶

优化 MySQL 服务器的配置，可根据实际情况增大 max_connections 值,减小 wait_timeout 值

问题原因¶

连接数超过了 mysql 设置的值，与 max_connections 和 wait_timeout 都有关系。wait_timeout 的值越大，连接的空闲等待就越长，这样就会造成当前连接数越大。

问题4：用客户端导入数据的时候报 1153 错误¶

问题现象¶

在客户端导入数据库数据的时候报如下错误，数据库终止了数据导入的操作。

ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

处理过程¶

一般情况下，服务器默认 max-allowed-packet 为 1MB，客户端的 max-allowed-packet 为 16MB,修改服务器 max-allowed-packet 大小

问题原因¶

在加载数据包时，该数据包大小与配置文件设置的大小不匹配造成的。

问题5：导出数据报 1290 错误¶

问题现象¶

在服务器上面进行表的导出时候报如下错误

mysql> select * from t1 into outfile "t1.txt";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

处理过程¶

查看数据库配置信息

mysql>  show global variables like '%secure%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| require_secure_transport | OFF   |
| secure_auth              | ON    |
| secure_file_priv         | NULL  |
+--------------------------+-------+

发现 secure_file_priv 未配置目录，并且 secure_file_priv 参数不能动态修改，需要在配置文件修改后重启服务。

在 mysql 的配置文件中增加 secure_file_priv = /data/mysql3308/data/ 之后进行数据的导出。

mysql> select * from t1 into outfile "t1.txt";
Query OK, 4194304 rows affected (1.83 sec)

问题原因¶

在导出数据库表文件的时候需要设置导出的目录地址，并且目录地址有 MySQL 的写入权限。

Galera 故障处理¶

问题1：其中一台机器 down 机，服务启动失败¶

问题现象¶

其中一台数据库服务因为主机重启导致服务挂掉，然后服务启动失败。

处理过程¶

启动 down 节点，在 error.log 里面有这样的报错。

WSREP_SST: [INFO] Cleaning the binlog directory /data/galera/openstack_18913/binlog as well (20180828 18:05:28.322)
removed ‘/data/galera/openstack_18913/binlog/mysql-bin.000001’
WSREP_SST: [INFO] Waiting for SST streaming to complete! (20180828 18:05:28.326)
2018-08-28 18:05:28 140640738342656 [Note] WSREP: (e2183df0, 'tcp://0.0.0.0:4567') connection to peer e2183df0 with addr tcp://10.129.178.148:4567 timed o
ut, no messages seen in PT3S
2018-08-28 18:05:28 140640738342656 [Note] WSREP: (e2183df0, 'tcp://0.0.0.0:4567') turning message relay requesting off
WSREP_SST: [ERROR] xtrabackup_checkpoints missing, failed innobackupex/SST on donor (20180828 18:05:36.910)
WSREP_SST: [ERROR] Cleanup after exit with status:2 (20180828 18:05:36.911)
2018-08-28 18:05:36 140640700593920 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'joiner' --address '10.129.178.148' --data
dir '/data/galera/openstack_18913/data/'  --defaults-file '/etc/my.cnf'  --parent '34310' --binlog '/data/galera/openstack_18913/binlog/mysql-bin' : 2 (No
 such file or directory)
2018-08-28 18:05:36 140640700593920 [ERROR] WSREP: Failed to read uuid:seqno and wsrep_gtid_domain_id from joiner script.
2018-08-28 18:05:36 140645796542400 [ERROR] WSREP: SST failed: 2 (No such file or directory)
2018-08-28 18:05:36 140645796542400 [ERROR] Aborting

然后查看正常复制节点日志

WSREP_SST: [ERROR] innobackupex finished with error: 1.  Check /data/galera/openstack_18913/data//innobackup.backup.log (20180828 18:03:52.611)
WSREP_SST: [ERROR] Cleanup after exit with status:22 (20180828 18:03:52.613)
WSREP_SST: [INFO] Cleaning up temporary directories (20180828 18:03:52.616)
2018-08-28 18:03:52 140361923536640 [ERROR] WSREP: Failed to read from: wsrep_sst_xtrabackup-v2 --role 'donor' --address '10.129.178.148:4444/xtrabackup_sst//1' --socket '/tmp/mysql.sock' --datadir '/data/galera/openstack_18913/data/'  --defaults-file '/etc/my.cnf'   --binlog '/data/galera/openstack_18913/binlog/mysql-bin' --gtid 'b5559508-9b7e-11e8-99e7-9a8f2cc26da4:18144963' --gtid-domain-id '0'
2018-08-28 18:03:52 140361923536640 [ERROR] WSREP: Process completed with error: wsrep_sst_xtrabackup-v2 --role 'donor' --address '10.129.178.148:4444/xtrabackup_sst//1' --socket '/tmp/mysql.sock' --datadir '/data/galera/openstack_18913/data/'  --defaults-file '/etc/my.cnf'   --binlog '/data/galera/openstack_18913/binlog/mysql-bin' --gtid 'b5559508-9b7e-11e8-99e7-9a8f2cc26da4:18144963' --gtid-domain-id '0': 22 (Invalid argument)
2018-08-28 18:03:52 140361923536640 [ERROR] WSREP: Command did not run: wsrep_sst_xtrabackup-v2 --role 'donor' --address '10.129.178.148:4444/xtrabackup_sst//1' --socket '/tmp/mysql.sock' --datadir '/data/galera/openstack_18913/data/'  --defaults-file '/etc/my.cnf'   --binlog '/data/galera/openstack_18913/binlog/mysql-bin' --gtid 'b5559508-9b7e-11e8-99e7-9a8f2cc26da4:18144963' --gtid-domain-id '0'

然后去查看 /data/galera/openstack_18913/data/innobackup.backup.log 这个日志，发现 innobackup 备份账号有问题，然后重新授权账号密码，再次启动 down 机节点成功。

问题原因¶

由于配置文件里面备份账号密码配置不正确，导致备份失败，从而不能拉取到数据，down 机节点无法加入集群

问题2：三节点全部 down 机，如何确定数据最新节点¶

问题现象¶

由于某些原因导致集群内机器全部 down 机，重新启动集群时候遇到 Mariadb Galera Cluster 集群无法正常启动，无法确认哪个节点数据最新。

处理过程¶

经分析日志发现 Mariadb Galera Cluster 节点宕机时会在日志中打印出如下信息：

[Note] WSREP: New cluster view: global state: 874d8e7e-5980-11e8-8c23-83493ba049c2:3111, view# -1: non-P
rimary, number of nodes: 0, my index: -1, protocol version 3

对比三个节点日志中的 New cluster view: global state 状态，可知道那个节点中的数据是最新的。用这个最新的节点中的数据以 wsrep_new_cluster 的方式将数据拉起即可。

[mysql@test45 logs]$ grep "New cluster view" mariadb.log |awk  -F: 'END { print $1":"$2":"$3 $6":"$7}'
180518 14:59:00 [Note] WSREP 874d8e7e-5980-11e8-8c23-83493ba049c2:<span style="color:#ff0000;"><strong>2840</strong></span>, view# 3

[mysql@test44 logs]$ grep "New cluster view" mariadb.log |awk  -F: 'END { print $1":"$2":"$3 $6":"$7}'
180518 15:55:03 [Note] WSREP 874d8e7e-5980-11e8-8c23-83493ba049c2:<strong><span style="color:#ff0000;">3068</span></strong>, view# 4

[mysql@test43 logs]$ grep "New cluster view" mariadb.log |awk  -F: 'END { print $1":"$2":"$3 $6":"$7}'
180518 15:55:41 [Note] WSREP 874d8e7e-5980-11e8-8c23-83493ba049c2:<strong><span style="color:#ff0000;">3111</span></strong>, view# -1

根据红色字体标记的数字得知三个节点数据的状态：3111>3068>2840

所以 [mysql@test43 logs]$ 上的 874d8e7e-5980-11e8-8c23-83493ba049c2:3111 节点上的数据是最新的。修改 [mysql@test43] 节点 $datadir 目录下的 grastate.dat 文件。将 seqno 设置为 3111。 safe_to_bootstrap 设置为 1。

[mysql@test43 script]$more grastate.dat
# GALERA saved state
version: 2.1
uuid:    874d8e7e-5980-11e8-8c23-83493ba049c2
seqno:   -1
safe_to_bootstrap: 0

修改为：
[mysql@test43 data]$  vim ../data/grastate.dat
# GALERA saved state
version: 2.1
uuid:    874d8e7e-5980-11e8-8c23-83493ba049c2
seqno:   3111
safe_to_bootstrap: 1

在 [mysql@test43] 节点以 wsrep_new_cluster 的方式启动 MySQL。

mysqld_safe --defaults-file=/data/conf/my.cnf  --user=mysql --wsrep_new_cluster &

2.以正常方式启动其它两个节点。

mysqld_safe --defaults-file=/data/conf/my.cnf  --user=mysql&

问题原因¶

当集群出现异常 down 机的时候，会导致集群内部无法确认哪一个为最新节点，需要手动的寻找最新点位的节点，手动指定主节点启动集群。

运维-07-数据库服务-mysql¶

常用命令¶

MySQL 服务常用命令¶

查看数据库服务状态¶

启动数据库服务¶

关闭数据库服务¶

数据库客户端连接¶

数据库表及索引常用命令¶

查看所有数据库¶

切换数据库¶

创建数据库¶

删除数据库¶

创建表¶

创建表原则¶

删除表¶

查看所有数据表¶

查看表相关信息¶

查看表索引¶

查看表字段¶

查看创建表语句¶

添加主键¶

添加唯一索引¶

创建单列索引¶

创建联合索引¶

删除索引¶

字符集常用命令¶

查看字符集¶

设置数据库字符集¶

修改单个表的字符集¶

不同字符集占用存储比较¶

事务常用命令¶

MySQL 事务处理主要有两种方法¶

事务示例¶

查看当前会话隔离级别¶

查看全局变量隔离级别¶

设置当前会话隔离级别¶

设置全局隔离级别¶

日志常用命令¶

二进制日志开启¶

查看二进制日志列表¶

查看二进制日志内容¶

清除二进制日志¶

刷新二进制文件¶

配置错误日志¶

查看错误日志¶

慢日志开启¶

查看慢日志¶

备份恢复常用命令¶

逻辑备份 mysqldump¶

逻辑备份恢复¶

物理备份 Xtrabackup¶

物理备份文件恢复¶

binlog 日志备份¶

权限管理 常用命令¶

创建用户¶

删除用户¶

查看用户权限¶

更改用户的权限¶

修改帐号密码¶

帐号连接问题¶

权限统计¶

性能测试常用命令¶

性能测试衡量指标¶

测试步骤¶

Galera 常用命令¶

集群主节点启动¶

其他节点启动¶

是否与集群的 wsrep_cluster_state_uuid 一致¶

集群提交次数¶

节点本身写入数据量¶

节点复制数据量¶

队列中事务并发执行占比¶

节点复制状态¶

节点地址¶

节点切换次数¶

当前集群节点数¶

集群当前状态¶

节点状态¶

节点可用性¶

数据库监控¶

权限管理常用命令¶