当前位置: 澳门新濠3559 > 操作系统 > 正文

Mysql性能优化二,不会阻塞任何应该程序而且能保

时间:2019-12-08 05:50来源:操作系统
1》Mysql的数据备份     mysqldump 工具 接上篇Mysql性能优化二 --single-transaction 该选项导出数据之前提交一个BEGINSQL语句,不会阻塞任何应该程序而且能保证导出数据时的一致性状态。 对表进

1》Mysql的数据备份
    mysqldump 工具

接上篇Mysql性能优化二

  --single-transaction
  该选项导出数据之前提交一个BEGIN SQL语句,不会阻塞任何应该程序而且能保证导出数据时的一致性状态。

对表进行水平划分           

如果一个表的记录数太多了,比如上千万条,而且需要经常检索,那么我们就有必要化整为零了。如果我拆成100个表,那么每个表只有10万条记录。当然这需要数据在逻辑上可以划分。一个好的划分依据,有利于程序的简单实现,也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能,那么把表按月拆分成12个,每个查询只查询一个表就够了。如果非要按照地域来分,即使把表拆的再小,查询还是要联合所有表来查,还不如不拆了。所以一个好的拆分依据是 最重要的。关键字:UNION
例:

  • 订单表根据订单产生时间来分表(一年一张)
  • 学生情况表
  • 查询电话费,近三个月的数据放入一张表,一年内的放入到另一张表

  --flush-logs
  开始导出之前刷新日志,请注意(选项--databases或者--all-databases),将会逐个数据库刷新日志,除使用--lock-all
  -tables 或者--master-data外,这种情况下日志将会被刷新一次,相应的所有表同时被锁定。因此,如果打算同时
  导出和刷新日志应该使用--lock-all-tables或者-master-data和--flush-logs

对表进行垂直划分  

有些表记录数并不多,可能也就2、3万条,但是字段却很长,表占用空间很大,检索表时需要执行大量I/O,严重降低了性能。这个时候需要把大的字段拆分到另一个表,并且该表与原表是一对一的关系。 (JOIN)        

澳门新濠3559 1

【试题内容】、【答案信息】两个表,最初是作为几个字段添加到【试题信息】里的,可以看到试题内容和答案这两个字段很长,在表里有3万记录时,表已经占 了1G的空间,在列试题列表时非常慢。经过分析,发现系统很多时候是根据【册】、【单元】、类型、类别、难易程度等查询条件,分页显示试题详细内容。而每 次检索都是这几个表做join,每次要扫描一遍1G的表。我们完全可以把内容和答案拆分成另一个表,只有显示详细内容的时候才读这个大表,由此 就产生了【试题内容】、【答案信息】两个表。

  --routines
  导出存储过程以及自定义函数
  --evnets
  导出事件

选择适当的字段类型,特别是主键      

选择字段的一般原则是保小不保大,能用占用字节小的字段就不用大字段。比如主键, 建议使用自增类型,这样省空间,空间就是效率!按4个字节和按32个字节定位一条记录,谁快谁慢太明显了。涉及到几个表做join时,效果就更明显了。
建议使用一个不含业务逻辑的id做主角如s1001。例:

int 4  bigint 8 mediumint smallint 2 tinyint 1
md5  char(32)
id :整数 tinyint samllint int bigint
student表
id stuno     stuname     adress
1  s1001    小民            深圳

  --master-data
  该项目binlog的位置和文件追加到输出文件中,如果1,将会输出CHANGE MASTER命令,输出的CHANGE MASTER命令前添加注释,该选项将打开--lock-all-                tables 选项,除非--single-transactoin也被指定(在这种情况下,全局读锁在开始导出时获得很短的时间,其它内容 参考下面的--single-transaction选项)

文件、图片等大文件用文件系统存储

数据库只存储路径。图片和文件存放在文件系统,甚至单独放在一台服务器(图床 / 视频服务器 ).

  例子:
    mysqldump -uroot -p123456 -h 192.168.1.201 -P4421 zytest --single-transaction --flush-logs --routines --evnets--master-data=2 >zytest.sql;

数据库参数配置

最重要的参数就是内存,我们主要用的innodb引擎,所以下面两个参数调的很大

innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size =1G

对于myisam,需要调整key_buffer_size,当然调整参数还是要看状态,用show status语句可以看到当前状态,以决定改调整哪些参数
在my.ini修改端口3306,默认存储引擎和最大连接数

在my.ini中.
port=3306 [有两个地方修改]
default-storage-engine=INNODB 
max_connections=100

  企业正式环境备份参数,不同的企业业务不一样,请根据实际业务进行调整

合理的硬件资源和操作系统

如果你的机器内存超过4G,那么毋庸置疑应当采用64位操作系统和64位mysql 5.5.19 or mysql5.6
读写分离
    如果数据库压力很大,一台机器支撑不了,那么可以用mysql复制实现多台机器同步,将数据库的压力分散。
澳门新濠3559 2

Master
  Slave1
  Slave2
  Slave3
    主库master用来写入,slave1—slave3都用来做select,每个数据库分担的压力小了很多。
  要实现这种方式,需要程序特别设计,写都操作master,读都操作slave,给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理,对程 序来读写哪些数据库是透明的。官方有个mysql-proxy,但是还是alpha版本的。新浪有个amobe for mysql,也可达到这个目的,结构如下 
澳门新濠3559 3

2》本地使用root用户备份
  (1)请备份zytest库下面的vendors这个表
    mysqldump -uroot -p123456 zytest vendors >vendors.sql

定时完成数据库的备份

项目实际需求,请完成定时备份某个数据库,或者定时备份数据库的某些表的操作
windows 下每隔1小时,备份一次数据newsdb
windows 每天晚上2:00   备份 newsdb 下 某一张表
cmd> mysqldump –u root –p密码  数据库名 > 把数据库放入到某个目录
案例,备份 mydb 库的所有表
进入mysqldump所在的目录
cmd> mysqldump –u root –phsp shop> d:/shop.log   [把shop数据库的所有表全部导出]
cmd> mysqldump –u root –phsp shop temusers emp > d:/shop2.log [shop数据库的 temusers和emp导出]
如何恢复数据的表
进入的mysql操作界面
mysql>source  备份文件的全路径
定时备份:(把命令写入到my.bat 问中)
windows 如何定时备份 (每天凌晨2:00)
使用windows自带的计划任务,定时执行批处理命令。

  (2)请备份整个zytest库(备份一个数据库,--databases可以省略)
    mysqldump -uroot -p123456 --databases zytest >zytest.sql

增量备份和还原

定义:mysql数据库会以二进制的形式,自动把用户对mysql数据库的操作,记录到文件,当用户希望恢复的时候,可以使用备份文件进行恢复。

增量备份会记录dml语句、创建表的语句,不会记录select。记录的东西包括:sql语句本身、操作时间,位置

进行增量备份的步骤和恢复

 注意:mysql5.0及之前的版本是不支持增量备份的
1、配置my.ini文件或者my.conf,启用二进制备份。
打开my.ini文件,查找log-bin,进行配置:log-bin=G:Databasemysqlbinlogmylog
在G:Database目录下面新建目录mysqlbinlog
2、重启mysql服务
这个时候会在mysqlbinlog目录下面看到以下两个文件:
mylog.000001:日志备份文件。如果要查看这个日志文件里面的信息,我们可以使用mysqlbinlog程序查看,mysqlbinlog程序存放在mysql的bin目录下面(“C:Program FilesMySQLMySQL Server 5.6bin”)。

执行sql语句

UPDATE emp set ename='zouqj' where empno=100003;

开始——运行——cmd,mysqlbinlog 备份文件路径

C:Program FilesMySQLMySQL Server 5.6bin>mysqlbinlog G:Databasemysqlbinlogmylog.000001

澳门新濠3559 4
mylog.index:日志索引文件,里面记录了所以的日志文件。(G:Databasemysqlbinlogmylog.000001)
3、假设现在问题来了,我这条update是误操作,如何进行恢复
在mysql日志中会记录每一次操作的时间和位置,所以我们既可以根据时间来恢复,也可以根据位置来恢复。
那么,我们现在马上可以从上图看出,这条语句产生的时间是"2016-04-17 12:01:36",位置是614

按时间来恢复

我们可以选择在语句产生时间的前一秒

执行cmd命令:mysqlbinlog --stop-datetime="2016-04-17 12:01:35" G:Databasemysqlbinlogmylog.000001 | mysql -uroot -p

这个时候我再执行SQL语句查看

SELECT * from emp where empno=100003;

结果变成了

澳门新濠3559 5

按位置来恢复

执行cmd命令:mysqlbinlog --stop-position="614" G:Databasemysqlbinlogmylog.000001 | mysql -uroot -p

这个时候再执行SQL来查看结果,又变回来了。

澳门新濠3559 6

  (3)备份多个库分别是gongda 和zytest 向备份多个库 --databases
    mysqldump -uroot -p123456 --databases zytest gongda >backup.sql

  (4)备份所有的库
    mysqldump -uroot -p123456 --all-databases >all.sql
  ******在innodb存储引擎禁止直接备份整个datadir目录,但是Myisam的可以支持。

3》数据恢复source工具,mysql工具
  source工具恢复方式
  #mysql -uroot -p123456
  mysql>source /opt/test.sql

  mysql工具
  #mysql -uroot -p123456 < /opt/zytest.sql

4》数据库迁移注意要点:
  所谓的数据库迁移,就是指数据库从一个系统迁移到另外一个系统上,数据库迁移的原因是多种多样的,肯能是硬件升级或者部署其它的业务 或者升级了mysql数        据库甚至选择其它的数据库都是有可能的。

迁移注意事项:
  (1) 相同的版本可以迁移
  (2) 注意版本所使用的引擎
  (3) 低版本可以向高版本迁移,高版本兼容低版本数据库
  (4) 高版本不能向低版本迁移,造成的数据不兼容的情况
  (5) 在迁移时请注意数据导出和数据备份,出现迁移失败,要立即启动备用方案,保证公司业务的连续性
  (6) 在数据迁移时,请先写好的你的技术文档,在本地做了完整的测试以后 方可尝试迁移
  (7) 对数据迁移的操作正常来说,选择用户量最小的时候,大部分操作都是在凌晨1-4之间做迁移
  (8) DBA在做数据迁移的时候,需要相关业务人员联合支持(业务相关人员,主程序员、测试人员、市场人员、产品人员、运维人员 至少需要1个)保证数据迁移后                的安全性和稳定性.

  ===========================权限:

1》权表介绍:
    安装mysql时自动生成一个名为mysql的数据,mysql数据库下面存储都是权限表,用户登录以后,mysql数据系统会根据 这些权限表的内容给每个用户赋予相应的      权限,其中最重要的表为:mysql中存在4个控制权限的表,分别为user表,db表,tables_priv表,columns_priv表

  • user表范围列决定是否允许或拒绝到来的连接。对于允许的连接,user表授予的权限指出用户的全局(超级用户)权限,这些权限适用于服务器上的all数据库;
  • db表范围列决定用户能从哪个主机存取哪个数据库,权限列决定允许哪个操作,授予的数据库级别的权限适用于数据库和它的表;
  • tables_priv和columns_priv表类似于db表,但是更精致:它们在表和列级应用而非在数据库级。授予表级别的权限适用于表和所有它的列,授予列级别的权限只适           用于专用列。
  • procs_priv表适用于保存的程序,授予程序级别的权限只适用于单个程序。

          1>user表
    user表用户包括了Host,User,Password,分别表示主机名、用户名、密码。
    select Host,User,Password from user;

                    澳门新濠3559 7

          2>db表
    db表Mysql数据库中非常重要的权限表,db表中存储了某个用户对一个数据库的权限。
    db表的用户列有3个字段,分别是 Host,Db和User,这个3个字段表示主机名、数据库名、用户名:
    host表的用户列有两个字段分别是Host和Db,这两个字段表示:主机名、数据库名
    Host表是db表的扩展,如果db表中找不到Host字段值。就需要到host表中去找。这个host表很少用到,同行db表满足需求了。

    user权限表:记录允许连接到服务器的用户帐号信息,里面的权限是全局级的。
    db权限表:记录各个帐号在各个数据库上的操作权限。
    table_priv权限表:记录数据表级的操作权限。
    columns_priv权限表:记录数据列级的操作权限。
    host权限表:配合db权限表对给定主机上数据库级操作权限作更细致的控制,这个权限表不受GRANT和REVOKE语句的影响。         

2》用户登录和退出mysql服务器:

   mysql命令语句用法:
      -h 参数后面接主机或者主机IP hostname为主机名,hostIP为主机IP
      -P 参数后面接Mysql服务的端口号,通过指定的端口,
      -u 参数后面链接用户,username为用户名
      -p 参数会提示密码
    DatabaseName 参数指明登录到哪一个数据库中,

  如果没有该参数,会直接登录到Mysql数据库中.然后可以使用USE命令来选择书库-e 参数可以可以直接加SQL语句,登录MYSQL服务器以后即可执行这个SQL语       句,然后退出Mysql服务器;    

3》创建和删除普通用户

  1>新建普通用户:
    create user ‘用户名称’@’访问地址本地还是远程?’indentified by ‘用户密码’
    create user ‘test1’@’localhost’identified by ‘test1’;
    grant 用户的权限(查询插入、更新等) on 在那个数据库的表 to ‘用户名称’@’授权方位地址’ identified by ‘用户密码’
    grant select on *.* to ‘test1’@’localhost’ identified by ‘test1’

  2>删除普通用户:
    DROP USER ‘user’@’localhost’
    drop user ‘test1’@’localhost’
    delete from mysql.user where host=’localhost’ and User=’test1’;     

4》普通用户和root用户的的密码管理:
  操作系统用的是root用户,数据库系统用也是root用户,这两用户不是一起的;

  1>Mysqladmin命令来修改root密码,只能超级用户来修改超级用户密码
    mysqladmin –u username –p password ‘new_password’;
    mysqladmin –u root –p password ‘123456’

  2>普通用户如何修自己的密码:password(‘strt’)函数加密
    mysql –u test1 –p 123456
    >set password=password(‘zytest123’);

  3>root用户密码丢失怎么办?
    使用–skip-grant-tables选项启mysql服务:

    /etc/init.d/mysqld start –skip-grant-tables 这个时候就不需要密码了。
    >update mysql.user set password=password(‘123456’) where User=’root’ and Host=’localhost’;
    >flush privileges;刷新(加载)权限

       4>增加远程超极管理用户
    >update table user set host='%' where user='root';
    > grant all privileges on *.* to admin@'%' identified by '123456' with grant option;
    >flush privileges;

5》DBA SQLyog工具使用
  以上所有的权限都可以通过工具来进行分配和使用。
  SQLyog-8.7.0-4
  注册码:
    Name: Any
    Registration Code: 26f359fc-e3f6-4727-8af1-72a1a4a0819d

  备份数据库的表时可能会遇到以下问题:

   mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': You have an error in your SQL syntax; check the manual that corresponds to your       MySQL server version for the right syntax to use near 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1 (1064)

  解决方法:
    mysqldump的版本比现在mysql 数据库的版本低
    cp /usr/local/mysql/bin/mysqldump /usr/bin/mysqldump

 

 =========================Mysql日志:

mysql日志是记录mysql数据库的日常操作和信息的文件,Mysql中日志可以分为:
    二进制日志
    错误日志
    通用查询日志
    慢查询日志
 分析这些日志可以了解整个mysql日志的运行情况。

mysql日志可以分为4种,
    二进制日志:以二进制文件的形式记录了数据库中,但是不记录查询语句
    错误日志:记录用户登录和记录查询的信息
    慢查询日志:记录执行时间超过指定时间的操作.
    通用查询日志:用查询日志用来记录用户的所有操作,包括启动和关闭MySQL服务、更新语句、查询语句等
    除了二进制日志外,其他日志都是文本文件,日志文件通常存储在Mysql数据库目录下。

1》二进制日志

*  二进制日志也就是变更日志(updatelog),主要用于记录数据库的变化情况,通过二进制日志可以查询Mysql数据库进行了那些改变;

  1>启动二进制日志
    默认情况下,二进制功能是关闭的,我们编辑my.cnf
    log-bin=/data/masterlog
    二进制日志与数据库数据最好不要放到同一块硬盘上面,如果一块硬盘坏掉了,另外一块硬盘存储二进制日志,我们可以通过二进制来恢复数据;

  2>删除所有二进制
    mysql->reset master;

  3>指定删除某范围二进制日志
    mysql->purge master logs to ‘mylog_0000021’ 删除从000001到00000021二进制日志

  4>根据创建时间来删除日志
    mysql->purge master before‘2010-12-10 16:00:00’ 删除2010-12-10 16:00:00 之前的二进制日志。*

**      5>mysqlbinlog命令来还原操作,还原日志从小到大

    mysqlbinlog mylog.000001 | mysql –uroot –p123456
    mysqlbinlog mylog.000002 | mysql –uroot –p123456
    mysqlbinlog mylog.000003 | mysql –uroot –p123456
    mysqlbinlog mylog.000004 | mysql –uroot –p123456
    mysqlbinlog mylog.000005 | mysql –uroot –p123456

  6>暂停二进制功能
    mysql->set sql_log_澳门新濠3559,bin=0; 暂停
    mysql->set sql_log_bin=1; 启动

   7>重新刷新二进制日志
    mysqladmin –u root –p flush-logs 系统会启用一个新的二进制日志文件
    flush-logs正确来说应该是刷新所有的日志。**

**     8>验证二进制日志文件pos号来恢复
    mysql>reset master; 清空所有的二进制日志
    mysql>create database gongdatest;
    mysql>use mysql;
    mysql>create databse alvin;
    mysql>drop database gongdatest;
    # mysqlbinlog masterlog.000001 开始查看二进制文件的内容,     
我们可以看到     at 120 为pos号的起点     at 232 为pos号的结束  **

                澳门新濠3559 8

**         # mysqlbinlog --start-position="120" --stop-position="232" masterlog.000001 查看一下是否正确,     # mysqlbinlog --start-position="120" --stop-position="232" masterlog.000001 | mysql -uroot –p开始恢复**

  9>二进制日志文件时间点来恢复
    # mysqlbinlog masterlog.000001 开始查看二进制文件的内容,
    我们可以看到其始时间点为:21:57:55,其结束时间点为:21:58:07,通过时间点恢复**

**                 澳门新濠3559 9**

**            # mysqlbinlog –start-datetime=”2016-08-13 21:57:55”–stop-datetime=”2016-08-13
    21:58:07” masterlog.000001 查看输出的是否正确?

   # mysqlbinlog –start-datetime=”2016-08-13 21:57:55”–stop-datetime=”2016-08-13
    21:58:07” masterlog.000001 | mysql –uroot –p 开始恢复数据**

**2》**错误日志****

**  在mysql数据库中,错误日志功能默认是开启的,一般存储在mysql数据库的文件夹下面,错误日志通常称为hostname.err 其中hostname表示mysql服务器的主机名,错误日志可以通过my.cnf 中log-error选入来设置
         log-error=/data/master.err**

*3》*通用查询日志****

**  默认情况下,功能日志是关闭的,通过my.cnf来开启日志:
    log=/data/acces.log
  用户所有的操作记录到通用查询日志当中.
  删除通用查询日志两种方法:
  1>删除源文件,使用mysqladmin 重新生成一个新的文件
    rm acces.log && mysqladmin –u root –p flush-logs
  2>直接清空源文件
    echo> acces.log**

**4》*慢查询日志*****

***  慢查询日志用来记录执行时间指定时间的查询语句,默认情况下,慢查询日志功能是关闭的,通过修改my.cnf来开启;

  log-slow-queries=/data/slow.log
  long_query_time=n n代表默认时间为10秒。可以自行设置
  删除慢查询日志两种方法:
  1>删除源文件,使用mysqladmin 重新生成一个新的文件
    rm slow.log && mysqladmin –uroot –p flush-logs
  2>直接清空源文件
    echo> slow.log***

***           ***

编辑:操作系统 本文来源:Mysql性能优化二,不会阻塞任何应该程序而且能保

关键词: