CentOS7 安装 MySQL

系统环境

centOS7.5 64位

开始部署

1. 添加mysql yum源

在centOS上直接使用yum install mysql安装,最后安装上的会是MariaDB,所以要先添加mysql yum

rpm -Uvh https://repo.mysql.com//mysql80-community-release-el7-2.noarch.rpm

2. 安装(如果要安装最新版,可直接开始安装)

查看yum源中所有Mysql版本

yum repolist all | grep mysql

此时的最新版本是mysql8.0,把它禁用掉

yum-config-manager --disable mysql80-community

mysql5.7是我要安装的版本,启用mysql5.7

yum-config-manager --enable mysql57-community

检查刚才的配置是否生效

yum repolist enabled | grep mysql

开始安装

yum install mysql-community-server

3. 启动服务

service mysqld start

启动完成之后检查mysql状态,

service mysqld status

查看临时密码

grep 'temporary password' /var/log/mysqld.log

登录

mysql -uroot -p

MySQL备份恢复:从Xtrabackup完整备份中恢复单个表

现在大多数同学在线上采取的备份策略都是xtrabackup全备+binlog备份,那么当某天某张表意外的删除,那么如何快速从xtrabackup全备中恢复呢?从MySQL 5.6版本开始,支持可传输表空间(Transportable Tablespace),那么利用这个功能就可以实现单表的恢复,同样利用这个功能还可以把innodb表移动到另外一台服务器上。可以参考:https://yq.aliyun.com/articles/59271

下面进行从xtrabackup全备恢复单表的测试。

1.开启了参数innodb_file_per_table

2.安装工具:mysql-utilities,其中mysqlfrm可以读取表结构。

$ yum install mysql-utilities -y

查看原表中的数据:

mysql> select count(*) from sbtest.sbtest1;
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

执行备份:

$ innobackupex --defaults-file=/etc/my.cnf --user=root --password=123456 /data/

apply-log

$ innobackupex --defaults-file=/etc/my.cnf --apply-log /data/2018-03-21_08-09-43

删除sbtest1表

mysql> drop table sbtest.sbtest1;

利用mysql-utilities工具读取表结构(不支持MariaDB哦)

$ mysqlfrm --diagnostic /data/2018-03-21_08-09-43/sbtest/sbtest1.frm

得到表结构

CREATE TABLE `sbtest1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

加一个写锁,确保安全

mysql> lock tables sbtest1 write;

丢弃表空间

mysql> alter table sbtest1 discard tablespace;  
Query OK, 0 rows affected (0.00 sec)

从备份中拷贝ibd文件,并且修改权限

$ cp /data/2018-03-21_08-09-43/sbtest/sbtest1.ibd /var/lib/mysql/sbtest/
$ chown -R mysql.mysql /var/lib/mysql/sbtest/sbtest1.ibd

这里有警告,可以忽略。详情可以看:https://yq.aliyun.com/articles/59271

查询数据是否一致:

mysql> alter table sbtest1 import tablespace; 
Query OK, 0 rows affected, 1 warning (0.08 sec)

mysql> select count(*) from sbtest1;  
+----------+
| count(*) |
+----------+
|    10000 |
+----------+
1 row in set (0.00 sec)

最后解锁:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

XtraBackup不停机不锁表热备mysql数据库

环境

  • mysql版本 Server version: 5.6.42-log MySQL Community Server
  • 数据库data目录 /data/mysql/
  • binlog日志目录 /data/bin_logs/
  • 备份目录 /data/backup/

完全备份脚本 (每日一次,00:10:00执行)

#!/bin/bash
base_dir=”/data/backup/`date +%F`”
[ -d ${base_dir} ] || mkdir ${base_dir}
[ -d ${base_dir}/full_`date +%F` ] && exit 0
innobackupex –defaults-file=/etc/my.cnf –user=root –password=’123456′ –no-timestamp ${base_dir}/full_`date +%F`
[ $? -eq 0 ] || echo “$(date +”%F_ %T”) 完全备份失败,请重新备份” >> /data/backup/backup.log

增量备份脚本 (每小时一次,xx:30:00执行)

#!/bin/bash
base_dir=”/data/backup/`date +%F`”
full_dir=”/data/backup/`date +%F`/full_`date +%F`”
inc_dir=”${base_dir}/inc_`date +%H`”
[ -d ${inc_dir} ] && exit 0
innobackupex –defaults-file=/etc/my.cnf –user=root –password=’123456′ –no-timestamp –incremental-basedir=${full_dir} –incremental ${inc_dir} &> /dev/null
[ $? -eq 0 ] || echo “$(date +”%F_ %T”) ${inc_dir}增量备份失败,请重新备份” >> /data/backup/backup.log

一键还原脚本

#!/bin/bash
base_dir=”/data/backup/`date +%F`”
full_dir=”${base_dir}/full_`date +%F`”
inc_dir=”${base_dir}/`ls ${base_dir} | tail -1`”
cp -a ${base_dir} /data/backup/`date +”%F-%T”`_bak
innobackupex –defaults-file=/etc/my.cnf –user=root –apply-log –redo-only ${full_dir}
innobackupex –defaults-file=/etc/my.cnf –user=root –apply-log –redo-only ${full_dir} –incremental-dir=${inc_dir}

service mysqld stop
mv /data/mysql /data/mysql_bak`date +”%F_%T”`
mkdir /data/mysql
innobackupex –defaults-file=/etc/my.cnf –user=root –copy-back ${full_dir}
chown -R mysql.mysql /data/mysql
service mysqld start

###binlog日志还原
###这里选择的是最后一个binlog日志,如果增量备份的一个小时内,有多个mysql-bin.0000xx生成,需要手动选择bin-log日志;
###还原执行13:30点后的所有sql命令
Hour=`basename ${inc_dir}|grep -o ‘[0-9]*’`
binlog=`ls /data/bin_logs/|grep [0-9]|tail -1`
mysqlbinlog –start-datetime=”`date +%F` ${Hour}:30:00″ /data/mysql/${binlog} | mysql -uroot -p ‘123456’

###还原执行13:30-14:10之间的所有sql命令
##mysqlbinlog –start-datetime=”2018-11-21 13:30:00″ –stop-datetime=”2012-03-15 14:10:00″ /data1/log/mysql/mysql-bin.000001 > /tmp/mysql_restore_030915.sql

计划任务

10 00 * * * /bin/bash /root/.scripts/full_back.sh
30 * * * * /bin/bash /root/.scripts/inc_back.sh

MySQL基本知识点梳理和查询优化

本文主要是总结了工作中一些常用的操作,以及不合理的操作,在对慢查询进行优化时收集的一些有用的资料和信息,本文适合有mysql基础的开发人员。

一、索引相关

1、索引基数:基数是数据列所包含的不同值的数量。例如,某个数据列包含值1、3、7、4、7、3,那么它的基数就是4。索引的基数相对于数据表行数较高(也就是说,列中包含很多不同的值,重复的值很少)的时候,它的工作效果最好。如果某数据列含有很多不同的年龄,索引会很快地分辨数据行。如果某个数据列用于记录性别(只有”M”和”F”两种值),那么索引的用处就不大。如果值出现的几率几乎相等,那么无论搜索哪个值都可能得到一半的数据行。在这些情况下,最好根本不要使用索引,因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候,它一般会忽略索引,进行全表扫描。惯用的百分比界线是”30%”。

2、索引失效原因:

1、对索引列运算,运算包括(+、-、*、/、!、<>、%、like'%_'(%放在前面)
2、类型错误,如字段类型为varchar,where条件用number。
3、对索引应用内部函数,这种情况下应该建立基于函数的索引
如select * from template t where ROUND(t.logicdb_id) = 1
此时应该建ROUND(t.logicdb_id)为索引,mysql8.0开始支持函数索引,5.7可以通过虚拟列的方式来支持,之前只能新建一个ROUND(t.logicdb_id)列然后去维护
4、如果条件有or,即使其中有条件带索引也不会使用(这也是为什么建议少使用or的原因),如果想使用or,又想索引有效,只能将or条件中的每个列加上索引
5、如果列类型是字符串,那一定要在条件中数据使用引号,否则不使用索引;
6、B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走
7、组合索引遵循最左原则

索引的建立

1、最重要的肯定是根据业务经常查询的语句

2、尽量选择区分度高的列作为索引,区分度的公式是 COUNT(DISTINCT col) / COUNT(*)。表示字段不重复的比率,比率越大我们扫描的记录数就越少

3、如果业务中唯一特性最好建立唯一键,一方面可以保证数据的正确性,另一方面索引的效率能大大提高

二、EXPLIAN中有用的信息

基本用法

1、desc 或者 explain 加上你的sql

2、extended explain加上你的sql,然后通过show warnings可以查看实际执行的语句,这一点也是非常有用的,很多时候不同的写法经过sql分析之后实际执行的代码是一样的

提高性能的特性

1、索引覆盖(covering index):需要查询的数据在索引上都可以查到不需要回表 EXTRA列显示using index

2、ICP特性(Index Condition Pushdown):本来index仅仅是data access的一种访问模式,存数引擎通过索引回表获取的数据会传递到MySQL server层进行where条件过滤,5.6版本开始当ICP打开时,如果部分where条件能使用索引的字段,MySQL server会把这部分下推到引擎层,可以利用index过滤的where条件在存储引擎层进行数据过滤。EXTRA显示using index condition。需要了解mysql的架构图分为server和存储引擎层

3、索引合并(index merge):对多个索引分别进行条件扫描,然后将它们各自的结果进行合并(intersect/union)。一般用OR会用到,如果是AND条件,考虑建立复合索引。EXPLAIN显示的索引类型会显示index_merge,EXTRA会显示具体的合并算法和用到的索引

extra字段

1、using filesort: 说明MySQL会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” ,其实不一定是文件排序,内部使用的是快排

2、using temporary: 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

3、using index: 表示相应的SELECT操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。

4、impossible where: WHERE子句的值总是false,不能用来获取任何元组

5、select tables optimized away: 在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作, 不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化

6、distinct: 优化distinct操作,在找到第一匹配的元祖后即停止找同样值的操作

using filesort,using temporary这两项出现时需要注意下,这两项是十分耗费性能的,在使用group by的时候,虽然没有使用order by,如果没有索引,是可能同时出现using filesort,using temporary的,因为group by就是先排序在分组,如果没有排序的需要,可以加上一个order by NULL来避免排序,这样using filesort就会去除,能提升一点性能。

type字段

  • system:表只有一行记录(等于系统表),这是const类型的特例,平时不会出现
  • const:如果通过索引依次就找到了,const用于比较主键索引或者unique索引。 因为只能匹配一行数据,所以很快。如果将主键置于where列表中,MySQL就能将该查询转换为一个常量
  • eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
  • ref:非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配 某个单独值的行,然而它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体
  • range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是在你的where语句中出现between、<、>、in等的查询,这种范围扫描索引比全表扫描要好,因为只需要开始于缩印的某一点,而结束于另一点,不用扫描全部索引
  • index:Full Index Scan ,index与ALL的区别为index类型只遍历索引树,这通常比ALL快,因为索引文件通常比数据文件小。 (也就是说虽然ALL和index都是读全表, 但index是从索引中读取的,而ALL是从硬盘读取的)
  • all:Full Table Scan,遍历全表获得匹配的行

参考地址:https://blog.csdn.net/DrDanger/article/details/79092808

三、字段类型和编码

1、mysql返回字符串长度:CHARACTER_LENGTH方法(CHAR_LENGTH一样的)返回的是字符数,LENGTH函数返回的是字节数,一个汉字三个字节

2、varvhar等字段建立索引长度计算语句:select count(distinct left(test,5))/count(*) from table; 越趋近1越好

3、mysql的utf8最大是3个字节不支持emoji表情符号,必须只用utf8mb4。需要在mysql配置文件中配置客户端字符集为utf8mb4。jdbc的连接串不支持配置characterEncoding=utf8mb4,最好的办法是在连接池中指定初始化sql,例如:hikari连接池,其他连接池类似spring.datasource.hikari.connection-init-sql=set names utf8mb4。否则需要每次执行sql前都先执行set names utf8mb4

4、msyql排序规则(一般使用_bin和_genera_ci):

  • utf8_genera_ci不区分大小写,ci为case insensitive的缩写,即大小写不敏感,

  • utf8_general_cs区分大小写,cs为case sensitive的缩写,即大小写敏感,但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则,直接使用utf8_bin替代。

  • utf8_bin将字符串中的每一个字符用二进制数据存储,区分大小写。

那么,同样是区分大小写,utf8_general_cs和utf8_bin有什么区别?
cs为case sensitive的缩写,即大小写敏感;bin的意思是二进制,也就是二进制编码比较。
utf8_general_cs排序规则下,即便是区分了大小写,但是某些西欧的字符和拉丁字符是不区分的,比如ä=a,但是有时并不需要ä=a,所以才有utf8_bin
utf8_bin的特点在于使用字符的二进制的编码进行运算,任何不同的二进制编码都是不同的,因此在utf8_bin排序规则下:ä<>a

5、sql yog中初始连接指定编码类型使用连接配置的初始化命令

未分类

四、SQL语句总结

常用的但容易忘的:

1、如果有主键或者唯一键冲突则不插入:insert ignore into

2、如果有主键或者唯一键冲突则更新,注意这个会影响自增的增量:INSERT INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf") ON DUPLICATE KEY UPDATE room_remarks="234"

3、如果有就用新的替代,values如果不包含自增列,自增列的值会变化: REPLACE INTO room_remarks(room_id,room_remarks) VALUE(1,"sdf")

4、备份表:CREATE TABLE user_info SELECT * FROM user_info

5、复制表结构:CREATE TABLE user_v2 LIKE user

6、从查询语句中导入:INSERT INTO user_v2 SELECT * FROM user或者INSERT INTO user_v2(id,num) SELECT id,num FROM user

7、连表更新:UPDATE user a, room b SET a.num=a.num+1 WHERE a.room_id=b.id

8、连表删除:DELETE user FROM user,black WHERE user.id=black.id

锁相关(作为了解,很少用)

1、共享锁: select id from tb_test where id = 1 lock in share mode;

2、排它锁: select id from tb_test where id = 1 for update

优化时用到:

1、强制使用某个索引: select * from table force index(idx_user) limit 2;

2、禁止使用某个索引: select * from table ignore index(idx_user) limit 2;

3、禁用缓存(在测试时去除缓存的影响): select SQL_NO_CACHE from table limit 2;

查看状态

1、查看字符集 SHOW VARIABLES LIKE 'character_set%';

2、查看排序规则 SHOW VARIABLES LIKE 'collation%';

SQL编写注意

1、where语句的解析顺序是从右到左,条件尽量放where不要放having

2、采用延迟关联(deferred join)技术优化超多分页场景,比如limit 10000,10,延迟关联可以避免回表

3、distinct语句非常损耗性能,可以通过group by来优化

4、连表尽量不要超过三个表

五、踩坑

1、如果有自增列,truncate语句会把自增列的基数重置为0,有些场景用自增列作为业务上的id需要十分重视

2、聚合函数会自动滤空,比如a列的类型是int且全部是NULL,则SUM(a)返回的是NULL而不是0

3、mysql判断null相等不能用“a=null”,这个结果永远为UnKnown,where和having中,UnKnown永远被视为false,check约束中,UnKnown就会视为true来处理。所以要用“a is null”处理

六、千万大表在线修改

mysql在表数据量很大的时候,如果修改表结构会导致锁表,业务请求被阻塞。mysql在5.6之后引入了在线更新,但是在某些情况下还是会锁表,所以一般都采用pt工具( Percona Toolkit)

如对表添加索引:

如下:

pt-online-schema-change --user='root' --host='localhost' --ask-pass --alter "add index idx_user_id(room_id,create_time)" 
D=fission_show_room_v2,t=room_favorite_info --execute

七、慢查询日志

有时候如果线上请求超时,应该去关注下慢查询日志,慢查询的分析很简单,先找到慢查询日志文件的位置,然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量,常用的sql如下:

-- 查看慢查询配置
-- slow_query_log  慢查询日志是否开启
-- slow_query_log_file 的值是记录的慢查询日志到文件中
-- long_query_time 指定了慢查询的阈值
-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE '%quer%';

-- 查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE 'log_output'

-- 查看慢查询的数量

mysqldumpslow的工具十分简单,我主要用到的是参数如下:

  • -t:限制输出的行数,我一般取前十条就够了
  • -s:根据什么来排序默认是平均查询时间at,我还经常用到c查询次数,因为查询次数很频繁但是时间不高也是有必要优化的,还有t查询时间,查看那个语句特别卡。
  • -v:输出详细信息

例子:mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500

八、查看sql进程和杀死进程

如果你执行了一个sql的操作,但是迟迟没有返回,你可以通过查询进程列表看看他的实际执行状况,如果该sql十分耗时,为了避免影响线上可以用kill命令杀死进程,通过查看进程列表也能直观的看下当前sql的执行状态,如果当前数据库负载很高,在进程列表可能会出现,大量的进程夯住,执行时间很长。命令如下:

--查看进程列表
SHOW PROCESSLIST;

--杀死某个进程
kill 183665

如果你使用的sqlyog,那么也有图形化的页面,在菜单栏-工具-显示-进程列表。在进程列表页面可以右键杀死进程。如下所示:

未分类

未分类

九、一些数据库性能的思考

在对公司慢查询日志做优化的时候,很多时候可能是忘了建索引,像这种问题很容易解决,加个索引就行了。但是有两种情况就不是简单能加索引能解决了:

1、业务代码循环读数据库: 考虑这样一个场景,获取用户粉丝列表信息 加入分页是十个 其实像这样的sql是十分简单的,通过连表查询性能也很高,但是有时候,很多开发采用了取出一串id,然后循环读每个id的信息,这样如果id很多对数据库的压力是很大的,而且性能也很低

2、统计sql:很多时候,业务上都会有排行榜这种,发现公司有很多地方直接采用数据库做计算,在对一些大表的做聚合运算的时候,经常超过五秒,这些sql一般很长而且很难优化, 像这种场景,如果业务允许(比如一致性要求不高或者是隔一段时间才统计的),可以专门在从库里面做统计。另外我建议还是采用redis缓存来处理这种业务

3、超大分页:在慢查询日志中发现了一些超大分页的慢查询如limit 40000,1000,因为mysql的分页是在server层做的,可以采用延迟关联在减少回表。但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的,所以很有可能是有恶意用户在刷接口,所以最好在开发的时候也对接口加上校验拦截这些恶意请求。

这篇文章就总结到这里,希望能够对你有所帮助!

彻底删除mysql方法

首先,先在服务(开始——>控制面板——>管理工具——>服务)里停掉MySQL的服务。打开控制面板-添加删除程序,找到MySQL,卸载。或者用360安全卫士来卸载也行。也可以用mysql的那个安装程序删除

把安装好的MYSQL卸载了,但这对于卸载MySQL来说这只是一半,还有重要的另一半是要清理注册表。我们要进入注册表在开始-运行里面输入regedt,打开注册表

找到关于MYSQL的项把他们都删除,要一个项一个项的查找把他们都删除,这样在安装的时候就可以了。其实注册表里MySQL的项就是这三项:

HKEY_LOCAL_MACHINE/SYSTEM/ControlSet001/Services/Eventlog/Application/MySQL

HKEY_LOCAL_MACHINE/SYSTEM/ControlSet002/Services/Eventlog/Application/MySQL

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/Eventlog/Application/MySQL

还有就是C:/WINDOWS 下的my.ini文件也删除!

这样,把上面的四项删除了之后,MySQL就基本卸载完全了。如果你还不放心的话,可以在C盘查找mysql,把相关的项都删除。

如何在Ubuntu 18.04上使用MySQL设置远程数据库以优化站点性能

介绍

随着您的应用程序或网站的增长,可能会出现您已经超出当前服务器设置的程度。 如果您在同一台计算机上托管Web服务器和数据库后端,最好将这两个功能分开,以便每个功能可以在自己的硬件上运行,并分担响应访问者请求的负载。

在本指南中,我们将介绍如何配置Web应用程序可以连接的远程MySQL数据库服务器。 我们将使用WordPress作为示例,以便有一些工作,但该技术广泛适用于MySQL支持的任何应用程序。

先决条件

在开始本教程之前,您将需要:

  • 两台Ubuntu 18.04服务器。 每个应该有一个具有sudo权限和启用UFW防火墙的非root用户,如我们的初始服务器设置与Ubuntu 18.04教程中所述。 其中一个服务器将托管您的MySQL后端,在本指南中,我们将其称为数据库服务器 。 另一个将远程连接到您的数据库服务器并充当您的Web服务器; 同样,我们将在本指南的过程中将其称为Web服务器 。

  • Nginx和PHP安装在您的Web服务器上 。 我们的教程如何在Ubuntu 18.04中安装Linux,Nginx,MySQL,PHP(LEMP)将指导您完成整个过程,但请注意,您应该跳过本教程的第2步,重点是安装MySQL,因为您将安装MySQL数据库服务器。

  • MySQL安装在您的数据库服务器上 。 按照“ 如何在Ubuntu 18.04上安装MySQL ”进行设置。

  • (可选)(但强烈推荐), Web服务器上安装的Let’s Encrypt的TLS / SSL证书。 您需要购买域名并为您的服务器设置DNS记录 ,但证书本身是免费的。 我们的指南如何使用Ubuntu 18.04上的Let加密来保护Nginx将向您展示如何获取这些证书。

第1步 – 配置MySQL以监听远程连接

将一个数据存储在单独的服务器上是一种在达到单机配置的性能上限后优雅扩展的好方法。 它还提供了以后负载平衡和扩展基础架构所需的基本结构。 按照先决条件教程安装MySQL后,您需要更改一些配置值以允许来自其他计算机的连接。

大多数MySQL服务器的配置更改都可以在mysqld.cnf文件中进行,该文件默认存储在/etc/mysql/mysql.conf.d/目录中。 在首选编辑器中使用root权限打开此文件。 在这里,我们将使用nano :

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

此文件分为由方括号( [和] )中的标签表示的部分。 找到标记为mysqld的部分:

/etc/mysql/mysql.conf.d/mysqld.cnf

. . .
[mysqld]
. . .

在此部分中,查找名为bind-address的参数。 这告诉数据库软件监听连接的网络地址。

默认情况下,它设置为127.0.0.1 ,这意味着MySQL配置为仅查找本地连接。 您需要将其更改为引用可以访问服务器的外部 IP地址。

如果两台服务器都位于具有专用网络功能的数据中心,请使用数据库服务器的专用网络IP。 否则,您可以使用其公共IP地址:

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
. . .
bind-address = db_server_ip

由于您将通过Internet连接到数据库,因此建议您需要加密连接以确保数据安全。 如果不加密MySQL连接,网络上的任何人都可能在Web和数据库服务器之间嗅探敏感信息。 要加密MySQL连接,请在刚刚更新的bind-address行之后添加以下行:

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
. . .
require_secure_transport = on
. . .

完成后保存并关闭文件。 如果您使用的是nano ,请按CTRL+X , Y ,然后按ENTER 。

要使SSL连接起作用,您需要创建一些密钥和证书。 MySQL附带了一个自动设置它们的命令。 运行以下命令,该命令将创建必要的文件。 它还通过指定mysql用户的UID使MySQL服务器可以读取它们:

sudo mysql_ssl_rsa_setup --uid=mysql

要强制MySQL更新其配置并读取新的SSL信息,请重新启动数据库:

sudo systemctl restart mysql

要确认服务器正在监听外部接口,请运行以下netstat命令:

sudo netstat -plunt | grep mysqld
tcp        0      0 db_server_ip:3306     0.0.0.0:*               LISTEN      27328/mysqld

netstat打印有关服务器网络系统的统计信息。 此输出向我们显示一个名为mysqld的进程附加到端口3306 (标准MySQL端口)的db_server_ip ,确认服务器正在监听相应的接口。

接下来,打开防火墙上的端口以允许流量通过:

sudo ufw allow mysql

这些是您需要对MySQL进行的所有配置更改。 接下来,我们将介绍如何设置数据库和一些用户配置文件,其中一个用于远程访问服务器。

第2步 – 设置WordPress数据库和远程凭据

即使MySQL本身正在监听外部IP地址,但目前还没有配置启用远程的用户或数据库。 让我们为WordPress和一对可以访问它的用户创建一个数据库。

首先以root用户身份连接到MySQL:

sudo mysql

注意:如果启用了密码身份验证,如前提条件MySQL教程的第3步中所述 ,则需要使用以下命令来访问MySQL shell:

mysql -u root -p

运行此命令后,将询问您的MySQL root密码,输入后,您将收到一个新的mysql>提示符。

在MySQL提示符下,创建一个WordPress将使用的数据库。 为此数据库提供可识别的名称可能会有所帮助,以便您以后可以轻松识别它。 在这里,我们将其命名为wordpress :

CREATE DATABASE wordpress;

现在您已经创建了数据库,接下来需要创建一对用户。 我们将创建一个仅限本地的用户以及与Web服务器的IP地址绑定的远程用户。

首先,创建本地用户wordpressuser ,并通过在声明中使用localhost使此帐户仅匹配本地连接尝试:

CREATE USER 'wordpressuser'@'localhost' IDENTIFIED BY 'password';

然后授予此帐户对wordpress数据库的完全访问权限:

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'localhost';

此用户现在可以对WordPress的数据库执行任何操作,但此帐户不能远程使用,因为它只匹配来自本地计算机的连接。 考虑到这一点,创建一个配对帐户,该帐户将仅与您的Web服务器上的连接匹配。 为此,您需要Web服务器的IP地址。

请注意,您必须使用与您在mysqld.cnf文件中配置的网络相同的IP地址。 这意味着如果您在mysqld.cnf文件中指定了专用网络IP,则需要在以下两个命令中包含Web服务器的专用IP。 如果您将MySQL配置为使用公共Internet,则应将其与Web服务器的公共IP地址进行匹配。

CREATE USER 'wordpressuser'@'web-server_ip' IDENTIFIED BY 'password';

创建远程帐户后,为其授予与本地用户相同的权限:

GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpressuser'@'web_server_ip';

最后,刷新权限,以便MySQL知道开始使用它们:

FLUSH PRIVILEGES;

然后输入以下命令退出MySQL提示符:

exit

现在您已经设置了新数据库和启用远程的用户,您可以继续测试是否能够从Web服务器连接到数据库。

第3步 – 测试远程和本地连接

在继续之前,最好验证您是否可以从本地计算机(数据库服务器)和每个wordpressuser帐户的Web服务器连接到数据库。

首先,尝试使用新帐户登录,从数据库服务器测试本地连接:

mysql -u wordpressuser -p

出现提示时,输入您为此帐户设置的密码。

如果给出MySQL提示,则本地连接成功。 您可以输入以下命令再次退出:

exit

接下来,登录Web服务器以测试远程连接:

ssh sammy@web_server_ip

您需要在Web服务器上安装一些MySQL客户端工具才能访问远程数据库。 首先,如果您最近没有这样做,请更新本地包缓存:

sudo apt update

然后安装MySQL客户端实用程序:

sudo apt install mysql-client

在此之后,使用以下语法连接到数据库服务器:

mysql -u wordpressuser -h db_server_ip -p

同样,您必须确保使用正确的数据库服务器IP地址。 如果您将MySQL配置为在专用网络上监听,请输入数据库的专用网络IP。 否则,请输入数据库服务器的公共IP地址。

系统将要求您输入wordpressuser帐户的密码。 输入后,如果一切正常,您将看到My​​SQL提示符。 使用以下命令验证连接是否正在使用SSL:

status

如果连接确实使用SSL,则SSL:行将指示此信息,如下所示:

--------------
mysql  Ver 14.14 Distrib 5.7.18, for Linux (x86_64) using  EditLine wrapper

Connection id:      52
Current database:
Current user:       [email protected]
SSL:         Cipher in use is DHE-RSA-AES256-SHA
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.18-0ubuntu0.16.04.1 (Ubuntu)
Protocol version:   10
Connection:     203.0.113.111 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:       3306
Uptime:         3 hours 43 min 40 sec

Threads: 1  Questions: 1858  Slow queries: 0  Opens: 276  Flush tables: 1  Open tables: 184  Queries per second avg: 0.138
--------------

验证可以远程连接后,继续并退出提示:

exit

有了这个,您已经验证了从Web服务器的本地访问和访问,但您尚未验证其他连接将被拒绝。 要进行额外检查,请尝试从未配置特定用户帐户的第三台服务器执行相同操作,以确保未授予此其他服务器访问权限。

请注意,在运行以下命令以尝试连接之前,您可能必须像上面那样安装MySQL客户端实用程序:

mysql -u wordpressuser -h db_server_ip -p

这不应该成功完成,并应该抛出一个看起来类似于此的错误:

ERROR 1130 (HY000): Host '203.0.113.12' is not allowed to connect to this MySQL server

这是预期的,因为您还没有创建允许从此服务器连接的MySQL用户,并且还希望,因为您要确保您的数据库服务器将拒绝未经授权的用户访问您的MySQL服务器。

成功测试远程连接后,您可以继续在Web服务器上安装WordPress。

第4步 – 安装WordPress

为了演示新的支持远程的MySQL服务器的功能,我们将在您的Web服务器上完成安装和配置WordPress(流行的内容管理系统)的过程。 这将要求您下载并解压缩软件,配置连接信息,然后运行WordPress的基于Web的安装。

在您的Web服务器上 ,将最新版本的WordPress下载到您的主目录:

cd ~
curl -O https://wordpress.org/latest.tar.gz

解压缩文件,这将在您的主目录中创建一个名为wordpress的目录:

tar xzvf latest.tar.gz

WordPress包含一个示例配置文件,我们将其用作起点。 制作此文件的副本,从文件名中删除-sample ,以便WordPress加载:

cp ~/wordpress/wp-config-sample.php ~/wordpress/wp-config.php

当您打开文件时,您的第一个业务订单将是调整一些密钥以为您的安装提供更高的安全性。 WordPress为这些值提供了一个安全的生成器,因此您不必尝试自己提供好的值。 这些仅在内部使用,因此在这里使用复杂,安全的值不会影响可用性。

要从WordPress密钥生成器中获取安全值,请键入:

curl -s https://api.wordpress.org/secret-key/1.1/salt/

这将为您的输出打印一些键。 您将立即将这些添加到您的wp-config.php文件中:

警告!

每次请求您自己的唯一值非常重要。

不要复制此处显示的值!

define('AUTH_KEY',         'L4|2Yh(giOtMLHg3#] DO NOT COPY THESE VALUES %G00o|te^5YG@)');
define('SECURE_AUTH_KEY',  'DCs-k+MwB90/-E(=!/ DO NOT COPY THESE VALUES +WBzDq:7U[#Wn9');
define('LOGGED_IN_KEY',    '*0kP!|VS.K=;#fPMlO DO NOT COPY THESE VALUES +&[%8xF*,18c @');
define('NONCE_KEY',        'fmFPF?UJi&(j-{8=$- DO NOT COPY THESE VALUES CCZ?Q+_~1ZU~;G');
define('AUTH_SALT',        '@qA7f}2utTEFNdnbEa DO NOT COPY THESE VALUES t}Vw+8=K%20s=a');
define('SECURE_AUTH_SALT', '%BW6s+d:7K?-`C%zw4 DO NOT COPY THESE VALUES 70U}PO1ejW+7|8');
define('LOGGED_IN_SALT',   '-l>F:-dbcWof%4kKmj DO NOT COPY THESE VALUES 8Ypslin3~d|wLD');
define('NONCE_SALT',       '4J(<`4&&F (WiK9K#] DO NOT COPY THESE VALUES ^ZikS`es#Fo:V6');

将收到的输出复制到剪贴板,然后在文本编辑器中打开配置文件:

nano ~/wordpress/wp-config.php

找到包含这些设置的虚拟值的部分。 它看起来像这样:

/wordpress/wp-config.php

. . .
define('AUTH_KEY',         'put your unique phrase here');
define('SECURE_AUTH_KEY',  'put your unique phrase here');
define('LOGGED_IN_KEY',    'put your unique phrase here');
define('NONCE_KEY',        'put your unique phrase here');
define('AUTH_SALT',        'put your unique phrase here');
define('SECURE_AUTH_SALT', 'put your unique phrase here');
define('LOGGED_IN_SALT',   'put your unique phrase here');
define('NONCE_SALT',       'put your unique phrase here');
. . .

删除这些行并粘贴从命令行复制的值。

接下来,输入远程数据库的连接信息。 这些配置行位于文件的顶部,就在您粘贴到键中的位置上方。 请记住使用先前在远程数据库测试中使用的相同IP地址:

/wordpress/wp-config.php

. . .
/** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', 'db_server_ip');
. . .

最后,在文件中的任何位置添加以下行,告诉WordPress使用与MySQL数据库的SSL连接:

/wordpress/wp-config.php

define('MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL);

保存并关闭文件。

接下来,将~/wordpress目录中的文件和目录复制到Nginx的文档根目录。 请注意,此命令包含-a标志以确保所有现有权限都被转移:

sudo cp -a ~/wordpress/* /var/www/html

在此之后,剩下要做的就是修改文件所有权。 将文档根目录中所有文件的所有权更改为WW-data ,Ubuntu的默认Web服务器用户:

sudo chown -R www-data:www-data /var/www/html

有了这个,就安装了WordPress,并且您已准备好运行其基于Web的安装程序。

第5步 – 通过Web界面设置Wordpress

WordPress有一个基于Web的设置过程。 当您浏览它时,它会询问几个问题并在数据库中安装所需的所有表。 在这里,我们将介绍设置WordPress的初始步骤,您可以将其用作构建使用远程数据库后端的自定义网站的起点。

导航到与您的Web服务器关联的域名(或公共IP地址):

http://example.com

您将看到WordPress安装程序的语言选择屏幕。 选择适当的语言,然后单击进入主安装屏幕:

未分类

提交信息后,您需要使用刚刚创建的帐户登录WordPress管理界面。 然后,您将进入仪表板,您可以在其中自定义新的WordPress站点。

结论

按照本教程,您已设置MySQL数据库以接受来自远程Wordpress安装的受SSL保护的连接。 本指南中使用的命令和技术适用于以任何编程语言编写的任何Web应用程序,但具体的实现细节将有所不同。 有关更多信息,请参阅您的应用程序或语言的数据库文档。

使用systemtap调试工具分析MySQL的性能

SystemTap是Linux下的动态跟踪工具,可以方便的监控、跟踪运行中的程序或Linux内核操作,我们通过写SystemTap脚本(与C语言类似),编译为内核模块,然后加载到内核中运行,它带来的性能损耗比较小。

一、 分析SQL语句在执行各个阶段的消耗

比如我们想统计一句SQL在解析阶段的执行时间,可以在mysqld进程的对应parse_sql函数上加探测点,统计函数执行时间。

未分类

脚本如下:

未分类

示例如下,当前QPS约1.6W,包含了SQL执行过程中解析、redo、binlog、undo、网络、IO读、IO写上的消耗,由于涉及的函数较多,这部分后续还需要整理完善。

未分类

也可以用来分析一句SQL的执行性能:

未分类

二、SQL执行时间统计分布

对于应用来说,主要关心数据库的吞吐量和响应时间,下面从应用负载的角度去衡量,服务器上当前执行SQL语句的性能分布情况。

下图是1秒聚合的SQL语句的执行时间分布图,表示大部分SQL的执行时间在128us以下:

未分类

性能开销

用sysbench做了简单压测,并发8线程时,运行stap脚本QPS从3.5W降到3.3W,性能损耗约6%;当并发32线程时,运行stap脚本QPS从9W降到7W,性能损耗约22%

未分类

应用场景

Systemtap功能非常强大,可以对mysqld进程下近25000个函数以及kernel下35000个函数自定义脚本进行跟踪统计,可用来分析内存、CPU、IO、网络等各种问题。

比MySQL快6倍 深度解析国内首个云原生数据库POLARDB的“王者荣耀”

随着移动互联网、电子商务的高速发展,被使用最多的企业级开源数据系统MySQL面临着巨大挑战——为迎接“双11″的高并发要提前做好分库分表;用户不断激增要将读写分离才能应对每天上亿次的访问,但读写分离后会导致数据同步延时严重、分布式事务复杂且效率低下、数据库可用性下降、MySQL的3T容量瓶颈等一系列问题都困扰着CTO和架构师们。

“要解决这些问题,阿里巴巴2008年就开始研发自主可控的数据系统,2014年基于MySQL的国内首个云原生数据库POLARDB开始孵化,到今天已累计超过PB的数据迁移至 POLARDB”阿里云研究员吕漫漪这样告诉记者。

荣耀一:超大容量 弹性扩展

虽然POLARDB是基于MySQL研发的关系型数据库,但存储容量完全不受到限制,存储容量最高可达100TB,单库最多可扩展到 16 个节点,性能提升是MySQL的6倍,并且完全兼容MySQL。

“由于MySQL 和POLARDB 百分之百兼容,有两种办法可以实现快速迁移,:一是直接做个备份,就可以从MySQL迁移到POLARDB,这种方式非常简单,还可以通过我们提供的DTS在线工具进行迁移。“吕漫漪表示:”对于一个完全基于云环境的数据库,利用云的弹性扩展是个基本项,用Serverless方式自动扩容,存储空间无需手动配置,根据数据量自动伸缩,用户只需为实际使用容量付费。”

当应对完一次“大促“释放数据资源、节点资源非常简单,POLARDB三分钟就可生效。“不同于MySQL的‘一主多从’POLARDB则采用‘一写多读共享同一份数据’的方式,无需额外建立多个从库。在几分钟内就可以增加一个只读节点并启动服务。采用物理日志(RedoLog)代替逻辑日志(Binlog)极大程度的减少的主备延迟和磁盘IO,使得主备延迟控制在毫秒级,并可根据实际情况修改为主备强同步。”

荣耀二:运维简单 安全可靠

操作简单一直是降低差错、提高可用性的不二法则。吕漫漪表示:“POLARDB的大容量、高性能简化了构架师对数据库的操作,不用去做分库分表、不用做冷热分离,同时有对查询的加速接口,可以直接通过调用智能接入层的一个接口就可完成复杂的查询。”

同时,数据越来越成为企业的重要资产,甚至是企业的生命线。“我们在数据库安全方面对POLARDB也做了很多改进。POLARDB共享分布式存储的设计彻底解决了MySQL Master-Slave异步复制所带来的备库数据非强一致的缺陷,使得整个数据库集群在应对任何单点故障时,可以保证数据 0 丢失。采用Active-Active的高可用集群架构,可读写的主节点和只读节点之间进行Failover切换,与传统的Active-Standby相比,用同样成本带来了更好的系统访问性能。“

POLARDB也加强了数据安全方面的措施,包括采用白名单、VPC网络、SSL加密、数据多副本存储等全方位的手段,对数据库数据访问、存储、管理等各个环节提供安全保障。“在实际场景中我们发现,误删除等低级错误时有发生造成的损失巨大,为此我们未来在新版本中还会提供‘回收站’的功能,能很快地将删除的数据库表进行恢复,此外,还利用POLARDB共享分布式存储的特点,可以快速将数据库恢复到某一个指定时间点,通过快照的功能通过快照和物理日志将数据库恢复到一个指定的时间点来提高数据库的高可用性。“

荣耀三:平滑演进 面向未来

对于POLARDB的演进方向,吕漫漪表示,“首先就是在靠可用性上下功夫。企业及客户对高可用性和数据库的安全要求特别高,所以我们会在高可用上加大投入。此外在性能上的提高,把更多的功能下推到存储层来做。在当今大数据的时代,企业级用户在功能上也有了新的要求,要求数据库既要是事务性数据库又要是分析性数据库,我们今后把这两个需求结合在一起,今后将推出并行查询、大幅提高大表查询、复杂查询的性能,这些都是我们的前进方向。

当前数据种类繁多,除了大家熟知的关系型数据库,图数据库、文件数据库、流数据库等非关系数据库也在崛起。吕漫漪认为,数据库的发展现在是百花齐放,由于应用场景的不同,用户可以选择不同的数据库,但我们可以看到MySQL数据库也发生了很多改变,它从一个纯关系型的数据库也开始支持文件存储,5.7版开始支持文件存储,关系型数据库的演变和MySQL的演变也开始支持更多的存储模式,我们可以给客户提供更多的选择。”

阿里云数据库事业部总经理鸣嵩认为:“十年时间,阿里云数据库团队从技术创造新商业到推动中国数据库技术变革。”从AliSQL到RDS,再到首个自研云原生数据库POLARDB,如今,阿里巴巴数据库技术得到了极大的提升,领跑数据时代。前不久,Gartner公布了2018全球数据库魔力象限,阿里云以国内云厂商第一成为首个入选的中国企业,分析师更是认为POLARDB云原生数据库在使用场景的应用值得期待。相信作为国产数据库的领跑者,阿里云将一直在为使命而努力奋斗,让我们拭目以待。

MySQL高可用之PXC简介

PXC简介:

galera产品是以galera cluster方式为mysql提高高可用集群解决方案的。galera cluster就是集成了galera插件的mysql集群。galera replication是codership提供的mysql数据同步方案,具有高可用性,方便扩展,并且可以实现多个mysql节点间的数据同步复制与读写,可保障数据库的服务高可用及数据强一致性。

PXC属于一套近乎完美的mysql高可用集群解决方案,相比那些比较传统的基于主从复制模式的集群架构MHA和MM+keepalived,galera cluster最突出特点就是解决了诟病已久的数据复制延迟问题,基本上可以达到实时同步。而且节点与节点之间,他们相互的关系是对等的。本身galera cluster也是一种多主架构。galera cluster最关注的是数据的一致性,对待事物的行为时,要么在所有节点上执行,要么都不执行,它的实现机制决定了它对待一致性的行为非常严格,这也能非常完美的保证MySQL集群的数据一致性;

对galera cluster的封装有两个,虽然名称不同,但实质都是一样的,使用的都是galera cluster。一个MySQL的创始人在自己全新的MariaDB上实现的MAriaDB cluster;一个是著名的MySQL服务和工具提供商percona实现的percona xtradb cluster,简称PXC

要搭建PXC架构至少需要3个mysql实例来组成一个集群,三个实例之间不是主从模式,而是各自为主,所以三者是对等关系,不分从属,这就叫multi-master架构。客户端写入和读取数据时,连接哪个实例都是一样的。读取到的数据时相同的,写入任意一个实例之后,集群自己会将新写入的数据同步到其他实例上,这种架构不共享任何数据,是一种高冗余架构。

galera cluster的功能有7点,如下:

①:多主架构:真正的多点读写集群,在任何时候读写的数据都是最新的;

②:同步复制:集群不同节点之间的数据同步,没有延迟,在数据库挂掉之后,数据不会丢失;

③:并发复制:从节点在apply数据时,支持并行执行,有更好的性能表现

④:故障切换:因为支持多点写入,所以在出现数据库故障时可以很容易的进行故障切换

⑤:热插拔:在服务期间,如果数据库挂了,只要监控程序发现的够快,不可服务时间就会非常少,在节点故障期间,节点本身对集群的影响非常小;

⑥:自动节点克隆:在新增节点或停机维护时,增量数据或基础数据不需要人工手动备份提供,galera cluster会自动拉取在线节点数据,集群最终会变为一致;

⑦:对应用透明:集群的维护,对应用程序是透明的,几乎感觉不到;

PXC原理:

PXC最常使用以下4个端口号:

  • 3306-数据库对外服务的端口号。

  • 4444-请求SST的端口(SST是指数据库一个备份全量文件的传输。)

  • 4567-组成员之间进行沟通的一个端口号

  • 4568-用于传输IST(相对于SST来说的一个增量)

PXC的操作流程:

首先客户端先发起一个事务,该事务先在本地执行,执行完成之后就要发起对事务的提交操作了。在提交之前需要将产生的复制写集广播出去,然后获取到一个全局的事务ID号,一并传送到另一个节点上面。通过合并数据之后,发现没有冲突数据,执行apply_cd和commit_cb动作,否则就需要取消此次事务的操作。而当前server节点通过验证之后,执行提交操作,并返回OK,如果验证没通过,则执行回滚。当然在生产中至少要有3个节点的集群环境,如果其中一个节点没有验证通过,出现了数据冲突,那么此时采取的方式就是讲出现不一致的节点踢出集群环境,而且它自己会执行shutdown命令,自动关机。

PXC的优点:

①:实现mysql数据库集群架构的高可用性和数据的 强一致性。

②:完成了真正的多节点读写的集群方案。

③:改善了传统意义上的主从复制延迟问题,基本上达到了实时同步。

④:新加入的节点可以自动部署,无须提供手动备份,维护起来很方便。

⑤:由于是多节点写入,所以数据库故障切换很容易。

PXC的缺点:

①:新加入的节点开销大,需要复制完整的数据。采用SST传输开销太大。

②:任何更新事务都需要全局验证通过,才会在每个节点库上执行。集群性能受限于性能最差的节点,也就是经常说的短板效应。

③:因为需要保证数据的一致性,所以在多节点并发写时,锁冲突问题比较严重。

④:存在写扩大问题,所有的节点上都会发生些操作。

⑤:只支持innodb存储引擎的表。

⑥:没有表级别的锁定,执行DDL语句操作会把整个集群锁住,而且也 kill 不了(建议使用Osc操作,即在线DDL)

⑦:所有的表必须含有主键,不然操作数据时会报错。

PXC搭建的注意点:

首先要规范集群中节点的数量,整个集群中节点数控制在最少3个、最多8个范围内。最少3个节点是为了防止出现脑裂现象,因为只有在两个节点下才会出现此现象。脑裂现象的标志就是输入任何命令、返回结果都是unkown command,节点在集群中,会因为新节点的加入或者故障,同步失效等而发生状态的切换。

节点状态变化阶段:

  • open:节点启动成功,尝试连接到集群。

  • primary:节点已处于集群中,在新节点加入时,选取donor进行数据同步时会产生的状态。

  • joiner:节点处于等待接收同步文件时的状态。

  • joined:节点完成数据同步的工作,尝试保持和集群进度一致。

  • synced:节点正常提供服务的状态,表示已经同步完成并和集群进度保持一致。

  • doner:节点处于为新加入的节点提供全量数据时的状态。

注意:doner节点就是数据的贡献者,如果一个新节点加入集群,此时又需要大量数据的SST传输,就有可能因此而拖垮整个集群的性能。所以在生产环境中,如果数据量小,还可以使用SST全量传输,但如果数据量很大就不建议使用这种方式了。可以考虑先建立主从关系,在加入集群。

PXC有两种节点的数据传输方式:一种叫SST全量传输,另一种叫IST增量传输。

SST传输有:xtrabackup、mysqldump和rsync三种方法。而增量传输就一种方法就是xtrabackup。但生产环境中一般数据量不大的时候,可以使用SST全量传输,但也只实现xtrabackup方法。

在PXC中还有一个特别重要的模块就是GCache。它的核心功能就是每个节点缓存当前最新的写集。如果有新节点加入进来,就可以把新数据的增量传递给新节点,而不需要再使用SST方式了。这样可以让节点更快地加入集群中。涉及参数如下:

  • gcache.size:代表用来缓存写集增量信息的大小。它的默认大小是128MB,通过wsrep_provider_options参数设置。建议调整为2GB-4GB范围,足够的空间便于缓存更多的增量信息。

  • gcache.mem_size:代表gcache中内存缓存的大小,适度调大可以提高整个集群的性能。

  • gcache.page_size:可以理解为如果内存不够用(gcache不足),就直接将写集写入磁盘文件中。

PXC的工作模式:

galera的工作模式是——某个节点写入一个事务,它会广播到其他节点,而这个所谓的其他节点,也包括自己。也就说自己发出来的事务,自己也会收到,只是在收到并产生GTID之后,就被简单忽略了,而不会再去apply一次。

galera的并发控制机制:

并发控制主要是在接口galera_pre_commit中完成的,这个接口是galera最重要的接口之一,这里面实现了最重要的复制、验证逻辑。目前,这个接口中包括的并发控制有以下几点:

①:数据复制:

目前的galera版本中,写集数据的发送是通过asio的异步方式将数据广播出去。这个发送是串行的,是一个临界区,因为在每次 发送前,逻辑上还需要分片,并且每次发送完成之后,需要等待一个GTID的值,所以为了保证数据的一致性,这个发送操作需要串行;

②:写集验证:

要求所有进入处理区的GTID必须是顺序的,因为GTID是顺序产生的,所以在顺序的基础上,同一时间必须只有一个事务可以进行处理,说白了就是串行;

受这种层次并发控制管理的操作主要有验证操作,因此说验证是串行的;

③:写集apply

④:事务commit

这个层次的并发控制机制,默认是3,建议也是3,就是串行提交,这样就保证了不管在主库还是从库,所有的节点产生的binlog都是完全相同的;

galera 接口:

---galera_init

这个接口的作用是初始化一个galera节点,这是一个PXC节点调用的第一个wsrep接口,在启动服务器的时候初始化,将所有需要的参数和环境变量初始化。(如:集群名字,实例地址、需要这个接口做binlog的复制等)

---galera_connect

这个接口是第二个调用的接口。这个接口的作用是将当前节点加入集群中。加入集群前会调用函数wsrep_view_handler_cb来判断新加入节点与集群的数据是否同步;

---galera_recv

这个接口的作用是,在这个函数里阻塞式的接收其他节点及本节点发送的数据,并且调用复制apply函数执行复制操作。(这个接口实际上是可以并行存在的。它对应的是参数wsrep_slave_threads有多少个线程,就有多少个galera_recv的调用)

---galera_pre_commit

这个接口是galera最重要的接口之一。它的作用包括两部分,首先是将当前指定的事务写集广播给整个集群节点,然后就是验证,如果验证成功,则将处理权交给上层,继续做数据库事务的提交操作;这个接口是在数据库事务提交时调用的,调用这个接口时,必须是本地事务已经执行完成;

---galera_replay_trx

这个接口的作用及使用,就是在验证过程中,由于数据库锁的冲突,当前操作被其他线程自治县了galera_abort_pre_com_mit,导致当前线程被强制中止,但是由于写集已经复制到其他节点,所以本节点这个事务必须要完成。通过这个接口,将这个事务的写集做一次apply,所以就叫replay;

---galera_append_key

这个接口就是所谓的galera验证,被验证的对象实际上就是写集,而构成写集的内容,其实就是通过这个接口来完成的;

---galera_append_data

这个接口是当前事务所生成的binlog内容,也就是说key在验证通过之后,使用data在从节点执行,即可做到数据同步;

---galera_post_commit

这个接口是用来真正提交事务的。这个接口包括4个功能:更新状态参数wsrep_last_committed的值,表示当前事务已经真正提交了;更新参数wsrep_local_commits的值,表示本地又成功提交了一个事务;检查当前验证写集缓冲区是不是可以做purge操作;

---galera_to_execute_start:

这个接口专门用来处理DDL语句的执行;

---galera_to_execute_end

这个接口实际上和galera_post_commit功能一样,成对出现,是为处理不同语句而设置的,主要就是为了从commit临界区中出来,从而让其他事务继续提交;

Linux安装MySQL

1、下载MySQL服务,MySQL下载地址 https://dev.mysql.com/downloads/mysql/

未分类

2、选择对应的系统下载之后,通过共享文件的方式,传到服务器/usr/local/目录下。然后解压

tar zxvf mysql-5.6.42-linux-glibc2.12-x86_64.tar.gz -C ./

3、安装依赖

yum -y install perl perl-devel autoconf libaio

4、复制解压后的mysql目录到系统的本地软件目录

cp -r mysql-5.6.33-linux-glibc2.5-x86_64 /usr/local/mysql

5、添加系统mysql组和mysql用户

groupadd mysql
useradd -r -g mysql -s /bin/false mysql

6、进入安装mysql软件目录,修改目录拥有者为mysql用户

cd mysql/
chown -R mysql:mysql ./

7、安装数据库,在/usr/local/mysql目录下执行下面命令

./scripts/mysql_install_db --user=mysql

8、修改当前目录拥有者为root用户

chown -R root:root ./

9、修改当前data目录拥有者为mysql用户

chown -R mysql:mysql data

10、添加mysql服务开机自启动,把启动脚本放到开机初始化目录。

cp support-files/mysql.server /etc/init.d/mysql
# 赋予可执行权限
chmod +x /etc/init.d/mysql
# 添加服务
chkconfig --add mysql 

11、查看服务列表

chkconfig --list

未分类

如果看到mysql的服务,并且3,4,5都是on的话则成功。如果是off,则执行

chkconfig --level 345 mysql on

12、先创建缺少的文件夹,再启动MySQL服务

#创建缺少的文件夹
mkdir /var/log/mariadb
#启动服务
service mysql start

如果在启动服务的时候出现下面这个问题:

未分类

这时候,需要创建一个日志文件:

touch /var/log/mariadb/mariadb.log

创建日志文件之后再启动,就OK了。

未分类

13、把mysql客户端放到默认路径

ln -s /usr/local/mysql/bin/mysql /usr/local/bin/mysql

14、登录MySQL

#默认root用户没有密码
mysql -uroot -p

这时候会出现一个错误:ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

解决方法:打开/etc/my.cnf,看看里面配置的socket位置是什么目录。“socket=/var/lib/mysql/mysql.sock”;路径和“/tmp/mysql.sock”不一致。
建立一个软连接:ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

15、设置root用户密码

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root');

未分类

结语:到这里,MySQL服务就算安装完成了。