标签：mysql

前言

最近买了个滴滴云，在上面装了wordpress，想看到数据库的内容，虽然可以直接用命令行的方式来看，但是实在是不友好，因此想用小海豚在本地连接远程数据库，实现增删改查。

赋予数据库账号远程连接的权限

首先用xshell登陆到vps，进入到数据库命令行：

mysql -u root -p

接着输入密码

执行：

grant all  on 数据库名.* to 用户名@'%' identified by 用户密码;

%代表的是全部IP都可访问。

扩展：

grant用法
    grant 权限 on 数据库.* to 用户名@'登录主机' identified by '密码'
权限：
    常用总结, ALL/ALTER/CREATE/DROP/SELECT/UPDATE/DELETE
数据库：
     *.*                    表示所有库的所有表
     test.*                表示test库的所有表
     test.test_table  表示test库的test_table表     
用户名：
     mysql账户名
登陆主机：
     允许登陆mysql server的客户端ip
     '%'表示所有ip
     'localhost' 表示本机
     '192.168.10.2' 特定IP
密码：
      账户对应的登陆密码

开放VPS的安全组策略

这步很重要，前面我一直连接不上就是因为这个原因，每个VPS厂商都有自己的安全组策略，什么是安全组策略呢，就是开放允许访问的端口，端口不通的话，流量是没办法进出的，那么也就没办法访问了。

那么我们去具体的VPS厂商的网站去更改安全组，开放数据库的端口即可，如果你没特殊设置的话，一般是3306。

mysql数据库导入导出整理。

1. 数据库导入

mysql -uroot -p123456 --default-character-set=utf8 liuyj_ctisd < liuyj_ctisd.sql

2. 数据库导出

导出整个数据库

mysqldump -uroot -p123456 liuyj_ctisd > liuyj_ctisd.sql

导出数据库单表

mysqldump -uroot -p123456 liuyj_ctisd log> liuyj_ctisd_log.sql

导出数据库表结构

mysqldump -uroot -p123456 -d --add-drop-table liuyj_ctisd> liuyj_ctisd.sql

-d  导出空表
--add-drop-table 每个数据表创建之前添加drop数据表语句

3. 问题记录

mysqldump在命令行导出时，如果输入密码，提示如下警告信息（已执行成功，只是会提示警告信息）

Warning: Using a password on the command line interface can be insecure.

3.1.修复方法

• 在命令行导出时，手动输入密码
• 在/etc/mysql/my.cnf文件最后追加

[mysqldump]
user=user_name
password=password

摘要： MySQL的瓶颈，一般分为IO密集型和CPU密集型 CPU出问题的情况比较少，最近就遇到过一次比较大的故障，这个话题后面会有一篇专题介绍 今天主要聊聊IO密集型的应用中，我们应该如何快速定位到是谁占用了IO资源比较多 背景 环境1.

MySQL的瓶颈，一般分为IO密集型和CPU密集型

CPU出问题的情况比较少，最近就遇到过一次比较大的故障，这个话题后面会有一篇专题介绍

今天主要聊聊IO密集型的应用中，我们应该如何快速定位到是谁占用了IO资源比较多

背景

• 环境

1. MySQL 5.7 +
    低版本MySQL这边不再考虑，就像还有使用SAS盘的公司一样，费时费力，MySQL5.7+ 标配
2. InnoDB 存储引擎
3. Centos 6

实战

关于IO的问题，大家能想到的监控工具有哪些

• iostat
• dstat
• iotop

没错，以上都是神器，可以直接用iotop找到占用资源最多的进程

先上一张图

是的，根据这张图，你能发现的就是MySQL的某个io线程占用了比较多的disk资源，然后呢？

然后，就是去MySQL里面去找，有经验的DBA会去看slow log，或者processlist中去查找相关的sql语句

通常情况下，DBA只会一脸茫然的看到一堆MySQL的query语句，一堆slow log里面去分析，有如大海捞针，定位问题繁琐而低效

如果，你使用的是MySQL5.7+ 版本，那么你就会拥有一件神器（说了好多遍了），可以快速而精准的定位问题

• 如何快速定位到IO瓶颈消耗在哪里

iotop + threads

dba:lc> select * from performance_schema.threads where thread_os_id=37012G
*************************** 1. row ***************************
          THREAD_ID: 96
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 15
   PROCESSLIST_USER: dba
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: sbtest
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: query end
   PROCESSLIST_INFO: INSERT INTO sbtest1(k, c, pad) VALUES(25079106, '33858784348-81663287461-16031064329-06006952037-79426243027-69964324491-90950423034-40185804987-62166137368-06259615216', '47186118229-42754
696460-81034599900-41836403072-66805611739'),(24907169, '77074724245-16833049423-38868029911-54850236074-63700733526-39699866447-52646750572-85552352492-59476301007-32196580154', '79013412600-99031855741-696987
96712-65630963686-19653514942'),(24896311, '28403978193-66350947863-03931166713-97714847962-65299790981-39948912629-14070597101-63277652140-34421148430-61801121402', '05239379274-22840441238-37771744512-9234774
1972-52847679847'),(18489383, '89292717216-01584483614-67433536730-45584233994-29817613740-77179131661-10692787267-83942773303-14971155500-36206705010', '55201342831-85536327239-84383935287-06948377235-96437333
726'),(24790463, '99362943588-41160434740-62783664419-16002619743-04761662097-94273988379-52564232648-19738707042-79143532768-89687113917', '09717575620-89781830996-88443720661-19001024583-14971953687'),(2
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: Socket
       THREAD_OS_ID: 37012
1 row in set (0.00 sec)

你看，消耗资源的SQL语句立刻就呈现在你眼前，就是如此高效

好了，以上列出的，还只是全部功能的冰山一角，更多的玩法等待你去解锁。

以上定位的问题也比较的简单，还有一些复杂的IO问题，比如：binlog写入过大、binlog扫描过多、同步线程阻塞、临时表造成的IO过大，等等问题，都可以用此神器一窥究竟

总结

1. MySQL5.7 默默的提供了非常多的实用工具和新特性，需要DBA们去挖掘和探索。将看似平淡无奇的特性挖掘成黑武器，你才能成为那闪着光芒的Top5 MySQLer

2. 工欲善其事必先利其器

MYSQL数据库适用场景广泛，相较于Oracle、DB2性价比更高，Web网站、日志系统、数据仓库等场景都有MYSQL用武之地，但是也存在对于事务性支持不太好（MySQL 5.5版本开始默认引擎才是InnoDB事务型）、存在多个分支、读写效率瓶颈等问题。

所以如何用好MYSQL变得至关重要，一方面需要通过MYSQL优化找出系统读写瓶颈，提高数据库性能；另一方面需要合理涉及数据结构、调整参数，以提高用户操作响应；同时还有尽可能节省系统资源，以便系统可以提供更大负荷的服务。本文将为大家介绍腾讯云团队是如何对Mysql进行内核级优化的思路和经验。

早期的CDB主要基于开源的Oracle MySQL分支，侧重于优化运维和运营的OSS系统。在腾讯云，因为用户数的不断增加，对CDB for MySQL提出越来越高的要求，腾讯云CDB团队针对用户的需求和业界发展的技术趋势，对CDB for MySQL分支进行深度的定制优化。优化重点围绕内核性能、内核功能和外围OSS系统三个维度展开，具体的做法如下：

一.内核性能的优化

由于腾讯云上的DB基本都需要跨园区灾备的特性，因此CDB for MySQL的优化主要针对主从DB部署在跨园区网络拓扑的前提下，重点去解决真实部署环境下的性能难题。经过分析和调研，我们将优化的思路归纳为：“消除冗余I/O、缩短I/O路径和避免大锁竞争”。以下是内核性能的部分案例：

1.主备DB间的复制优化

问题分析

如上图所示，在原生MySQL的复制架构中，Master侧通过Dump线程不断发送Binlog事件给Slave的I/O线程，Slave的I/O线程在接受到Binlog事件后，有两个主要的动作：

• 写入到Relay Log中，这个过程会和Slave SQL线程争抢保护Relay Log的锁。
• 更新复制元数据(包含Master的位置等信息)。

优化方法

经过分析，我们的优化策略是：

• Slave I/O线程和Slave SQL线程是典型的单写单读生产者-消费者模型，是可以做到无锁设计的；因此实现思路就是SlaveI/O线程在每次写完数据后，原子更新Relay Log的长度信息，Slave SQL线程读取RelayLog的时以长度信息为边界。这样就将原本竞争激烈的Relay Log锁化解为无锁；
• 由于Binlog事件中的GTID(Global Transaction Identifier)和DB事务是一一对应的关系，所以RelayLog中的数据本身已经包含了所需要的复制元数据，所以我们可以不写Master info文件，消除了冗余的文件I/O；
• 于DB都是以事务为更新粒度的，因为在RelayLog文件I/O上，我们通过合并离散小I/O为事务粒度的大I/O等手段，使磁盘I/O得以大幅提升。

优化效果

如上图所示，经过优化：左图35.79%的锁竞争(futex)已经被完全消除；同压测压力下，56.15%的文件I/O开销被优化到19.16%，Slave I/O线程被优化为预期的I/O密集型线程。

2.主库事务线程和Dump线程间的优化

问题分析

如上图所示，在原生MySQL中多个事务提交线程TrxN和多个Dump线程之间会同时竞争Binlog文件资源的保护锁，多个事务提交线程对Binlog执行写入，多个Dump线程从Binlog文件读取数据并发送给Slave。所有的线程之间是串行执行的！

优化方法

经过分析，我们的优化策略是：

• 将读写分离开来，多个写入的线程还是在锁保护下串行执行，每一个写入线程写入完成后更新当前Binlog的长度信息，多个Dump线程以Binlog文件的长度信息为读取边界，多个Dump线程之间并行执行。以这种方式来让复制拓扑中的Dump线程发送得更快！

效果

经过测试，优化后的内核，不仅提升了事务提交线程的性能，在Dump线程较多的情况下，对主从复制性能有较大提升。

二.主备库交互流程优化

问题分析

如上图所示，在原生MySQL中主备库之间的数据发送和ACK回应是简单的串行执行，在上一个事件ACK回应到达之前，不允许继续发送下一个事件；这个行为在跨园区(RTT 2-3ms)的情况性能非常差，而且也不能很好地利用带宽优势。

优化方法

经过分析，我们的优化策略是：

• 将发送和ACK回应的接收独立到不同的线程中，由于发送和接收都是基于TCP流的传输，所以时序性是有保障的；这样发送线程可以在未收ACK之前继续发送，接受线程收到ACK后唤醒等待的线程执行相应的任务。

效果

根据实际用例测试，优化后的TPS提升为15%左右。

三.内核功能的优化

1. 预留运维帐号连接数配额

在腾讯云上，不时遇到用户APP异常或者BUG从而占满DB的最大连接限制，这是CDB OSS帐号无法登录以进行紧急的运维操作。针对这个现状，我们在MySQL内核单独开辟了一个可配置的连接数配额，即便在上述场景下，运维帐号仍然可以连接到DB进行紧急的运维操作。极大地降低了异常情况下DB无政府状态的风险。该帐号仅有数据库运维管理权限，无法获取用户数据，也保证了用户数据的安全性。

2. 主备强同步

针对一些应用对数据的一致性要求非常高，CDB在MySQL原生半同步的基础上进行了深度优化，确保一个事务在主库上提交之前一定已经复制到至少一个备库上。确保主库宕机时数据的一致性。

四.外围系统的优化

除了以上提到的MySQL内核侧的部分优化，我们也在外围OSS平台进行了多处优化。例如使用异步MySQL ping协议实现大量实例的监控、通过分布式技术来加固原有系统的HA/服务发现和自动扩容等功能、在数据安全/故障切换和快速恢复方面也进行了多处优化。

在此我向大家推荐一个架构学习交流群。交流学习群号：478030634 里面会分享一些资深架构师录制的视频录像：有Spring，MyBatis，Netty源码分析，高并发、高性能、分布式、微服务架构的原理，JVM性能优化、分布式架构等这些成为架构师必备的知识体系。还能领取免费的学习资源，目前受益良多

mysql的UNIX_TIMESTAMP用法

一、UNIX_TIMESTAMP 一般是用于unix的时间戳。

例子一、日期转化为时间戳

SELECT UNIX_TIMESTAMP("2016-07-11")

结果：
— 1468166400

例子二、日期转化为时间戳

SELECT UNIX_TIMESTAMP("2016-07-17 23:59:59")

结果：

— 1468771199

二、FROM_UNIXTIME：表示把UNIX_TIMESTAMP还原成标准的时间格式

SELECT FROM_UNIXTIME(1468166400),FROM_UNIXTIME(1468771199)

结果：

2016-07-11 00:00:00 2016-07-17 23:59:59

MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值，并产生另一个类型的值。两者具体的语法如下：

CAST(value as type);  
CONVERT(value, type);  

就是CAST(xxx AS 类型), CONVERT(xxx,类型)。

mysql> SELECT CAST('3.35' AS signed);  
+------------------------+  
| CAST('3.35' AS signed) |  
+------------------------+  
|                      3 |  
+------------------------+  
1 row in set  

可以转换的类型是有限制的。这个类型可以是以下值其中的一个：

• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

下面举几个例子：

例一

mysql> SELECT CONVERT('23',SIGNED);  
+----------------------+  
| CONVERT('23',SIGNED) |  
+----------------------+  
|                   23 |  
+----------------------+  
1 row in set  

例二

mysql> SELECT CAST('125e342.83' AS signed);  
+------------------------------+  
| CAST('125e342.83' AS signed) |  
+------------------------------+  
|                          125 |  
+------------------------------+  
1 row in set  

例三

mysql> SELECT CAST('3.35' AS signed);  
+------------------------+  
| CAST('3.35' AS signed) |  
+------------------------+  
|                      3 |  
+------------------------+  
1 row in set  

像上面例子一样，将varchar 转为int 用 cast(a as signed)，其中a为varchar类型的字符串。