环境规划: 节点说明 主机名 IP地址 管理节点 tong3 192.168.1.249 主节点 tong2 192.168.1.248 主节点 tong1 192.168.1.247 mysqldba技术群 378190849 武汉-linux运维群 236415619 1.网络和主机名配置 设置每个主机的ＩＰ地址和/etc/hosts文件互相解析 [root
环境规划:
节点说明 主机名 IP地址
管理节点 tong3 192.168.1.249
主节点 tong2 192.168.1.248
主节点 tong1 192.168.1.247
mysql dba技术群 378190849
武汉-linux运维群 236415619
1.网络和主机名配置
设置每个主机的ＩＰ地址和/etc/hosts文件互相解析
[root@tong1 ~]# cat /etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong1 ~]# ping tong1 -c1 --网络必须ping通
PING tong1 (192.168.1.247) 56(84) bytes of data.
64 bytes from localhost (192.168.1.247): icmp_seq=1 ttl=64 time=0.021 ms
--- tong1 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.021/0.021/0.021/0.000 ms
[root@tong1 ~]# ping tong2 -c1
PING tong2 (192.168.1.248) 56(84) bytes of data.
64 bytes from tong2 (192.168.1.248): icmp_seq=1 ttl=64 time=0.109 ms
--- tong2 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.109/0.109/0.109/0.000 ms
[root@tong1 ~]# ping tong3 -c1
PING tong3 (192.168.1.249) 56(84) bytes of data.
64 bytes from tong3 (192.168.1.249): icmp_seq=1 ttl=64 time=0.124 ms
--- tong3 ping statistics ---
1 packets transmitted, 1 received, 0% packet loss, time 0ms
rtt min/avg/max/mdev = 0.124/0.124/0.124/0.000 ms
[root@tong1 ~]#
2.安装mha管理软件mha manager
tong3管理节点:
[root@tong3 ~]# yum install perl-DBD-MySQL cpan --安装perl工具
[root@tong3 ~]# tar xvf mha4mysql-manager-0.53.tar.gz -C /usr/local/
[root@tong3 ~]# cd /usr/local/mha4mysql-manager-0.53/
[root@tong3 mha4mysql-manager-0.53]# perl Makefile.PL
[root@tong3 mha4mysql-manager-0.53]# echo $?
0
[root@tong3 mha4mysql-manager-0.53]# make && make install
[root@tong3 mha4mysql-manager-0.53]# echo $?
0
[root@tong3 mha4mysql-manager-0.53]#
3.在各数据节点安装mha node
[root@tong2 ~]# tar xvf mha4mysql-node-0.53.tar.gz
[root@tong2 ~]# cd mha4mysql-node-0.53
[root@tong2 mha4mysql-node-0.53]# yum install perl-DBD-mysql cpan -y
[root@tong2 mha4mysql-node-0.53]# perl Makefile.PL
[root@tong2 mha4mysql-node-0.53]# make && make install
[root@tong2 mha4mysql-node-0.53]# echo $?
0
[root@tong2 mha4mysql-node-0.53]#
4.各节点ssh互相信任
[root@tong3 ~]# ssh-keygen -t dsa
[root@tong3 ~]# cd .ssh
[root@tong3 .ssh]# cat id_dsa.pub > authorized_keys
[root@tong3 .ssh]# scp 192.168.1.247:/root/.ssh/id_dsa.pub 247
[root@tong3 .ssh]# scp 192.168.1.248:/root/.ssh/id_dsa.pub 248
[root@tong3 .ssh]# cat 248 247 >> authorized_keys
[root@tong3 .ssh]# scp authorized_keys 192.168.1.248:/root/.ssh/
authorized_keys 100% 1800 1.8KB/s 00:00
[root@tong3 .ssh]# scp authorized_keys 192.168.1.247:/root/.ssh/
root@192.168.1.247's password:
authorized_keys 100% 1800 1.8KB/s 00:00
[root@tong3 .ssh]# ssh tong1 date
Tue Apr 28 12:57:02 CST 2015
[root@tong3 .ssh]# ssh tong2 date
Tue Apr 28 12:59:57 CST 2015
[root@tong3 .ssh]# ssh tong3 date
Tue Apr 28 12:57:25 CST 2015
[root@tong3 .ssh]#
5.在管理节点编辑配置文件
[root@tong3 .ssh]# mkdir /etc/mysqlmha
[root@tong3 .ssh]# cd /etc/mysqlmha/
[root@tong3 mysqlmha]# cp -a /usr/local/mha4mysql-manager-0.53/samples/* .
[root@tong3 mysqlmha]# vim conf/app1.cnf
[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
user=root1 --远程登陆用户
password=system
ssh_user=root
repl_user=repl_user --复制用户
repl_password=system!#%246
ping_interval=1 --心跳检测
[server1]
hostname=192.168.1.249
master_binlog_dir="/usr/local/mysql-5.6.23/data/"
# candidate_master=1
no_master=1 --不能切换成主数据库
[server2]
hostname=192.168.1.248
master_binlog_dir="/usr/local/mysql-5.6.23/data/" --二进制日志文件存放
candidate_master=1 --可以切换成主数据库
[server4]
hostname=192.168.1.247
master_binlog_dir="/usr/local/mysql-5.6.23/data/"
candidate_master=1 -可以切换成主数据库
[root@tong3 ~]# masterha_check_ssh --conf=/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:39:21 2015 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Tue Apr 28 15:39:21 2015 - [info] Reading application default configurations from /etc/mysqlmha/conf/app1.cnf..
Tue Apr 28 15:39:21 2015 - [info] Reading server configurations from /etc/mysqlmha/conf/app1.cnf..
Tue Apr 28 15:39:21 2015 - [info] Starting SSH connection tests..
Tue Apr 28 15:39:22 2015 - [debug]
Tue Apr 28 15:39:21 2015 - [debug] Connecting via SSH from root@192.168.1.249(192.168.1.249:22) to root@192.168.1.248(192.168.1.248:22)..
Tue Apr 28 15:39:22 2015 - [debug] ok.
Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.249(192.168.1.249:22) to root@192.168.1.247(192.168.1.247:22)..
Tue Apr 28 15:39:22 2015 - [debug] ok.
Tue Apr 28 15:39:23 2015 - [debug]
Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.248(192.168.1.248:22) to root@192.168.1.249(192.168.1.249:22)..
Tue Apr 28 15:39:22 2015 - [debug] ok.
Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.248(192.168.1.248:22) to root@192.168.1.247(192.168.1.247:22)..
Tue Apr 28 15:39:23 2015 - [debug] ok.
Tue Apr 28 15:39:24 2015 - [debug]
Tue Apr 28 15:39:22 2015 - [debug] Connecting via SSH from root@192.168.1.247(192.168.1.247:22) to root@192.168.1.249(192.168.1.249:22)..
Tue Apr 28 15:39:23 2015 - [debug] ok.
Tue Apr 28 15:39:23 2015 - [debug] Connecting via SSH from root@192.168.1.247(192.168.1.247:22) to root@192.168.1.248(192.168.1.248:22)..
Tue Apr 28 15:39:23 2015 - [debug] ok.
Tue Apr 28 15:39:24 2015 - [info] All SSH connection tests passed successfully.
[root@tong3 ~]#
6.修改数据库配置文件和创建用户
在所有节点创建相同的用户:
mysql> grant all privileges on *.* to root1@'192.168.1.%' identified by 'system';
mysql> grant replication slave,replication client on *.* to repl_user@'192.168.1.%' identified by 'system!#%246';
tong1节点:
[root@tong1 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 20
socket = /tmp/mysql.sock
replicate-do-db=tong
replicate-ignore-db=mysql
log-bin=mysql-bin
log-bin-index=mysql-bin-index
auto_increment_offset=1
auto_increment_increment=2
relay_log_purge=0
read-only=1
[root@tong1 ~]#
tong2节点:
[root@tong2 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 10
socket = /tmp/mysql.sock
replicate-do-db=tong
replicate-ignore-db=mysql
log-bin=mysql-bin
log-bin-index=mysql-bin-index
auto_increment_offset=2
auto_increment_increment=2
read-only=1
relay_log_purge=0
[root@tong2 ~]#
tong3节点:
[root@tong3 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 30
socket = /tmp/mysql.sock
replicate-do-db=tong
replicate-ignore-db=mysql
[root@tong3 ~]#
7.将tong1和tong2搭建为主主模式
tong1主机:
[root@tong1 .ssh]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> change master to master_host='192.168.1.248',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000010',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.45 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql>
tong1主机:
[root@tong2 .ssh]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
change master to
master_host='192.168.1.247',master_port=3306,master_user='repl_user',master_password='system!#%246',master_log_file='mysql-bin.000010',master_log_pos=120;
Query OK, 0 rows affected, 2 warnings (0.45 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql>
8.检查复制是否有错
[root@tong3 ~]# masterha_check_repl --conf=/etc/mysqlmha/conf/app1.cnf
Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.249..
Tue Apr 28 15:53:23 2015 - [info] ok.
Tue Apr 28 15:53:23 2015 - [info] Checking replication health on 192.168.1.248..
Tue Apr 28 15:53:23 2015 - [info] ok.
Tue Apr 28 15:53:23 2015 - [warning] master_ip_failover_script is not defined.
Tue Apr 28 15:53:23 2015 - [warning] shutdown_script is not defined.
Tue Apr 28 15:53:23 2015 - [info] Got exit code 0 (Not master dead).
MySQL Replication Health is OK.
[root@tong3 mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[root@tong3 mysqlmha]# jobs
[1]+ Running nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[root@tong3 mysqlmha]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf
app1 (pid:24330) is running(0:PING_OK), master:192.168.1.247 --此时主节点在247服务器上
[root@tong3 mysqlmha]#
9.故障测试
tong1节点:
[root@tong1 ~]# /etc/init.d/mysqld stop --停掉tong1节点的数据库
Shutting down MySQL............ SUCCESS!
[root@tong2 ~]# /etc/init.d/mysqld start --节点必须启动才能做切换
Starting MySQL. SUCCESS!
[root@tong2 ~]#
查看tong2主机的日志状态,将tong1节点必须要同步到tong2节点上才可以切换(change master to master_host='192.168.1.248,master_ ....................)
tong3节点:
[root@tong3 mysqlmha]# rm -rf /var/log/masterha/app1/app1.failover.complete
[1]+ Done nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 --必须删除管理节点的监控文件
[root@tong3 mysqlmha]# nohup masterha_manager --conf=/etc/mysqlmha/conf/app1.cnf >> /tmp/mha_manager 2>&1 &
[root@tong3 ~]# masterha_check_status --conf=/etc/mysqlmha/conf/app1.cnf
app1 (pid:27870) is running(0:PING_OK), master:192.168.1.248
[root@tong3 ~]#
mysql modifier le nom de la table de données
MySQL crée une procédure stockée
La différence entre MongoDB et MySQL
Comment vérifier si le mot de passe MySQL est oublié
mysql créer une base de données
niveau d'isolement des transactions par défaut de MySQL
La différence entre sqlserver et mysql
mysqlmot de passe oublié
![[Web front-end] Démarrage rapide de Node.js](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
