1、编写脚本,支持让用户自主选择,使用mysqldump还是xtraback全量备份
#!/bin/bash
case $1 in
mysqldump)
mysqldump -A -F --single-transaction --master-data=2 > /data/all.sql
echo 备份完成
;;
xtrabackup)
xtrabackup --backup --target-dir=/data/backup &>/dev/null
echo 备份完成
;;
*)
echo "Usage: $(basename $0) {mysqldump|xtrabackup"
;;
esac
2、配置Mysql主从同步
主服务器配置
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin=/data/mysql_binlog/mysql-bin
binlog_format=MIXED
[root@master ~]# mkdir /data/mysql_binlog
[root@master ~]# setfacl -m u:mysql:rwx /data/mysql_binlog/
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos';
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 264 |
| mysql-bin.000002 | 399 |
+------------------+-----------+
从服务器配置
[root@slaver ~]# vim /etc/my.cnf
server_id=2
read_only
[root@slaver ~]# systemctl start mariadb
[root@slaver ~]# mysql
MariaDB [(none)]> CHANGE MASTER TO
-> MASTER_HOST='192.168.8.17',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='centos',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=399;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
3、使用MHA实现Mysql高可用
master服务器主从配置
[root@master ~]# vim /etc/my.cnf
[mysqld]
server_id=1
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin
binlog_format=MIXED
[root@master ~]# mkdir /data/mysql_binlog
[root@master ~]# setfacl -m u:mysql:rwx /data/mysql_binlog/
[root@master ~]# systemctl start mariadb
[root@master ~]# mysqldump -A -F --single-transaction --master-data=1 >/data/all.sql
[root@master ~]# mysql
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 28394 |
| mysql-bin.000005 | 245 |
+------------------+-----------+
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.8.%' identified by 'centos';
[root@master ~]# scp /data/all.sql 192.168.8.7:/data
[root@master ~]# scp /data/all.sql 192.168.8.27:/data
slave服务器主从配置
[root@slaver1 ~]# vim /etc/my.cnf
server_id=2
read_only
[root@slaver1 ~]# systemctl start mariadb
[root@slaver1 ~]# mysql </data/all.sql
[root@slaver1 ~]# mysql
MariaDB [(none)]>CHANGE MASTER TO
MASTER_HOST='192.168.8.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
[root@slaver2 ~]# vim /etc/my.cnf
[mysqld]
server_id=3
read_only
[root@slaver2 ~]# systemctl start mariadb
[root@slaver2 ~]# mysql </data/all.sql
[root@slaver2 ~]# mysql
MariaDB [(none)]>CHANGE MASTER TO
MASTER_HOST='192.168.8.17',
MASTER_USER='repluser',
MASTER_PASSWORD='centos',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000005',
MASTER_LOG_POS=245;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show slave status\G
mha设置
- mater服务器配置
[root@master ~]# vim /etc/my.cnf
[mysqld]
skip_name_resolve=1 新增
[root@master ~]# systemctl restart mariadb
[root@master ~]# mysql
MariaDB [(none)]> grant all on *.* to mhauser@'192.168.8.%' identified by 'centos';
[root@master ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
- slaver服务器配置
[root@slaver2 ~]# vim /etc/my.cnf
[mysqld]
log_bin
relay_log_purge=0
skip_name_resolve=1
[root@slaver2 ~]# systemctl start mariadb
[root@slaver2 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
[root@slaver1 ~]# vim /etc/my.cnf
[mysqld]
log_bin
relay_log_purge=0
skip_name_resolve=1
[root@slaver1 ~]# systemctl start mariadb
[root@slaver1 ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
mha管理机配置
[root@mhamanager ~]# yum install mha4mysql-node-0.56-0.el6.noarch.rpm -y
[root@mhamanager ~]# yum install mha4mysql-manager-0.56-0.el6.noarch.rpm -y
[root@mhamanager ~]# mkdir /etc/mastermha
[root@mhamanager ~]# vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=centos
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=repluser
repl_password=centos
ping_interval=1
[server1]
hostname=192.168.8.17
candidate_master=1
[server2]
hostname=192.168.8.7
candidate_master=1
[server3]
hostname=192.168.8.27
[root@mhamanager ~]# ssh-keygen -t rsa -P "" -f /root/.ssh/id_rsa
[root@mhamanager ~]# ssh-copy-id -i /root/.ssh/id_rsa.pub 127.0.0.1
[root@mhamanager ~]# scp -r /root/.ssh 192.168.8.17:/root/
[root@mhamanager ~]# scp -r /root/.ssh 192.168.8.7:/root/
[root@mhamanager ~]# scp -r /root/.ssh 192.168.8.27:/root/
[root@mhamanager ~]# masterha_check_ssh --conf=/etc/mastermha/app1.cnf
[root@mhamanager ~]# masterha_check_repl --conf=/etc/mastermha/app1.cnf
[root@mhamanager ~]# masterha_manager --conf=/etc/mastermha/app1.cnf