mysql运维入门1：基础及备份还原

存储引擎

myisam

表强调的是性能
执行速度比innodb类型更快
不提供事务支持
如果执行大量的select操作，是首选
支持表锁，不支持行锁

innodb

提供事务支持、外键等高级数据库功能
出于性能考虑，是首选
支持行锁

安装方式

yum安装

yum install -y mysql-server mysql-devel mysql

源码安装

cd /usr/src

wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.63.tar.gz

tar xvf mysql-5.1..tar.gz

cd mysql-5.1.

./config --prfix=/usr/local/mysql --enable-assembler && make && make install

配置mysql为系统服务

cd /usr/local/mysql/share/mysal/my-medium.cnf /etc/my.cnf

cp /usr/local/mysql/share/mysql/mysql.server /etc/rc.d/oinit.d/mysqld

chkconfig --add mysqld

chkconfig --level  mysqld on

cd /usr/local/mysql

useradd mysql

chown -R mysql.mysql /usr/local/mysql

/usr/local/mysql/bin/mysql_install_db --user=mysql

chown -R mysql var

/usr/local/mysql/bin/mysqld_safe --user-mysql $

日常操作命令

建库 create database dbname;
用库 use dbname
看库的表 show tables;

授权

grant all privileges on dbname.* to username@hostip identified by password;
grant all on dbname.* to username@hostip identified by password;
grant select,insert,update,delete on *.* to username@hostip identified by password;

刷新权限 flush privileges;
mysql备份或者导出 mysqldump -uroot -ppassword dbname > /tmp/dbname.sql
mysql导入 mysql -uroot -ppassword dbname < /tmp/dbname.sql
修改mysql root密码 mysqladmin -uroot -ppassword password newpassword;
删库 drop database dbaname;
清空表 truncate tabname 或者 delete from tabname
自动备份mysql数据库脚本

#! /bin/sh

#auto backup mysql

#auth date

#define path

BAKDIR=/data/backup/mysql/`date +%Y-%m-%d`

MYSQLDB=webapp

MYSQLPW=backup

MYSQLUSER=backup

#must use root user run scirpts

if

    [ $UID -ne  ];then

    echo This script must use the root user!!!

    sleep

    exit

fi

#Define DIR and mkdir DIR

if

    [ ! -d $BAKDIR ];then

    mkdir -p $BAKDIR

else

    echo This is $BAKDIR exists ...

fi

#Use mysqldump backup mysql

/usr/bin/mysqldump -u$MYSQLUSR -p$MYSQLPW -d $MYSQLDB > $BAKDIR/webapp_db.sql

echo "This mysql backup successfully"

utf-8设置方法和密码破解

/etc/myc.cnf加入配置：

[client]

default-character-set=utf8

[mysqld]

character-set-server=utf8

[mysql]

defualt-character-set=utf8

stop mysqld

start mysqld: /usr/bin/mysqld_safe --user=mysql --skip-grant-tables &
change password: update user set password=password('newpassword') where use='root';
flush privileges
stop mysqld;start mysqld

mysql+discuz

yum安装LAMP

yum install httpd httpd-devel mysql-server mysql-devel php php-devel php-mysql -y

源码安装Apache

yum install apr-devel apr-util-devel -y

cd /usr/src

wget http://mirror.bit.edu.cn/apache/httpd/httpd-2.2.27.tar.gz

tar xzf httpd-2.2..tar.gz

cd httpd-2.2.

./configure --prefix=/usr/local/apache --enable-so --enable-rewrite &&make &&make install

源码安装mysql(见上面)

源码安装php

cd /usr/src

wget http://mirrors.sohu.com/php/php-5.3.28.tar.bz2

tar jxf php-5.3..tar.bz2 ;cd php-5.3.

./configure --prefix=/usr/local/php5 --with-config-file-path=/usr/local/php/etc --with-apxs2=/usr/local/apache/bin/apxs --with-mysql=/usr/local/mysql
make && make install

源码安装整合apache+php

httpd.conf增加：

LoadModule     php5_module modules/libphp5.so （默认已存在）

AddType     application/x-httpd-php .php

DirectoryIndex  index.php index.html (把index.php加入index.html之前)

建index.php

cat >>/usr/local/apache/htdocs/index.php <<EOF

<?php

phpinfo();

?>
EOF

restart apache

源码安装discuz

cd  /usr/src ;wget http://download.comsenz.com/DiscuzX/3.1/Discuz_X3.1_SC_UTF8.zip

unzip Discuz_X3.1_SC_UTF8.zip -d /usr/local/apache/htdocs/

cd /usr/local/apache/htdocs/

chmod  -R data/ uc_server/ config/ uc_client/

数据创建及授权

create database discuz charset=utf8;

grant all on discuz.* to root@'localhost' identified by "";

备份与恢复

常见备份方法

mysqldump备份
cp备份
sqlhotcopy备份
主从同步备份
xtrabackup备份

mysqldump备份会锁表，锁表直接导致数据库只能select，不能执行insert、update等操作

如果myisam表可以执行参数 --local-tables=false禁用锁表，但是可能会造成数据信息不一致
如果是支持事务的表，例如innodb和BDB， --single-transaction参数是一个更好的选择，因为它不锁定表
```
mysqldump -uroot -p123456 --all-databases --opt --single-transaction > backupall.sql
```
--opt等价于--add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset，默认开启
mysqldump如果没有指定--quick或者--opt选项，则会将整个结果集放在内存中，导出大数据的话会出问题，毕竟内存存不了这么多数据

xtrabackup

mysql冷备、mysqldump、mysql热拷贝均不能实现增量备份
percona-xtrabackup可以实现增量备份
包含两个工具：xtrabackup、innobackupex
xtrabackup只能备份innodb和xtradb两种数据表，不能备份myisam数据表
innobackupex-1.5.1则封装了xtrabackup，是一个脚本封装，可以同时处理innodb和myisam
在处理myisam时需要加一个读锁
http://www.percona.com/docs/wiki/percona-xtrabackup:start

安装xtrabackup

wget http://www.percona.com/redir/downloads/XtraBackup/XtraBackup-2.0.0/binary/Linux/x86_64/percona-xtrabackup-2.0.0.tar.gz

tar zxvf percona-xtrabackup-2.0..tar.gz

cp  percona-xtrabackup-2.0./bin/innobackupex /usr/bin/innobackupex

cp  percona-xtrabackup-2.0./bin/xtrabackup /usr/bin/xtrabackup

cp  percona-xtrabackup-2.0./bin/xtrabackup_51 /usr/bin/xtrabackup_51

cp  percona-xtrabackup-2.0./bin/xtrabackup_55 /usr/bin/xtrabackup_55

数据库全备份

innobackupex --user=root --password= /data/backup/mysql/

数据库恢复

```
innobackupex --defaults-file=/etc/my.cnf --user=root --password= --apply-log  /data/backup/mysql/--20_00--
```
apply-log 备份过程产生新的写操作，造成数据库备份不一致，该选项会记录写过程的相关log，恢复时把已提交的事务写入数据文件，没有提交的事务进行回滚
apply-incremental-backup 不会对redo log中没有提交的事务进行回滚，因为下次增量备份该事物已经提交了
bakup和apply-log所用的mysqlbackup版本要一致
恢复数据库

innobackupex --defaults-file=/etc/my.cnf --user=mysql --password= --copy-back /data/backup/mysql/--20_00--/

恢复出来的os文件属主是owner，需要更改权限

```
chown -R mysql:mysql  /var/lib/mysql/
```

增量备份

增量备份仅能引用于innodb和xtradb表
对于myisam表而言，执行增量备份时其实进行的是完全备份
先做一次全备

innobackupex --user=root --password= --databases=wugk01 /data/backup/mysql/

第一次增量，base以上次全备为基础

innobackupex --defaults-file=/etc/my.cnf --user=root --password= --databases=wugk01 --incremental /data/backup/mysql/ --incremental-basedir=/data/backup/mysql/--20_13--/

第二次增量，base以上次增备为基础

innobackupex --defaults-file=/etc/my.cnf --user=root --password= --databases=wugk01 --incremental /data/backup/mysql/ --incremental-basedir=/data/backup/mysql/--20_13--/

恢复，前面的apply-log需要加read-only进行prepare

innobackupex --defaults-file=/etc/my.cnf --user=root --password=  --apply-log --redo-only /data/backup/mysql/--20_13--/

innobackupex --defaults-file=/etc/my.cnf --user=root --password=  --apply-log --redo-only /data/backup/mysql/--20_13--/ --incremental-dir=/data/backup/mysql/--20_13--/

innobackupex --defaults-file=/etc/my.cnf --user=root --password=  --apply-log --redo-only /data/backup/mysql/--20_13--/ --incremental-dir=/data/backup/mysql/--20_13--/

innobackupex --defaults-file=/etc/my.cnf --user=root --password=  --copy-back /data/backup/mysql/--20_13--/

巴特西