MySQL基本运维操作指南, 纯工具文
为了方便区分 mysql> 表示为MySQL命令行模式，其他为shell终端模式

1. 快速复制MySQL数据库

mysql> CREATE DATABASE `newDB` DEFAULT CHARACTER SET UTF8 COLLATE UTF8_GENERAL_CI; # 创建一个新库
shell# mysqldump olddb -uroot -ppassword --add-drop-table | mysql -uuser -ppassword # 本地快速拷贝
shell# mysqldump olddb -uroot -ppassword --add-drop-table | mysql -hlocalhost -uuser -ppassword # 远程快速拷贝
shell# mysqldump -u root --password=xxx dbname | mysql -u root --password=yyy destinationhost dbname # 更加常规的写法

2. 备份方案

mysqldump

(1)mysqldump命令生成的SQL文件有最好的兼容性.缺点是速度较慢.
(2)在备份期间，所有的写操作都将被阻断.
(3)shell# mysqldump -u root -p –all-databases > backup.sql
(4)shell# mysqldump -u root -p dbname [tables] > backup.sql # 在大型系统中，可以只日常备份用户的UGC数据，而忽略系统本身已经备份或者产生的元数据
(5)shell# mysqldump [options] –databases dbname1 dbname2 … > backup.sql
(6)备份原理: 备份前给每个表加上一个读操作锁,每条INSERT命令对应着尽可能多的数据记录,插入DROP TABLENAME命令,保留所有数据库属性,使用UTF8字符集创建备份文件,并且增加SQL命令保留实际字符集信息.
(7)shell# mysqldump -u root -p –lock-all-tables databasename > backup.sql # 备份MyISAM数据表
(8)shell# mysqldump -u root -p –skip-opt –single-transaction –add-drop-table \ # 禁用默认选项，以使用–single-transaction事务
–create-options –quick –extended-insert \
–set-charset –disable-keys databasename > backup.sql #把整个备份工作放在一个事务当中.
(9)可以使用 mybackup脚本进行备份，更加方便. http://www.mswanson.com/mybackup/

(10) 从sql文件恢复数据库
shell# mysql -u root -p –default-character-set=latin1 databasename < backup.sql # 也许需要使用 mysqladmin create databasename 重建数据库
mysql> source /path/to/backup.sql # 通过mysql控制台执行SQL文件
(11) 如何快速复制数据库请参考 1. 快速复制MySQL数据库 # vim在非insert模式下可以使用 Shift+#快速跳转
(12) UNIX/Linux下可以用 mysql_fix_privilege_tables脚本对用老版本的备份恢复出来的mysql数据表进行升级
(13) 可以考虑把老版本的mysql库迁移到新版本的mysql库，重命名为 mysql_4 #加版本号
(14) 各个主流的数据库厂商都会拥有跨平台迁移(到自己平台)的工具，当需要跨平台迁移时，请采用MySQL Migration Toolkit,可以参考4-数据库迁移报告.

导入和导出文本文件

mysql> LOAD DATA # 读入一个文本文件并导入到数据表
shell# mysqlimport # 命令行版本

mysql> SELECT .. INTO FILE # 查询结果写入文件
shell# mysqldump #类似于SELECT .. INFO file
mysql> SELECT * INTO OUTFILE ‘/tmp/mysql_user.txt’ FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘\”‘ FROM user ; # 将user表导入文件
shell# mysqldump -u root -p –xml mysql user > /tmp/mysql_user.xml # 将数据库导出成为xml文件

导入导出csv文件

FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘ LINES TERMINATED BY ‘\r\n’ #常规
shell# mysqlimport –local “–fields-enclosed-by=\”” \
“–lines-terminated-by=\r\n” exceptions /tmp/backup.csv

便于阅读的mysql文件

shell# mysql -u root –password=151warden –batch –default-character-set=latin1 “–execute=SELECT FROM user;” mysql > /tmp/mysql_output.txt # 各个数据列将由制表符隔开，并且包含各个数据列的标题．可以转化为csv文件
shell# mysql -u root –password=151warden –html –default-character-set=latin1 “–execute=SELECT FROM user;” mysql > /tmp/mysql_output.html # 导出html表格文件
shell# mysql -u root –password=151warden –xml –default-character-set=latin1 “–execute=SELECT * FROM user;” mysql > /tmp/mysql_output.xml # 导出成为xml文件

视图的备份与数据表区别并不大，但是存储过程最好的备份方法是将其当做源代码，与源代码共同管理,并加上MySQL对应的数据库时间戳.

mysqlhotcopy 可以保证伪热备份，但是只支持MyISAM引擎，所以一般不考虑

shell# mysqlhotcopy dbname1 dbname2 .. backup/ # 将数据库文件直接hotcopy
shell# chown -R mysql.mysql /var/lib/mysql/dbname # 注意将文件属主修改为mysql.mysql

InnoDB hot Backup工具可以进行热备份

3. 日志文件与定期制作增量备份文件相结合.

启用日志配置/etc/my.cnf 并且重启mysql服务

[mysqld]
log-bin=/path/to/mysql_bin_log # 将在/var/lib/mysql/目录下生成 mysql_bin_log.000001  mysql_bin_log.index 两个文件
请务必在使用数据库镜像的时候，建立这样的文件.否则将以服务器主机名称作为文件名，一旦切换则无法继续使用.
mysql>FLUSH LOGS; #切换到新的日志文件的最佳时机是在对系统进行了一次完整备份之后.
shell# mysqladmin flush-logs # 同上
PURGE MASTER LOGS TO # 删除日志文件

利用日志文件恢复,应该用最近一次制作的完整备份恢复数据库，然后依次使用日志文件恢复到最近的可用状态

shell# mysqlbinlog name-bin.00031 | mysql -u root -p
shell# mysqlbinlog name-bin.00032 | mysql -u root -p

执行数据备份的流程如下

全量备份: 全量备份当前数据库,删除所有日志文件，或者删除其引用.启用新的日志文件
增量备份: 启用新的日志文件

执行恢复或者合并时: 从全量备份数据库文件重建数据库,执行日志恢复

4. 数据库镜像

为镜像机制创建一个MySQL用户账号

(1)GRANT REPLICATION SLAVE ON . TO replicuser@slavehostname IDENTIFIED BY ‘slavepassword’;
(2)slavehostname需要在路由表中配置
(3)在从属系统上使用 mysql -u replicuser -p slavepassword -h masterhostname来测试是否连接成功
(4) 在配置从属计算机的时候，需要用一个日志文件名和这个日志文件的起始位置，从计算机将从这个日志文件里的这个位置开始读取数据.

mysql> FLUSH TABLES WITH READ LOCK;  
Query OK, 0 rows affected (0.00 sec)  

mysql> SHOW MASTER STATUS;  
+----------------------+----------+--------------+------------------+  
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB |  
+----------------------+----------+--------------+------------------+  
| mysql_bin_log.000002 |      106 |              |                  |  
+----------------------+----------+--------------+------------------+  

1 row in set (0.00 sec)

(5) 执行mysqladmin -uroot -p shutdown关闭MySQL服务器
(6) 日志文件应该单独指定文件夹，以免镜像时的复杂操作弄混
(7) 创建数据库的初始副本
cd /var/lib/mysql/
tar vzcf snapshot.tgz .
(8) 配置/etc/my.cnf

[mysqld]  
log-bin=/var/log/mysqllog/mysqlmaster
server-id=1

(9) 如果配置文件路径不存在，需要建立并且赋予mysql.mysql属主权限
shell# chown -R mysql.mysql mysqllog/
shell# chmod -R 775 mysqllog/
(10) 重启数据库服务器(Master)
(11) 关闭数据库服务器(Slave)
/etc/init.d/mysqld stop
(12) 拷贝数据库备份文件到从属服务器并且重建数据库
scp root@192.168.209.129:/tmp/snapshot.tgz /tmp/
cd /var/lib/
mv mysql/ mysql-bak/ # 将安装后的mysql文件先备份起来
mkdir mysql # 新建mysql文件夹,因为/etc/my.cnf中应该还是这么配置的
cd mysql
tar xzfv /tmp/snapshot.tgz # 解压快照文件到当前目录
cd ..
chown mysql.mysql -R mysql/ # 将mysql文件夹的属主换为mysql用户
chcon -R -t mysqld_db_t /var/lib/mysql/ # 将模式切换位 mysqld_db_t
(13) 配置从属服务器的/etc/my.cnf
[mysqld]
server-id=11
(14) 启动镜像机制
CHANGE MASTER TO
MASTER_HOST=’192.168.209.129’,
MASTER_PORT=3306,
MASTER_USER=’replicuser’,
MASTER_PASSWORD=’151warden’,
MASTER_LOG_FILE=’mysqlmaster.000002’,
MASTER_LOG_POS=350;
(15) 关闭主服务器上的防火墙
service iptables stop
iptables: Setting chains to policy ACCEPT: filter [ OK ]
iptables: Flushing firewall rules: [ OK ]
iptables: Unloading modules: [ OK ]
(16) 测试连接,在slave机上输入
shell# mysql -ureplicuser -p -h192.168.209.129 # 如果失败，请检查
(17) 启动slave
mysql> start slave；
mysql> show slave status \G
Slave_IO_Running: Yes #确保为yes
Slave_SQL_Running: Yes #确保为yes
如果上述步骤出错，请检查slave服务器的数据库日志/var/log/mysqld.log
(18) 在主服务器插入数据，检查是否在从服务器有相应的数据

本文标题:30-mysql数据备份基础

文章作者:盛领

发布时间:2015年08月30日 - 20:40:01

原始链接:http://blog.xiaoyuyu.net/post/16fe81c6.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际转载请保留原文链接及作者。

如您有任何商业合作或者授权方面的协商，请给我留言：sunsetxiao@126.com

欢迎您扫一扫上面的微信公众号，订阅我的博客！

坚持原创技术分享，您的支持将鼓励我继续创作！