MySQL数据备份与还原、日志和性能优化

1.数据备份与还原

数据备份

使用mysqldump命令备份

备份一个数据库：

Mysqldump –u  username     -p      dbname table1         table2       …>BackupName.sql

备份多个数据库：

Mysqldump      -u      username         -p      --databases  dbname1  dbname2…>BACKUPname.sql

备份所有数据库：

Mysqldump      -u      username         -p      --alladatabases>BackupName.sql

使用mysqlhotcopy工具快速备份：

[root@localhost~]#mysqlhotcopy            [option]    dbname1  dbname2… backupDir/

数据还原

使用mysql命令还原：

Mysql       -u      root           -p      [dbname]<backup.sql

直接复制到数据库目录：

Chown      -R     mysql.mysql     dataDir

数据库迁移

相同版本的Mysql数据库之间的迁移：

Mysqldump      -h      name1      -u      root           --password=password1   --all-databases|mysql      -h      host2        -u      root          --password=password2

不同版本的mysql数据库之间的转移

表的导出和导入

用SELECT…INTO     OUTFILE导出文本文件：

SELECT     [列名]       FROM       table         [WHERE   语句]INTO        OUTFILE   ‘目标文件’[OPTION]

用mysqldump命令导出文本文件：

Mysqldump      -u      root -p      password                   -T      目标目录         dbname   table[option];

用mysql命令导出文本文件：

Mysql       -u      root           -p      password                   -e”SELECT 语句”    dbname>路径

用LOAD DATA INFILE方式导入文本文件：

LOAD        DATA         [LOCAL]    INFILE       file    INTO         TABLE       table[OPTION];

用MySQLimport命令导入文本文件：

MySQLimport  -u      root -pPassword [--LOCAL]      dbname   file    [OPTION]

2.MySQL日志

Mysql日志是用来记录mysql数据库的客户端连接情况、SQL语句的执行情况和错误信息等

Mysql日志分为四种：

二进制日志：以二进制文件的形式记录了数据库中的操作，但不记录查询语句

错误日志：记录mysql服务器 的启动、关闭和运行错误等信息

通用查询日志：记录用户登录和记录查询的信息

慢查询日志：记录执行时间超过指定时间的操作

二进制日志

启动和设置二进制日志:

Log-bin     [=DIR\[filename]]

查看二进制日志:

Mysqlbinlog     filename.number

删除二进制日志

删除所有二进制日志:

RESET       MASTER;

根据编号来删除二进制日志:

PURGE     MASTER   LOGS         TO    ‘filename.number’

根据创建时间来删除二进制日志:

PURGE     MASTER   LOGS         BEFFORE’yyyy-mm-dd hh:MM;ss’

使用二进制日志还原数据库:

Mysqlbinlog     filename.number     |mysql –u         root -p

暂时停止二进制日志功能:

SET   SQL_LOG_BIN=0(1);

错误日志

启动和设置错误日志

查看错误日志

删除错误日志

Mysqladmin     -u      root -p      flush-logs

通用查询日志

启动和设置通用查询日志

查看通用查询日志

删除通用查询日志

Mysqladmin     -u      root           -p      flush-logs

慢查询日志

启动和设置慢查询日志

查看慢查询日志

删除慢查询日志

Mysqladmin     -u      root           -p      flush-logs

3.性能优化

优化简介:

SHOW       STATUS     LIKE ‘value’

Value参数介绍:

Connections:连接Mysql服务器的次数

Uptime:mysql服务器的上线时间

Slow_queries:慢查询的次数

Com_select：查询操作的次数

Com_insert：插入操作的次数

Com_update：更新操作的次数

Com_delete：删除操作的次数

优化查询

分析查询语句:

EXPLAIN   SELECT     语句

DESCRIBE                  SELECT     语句

索引对查询速度的影响

使用索引查询

查询语句中使用like关键字

查询语句中使用多列索引

查询语句中使用OR关键字

优化子查询

优化数据库结构

将字段很多的表分解成多个表:

SELECT     *       FROM       student,student_extra    WHERE    student.id=student_extra.id

增加中间表

增加冗余字段

优化插入记录的速度

禁用索引(开启)       ALTER       table         表名         DISABLE   KEYS（ENABLE         KEYS）\

禁用唯一性检查(开启)  SET          UNIQUE_CHECKS=0(1);

优化INSERT语句

分析表、检查表和优化表

分析表:

ANALYZE           TABLE       表名[表名2]

检查表:

CHECK      TABLE       表名1[表名2][option]

优化表:

OPTIMIZE         TABLE       表名1[表名2]

优化Mysql服务器

优化服务器硬件

优化Mysql的参数

在my.cdf或者my.ini文件的[mysql]组中，参数有：

Key_buffer_size

Table_cache

Query_cache_type

Max_connections

Sort_buffer_size

Read_buffer_size

Read_rnd_buffer_size

Innodb_buffer_pool_size

Innodb_flush_log_at_trx_commit