MYSQL主从备份还原

mysql主从复制用途：

实时灾备，用于故障切换
读写分离，提供查询服务
备份，避免影响业务
实现MySQL主从复制需要进行的配置：

主服务器配置

主服务器：
开启二进制日志
配置唯一的server-id
获得master二进制日志文件名及位置
创建一个用于slave和master通信的用户账号

从服务器：
配置唯一的 server-id
使用master分配的用户账号读取master二进制日志
启用slave服务

1.主从数据库版本最好一致
2.主从数据库内数据保持一致

主数据库master修改：

1.修改mysql配置：

vim /etc/mysql/my.cnf

[mysqld]
log-bin=mysql-bin  #开启二进制日志
server-id=1  #设置server-id

重启mysql，创建用于同步的用户账号

2.登录mysql

mysql -hlocalhost -uname -ppassword

#创建用户并授权：用户：rel1  密码：slavepass
mysql> CREATE USER 'repl'@'123.57.44.85' IDENTIFIED BY 'slavepass'; #创建用户
mysql> GRANT REPLICATION SLAVE ON . TO 'repl'@'123.57.44.85'; #分配权限
mysql>flush privileges; #刷新权限

3.查看master状态，记录二进制文件名(mysql-bin.000003)和位置(73)：

mysql > SHOW MASTER STATUS;

±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000003 | 73 | test | manual,mysql |
±-----------------±---------±-------------±-----------------+

从服务器slave配置：

1.修改mysql配置:

vim /etc/mysql/my.cnf

[mysqld]
server-id=2  #设置server-id，必须唯一

2.重启mysql，打开mysql会话，执行同步SQL语句(需要主服务器主机名，登陆凭据，二进制文件的名称和位置)

mysql> CHANGE MASTER TO
-> MASTER_HOST='182.92.172.80',
-> MASTER_USER='rep1',
-> MASTER_PASSWORD='slavepass',
-> MASTER_LOG_FILE='mysql-bin.000003',
-> MASTER_LOG_POS=73;

3.启动slave同步进程：

mysql>start slave;

4.查看slave状态：

mysql> show slave status\G;

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 182.92.172.80
Master_User: rep1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 11662
Relay_Log_File: mysqld-relay-bin.000022
Relay_Log_Pos: 11765
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:

当Slave_IO_Running和Slave_SQL_Running都为YES的时候就表示主从同步设置成功了。接下来就可以进行一些验证了，比如在主master数据库的test数据库的一张表中插入一条数据，在slave的test库的相同数据表中查看是否有新增的数据即可验证主从复制功能是否有效，还可以关闭slave（mysql>stop slave;）,然后再修改master，看slave是否也相应修改（停止slave后，master的修改不会同步到slave），就可以完成主从复制功能的验证了。

master开启二进制日志后默认记录所有库所有表的操作，可以通过配置来指定只记录指定的数据库甚至指定的表的操作，具体在mysql配置文件的 [mysqld] 可添加修改如下选项：

[mysqld]
#不同步那些文件
binlog-ignore-db = mysql
binlog-ignore-db = test
binlog-ignore-db = information_schema
#只同步那些数据
binlog-do-db = game 

备份和还原

mysqldump：备份工具，基于mysql协议向mysql发起查询请求，并将查得的所有数据转换成insert等写操作语言，保存文本中。
mysqlimport：数据导入工具

备份

mysqldump -u 用户名 -p 库名 表名 > 目标
mysqldump -u 用户名 -p 库名 > 目标
mysqldump -uroot -p --all-databases  >/tmp/mysqldump1116 #--all-databases备份所有数据库
tar -czvf mysqldump1116.tar.gz mysqldump1116 #对备份文件打包压缩

还原两种方式：

第一种方式

mysql -u [用户名] -p[密码] [数据库名] < backup.sql

tar -xzvf  mysqldump1116.tar.gz
mysql -uroot -p < mysqldump1116

第二种方式

登录mysql 后
source 备份文件名

tar -xzvf  mysqldump1116.tar.gz   -C  /opt/mysqldump/   #解压备份文件
source /opt/mysqldump/mysqldump1116

定期备份与清理

#!/bin/bash
#备份保存路径
backup_dir=/opt/backup/mysqldump
#日期
dd=`date +%Y-%m-%d-%H-%M-%S`
#备份工具
tool=/usr/bin/mysqldump
#用户名
username=root
#密码
password=123456
#将要备份的数据库
#database_name=testdb

#如果文件夹不存在则创建
if [ ! -d $backup_dir ];
then
    mkdir -p $backup_dir;
fi

#简单写法  mysqldump -u root -p123456 users > /root/mysqlbackup/users-$filename.sql  
#--all-databases是备份所有库
$tool -u$username -p$password --all-databases > $backup_dir/$dd.sql
cd $backup_dir
#对dump文件打包压缩
tar -czvf  $dd.tar.gz $dd.sql
sleep 10s
#移除dump文件
rm -rf $dd.sql

#写创建备份日志
echo -e "\n##########################################" >> $backup_dir/log.txt
echo "start `date`" >> $backup_dir/log.txt
echo "create $backup_dir/$dd.tar.gz" >> $backup_dir/log.txt
#删除30天以上备份
find $backup_dir -name "*.tar.gz" -type f -ctime +31 -exec rm -rf {} \; > /dev/null 2>&1
#30天以上备份文件写到日志
find $backup_dir -name "*.tar.gz" -type f -ctime +30 -exec echo >>$backup_dir/log.txt {} \;
echo -e "complete mysqldump `date` \n" >> $backup_dir/log.txt

计划任务

0 3 * * * bash /opt/mybackup/mydump_scrip.sh

拓展，清理30天以上的文件（此步不做）

0 6 * * *    /usr/bin/find /opt/backup/file/* -mtime +30 -delete