第一篇: MySQL 系列(一) 生产标准线上环境安装配置案例及棘手问题解决
第二篇: MySQL 系列(二) 你不知道的数据库操作
第三篇: MySQL 系列 (三) 你不知道的 视图触发器存储过程函数事务语句
第四篇: MySQL 系列(四) 主从复制读写分离模拟宕机备份恢复方案生产环境实战
去年公司有一个七年 PHP 开发经验的工程师, 想要跳槽
去国内某知名互联网公司面试后, 被虐惨了, 非要我给他讲讲什么是主从复制
那好吧, 读这篇文章的人好帅!
元芳, 你怎么看?
本章内容:
主从复制
简介原理
备份主库及恢复从库, 配置从库生效
读写分离
如果主宕机了, 怎么办?
双主的情况
MySQL 备份及恢复方案
备份单个及多个数据库
mysqldump 的常用参数
如何增量恢复呢?
增量恢复的必要条件
生产环境 mysqldump 备份命令
恢复喽
一 MySQL 主从复制
1 简介
我们为什么要用主从复制?
主从复制目的:
可以做数据库的实时备份, 保证数据的完整性;
可做读写分离, 主服务器只管写, 从服务器只管读, 这样可以提升整体性能
原理图:
从上图可以看出, 同步是靠 log 文件同步读写完成的
2 更改配置文件
两天机器都操作, 确保 server-id 要不同, 通常主 ID 要小于从 ID 一定注意
- # 3306 和 3307 分别代表 2 台机器
- # 打开 log-bin, 并使 server-id 不一样
- #vim /data/3306/my.cnf
- log-bin = /data/3306/mysql-bin
- server-id = 1
- #vim /data/3307/my.cnf
- log-bin = /data/3307/mysql-bin
- server-id = 3
- # 检查
- 1
- [root@bogon ~]# egrep "log-bin|server-id" /data/3306/my.cnf
- log-bin = /data/3306/mysql-bin
- server-id = 1
- [root@bogon ~]# egrep "log-bin|server-id" /data/3307/my.cnf
- log-bin = /data/3307/mysql-bin
- server-id = 3
- 2
- [root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock -e "show variables like'log_bin';"
- Enter password:
- +-----------------------+--------+
- | Variable_name | Value |
- +-----------------------+--------+
- | log_bin | ON | # ON 为开始开启成功
- +-----------------------+--------+
3 建立用于从库复制的账号 rep
通常会创建一个用于主从复制的专用账户, 不要忘记授权
- # 主库授权, 允许从库来连接我取日志
- [root@localhost ~]# mysql -uroot -p -S /data/3306/mysql.sock
- Enter password:
- # 允许从库 192.168.200 网段连接, 账号 rep, 密码 nick
- mysql> grant replication slave on *.* to 'rep'@'192.168.200.%' identified by 'nick';
- Query OK, 0 rows affected (0.00 sec)
- mysql> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
- # 检查创建的 rep 账号:
- mysql> select user,host from mysql.user;
- +--------+-----------------------------+
- | user | host |
- +--------+------------------------------+
- | root | 127.0.0.1 |
- | rep | 192.168.200.% |
- | root | localhost |
- | root | localhost.localdomain |
- +--------+------------------------------+
- 7 rows in set (0.00 sec)
4 备份主库, 及恢复到从库
把主库现有数据备份下来, 再恢复到从库, 此时两个主机的数据一致
如果事先有数据的话, 这不不能忘
1) 在主库上加锁, 使只有只读权限
- mysql> flush table with read lock;
- Query OK, 0 rows affected (0.00 sec)
- #5.15.5 锁表命令略有不同
- # 5.1 锁表: flush tables with read lock;
- # 5.5 锁表: flush table with read lock;
2) 记住就是这个点备份的
- mysql> show master status;
- +---------------------------+-------------+-------------------+--------------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +----------------------------+------------+--------------------+-------------------------+
- | mysql-bin.000013 | 410 | | |
- +----------------------------+------------+--------------------+-------------------------+
- 1 row in set (0.00 sec)
3) 克隆窗口, 备份数据
- [root@bogon ~]# mysqldump -uroot -p -S /data/3306/mysql.sock -A -B --events --master-data=2|gzip >/opt/rep.sql.gz
- Enter password:
参数: -A: 备份所有的
- # 看 rep.sql.gz 参数
- vim /opt/rep.sql.gz
- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=410;
4) 查看 master status; 数值是否正常
- mysql> show master status;
- +--------------------------+------------+--------------------+-------------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
- +--------------------------+------------+--------------------+--------------------------+
- | mysql-bin.000013 | 410 | | |
- +--------------------------+------------+--------------------+--------------------------+
- 1 row in set (0.00 sec)
5) 解锁库
- mysql> unlock tables;
- Query OK, 0 rows affected (0.00 sec)
6) 恢复到从库
- [root@bogon ~]# gunzip < /opt/rep.sql.gz | mysql -uroot -p -S /data/3307/mysql.sock
- Enter password:
5 配置从库及生效
更改从库和主库的连接参数, 配置生效检查就成功了!
1) 进入从库
- [root@bogon ~]# mysql -uroot -p -S /data/3307/mysql.sock
- Enter password:
2) 更改从属服务器用于与主服务器进行连接和通讯的参数
- mysql> CHANGE MASTER TO
- MASTER_HOST='192.168.200.98',
- MASTER_PORT=3306,
- MASTER_USER='rep',
- MASTER_PASSWORD='nick',
- MASTER_LOG_FILE='mysql-bin.000013',
- MASTER_LOG_POS=410;
- Query OK, 0 rows affected (0.01 sec)
3) 查看更改的参数
- [root@localhost ~]# cd /data/3307/data/
- [root@localhost data]# cat master.info
- 18
- mysql-bin.000013
- 410
- 192.168.200.98
- REP
- nick
- 3306
- 60
- 0
- 0
- 1800.000
- 0
4) 生效!
- mysql> start slave;
- Query OK, 0 rows affected (0.01 sec)
5) 检查下列参数, 符合则正常!
- mysql> show slave status\G
- Relay_Master_Log_File: mysql-bin.000013
- Slave_IO_Running: Yes #取 logo
- Slave_SQL_Running: Yes #读 relay-binlogo, 写数据
- Seconds_Behind_Master: 0 #落后主库的秒数
6) 查看 relay-bin.logo
- [root@localhost 3307]# cd /data/3307
- [root@localhost 3307]# ll
总用量 48
drwxr-xr-x. 9 mysql mysql 4096 10 月 29 18:52 data
-rw-r--r--. 1 mysql mysql 1900 10 月 29 11:45 my.cnf
-rwx------. 1 root root 1307 10 月 20 17:06 mysql
-rw-rw----. 1 mysql mysql 6 10 月 29 11:00 mysqld.pid
-rw-r-----. 1 mysql mysql 15090 10 月 29 18:49 mysql_nick3307.err
srwxrwxrwx. 1 mysql mysql 0 10 月 29 11:00 mysql.sock
-rw-rw----. 1 mysql mysql 150 10 月 29 18:49 relay-bin.000001
-rw-rw----. 1 mysql mysql 340 10 月 29 18:52 relay-bin.000002
-rw-rw----. 1 mysql mysql 56 10 月 29 18:49 relay-bin.index
-rw-rw----. 1 mysql mysql 53 10 月 29 18:52 relay-log.info
7) 查看 relay-log.info
- [root@localhost 3307]# cat relay-log.info
- /data/3307/relay-bin.000002
- 340
- mysql-bin.000013
- 497
8) 查看 master.info
- [root@localhost 3307]# cat data/master.info
- 18
- mysql-bin.000013
- 497
- 192.168.200.98
- rep
- nick
- 3306
- 60
- 0
- 0
- 1800.000
- 0
6 读写分离
读写分离在生产环境比比皆是, 也是必备技能
忽略 MySQL 主从复制授权表同步, 读写分离
- [root@bogon 3306]# vim my.cnf
- # 添加以下四行
- replicate-ignore-db = mysql
- binlog-ignore-db = mysql
- binlog-ignore-db = performance_schema
- binlog-ignore-db = information_schema
- server-id = 1
1) 通过 read-only 参数防止数据写入从库的方法
- # 修改配置文件
- vim /data/3307/my.cnf
- [mysqld]
- read-only
- # 对用户授权事不能指定有 super 或 all privileges 权限不然没效果
- # 创建账户 suoning, 并刷新权限
- mysql> grant select,insert,update,delete on *.* to 'suoning'@'localhost' identified by '123';
- Query OK, 0 rows affected (0.00 sec)
- mysql> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
- # 用创建账户登录, 并创建库
- [root@bogon 3307]# mysql -usuoning -p123 -S /data/3307/mysql.sock
- mysql> create user kangkangkang@'192.%' identified by 'old123';
- ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
2) 主从同步故障
- A. Last_SQL_Errno: 1007
- stop slave;
- set global sql_slave_skip_counter = 1;
- start slave;
B. 忽略
- skip-name-resolve #忽略名字解析
- slave-skip-errors = 1032,1062,1007 #忽略故障编号
- server-id = 3
3) 从库开启 bin-log
- vim my.cnf
- log-bin = /data/3307/mysql-bin
- log-slave-updates #表示从库记录 bin-log
- expire_logs_days = 7 #保留 7 天 bin-log
7 主宕机
我们来模拟一下, 如果主机宕机了, 那我们该如何让从快速替换, 让损失降到最小? 当然了, 双机热备也是一个不错的选择, 那下节会给大家讲解
一主多从的环境下, 如果主宕机了, 选一台从做主, 继续和其它从同步
A. 查看每个从库的 master.info, 看谁的更靠前, 最新, 更大, 丢的数据最少
- [root@localhost 3307]# cat /data/3307/data/master.info
- mysql-bin.000015
- 326
B. 确保所有 relay log 全部更新完毕
在每个从库上执行 stop slave io_thread;show processlist;
知道看到 Has read all relay log; 表示从库更新都执行完毕
C. 登陆 mysql -uroot -p -S /data/3307/mysql.sock
- stop slave;
- reset master;
- quit
D. 进入到数据库目录, 删除 master.info relay-log.info
- cd /data/3307/data/
- rm -f master.info relay-log.info
E. 3307 提升为主库
vim /data/3307/my.cnf
开启 log-bin = /data/3307/mysql-bin
如存在 log-slave-updates,read-only 等一定要注释
/data/3307/mysql restart
F. 其它从库操作
- stop slave;
- change master to master_host ='192.168.200.98';
- start slave;
- show slave status\G
8 双主
使用主主前提: 表的主键自增
- [root@localhost 3307]# vim my.cnf
- [mysqld]
- auto_increment_increment = 2
- auto_increment_offset = 2
- [root@localhost 3307]# ./mysql restart
- [root@localhost 3306]# vim my.cnf
- [mysqld]
- auto_increment_increment = 2
- auto_increment_offset = 1
- log-bin = /data/3306/mysql-bin
- log-slave-updates
- [root@localhost 3306]# ./mysql restart
- [root@localhost 3306]# mysqldump -uroot -pnick -S /data/3307/mysql.sock -A -B --master-data=1 -x --events > /opt/3307bak.sql
- [root@localhost 3306]# mysql -uroot -pnick -S /data/3306/mysql.sock < /opt/3307bak.sql
- mysql> CHANGE MASTER TO
- MASTER_HOST='192.168.200.98',
- MASTER_PORT=3307,
- MASTER_USER='rep',
- MASTER_PASSWORD='nick';
- mysql> start slave;
- Query OK, 0 rows affected (0.00 sec)
- mysql> show slave status \G
二 MySQL 备份及恢复
1 备份单个数据库
最基础的备份单个数据库
1> 语法: mysqldump u 用户名 p 数据库名> 备份的数据库名
2> 备份 nick_defailt 数据库, 查看内容
- [root@localhost ~]# mysqldump -uroot -p -B nick_defailt >/opt/mysql_nick_defailt.bak
- Enter password:
- [root@localhost ~]# egrep -v "#|\*|--|^$" /opt/mysql_nick_defailt.bak
- DROP TABLE IF EXISTS `oldsuo`;
- CREATE TABLE `oldsuo` (
- `id` int(4) NOT NULL,
- `name` char(20) NOT NULL,
- `age` tinyint(2) NOT NULL DEFAULT '0',
- `dept` varchar(16) DEFAULT NULL
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
- LOCK TABLES `oldsuo` WRITE;
- INSERT INTO `oldsuo` VALUES (2,'ç´¢å®',0,NULL),(3,'索尼',0,NULL),(4,'åºåº',0,NULL);
- UNLOCK TABLES;
- DROP TABLE IF EXISTS `student`;
- CREATE TABLE `student` (
- `qq` varchar(15) DEFAULT NULL,
- `id` int(4) NOT NULL AUTO_INCREMENT,
- `name` char(20) NOT NULL,
- `suo` int(4) DEFAULT NULL,
- `age` tinyint(2) NOT NULL DEFAULT '0',
- `dept` varchar(16) DEFAULT NULL,
- `sex` char(4) DEFAULT NULL,
- PRIMARY KEY (`id`),
- KEY `index_name` (`name`)
- ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
- LOCK TABLES `student` WRITE;
- INSERT INTO `student` VALUES (NULL,2,'oldsuo',NULL,0,NULL,NULL),(NULL,3,'kangknag',NULL,0,NULL,NULL),(NULL,4,'kangkang',NULL,0,NULL,NULL),(NULL,5,'oldsuo',NULL,0,NULL,NULL),(NULL,6,'kangknag',NULL,0,NULL,NULL),(NULL,7,'kangkang',NULL,0,NULL,NULL);
- UNLOCK TABLES;
3> 启用压缩备份数据库
- [root@localhost~]#mysqldump -uroot -p -B nick_defailt|gzip>/opt/mysql_nick_defailt.bak.gz
- Enter password:
- [root@localhost ~]# ll /opt/
总用量 28
-rw-r--r--. 1 root root 2109 10 月 24 16:36 data_bak.sq2
-rw-r--r--. 1 root root 2109 10 月 24 16:36 data_bak.sql
-rw-r--r--. 1 root root 1002 10 月 27 11:55 mysql_nick_defailt.bak
-rw-r--r--. 1 root root 1002 10 月 27 11:56 mysql_nick_defailt.bak.gz
-rw-r--r--. 1 root root 3201 10 月 27 11:46 mysql_nick_defailt_B.bak
drwxr-xr-x. 2 root root 4096 11 月 22 2013 rh
-rw-r--r--. 1 root root 1396 10 月 24 16:11 student_bak.sql
4> 恢复 nick_defailt 数据库
- [root@localhost ~]# mysql -uroot -p nick_defailt </opt/mysql_nick_defailt.bak
- Enter password:
- # 加 - B 恢复方法
- [root@localhost ~]# mysql -uroot -p </opt/mysql_nick_defailt_B.bak
- Enter password:
5> 总结
1 备份用 - B 参数增加 use db, 和 create database 的信息
2 用 gzip 对备份的数据压缩
2 备份多个数据库
备份多个数据库的情况呢?
- # 多个数据库名中间加空格
- [root@localhost ~]# mysqldump -uroot -p -B nick_defailt oldsuo oldsuo_1|gzip>/opt/mul.sql.gz
- Enter password:
3 备份单个及多个表
那如果备份单个和多个表, 怎么办?
1> 语法: mysqldump -u 用户名 -p 数据库名 表名 > 备份的文件名
- [root@localhost ~]# mysqldump -uroot -p nick_defailt student >/opt/mysql_nick_defailt_student.bak
- Enter password:
2> 语法: mysqldump -u 用户名 -p 数据库名 表名 1 表名 2 > 备份的文件名
- [root@localhost ~]# mysqldump -uroot -p nick_defailt student oldsuo >/opt/mysql_nick_defailt.bak
- Enter password:
4mysqldump 的参数
mysqldump 的关键参数
1 -B 指定多个库, 增加建库语句和 use 语句
2 --compact 去掉注释, 适合调试输出, 生产不用
3 -A 备份所有库
4 -F 刷新 binlog 日志
5 --master-data 增加 binlog 日志文件名及对应的位置点
- -x,--lock-all-tables
- -l,--locktables
8 -d 只备份表结构
9 -t 只备份数据
10 --single-transaction 适合 innodb 事务数据库备份
5 增量恢复
重要的来了, 生产环境一般是增量备份与恢复;
所谓增量, 就是在原数据的基础上继续添加数据, 不必每次都重新添加, 省时省力
A: 增量恢复必备条件:
1. 开启 MySQL 数据库 log-bin 参数记录 binlog 日志
- [root@localhost 3306]# grep log-bin /data/3306/my.cnf
- log-bin = /data/3306/mysql-bin
2. 存在数据库全备
B: 生产环境 mysqldump 备份命令:
- # 进行数据库全备,(生产环境还通过定时任务每日凌晨执行)
- mysqldump -uroot -pnick -S /data/3306/mysql.sock --default-character-set=gbk --single-transaction -F -B nick |gzip >/server/backup/mysql_$(date +%F).sql.gz
- # innodb 引擎备份
- mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F --single-transaction -A -B |gzip >$DATA_FILE
- # myisam 引擎备份
- mysqldump -u$MYUSER -p$MYPASS -S $MYSOCK -F -A -B --lock-all-tables |gzip >$DATA_FILE
C: 恢复:
- # 通过防火墙禁止 web 等应用向主库写数据或者锁表让主库暂时停止更新, 然后再进行恢复
- # 误操作删除 nick 库!
1. 检查全备及 binlog 日志
- [root@localhost 3306]# cd /server/backup/
- [root@localhost backup]# gzip -d mysql_2015-10-31.sql.gz
- [root@localhost backup]# vim mysql_2015-10-31.sql
- [root@localhost backup]# grep -i "change" mysql_2015-10-31.sql
2. 立即刷新并备份出 binlog
- [root@localhost 3306]# mysqladmin -uroot -pnick -S /data/3306/mysql.sock flush-logs
- [root@localhost 3306]# cp /data/3306/mysql-bin.000030 /server/backup/
- # 误操作 log-bin, 倒数第二
- [root@localhost backup]# mysqlbinlog -d nick mysql-bin.000030 >bin.sql #导出为. sql 格式
- [root@localhost backup]# vim bin.sql
找到语句 drop database nick 删除!!!(误操作语句)
3. 恢复
- [root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock <mysql_2015-10-31.sql #恢复之前的数据库全备
- [root@localhost backup]# mysql -uroot -pnick -S /data/3306/mysql.sock nick < bin.sql
- # 恢复删除误操作语言的 bin-log
- # 搞定!!!
来源: http://www.cnblogs.com/suoning/p/5778243.html