mysql-note's People
mysql-note's Issues
mysql-fabric-note
mysql fabric常用指令
mysqlfabric manage setup # 创建fabric管理库。(在fabric管理节点的数据库中,创建一个库,库名根据/etc/mysql/fabric.cfg配置生成)
mysqlfabric manage start --daemonize # 启动fabric
mysqlfabric manage ping # 查看启动状态
mysqlfabric manage stop # 停止fabric
mysqlfabric group create mifpay_group # 建立HA服务器组
mysqlfabric group add mifpay_group 192.168.137.201:3306 # 添加HA组成员 192.168.137.201:3306
mysqlfabric group remove mifpay_group 192.168.137.201:3306 # 移除HA组成员 192.168.137.201:3306
mysqlfabric group promote mifpay_group # 在组mifpay_group中自动选主。会自动配置主从同步,最好再检查下同步是否正常
mysqlfabric group promote mifpay_group --slave_id=2af610dd-c3df-11e4-8570-005056ac54a3 --update_only # 手动将一台mysql服务器为server_uuid的机器提为主库。这需要自己配置主从同步
mysqlfabric group demote mifpay_group # 关闭组mifpay_group自动选主库
mysqlfabric group activate mifpay_group # 开启故障检测自动切换
mysqlfabric group deactivate mifpay_group # 关闭故障检测自动切换
mysqlfabric server set_status 192.168.137.201:3306 spare # 将挂掉的mysql修改为spare状态
mysqlfabric server set_status 192.168.137.201:3306 secondary # 将挂掉的mysql修改为secondary状态 , 状态为secondary时才能参与选主
mysqlfabric group lookup_servers mifpay_group # 查看mifpay_group组中各节点的状态
mysqlfabric group health mifpay_group # 检查各节点的健康状态(同步状态)
#### 部署配置可参考
- http://www.iyunv.com/thread-38419-1-1.html
- 本git项目中的 MySql_Fabric_-_HA方案学习.docx,该文档非常详细。
#### mysql fabric需要安装的东西
- mysql-server
- mysql-client
- mysql-utilities
### 设置数据库主从同步命令
#### 设置同步前,可以先清理日志,将两个库的日志保持一致。如以前同步过数据了,最好不要再清理日志。
mysql> stop slave; # 停止binlog 数据主从同步
mysql> reset slave;
mysql> change master to master_host='192.168.137.201',master_port=3306, master_user='fabric', master_password='secret',master_auto_position=0; # 设置本数据库去向主库(201)同步数据
mysql> start slave; # 启动同步
mysql> show slave status\G; # 查看同步状态
清理数据库binlog日志命令
此清理日志最好不要使用,因为同步时基于该binlog日志进行同步数据的。不小心会导致两个库数据不一致
一开始设置同步之前,可将主从的日志清理,两个服务器的日志保持一致。然后可以设置主从同步
mysql> flush logs; # 刷新日志,会新增加一个日志文件,日志会记在新日志文件中
mysql> show binary logs; # 查看日志文件
mysql> PURGE MASTER LOGS to 'sccwebappuat01-bin.000010'; # 将这个日志之前的日志全部清理
备份命令(全量备份,可使用于在线备份)
mysqldump备份方式
time mysqldump -uroot -pcsii123456 --single-transaction --all-database --default-character-set=utf8 --opt > /var/backup/all_$(date +%F-%H-%M).sql
xtrabackup备份方式
1.需安装percona-xtrabackup
yum install percona-xtrabackup
2.备份
time innobackupex --defaults-file=/usr/my.cnf--no-lock --user=root --password=csii123456 /var/backup/base
恢复命令
mysqldump恢复方式
mysql> source /tmp/backup/all_XXXX.sql
xtrabackup恢复方式
恢复备份文件时,my.cnf文件必须要有datadir=/var/lib/mysql存储数据文件地址的参数配置
1.关闭mysql服务
2.恢复备份文件可用
innobackupex --user=root --password=csii123456 --apply-log --defaults-file=/usr/my.cnf /var/backup/base/2015-xx-xx_xx-xx-xx/
3.删除原有的数据文件
rm–rf /var/lib/mysql/*
4.将备份文件拷贝到mysql存储数据的目录下(/var/lib/mysql)
innobackupex --user=root --password=csii123456 --copy-back --defaults-file=/usr/my.cnf /var/backup/base/2015-xx-xx_xx-xx-xx/
5.设置/var/lib/mysql所属权限
chown -R mysql:mysql /var/lib/mysql
6.启动服务
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.