MySQL数据库

2015年五月五日   

数据库备份方案

导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
例：mysqldump -u dbadmin -p myblog > /home/zhangy/blog/database_bak/myblog.sqlmysqlmys
一备份整个库文件

mysqldump -u root -p zhidian_test > /mysqldate
2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size = 100M
binlog_do_db = myslave #如果需要同时同步多个数据库，请另起一行设置binlog_do_db=需要同步的数据库名称
binlog_ignore_db = mysql

冷备份：停服务后再备份。
全库备份
service mysqld stop 停止服务
cp -r /var/lib/mysql/* /opt 拷贝原MySQL数据到备份目录
rm -rf /var/lib/mysql/* 删除原MySQL数据
service mysqld start 重新加载MySQL
cp -r /opt/mysql/* /var/lib/mysql/ 将备份的MySQL数据拷贝回原目录
chown -R mysql.mysql /var/lib/mysql 给 MySQL目录设权限
flush privileges; 刷新表的权限：如果在没退出MySQL服务的情况下，可以刷新表的权限。
service mysqld restart 重新启动服务

单表备份
myisam引擎的单表还原，可以修改库名
cp /opt/mysql/gbc/T12.* /var/lib/mysql/ 拷贝备份表T12.MYD T12.MYI T12.frm
chown -R mysql.mysql /var/lib/mysql/T12.* 给表T12赋权限

innodb引擎的单表还原，不能修改库名
cp /opt/mysql/gbc/T6.frm /var/lib/mysql/ 拷贝备份表T6.frm
cp /opt/mysql/ibdata1 /var/lib/mysql/ 拷贝备份表T6的数据
cp /opt/mysql/ib_logfile* /var/lib/mysql/ 拷贝备份表T6的日志
chown -R mysql.mysql /var/lib/mysql/ 给表T6赋权限

热备份：不影响线上业务的情况下备份
mysqldump 在线备份数据库的命令 mydumper也是在线备份命令

格式
mysqldump [options] [db_name [tbl_name ...]]
例如：
备份全库
mysqldump -A -x > /opt/all.sql
等价于下面的命令
mysqldump -h IP -u user -p passwd -A -x > /opt/all.sql
还原全库
mysql -h -u -p < /opt/all.sql

参数
-A 所有库
-x 加读锁
-h 要访问数据库的IP
-u 数据库的用户
-p 数据库的用户密码

备份单库
mysqldump -h localhost -u root -p -x gbc > /opt/gbc.sql
还原单库，要先建库，然后再还原
mysql gbc < /opt/gbc.sql

备份单表
mysqldump -x gbc T6 > /opt/T6.sql
还原单表
mysql gbc < /opt/T6.sql

增量备份
启用二进制的binglog日志,记录增删改的操作记录
vim /etc/my.cnf
log-bin=binlog 启用log-bin=名字
log-bin-index=binlog.index 启用binlong索引 = 索引名字
mysqlbinlog binlog.000001 打开binlog日志只能用mysqlbinlog这个命令。

重启mysql服务也自动增加binlog日志
mysql -e "flush logs" 会刷新新的binlog日志

备份binlog日志
mysqlbinlog binlog.000001 > /opt/day1.sql

还原binlog日志
mysq < /opt/day1.sql

每周一全备份，每天一增量备份
show master logs 查看binlog日志结构

从某个时间点还原
mysqlbinlog binlog.00000[1-3] --start-datetime="2014-07-02 14:03:00" > /opt/1.sql | mysql

还原到某个时间点之前
mysqlbinlog binlog.00000[1-3] --stop-datetime="2014-07-02 14:09:00" > /opt/1.sql | mysql

MySQL的AB备份(active/backup)
备份的服务器有2个线程，一个是i /o线程，一个是SQL线程，当备份服务器发现主服务器数据发生改变以后，
备份服务器的i/o线程会找主服务器的binlog日志，把改变的binlog日志交给SQL线程执行一遍，这样就同步了。

在主服务器上授权，把所有库的所有表的所有权限赋予给zhidian的用户，只能从192.168.0.100的IP登陆主服务器，密码是 zhidian
grant all privileges on *.* to 'zhidian'@'192.168.0.100' identified by "zhidian";
flush privileges; 刷新表的权限：如果在没退出MySQL服务的情况下，可以刷新表的权限。

查看谁登陆了主服务器
show full processlist;

在主服务器上/etc/my.cnf里定义一个优先级
server-id=1(数字越小，优先级越高)

然后在备用服务器上/etc/my.cnf里
定义优先级和主服务器、用户、密码、中继日志、中继日志索引
server-id=4
master-host=192.168.0.100
master-user=zhidian
master-password=zhidian
relay_log=/var/lib/mysql/mysql-relay-bin
relay_log_index=/var/lib/mysql/mysql-relay-bin.index

查看主服务状态
show master status

查看备用服务器状态
show slave status

手动指定主服务器
change master to
master-host='192.168.0.100'
master-user='zhidian'
master-password='zhidian'
master_log_file='binlog.000002'

在主服务器上my.cnf中可以设置
binlog_do_db=zhidian_test #只备份指定的库
expire_logs_days=10 #按天保存binlog，每10天生成一个binlog
max_binlog_size=2G #按大小保存binlog，每2G生成一个binlog
sync-binlog=1 #立马写到硬盘（0先放到内存再放进硬盘）

针对innodb引擎设置的
innodb_data_file_path=ibdata1:autoextend 自动扩大数据库大小
innodb_log_file_size=100M 按大小保存日志，每100M生成一个日志

其它说明
主服务器my.cnf
#binlog-do-db=需要备份的数据库名，可写多行
#binlog-ignore-db=不需要备份的数据库名，可写多行
从服务器my.cnf
# replicate-do-db=test 需要备份的数据库名
# replicate-ignore-db=mysql 忽略的数据库
# master-connect-retry=60 如果从服务器发现主服务器断掉，重新连接的时间差(秒)
以下设置也可直接修改my.cnf配置文件
log-bin=mysql-bin
master-host=192.168.1.22
master-user=repl
master-password=repl
master-port=3306

主从服务器同步维护
由于各种原因，导致主从数据不一致，在负载低的时候，进行手动同步.
在主服务器上执行

mysql>flush tables with read lock;
Query OK,rows affected (0.01 sec)
mysql>show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.0000011 | 260| | |
+------------------+----------+--------------+------------------+
在从服务器上执行
先得到当前主服务器的二进制文件名和偏移量,执行命令使从服务器与主服务器同步
mysql>select master_pos_wait('mysql-bin.0000011','260');
+--------------------------------------------------+
| master_pos_wait('mysql-bin.0000011','260') |
+--------------------------------------------------+
| 0 |
+--------------------------------------------------+
1 row in set (0.01 sec)
同步完成后，在主服务器上执行解锁
mysql>unlock tables;

切换主从服务器

当主服务器出现故障时，可将从服务器当主服务器来使用.步骤如下:
1、保证所有从数据库都已经执行了relay log中的全部更新，在从服务器中执行
stop slave io_thread,用show processlist检查，查看状态是否是Has read all relay log,表示更新完成.
mysql>stop slave io_thread;
Query OK,0 affected (0.00 sec)
mysql>show processlistG;
*************************** 2. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 4757
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
2、在从服务器上执行stop slave，reset master命令，重置成主数据库
mysql>stop slave;
Query OK,0 affected (0.00 sec)
mysql>reset master;
Query OK,0 affected (0.00 sec)