目录: 1.备份与恢复 2.主从复制 3.主主复制 4.半同步复制 5.MHA 6.centos7搭建mariadb Galera集群 1.备份和恢复 备份工具:mysqldump+复制binlog xtrabackup (1) mysqldump+复制binlog 备份: mysqldump -E -R –triggers –master-data=2 –flush-logs –single-transaction –databases hellodb > /tmp/backup 恢复: mysql < /tmp/backup 时间点还原: mysqlbinlog –start-position=245 /tmp/log.100004 > /tmp/backup mysql < /tmp/backup –flush-log:滚动一下日志,以方便用二进制日志文件进行时间点还原 –single-transaction:mysqldump热备时需要执行此选项,备份是启用一个大的事务完成的备份(在常用的两种数据库引擎中,myisam仅支持温备,innodb支持热备) –databases:指定备份哪一个数据库 MyISAM:支持温备,锁定备份库而后启动备份操作 InnoDB:支持热备 -E:--event 备份指定库相关的所有事件 -R:--routiness 备份指定数据库相关的存储过程和存储函数 –triggers:备份表相关的触发器 –master-data=#:0不使用,1记录为master to 注意:二进制日志文件不应该与数据文件放在同一磁盘上 (2) xtrabackup # yum install percona-xtrabackup-2.3.2-1-el7.x86_64.rpm 完全备份: innobackupex --user= --password= /path/to/backup/dir # innobackupex --user=root /backups/ 还原: # scp /backups/2017-11-21_12-00-30/ node3:/root # innobackupex --apply-log /backups/2017-11-21_12-00-30/ 整理 Complete OK ! # innobackupex --apply-back /backups/2017-11-21_12-00-30/ 恢复 Complete OK ! # cd /data/mysql # chown -R mysql.mysql /data/mysql/* 增量备份: innobackupex –incremental /backups/ –incremental-basedir=/backups/2017-11-21_12-00-30/ 恢复: innobackupex –redo-only –apply-log /backups/2017-11-21_12-00-30/ innobackupex –redo-only –apply-log /backups/2017-11-21_12-00-30/ –-incremental-dir=/backups/2017-11-21_12-15-35/ innobackupex –copy-back /backups/2017-11-21_12-00-30/ –databases:指明备份哪个库,到哪个位置即可 –incremental:指明这次增量备份 –incremental-basedir:指明以那一次为基础的增量 –incremental-dir:指明增量备份是哪一个 –copy-back:代表还原,要注意的是指明的目录为完全备份的目录 2.主从复制 主节点:①启用二进制 ②为当前节点设置一个全局唯一的ID号 ③创建有复制权限的用户 从节点:①启用中继日志 ②为当前节点设置一个全局唯一的ID号 ③使用有复制权限的用户连接主服务器,并启用复制线程 (1)主节点: # vim /etc/my.cnf 在[mysqld]段的最后面添加如下内容 innodb_file_per_table = ON skip_name_resolve = ON server-id = 1 log-bin = master-log # systemctl start mariadb.service # mysql > show global variables like '%log%'; > show master logs; > show global variables like '%server%'; > grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.%’ identified by ‘replpass’; > flush privileges; 2.从节点 # vim /etc/my.cnf 在[mysqld]段的最后面添加如下内容 innodb_file_per_table = ON skip_name_resolve = ON server-id = 5 relay-log = relay-log read-only = 1 relay-log-purge = 0 # systemctl start mariadb.service # mysql > change master to master_host=’192.168.1.32′,master_user=’repluser’,master_password=’replpass’, master_log_file=’master-bin.000001′,从那个二进制日志文件复制 master_log_pos=245; > show slave status\G; 查看自己的从信息 > start slave; 启动复制线程 > show slave status\G; 验证:主节点:> create database mydb; > show databases; 从节点:> show databases; 注意:主从复制要关闭selinux iptables -F 网络要通,可以用ping或telnet 主从同步出现问题,可参考此文档http://www.linuxidc.com/Linux/2012-03/57708.htm 3.主主复制: 互为主从 ① 各节点使用唯一server-id ② 都启动binary log和relay log ③ 创建拥有复制权限的用户账号 ④ 定义自动增长id字段的数值范围为奇偶 ⑤ 均把对方指定为主节点,并启动复制线程 # vim /etc/my.cnf 主mysql主机操作: 在[mysqld]段的最后添加以下内容 skip_name_resolve = ON innodb_file_per_table = ON server-id = 1 id号不能跟从服务器相同 log-bin = master-bin 自定义主服务器的二进制日志文件名 relay-log = relay-log 自定义从服务器的二进制日志文件名 auto_increment_offset = 1 auto_increment_increment = 2 另个主mysql主机操作: 在[mysqld]段的最后添加以下内容 skip_name_resolve = ON innodb_file_per_table = ON server-id = 5 relay-log = relay-log lob-bin = master-bin auto_increment_offset = 2 auto_increment_increment = 2 2.授权具有复制权限的用户,指定master节点 主mysql主机操作: (1)授权 grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.%’ identified by ‘replpass’; (2)先在另个主mysql上查看节点 show master status\G; (3)指定节点 change master to master_host=’192.168.1.33′,master_user=’repluser’,master_password=’replpass’,master_log_file=’master-bin.000001′,master_log_pos=245; 另个主mysql主机操作: (1)授权 grant replication slave,replication client on *.* to ‘repluser’@’192.168.1.%’ identified by ‘replpass’; (2)先在另个主mysql上查看节点 show master status\G (3)指定节点 change master to master_host=’192.168.1.32′,master_user=’repluser’,master_password=’replpass’,master_log_file=’master-bin.000002′,master_log_pos=245; 4.半同步复制: 1.同样配置为主从复制,配置文件也同主从复制(my.cnf) 2.安装rplsemisync_master插件,并启用 主节点: # mysql > install plugin rpl_semi_sync_master soname ‘semisync_master.so’; > set global rpl_semi_sync_master_enabled = 1; > show plugins; 查看可用插件 从节点: # mysql > install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’; > set global rpl_semi_sync_slave_enabled = 1; > start slave; > show slave status\G; 5.MHA: node1:MHA Manager //192.168.1.31 node2:MariaDB master //192.168.1.33 node3:MariaDB slave //192.168.1.35 node4:MariaDB slave //192.168.1.47 1.将node2与node3配置为主从复制节点,将node2与node4配置为半同步复制 2.在master节点上创建一个管理mysql的用户 > grant all on *.* to ‘mhuser’@’192.168.1.%’ identified by ‘mhapass’; 3.配置4个主机间能基于ssh互相通信 node1: # ssh-keygen -t rsa -P ” 创建私钥 # cat .ssh/id_rsa.pub > .ssh/authorized_keys 覆盖 # scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub node2:/root/.ssh/ # scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub node3:/root/.ssh/ # scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub node4:/root/.ssh/ 测试:# ssh node2 'ifconfig' # ssh node3 'ifconfig' # ssh node4 'ifconfig' 4.下载安装MHA node1: # yum install mha4mysql-manager-0.56-0.el6.noarch.rpm mha4mysql-node-0.56-0.el6.noarch.rpm node2&3&4 # yum install mha4mysql-node-0.56-0.el6.noarch.rpm 5.配置MHA # vim /etc/masterha/app1.cnf [server default] user=mhauser //mysql的管理用户 password=mhapass //mysql的管理用户的密码 manager_workdir=/data/masterha/app1 //manager的工作路径,会自动创建 manager_log=/data/masterha/app/manager.log //manager日志文件 remote_workdir=/data/masterha/app //远程主机的工作目录 ssh_user=root repl_user=repluser repl_password=replpass ping_intervarl=1 //心跳信息传递间隔 [server1] hostname=192.168.1.33 ssh_port=22 candidate_master=1 //将来这个节点是否参与成为主节点 [server2] hostname=192.168.1.35 ssh_port=22 candidate_master=1 [server3] hostname=192.168.1.47 ssh_port=22 candidate_master=1 6.检测各节点间ssh互相通信配置是否OK,检查管理的mysql复制集群的连接配置参数是否OK # masterha_check_ssh –conf=/etc/masterha/app1.cnf # masterha_check_repl –conf=/etc/masterha/app1.cnf 启动MHA masterha_manager –conf=/etc/masterha/app1.cnf 7.测试 node2: # killqll mysqld mysqld_safe node4: > show slave status\G; 主节点发生变化 6.centos7搭建mariadb Galera集群
node1 IP:192.168.1.32 node2 IP:192.168.1.33 node3 IP:192.168.1.35 node1: # vim /etc/yum.repos.d/galera.repo [galera] name=galera baseurl=http://mirrors.tuna.tsinghua.edu.cn/mariadb/mariadb-5.5.57/yum/centos7-amd64/ gpgcheck=0 # scp /etc/yum.repos.d/galera.repo 192.168.1.33:/etc/yum.repos.d/ # scp /etc/yum.repos.d/galera.repo 192.168.1.35:/etc/yum.repos.d/ node1&2&3: # yum isntall MariaDB-Galera-server -y node1: # rpm -ql galera | grep -i smm.so /usr/lib64/galera/libgalera_smm.so # vim /etc/my.cnf.d/server.cnf [galera] wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_cluster_address="gcomm://192.168.1.32,192.168.1.33,192.168.1.35" binlog_format=row //二进制日志格式 default_storage_engine=InnoDB //默认引擎 innodb_autoinc_lock_mode=2 //锁格式 bind-address=0.0.0.0 //监听地址 wsrep_cluster_name="galera_cluster" //集群名称 wsrep_slave_threads=1 //复制线程 innodb_flush_log_at_trx_commit=0 //是否在每次事务提交时都刷新事务日志 # scp /etc/my.cnf.d/server.cnf node2:/etc/my.cnf.d/server.cnf # scp /etc/my.cnf.d/server.cnf node3:/etc/my.cnf.d/server.cnf 首次启动时,需要初始化集群,在其中一个节点执行 # /etc/init.d/mysql start --wsrep-new-cluster node2&3: # /etc/init.d/mysql start SUCCESS! node1: # mysql > create database mydb; > show databases; node2: # mysql > show databases;
原创文章,作者:nene,如若转载,请注明出处:http://www.178linux.com/88767