1.高性能MySQL运维实战

高性能 MySQL 运维实战51CTO 学院 高 级讲师：高俊峰课程学习安排1、MySQL发行版的选择（Percona Server、MariaDB和MYSQL）2、Mysql常用基础命令操作3、Mysql数据库备份工具xtrabackup4、常见的高可用MySQL解决方案5、通过KeepAlived搭建MySQL双主模式的高可用集群系统6、Mysql集群架构MHA应用实战7、通过ProxySQL实现Mysql读、写分离8、 MHA+ ProxySQL架构与应用案例一、 MySQL发行版的选择1、 MySQL官方发行版MySQL是最流行的数据库，主要特点：? 简单：MySQL使用很简单，任何稍微有IT背景的技术人员都可以无师自通地参照文档安装运行和使用MySQL,这几乎没有什么门槛。? 开源：开源意味着流行和免费。? 支持多种存储引擎：MySQL有多种存储引擎，每种存储引擎有各自的优缺点，可以择优选择使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。? 支持高可用架构：MySQL自身提供的replication（主从复制）功能可以实现MySQL数据的实时备份。

2、Mysql与存储引擎Mysql最常用的有两个存储引擎：MyISAM和 InnoDBMySQL4和5使用默认的MyISAM存储引擎。从MYSQL5.5开始，MySQL已将默认存储引擎从MyISAM更改为InnoDB。两种存储引擎的大致区别表现在：1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中出错还可以回滚还原，而MyISAM就不可以了。2）MyISAM查询数据相对较快，适合大量的select ，可以全文索引，InnoDB适合频繁修改以及涉及到安全性较高的应用.3）InnoDB支持外键，支持行级锁，MyISAM不支持4） MyISAM索引和数据是分开的，而且其索引是压缩的，缓存在内存的是索引，不是数据。而InnoDB缓存在内存的是数据，相对来说，服务器内存越大，InnoDB发挥的优势越大。5） InnoDB可支持大并发请求，适合大量insert、update操作。关于MyISAM与InnoDB选择使用1）如果应用程序一定要使用事务，毫无疑问要选择INNODB引擎。2）如果应用程序对查询性能要求较高，就要使用MYISAM了。

MYISAM拥有全文索引的功能，这可以极大地优化查询的效率。一、 MySQL发行版的选择3、Percona Server分支Percona Server由领先的MySQL咨询公司Percona发布。Percona Server是一款独立的数据库产品，其可以完全与MySQL兼容，可以在不更改代码的情况了下将存储引擎更换成XtraDB。是最接近官方MySQL Enterprise发行版的版本。Percona提供了高性能XtraDB引擎，还提供PXC高可用解决方案，并且附带了percona-toolkit等DBA管理工具箱。4、MariaDBMariaDB由MySQL的创始人开发，MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。MariaDB提供了MySQL提供的标准存储引擎，即MyISAM和InnoDB，10.0.9版起使用XtraDB（名称代号为Aria）来代替MySQL的InnoDB。5、如何选择综合多年使用经验和性能对比，线上业务系统首选Percona分支，其次是MYSQL版本，最后是MariaDB。二、 Mysql常用基础命令操作1、连接Mysql （客户端工具NaviCat、phpMyAdmin、MySQL-Front）格式： mysql -h 主机地址 -u用户名 -p用户密码（1）、连接到本机上的MYSQL。

首先进入Mysql安装程序的bin目录下，再键入命令./mysql -u root -p，回车后提示你输密码.注意用户名前可以有空格也可以没有空格，但是密码前必须没有空格，否则让你重新输入密码。（2）、连接到远程主机上的MYSQL。假设远程主机的IP为：110.110.110.110，用户名为root,密码为abcd123。则键入以下命令：mysql -h110.110.110.110 -u root -p123;（注:u与root之间可以不用加空格）（3）、退出MYSQL命令： exit （回车）2、修改密码格式：mysqladmin -u用户名 -p旧密码 password 新密码（1）、首先在Mysql安装目录下面的bin目录，然后键入以下命令mysqladmin -u root -p旧密码 password ab12注：因为开始时root没有密码，所以-p旧密码一项就可以省略了。（2）、再将root的密码改为abc345。mysqladmin -u root -p ab12 password abc345二、 Mysql常用基础命令操作3、增加新用户/授权用户注意：和上面不同，下面的因为是MYSQL命令行中的命令MySQL 选择数据库，所以后面都带一个分号作为命令结束符格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”（1）、增加一个用户test1密码为abc，让他可以在任何主机上登录，并对所有数据库有查询、插入、修改、删除的权限。

首先用root用户连入MYSQL，然后键入以下命令：mysql>grant select,insert,update,delete on *.* to test1@"%" Identified by "abc";但这种权限增加的用户是十分危险的，如某个人知道test1的密码，那么他就可以在internet上的任何一台电脑上登录这台mysql数据库，并可对数据进行任意操作，解决办法是设置登录权限。（2）、增加一个用户test2密码为abc,让它只可以在localhost上登录，并可以对数据库mydb进行查询、插入、修改、删除的操作（localhost指本地主机，即MYSQL数据库所在的那台主机），这样用户即使用知道test2的密码，他也无法从internet上直接访问数据库。mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by "abc";如果你不想test2有密码，可以再执行下面这个命令将密码取消掉。mysql>grant select,insert,update,delete on mydb.* to test2@localhost identified by "";如果想给一个用户test2授予访问mydb数据库的所有权限，并且仅允许test2在192.168.11.121这个客户端ip登录访问，可执行如下命令：mysql>grant all on mydb.* to test2@192.168.11.121 identified by "abc";二、 Mysql常用基础命令操作4、数据库基础操作（1）、创建数据库注意：创建数据库之前要先连接Mysql服务器命令：create database <数据库名>创建数据库，并分配用户方法：CREATE DATABASE 数据库名;GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER ON 数据库名.* TO 数据库名@localhost IDENTIFIED BY '密码';例1：建立一个名为abc的数据库mysql> create database abc;（2）、显示数据库命令：show databases （注意：最后有个s）mysql> show databases;二、 Mysql常用基础命令操作4、数据库基础操作（3）、删除数据库命令：drop database <数据库名>例如：删除名为 iivey的数据库mysql> drop database iivey;mysql> drop database if exists drop_database; //if exists 判断数据库是否存在，不存在也不产生错误Query OK, 0 rows affected (0.00 sec)（4）、连接数据库命令： use <数据库名>例如：如果iivey数据库存在，尝试存取它：mysql> use iivey;use语句可以通告MySQL把iivey数据库作为默认（当前）数据库使用，用于后续语句。

该数据库保持为默认数据库，直到语段的结尾，或者直到发布一个不同的USE语句。二、 Mysql常用基础命令操作5、Mysql表操作（1）、创建数据表命令：create table <表名> ( <字段名1> <类型1> [,..<字段名n> <类型n>]);mysql> create table MyClass(> id int(4) not null primary key auto_increment,> name char(20) not null,> sex int(4) not null default '0',> degree double(16,2));（2）、删除数据表命令：drop table <表名>例如：删除表名为 MyClass 的表mysql> drop table MyClass;二、 Mysql常用基础命令操作5、Mysql表操作（3）、表插入数据命令：insert into <表名> [( <字段名1>[,..<字段名n > ])] values ( 值1 )[, ( 值n )]例如：在表MyClass中插入二条记录, 这二条记录表示：编号为1的名为Tom的成绩为90.45, 编号为2 的名为Joan 的成绩为88.99， 编号为3的名为Wang的成绩为99.5。

mysql> insert into MyClass values(1,'Tom',90.45),(2,'Joan',88.99), (3,'Wang', 99.5);（4）、查询表中的数据1)、查询所有行命令： select <字段1，字段2，...> from < 表名 > where < 表达式 >例如：查看表 MyClass 中所有数据mysql> select * from MyClass;2）、查询前几行数据例如：查看表 MyClass 中前2行数据mysql> select * from MyClass order by id limit 0,2;二、 Mysql常用基础命令操作5、Mysql表操作（5）、删除表中数据命令：delete from 表名 where 表达式例如：删除表 MyClass中编号为1的记录mysql> delete from MyClass where id=1;（6）、修改表中数据语法：update 表名 set 字段=新值,… where 条件mysql> update MyClass set where id=1;（7）、增加字段命令：alter table 表名 add 字段 类型 其他;例如：在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0mysql> alter table MyClass add passtest int(4) default '0'（8）、修改表名命令：rename table 原表名 to 新表名;例如：在表MyClass名字更改为YouClassmysql> rename table MyClass to YouClass;二、 Mysql常用基础命令操作6、备份数据库（1）、导出整个数据库导出文件默认是存在mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldump -u user_name -p123456 database_name > outfile_name.sql（2）、导出一个表mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名mysqldump -u user_name -p database_name table_name > outfile_name.sql（3）、导出一个数据库结构mysqldump -u user_name -p -d --add-drop-table database_name > outfile_name.sql含义：-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table（4）、带语言参数导出mysqldump -uroot -p –default-character-set=latin1 --set-charset=gbk --skip-opt database_name > outfile_name.sql三、 Mysql数据库备份工具xtrabackup3.1、安装xtrabackup工具包1、几个percona官方yum源://www.percona.com/downloads/percona-release/2、下载yum源、安装yum源[root@localhost app]# rpm -ivh percona-release-0.1-4.noarch.rpm4、测试安装库[root@localhost app]# yum list percona-xtrabackup*通过yum方式安装percona-xtrabackup：[root@localhost app]# yum install percona-xtrabackup-24.x86_64三、 Mysql数据库备份工具xtrabackup3.2、如何通过Xtrabackup进行备份1、xtrabackup工具介绍xtrabackup有两个主要的工具：xtrabackup、innobackupex。

xtrabackup只能备份InnoDB和XtraDB两种数据表，而且只有ibd文件，frm文件它不管，恢复时就需要DBA提供frm ，同时，xtrabackup做备份的时候不能备份表结构、触发器等等。innobackupex是perl脚本对xtrabackup的封装和功能扩展。 innobackupex可以备份和恢复MyISAM、 InnoDB和XtraDB表以及frm文件，所以我们使用innobackupex来备份MySQL数据库。Xtrabackup以及innobackupex是根据my.cnf配置文件来获取备份的文件，同时，需要连接到数据库和datadir操作权限。需要注意的是：innobackupex在备份MyISAM表之前要对全库进行加READ LOCK，会阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对InnoDB表备份不会阻塞读写。三、 Mysql数据库备份工具xtrabackup3.2、如何通过xtrabackup进行备份2、 xtrabackup备份恢复过程在InnoDB内部会维护一个redo日志文件，我们也可以叫做事务日志文件。事务日志会存储每一个InnoDB表数据的记录修改。

当InnoDB启动时，InnoDB会检查数据文件和事务日志，并执行两个步骤：它应用（前滚）已经提交的事务日志到数据文件，并将修改过但没有提交的数据进行回滚操作。xtrabackup在启动时会记住log sequence number（LSN），并且复制所有的数据文件。复制过程需要一些时间，所以这期间如果数据文件有改动，那么将会使数据库处于一个不同的时间点。这时，xtrabackup会运行一个后台进程，用于监视事务日志，并从事务日志复制最新的修改。xtrabackup必须持续的做这个操作，是因为事务日志是会轮转重复的写入，并且事务日志可以被重用。所以xtrabackup自启动开始，就不停的将事务日志中每个数据文件的修改都记录下来。Innobackupex会启动xtrabackup，xtrabackup准备复制数据文件前，首选执行FLUSH TABLES WITH READ LOCK来阻止新的写入进来，同时把MyISAM表数据刷到硬盘上，然后开始复制MyISAM数据文件，文件复制完成，最后释放锁。上面就是xtrabackup的备份过程。接下来是准备（prepare）过程。在这个过程中，xtrabackup使用之前复制的事务日志，对各个数据文件执行灾难恢复（就像MySQL刚启动时要做的一样）。

当这个过程结束后，数据库就可以做恢复还原了。备份MyISAM和InnoDB表最终会处于一致，在准备（prepare）过程结束后，InnoDB表数据已经前滚到整个备份结束的点，而不是回滚到xtrabackup刚开始时的点。这个时间点与执行FLUSH TABLES WITH READ LOCK的时间点相同，所以MyISAM表数据与InnoDB表数据是同步的。类似Oracle的，InnoDB的prepare过程可以称为recover（恢复），MyISAM的数据复制过程可以称为restore（还原）。三、 Mysql数据库备份工具xtrabackup3.3、利用xtrabackup进行完全备份1、innobackupex的常用选项 ：--host：指定数据库服务器地址--port：指定连接到数据库服务器的哪个端口--socket：连接本地数据库时使用的套接字路径--no-timestamp：在使用innobackupex进行备份时，可使用--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录...

（编辑：源码门户网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!