登录
登录 注册新账号
注册
已有账号登录
第十五周
fangzhen 阅读 121 次
4月7日发布

1、编写脚本,支持让用户自主选择,使用mysqldump还是xtraback全量备份。

!/bin/bash

PS3="Please input a choice:"

select menu in mysqldump xtrabackup; do

case $REPLY in

1)

mkdir /backup/dump/ &> /dev/null

mysqldump -F -A --single-transaction --master-data=1 > /backup/dump/all.sql

break

;;

2)

mkdir /backup/test/ &> /dev/null

xtrabackup --backup --target-dir=/backup/test/ &> /dev/null

break

;;

*)

echo "please input again!"

esac

done

图片描述

2、配置Mysql主从同步

环境:192.168.43.127 master

192.168.43.137 slave

1、master配置文件:
[root@centos7 backup]#cat /etc/my.cnf
[mysqld]
**server-id=1**
max_connections=2000
binlog_format=ROW
**log-bin=/data/mysql/logbin/mysql-bin**
transaction-isolation=SERIALIZABLE
datadir=/data/mysql/data
socket=/var/lib/mysql/mysql.sock
[mysqld_safe]
log-error=/data/mysql/log/mariadb/mariadb.log
pid-file=/data/mysql/mariadb/mariadb.pid

!includedir /etc/my.cnf.d

2、在master上添加复制用户

MariaDB [(none)]>grant replication slave on . to 'repluser'@'192.168.43.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

3、备份master数据库,并拷贝到slave上:

[root@centos7 mysql]#mysqldump -F -A --single-transaction --master-data=1 > /backup/all.sql

[root@centos7 backup]#scp -r /backup/all.sql 192.168.43.137:/data/

4、slave节点配置:

[mysqld]

socket = /data/mysql/mysql.sock

datadir = /data/mysql

**server-id=2

read_only**

(1)slave导入数据库之前数据

[root@ka1 mysql]#mysql

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 9

Server version: 10.2.25-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

**MariaDB [(none)]> show databases;

+--------------------+

$TABLECOL Database $TABLECOL

+--------------------+

$TABLECOL informationschema |

$TABLECOL mysql $TABLECOL

$TABLECOL performanceschema |

+--------------------+**

3 rows in set (0.00 sec)

(2)导入数据库,与主服务器保持数据一致:

[root@ka1 mysql]#mysql < /data/all.sql

MariaDB [(none)]> show databases;

+--------------------+

$TABLECOL Database $TABLECOL

+--------------------+

$TABLECOL hellodb $TABLECOL

$TABLECOL informationschema |

$TABLECOL mysql $TABLECOL

$TABLECOL performanceschema |

$TABLECOL test $TABLECOL

+--------------------+

5 rows in set (0.00 sec)

图片描述

(3)配置主从同步(slave上操作)

查看数据库备份文件主服务二进制日志位置:

图片描述

设置之前,slave状态是空的

MariaDB [(none)]> show slave status\G

Empty set (0.00 sec)

MariaDB [(none)]>**CHANGE MASTER TO

-> MASTER_HOST='192.168.43.127',

-> MASTER_USER='repluser',

-> MASTER_PASSWORD='123456',

-> MASTER_PORT=3306,

-> MASTERLOGFILE='mysql-bin.000005',

-> MASTERLOGPOS=245;**

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> start slave;

Query OK, 0 rows affected (0.00 sec)

设置之后可以看到slave状态如下:

MariaDB [(none)]>show slave status\G

*1. row*

SlaveIOState: Waiting for master to send event

Master_Host: 192.168.43.127

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

MasterLogFile: mysql-bin.000005

ReadMasterLog_Pos: 245

RelayLogFile: ka1-relay-bin.000002

RelayLogPos: 544

RelayMasterLog_File: mysql-bin.000005

SlaveIORunning: Yes

SlaveSQLRunning: Yes

ReplicateDoDB:

ReplicateIgnoreDB:

ReplicateDoTable:

.........

图片描述

图片描述

5、主从同步测试:

(1) master删除数据:

MariaDB [(none)]> use hellodb;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [hellodb]> select * from teachers

-> ;

+-----+---------------+-----+--------+

$TABLECOL TID $TABLECOL Name $TABLECOL Age $TABLECOL Gender |

+-----+---------------+-----+--------+

$TABLECOL 1 $TABLECOL Song Jiang $TABLECOL 45 $TABLECOL M |

$TABLECOL 2 $TABLECOL Zhang Sanfeng $TABLECOL 94 $TABLECOL M |

$TABLECOL 3 $TABLECOL Miejue Shiitai $TABLECOL 77 $TABLECOL F |

+-----+---------------+-----+--------+

3 rows in set (0.00 sec)

MariaDB [hellodb]> delete from teachers where Age=77;

Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;

+-----+---------------+-----+--------+

$TABLECOL TID $TABLECOL Name $TABLECOL Age $TABLECOL Gender |

+-----+---------------+-----+--------+

$TABLECOL 1 $TABLECOL Song Jiang $TABLECOL 45 $TABLECOL M |

$TABLECOL 2 $TABLECOL Zhang Sanfeng $TABLECOL 94 $TABLECOL M |

+-----+---------------+-----+--------+

2 rows in set (0.00 sec)

(2)从服务器实时同步数据:

MariaDB [hellodb]> select * from teachers;

+-----+---------------+-----+--------+

$TABLECOL TID $TABLECOL Name $TABLECOL Age $TABLECOL Gender |

+-----+---------------+-----+--------+

$TABLECOL 1 $TABLECOL Song Jiang $TABLECOL 45 $TABLECOL M |

$TABLECOL 2 $TABLECOL Zhang Sanfeng $TABLECOL 94 $TABLECOL M |

+-----+---------------+-----+--------+

2 rows in set (0.00 sec)

图片描述

3、使用MHA实现Mysql高可用。

**在主从复制基础之上,实现MHA高可用;节点要开启二进制日志及中继日志;从节点为 read_only;关闭从节点的 relaylogpurge 中继日志自动清理功能环境准备:master 192.168.43.127slave 192.168.43.137slave/manager 192.168.43.147**

1、master的配置文件:

[mysqld]

server-id=1

skipnameresolve=1

relay-log=relay-log

log-bin=bin-log

innodb-file-per-table=ON

datadir=/data/mysql/data

slave1配置文件:

[mysqld]

datadir=/data/mysql/data

server-id=2

skipnameresolve=1

read_only

relaylogpurge=0

relay-log=relay-log

log-bin=bin-log

innodb-file-per-table=ON

slave2配置文件:

[mysqld]

datadir=/data/mysql/data

server-id=3

skipnameresolve=1

read_only

relaylogpurge=0

relay-log=relay-log

log-bin=bin-log

innodb-file-per-table=ON

2、各个节点上安装mha4mysql-node,manager节点上安装mha4mysql-manager

[root@]#yum install mha4mysql-node-0.56-0.el6.noarch.rpm

[root@]#yum install mha4mysql-manager-0.56-0.el6.noarch.rpm

3、master上创建MHA管理用户核复制用户,在主从同步正常情况下会同步给slave和slave2:

MariaDB [(none)]> grant replication slave on . to repluser@'192.168.43.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> grant all on . to mhauser@'192.168.43.%' identified by '123456';

Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4、各节点之间免密登录:

(1)各节点上生成密钥:[br/>[root@]#ssh-keygen

图片描述](mailto:br/>[root@]#ssh-keygen

[(2)后用命令将公钥拷贝到其他节点

[root@]#ssh-copy-id 192.168.43.147

[root@]#ssh-copy-id 192.168.43.137

[root@]#ssh-copy-id 192.168.43.127

[root@]#ssh-copy-id 192.168.43.137

[root@]#ssh-copy-id 192.168.43.127

[root@]#ssh-copy-id 192.168.43.147](mailto:br/>[root@]#ssh-keygen
[**5、在manager节点生成mha配置文件:**

[root@]#cat /etc/mastermha/app1.cnf

[server default]

user=mhauser

password=123456

manager_workdir=/data/mastermha/mysql/

manager_log=/data/mastermha/mysql/manager.log

remote_workdir=/data/mastermha/mysql/

ssh_user=root

repl_user=repluser

repl_password=123456

ping_interval=1

[server1]

hostname=192.168.43.127

candidate_master=1

masterbinlogdir=/data/mysql/data/

[server2]

hostname=192.168.43.137

candidate_master=1

masterbinlogdir=/data/mysql/data/

[server3]

hostname=192.168.43.147

masterbinlogdir=/data/mysql/data/
注释:

masterbinlogdir #mysql的binlog日志目录没有在默认的/var/log/mysql下,需要配置此项,写明具体路径

candidate_master=1 #是否 可以成为master,1是
6、检查

(1) 检查ssh登录是否正常:

[root@]#masterhacheckssh --conf=/etc/mastermha/app1.cnf

图片描述

(2)检查节点之间复制是否正常:

[root@]# masterhacheckrepl --conf=/etc/mastermha/app1.cnf

图片描述
7、启动MHA:

[root@]# nohup masterha_manager --conf=/etc/mastermha/app1.cnf &

图片描述

8、检查MHA状态:

[root@]#masterhacheckstatus --conf=/etc/mastermha/app1.cnf

app1 (pid:75633) is running(0:PING_OK), master:192.168.43.127
图片描述
9、故障转移测试:

(1)在master上停止mariadb服务

[root@]#systemctl stop mariadb

(2)在manager上查看日志:

[root@]#tail -f /data/mastermha/mysql/manager.log

图片描述
图片描述
(3)在备用master上状态,已经由slave变成了master:
MariaDB [hellodb]> show slave status\G
图片描述
(4)在slave2上查看slave状态,此时的master已经由192.168.43.127变成了192.168.43.137
MariaDB [(none)]> show slave status\G
图片描述](mailto:br/>[root@]#ssh-keygen
©著作权归作者所有:来自51CTO博客作者wyy1991的原创作品,谢绝转载,否则将追究法律责任