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

原创文章,作者:z long,如若转载,请注明出处:/84238

发表评论

登录后才能评论

评论列表(2条)

  • 黑白子 2018-01-24 11:41

    作者,你好,想问一下,你的mha4mysql-manager-0.56-0.el6.noarch.rpm和mha4mysql-node-0.56-0.el6.noarch.rpm是在哪下载的?我找了半天没找到这么新的包

  • 阿飞 2018-01-25 16:50

    你好作者,关于出现这个e2f028efa4659695d0a16d6b46583c98你是怎么解决的啊

联系我们

400-080-6560

在线咨询:点击这里给我发消息

邮件:1823388528@qq.com

工作时间:周一至周五,9:30-18:30,节假日同时也值班

友情链接:测试  万达娱乐主管QQ  万达娱乐直属QQ  万达娱乐平台  万达娱乐主管QQ  万达招商QQ  万达直属QQ  万达注册  万达登录