运维工具
系统备份和恢复
Clonezilla
install clonezilla network boot system
clonezilla push setting
clonezilla pxelinux.cfg default config
clonezilla pxe boot password
kickstart
kickstart centos5.6-i386 config
kickstart auto install
kickstart example
Cobbler
cobbler backups-restore
aomei 傲梅
傲梅集中备份
傲梅备份 分类
系统监控工具
collectd
collectd 收集系统性能
NMON monitor
Nmon monitor system
njmon script initd code
nmon 2 data to influx db
Zabbix
zabbix 3.2 install on centos 6
zabbix firewall
zabbix support windows
Supervisor
supervisor shell script
supervisor config example
boot supervisrd with systemd on centos7
webhook and github to do file sync with supervisor
日志管理系统
Grafana
grafana install on centos 6
grafana install plugin
grafana influx version
Filebeat
filebeat yum install
Kibana
kibana yum install
Logstash
logstash install on centos
Elasticsearch
yum install elasticsearch 6.8
elasticsearch install on Centos 6.7
ELK init env config
Elasticsearch 错误集锦
Rsyslog
centos 6 rsyslog 日志实时同步
Rsyslog mysql log to master analyzer
Deploy
Jumpserver
jumpserver 0.3.2 install on centos 6
jumpserver 0.3.2 docker install on centos 7
jumpserver 0.5 install on centos 7
Opsmanage
opsmanage install centos7
Jenkins
jenkins install on centos 6
install plugin in jenkins…
jenkins set time execution
tomcat jenkins ansible
jenkins ansible ad-hoc command
jenkins ansible playbook project
jenkins publish over_ssh
Paramike
PSSH
Centos7部署工具pssh
Ansible
how to install ansible with yum
bit rpm install ansible
source to install ansible
ansible init system centos 6
ansible using method
ansible install httpd servers
copy code with ansible-playbook
ansible ping host actived
ansible add username
ansible config parameter
ansible config write format
ansible api study
ansible hosts config ssh
ansible get nmon data
ansible setup filter information
yum repo with ansible-playbook
ansible gitlab ci runner
ansible get data list
ansible get text content
ansible get linux release debug infor
ansible support windows pywinrm
Saltshaker
saltshaker use docker
how to install saltstack
saltshaker manual install
salt install and config
salt init system
deploy saltshaker on centos7
salt file config desc
salt cmdb
saltshaker dj mq mysql build
salt data to mysql
Rsync
rsync + inotify 数据实时同步
代码仓库
Gitlab
yum install gitlab
gitlab-ce and runner
gitlib api example
gitlab config
gitlab ce and ee
Gogs
Gitea
虚拟化系统
Vmware
centos 6 install vmware 14
Vagrant
从零开始创建基础 Box
打包我的 BOX
Vagrantfile Config
Vagrant简易教程
给 Vagrant 从 VirtualBox换用 VMware 或 虚拟机
优化 vagrantFile config
Vagrant 主机定义
vagrant SSH
Vagrant 搭建 说明
WSL
pycharm 安装支持 WSL
windows-WSL 安装
Docker
how to install docker on centos 6
docker config source daoclound
how to use docker by step
how to creat docker subnet
gogs deploy on docker
jenkins use docker to deploy
mysql use docker to deploy
docker-example-1
docker-example-2
mac-osx-toolbox-install-docker
Data DB
Influx DB
InfluxDB install on centos
Mysql DB
centos 6 install mysql 5.5
mysql 5.7 install centos 6
install percona mysql 5.6 with yum
centos 7 install mysql config
mysql data rsync from master/slave
About me
本文档使用 MrDoc 发布
-
+
首页
mysql data rsync from master/slave
# write and read while master/slave setting --- ## 部署时间服务器 # yum install ntpd -y # vim /etc/ntp.conf ```bash server stdtime.gov.hk ``` ## 添加自定义的时间服务器 #server 0.rhel.pool.ntp.org iburst #server 1.rhel.pool.ntp.org iburst #server 2.rhel.pool.ntp.org iburst #server 3.rhel.pool.ntp.org iburst # chkconfig ntpd on # service ntpd start ## 部署 YUM 源设置 YUM 软件安装说明 阿里云的yum # wget http://mirrors.aliyun.com/repo/Centos-6.repo /etc/yum.repos.d/Centos-6.repo 网易的yum # wget http://mirrors.163.com/.help/CentOS6-Base-163.repo /etc/yum.repos.d/CentOS6-Base-163.repo epel yum # wget http://mirrors.aliyun.com/repo/epel.repo /etc/yum.repos.d/epel.repo # yum clean all # yum makecache # yum update # reboot 此时最后重新启动一下电脑 ## MySQL的复制集群 Master1 ←—→ Master2 互为主从,实现mysql的双机热备(AB复制) 这里我只部署主机两台,如有条件可以再增加 slave2/3 ```python master.jbboo.com eth0 192.168.1.2/24 slave1.jbboo.com eth0 192.168.1.3/24 slave2.jbboo.com eth0 192.168.1.4/24 ``` ## 步骤归纳: - 1、master必须启动二进制和使用唯一的server-id=1 - 2、master要授权从服务器连接 - 3、对master做一个完整备份 - 4、配置slave1服务器:打开中继日志,使用唯一的server-id=2,可选打开二进制日志 - 5、使用master的完整备份去恢复到slave1的数据库 - 6、在slave上启动复制(从正确binlog和pos开始提取记录) 两台机器都必须相互绑定主机到/etc/hosts - 192.168.1.2 master - 192.168.1.3 slave1 - 192.168.1.4 slave2 部署 LAMP 服务设置 这里我直接使用 www.lnmp.org 军哥的 lnmp服务一键安装包。不管你是安装lnmp/lamp,由你决定。 完整版:312 MB # wget http://soft.vpser.net/lnmp/lnmp1.2-full.tar.gz # wget https://api.sinas3.com/v1/SAE_lnmp/soft/lnmp1.2-full.tar.gz --no-check-certificate # yum install screen cmake gcc-c++ vim ftp kernel kernel-devel -y 我这里直接安装了LAMP包,安装方法自己可以去看看LANMP # screen -S lamp # ./install.sh lamp # lamp restart 安装完后的LAMP mysql的列表。 # ll /usr/local/mysql ```bash 总用量 220 drwxr-xr-x 2 root mysql 4096 5月 22 23:28 bin -rw-r--r-- 1 root mysql 17987 1月 7 2015 COPYING drwxr-xr-x 3 root mysql 4096 5月 22 23:27 data drwxr-xr-x 2 root mysql 4096 5月 22 23:27 docs drwxr-xr-x 3 root mysql 4096 5月 22 23:27 include -rw-r--r-- 1 root mysql 147959 1月 7 2015 INSTALL-BINARY drwxr-xr-x 3 root mysql 4096 5月 22 23:27 lib drwxr-xr-x 4 root mysql 4096 5月 22 23:28 man drwxr-xr-x 10 root mysql 4096 5月 22 23:28 mysql-test -rw-r--r-- 1 root mysql 2496 1月 7 2015 README drwxr-xr-x 2 root mysql 4096 5月 22 23:28 scripts drwxr-xr-x 27 root mysql 4096 5月 22 23:28 share drwxr-xr-x 4 root mysql 4096 5月 22 23:28 sql-bench drwxr-xr-x 2 root mysql 4096 5月 22 23:28 support-files drwx------ 6 mysql mysql 4096 5月 27 23:20 var 修改 mysql.cnf 设置 增加 (log-bin / relay-log) 和修改 数据文件目录 ``` # vim /etc/my.cnf ```bash # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 socket = /tmp/mysql.sock # The MySQL server [mysqld] port = 3306 socket = /tmp/mysql.sock datadir = /usr/local/mysql/var skip-external-locking max_connections = 1000 key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M log-bin=mysql-bin log-bin-index=mysql-bin skip-name-resolve server-id = 1 relay-log=relay-log relay-log-index=relay-log # binary logging format - mixed recommended binlog_format=mixed # Uncomment the following if you are using InnoDB tables innodb_data_home_dir = /usr/local/mysql/var innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /usr/local/mysql/var # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 16M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 5M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M #[mysql] #no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M #[mysqlhotcopy] #interactive-timeout expire_logs_days = 10 ``` ## 安装 mysql 服务步骤 安装服务 # cp -a /usr/local/mysql/var /usr/local/mysql/var_bak # 备份 数据目录 # rm -rf /usr/local/mysql/var/* # 清空数据目录 # ln -s scripts/mysql_install_db /usr/local/mysql/bin/ # lnmp.org 的安装包在 scripts中 # ./bin/mysql_install_db --defaults-file=/etc/my.cnf # 配制 config # /etc/init.d/mysql start # 启动数据库 # ./bin/mysql_secure_installation # 优化数据为 新建一些库和表,并且插入数据 # mysql -uroot -p # msyql> create database jbboo default charset utf8; # mysql> use jbboo; # mysql> create table user ( id int unsigned primary key auto_increment, name varchar(32) ) engine=innodb; # mysql> insert into user values (null,'bobby'),(null,'marry'),(null,'lili'); # mysql> select * from user; 创建帐号 创建用于复制使用的帐号, 该帐号是给slave服务连接master的时候使用。 # mysql> grant replication slave on *.* to slave@'192.168.1.%' identified by 'tester'; # mysql> grant replication slave on *.* to slave@'%jbboo.com' identified by 'tester'; 如果要用基于域名登录,请取消 my.cnf 中的 skip-name-resolve 否则不能登录到 master 的数据库 备份完整数据库 # mysqldump -u root -ptester --all-databases --lock-all-tables --master-data=2 > /tmp/master-all-pkg.sql ```bash +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000004 | 202 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec) ``` ## 在slave服务器上部署一个全新的数据库 my.cnf 在配制上基本上可参考 master的 设置,只是需要打开 relay-log 和 id=2 ```bash log-bin=mysql-bin log-bin-index=mysql-bin skip-name-resolve server-id = 2 relay-log=relay-log relay-log-index=relay-log ``` # ./bin/mysql_install_db --defaults-file=/etc/my.cnf # 配制 config # /etc/init.d/mysql start # 启动数据库 导入master数据到 slave1 # scp master.jbboo.com:/tmp/master-all-pkg.sql /tmp/master-all-pkg.sql # mysql> set session sql_log_bin=0; # 暂停二进制日志的记录 # mysql> source /tmp/master-all-pkg.sql; # 导入数据 # mysql> set session sql_log_bin=1; # 恢复二进制日志的记录 # service mysqld restart # 恢复完毕直接重启 在slave上启用复制 在 master 上新增加几条数据到数据库中,然后配制 slaver 复制功能,开启复制。 # mysql> insert into user values (null,'bobby1'),(null,'marry1'),(null,'lili1'); # mysql> insert into user values (null,'bobby2'),(null,'marry2'),(null,'lili2'); # mysql> insert into user values (null,'bobby3'),(null,'marry3'),(null,'lili3'); ## 让db2成为db1的从 设置复制功能, 在slave上执行 ```mysql mysql> change master to -> master_host='192.168.1.2', -> master_user='slave', -> master_password='tester', -> master_log_file='mysqld-bin.000033', -> master_log_pos=725989; ``` 关于二进制日志的名字和pos必须是看恢复的时候使用的备份文件记录的信息 ## 启动复制 # mysql> start slave; 启动复制 # mysql> show slave status \G; Slave_IO_Running: Yes # 代表连接master成功,而且成功下载二进制记录 Slave_SQL_Running: Yes # 代表执行下载回来的二进制日志文件的SQL语句成功 ## 查看更新动态 # cat /data/master.info 动态更新的文件 复制配置失败、错误,那么你必须先stop slave ,然后重新change master ….,修正配置之后再start slave 让db1成为db2的从 建议确定db1上的配置文件存在relay-log的选项,如果不固定relay-log的路径,默认名字有可能会变 ```mysql [mysqld] relay-log=/data/relay-log relay-log-index=/data/relay-log ``` # service mysql restart 获得db2当前数据库的二进制日志文件状态 slave1 mysql> show master status; ```mysql +-------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+--------------+------------------+ | mysql-bin.000038 | 108 | | | +-------------------+----------+--------------+------------------+ ``` 互为主从的架构中,只要db1一切正常,db2是不允许接受任何写请求 去db1那里设定复制配置 ```mysql master mysql> change master to -> master_host='192.168.1.3', -> master_user='slave', -> master_password='tester', -> master_log_file='mysql-bin.000038', -> master_log_pos=108; ``` mysql> start slave; - 让 slave2 成为master的从,可以参考 让db2成为db1的从 - 让 slave3 成为master的从,可以参考 让db2成为db1的从 Over
Bobby
2021年12月21日 03:39
分享文档
收藏文档
阅读
398
上一篇
下一篇
微信扫一扫
复制链接
手机扫一扫进行分享
复制链接
关于 MrDoc
觅思文档MrDoc
是
州的先生
开发并开源的在线文档系统,其适合作为个人和小型团队的云笔记、文档和知识库管理工具。
如果觅思文档给你或你的团队带来了帮助,欢迎对作者进行一些打赏捐助,这将有力支持作者持续投入精力更新和维护觅思文档,感谢你的捐助!
>>>捐助鸣谢列表
微信
支付宝
QQ
PayPal
Markdown文件
分享
链接
类型
密码
更新密码