mysql配置详解-备份-主从-MHA

目录：

1.备份和恢复

2.主从复制

3.主主复制

4.半同步复制

5.proxysql_读写分离

6.MHA

1.备份和恢复

·mysqldump

备份：

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

-E:备份指定库的事件调度器

-R：备份存储过程和存储函数

–triggers：备份触发器

–master-data=2：指定此事件记载的2进制文件开始位置，及哪个文件

–flush-log：滚动一下日志，以方便用二进制日志文件进行时间点还原

–single-transaction：mysqldump热备时需要执行此选项，备份是启用一个大的事务完成的备份（在常用的两种数据库引擎中，myisam仅支持温备，innodb支持热备）

–databases：指定备份哪一个数据库

·xtrabackup

备份：

innobackupex –databases hellodb /tmp

增量备份：

innobackupex –incremental /tmp/ –incremental-basedir=/tmp/$data

恢复：

innobackupex –redo-only –apply-log /tmp/$data

innobackupex –redo-only –apply-log /tmp/$data –-incremental-dir=/tmp/$data

innobackupex –copy-back /tmp/$data

–databases：指明备份哪个库，到哪个位置即可

–incremental：指明这次增量备份

–incremental-basedir：指明以那一次为基础的增量

–incremental-dir：指明增量备份是哪一个

–copy-back：代表还原，要注意的是指明的目录为完全备份的目录

2.主从复制

1.编辑/etc/my.cnf文件

主mysql主机操作：

在[mysqld]段的最后面添加如下内容

innodb_file_per_table = ON

skip_name_resolve = ON

server-id = 1

relay-log = relay-log

log-bin = master-log

从mysql主机操作：

在[mysqld]段的最后面添加如下内容

innodb_file_per_table = ON

skip_name_resolve = ON

server-id = 2 # 注意slave2的server-id = 3

relay-log = relay-log

log-bin = master-log

read-only = 1

relay-log-purge = 0

2.授权具有复制权限的用户，指定master节点

主mysql主机操作：

（1）启动数据库

systemctl start mariadb.service

mysql

（2）授权

grant replication slave,replication client on *.* to ‘tom’@’172.16.42.%’ identified by ‘mageedu’;

（3）查看节点

flush privileges;

show master status;

show binlog events in ‘master-log.000003’;

从mysql主机操作：

（1）启动数据库

systemctl start mariadb.service

mysql

（2）指定节点

change master to master_host=’172.16.42.1′,master_user=’tom’,master_password=’mageedu’,master_log_file=’master-log.000003′,master_log_pos=245;

（3）启动节点

start slave;

show slave status\G

select user,host from mysql.user；

3.主主复制：

1.编辑/etc/my.cnf文件

主mysql主机操作：

在[mysqld]段的最后添加以下内容

skip_name_resolve = ON

innodb_file_per_table = ON

server-id = 1 （id号不能跟从服务器相同）

log-bin = master-log （自定义主服务器的二进制日志文件名）

relay-log = slave-log （自定义从服务器的二进制日志文件名）

auto_increment_offset = 1

auto_increment_increment = 2

另个主mysql主机操作：

在[mysqld]段的最后添加以下内容

skip_name_resolve = ON

innodb_file_per_table = ON

server-id = 2

relay-log = slave-log

lob-bin = master-log

auto_increment_offset = 2

auto_increment_increment = 2

2.授权具有复制权限的用户，指定master节点

主mysql主机操作：

（1）授权

grant replication slave,replication client on *.* to ‘repluser’@’10.1.51.%’ identified by ‘replpasswd’;

（2）先在另个主mysql上查看节点

show master status\G

（3）指定节点

change master to master_host=’10.1.51.50′,master_user=’repluser’,master_password=’replpasswd’,master_log_file=’master-log.000003′,master_log_pos=422;

另个主mysql主机操作：

（1）授权

grant replication slave,replication client on *.* to ‘repluser’@’10.1.51.%’ identified by ‘replpasswd’;

（2）先在另个主mysql上查看节点

show master status\G

（3）指定节点

change master to master_host=’10.1.51.50′,master_user=’repluser’,master_password=’replpasswd’,master_log_file=’master-log.000003′,master_log_pos=422;

4.半同步复制：

1.编辑/etc/my.cnf

主mysql主机操作：

在[mysqld]段的最后添加以下内容

skip_name_resolve = ON

innodb_file_per_table = ON

server-id = 1

log-bin = master-log

从mysql主机操作：

在[mysqld]段的最后添加以下内容

skip_name_resolve = ON

innodb_file_per_table = ON

server-id = 2 （id号不能跟主服务器相同）

relay-log = slave-log （自定义二进制日志文件名）

2.授权具有复制权限的用户，指定master节点

主mysql主机操作：

systemctl start mariadb.service

mysql

grant replication slave,replication client on *.* to ‘tom’@’172.16.42.%’ identified by ‘mageedu’;

flush privileges;

show master status;

show binlog events in ‘master-log.000003’;

从mysql主机操作：

systemctl start mariadb.service

mysql

change master to master_host=’172.16.42.1′,master_user=’tom’,master_password=’mageedu’,master_log_file=’master-log.000003′,master_log_pos=245;

start slave;

show slave status\G

select user,host from mysql.user；

3.安装rplsemisync_master插件，并启用

主mysql主机操作：

mysql

install plugin rpl_semi_sync_master soname ‘semisync_master.so’;

set global rpl_semi_sync_master_enabled = ON;

从mysql主机操作：

mysql

install plugin rpl_semi_sync_slave soname ‘semisync_slave.so’;

set global rpl_semi_sync_slave_enabled = ON;

start slave;

5.proxysql_读写分离：

1.下载安装
wget -c ftp://172.16.0.1/pub/Sources/7.x86_64/proxysql/proxysql-1.3.6-1-centos7.x86_64.rpm
yum install proxysql-1.3.6-1-centos7.x86_64.rpm -y
yum -y install mariadb-server
2.编辑配置文件
vim /etc/proxysql.cnf
datadir=”/var/lib/proxysql”
admin_variables=
{
admin_credentials=”admin:admin”
mysql_ifaces=”127.0.0.1:6032;/tmp/proxysql_admin.sock”
}
mysql_variables=
{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
interfaces=”0.0.0.0:3306;/tmp/mysql.sock”
default_schema=”information_schema”
stacksize=1048576
server_version=”5.5.30″
connect_timeout_server=3000
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}
mysql_servers =
(
{
address = “172.18.0.67” # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain
hostgroup = 0 # no default, required
status = “ONLINE” # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = “172.18.0.68”
port = 3306
hostgroup = 1
status = “ONLINE” # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
},
{
address = “172.18.0.69”
port = 3306
hostgroup = 1
status = “ONLINE” # default: ONLINE
weight = 1 # default: 1
compression = 0 # default: 0
}
)
mysql_users:
(
{
username = “root”
password = “mageedu”
default_hostgroup = 0
max_connections=1000
default_schema=”mydb”
active = 1
}
)
mysql_replication_hostgroups=
(
{
writer_hostgroup=0
reader_hostgroup=1
}
)
3.启动服务

service proxysql start

6.MHA：

1.准备基于ssh互相通信环境

ssh-keygen -t rsa -P ” #创建私钥

cat .ssh/id_rsa.pub > .ssh/authorized_keys

scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub 10.1.51.30:/root/.ssh/

scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub 10.1.51.50:/root/.ssh/

scp .ssh/authorized_keys .ssh/id_rsa .ssh/id_rsa.pub 10.1.51.60:/root/.ssh/

2.下载安装MHA

wget -c http://192.168.42.26/install_package/down/Sources/mha/mha4mysql-manager-0.56-0.el6.noarch.rpm

wget -c http://192.168.42.26/install_package/down/Sources/mha/mha4mysql-node-0.56-0.el6.noarch.rpm

3.在master节点上创建一个管理mysql的用户

grant all on *.* to ‘mhaadmin’@’10.1.51.%’ identified by ‘mhapass’;

4.编辑/etc/masterha/app.cnf

[server default]

user=mhaadmin #mysql的管理用户

password=mhapass #mysql的管理用户的密码

manager_workdir=/data/masterha/app #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=10.1.51.30

ssh_port=22

candidate_master=1

[server2]

hostname=10.1.51.50

ssh_port=22

candidate_master=1

[server3]

hostname=10.1.51.60

ssh_port=22

candidate_master=1

5.检测各节点间ssh互相通信配置是否正常，检查管理的mysql复制集群的连接配置参数是否正常

masterha_check_ssh –conf=/etc/masterha/app.cnf

masterha_check_repl –conf=/etc/masterha/app.cnf

6.启动MHA

masterha_manager –conf=/etc/masterha/app.cn