MySQL主从集群构建及故障切换
MYSQL数据 库主 从配置
环境准备：192.168.227.138为master主服务器，192.168.227.139o slave从服务器
在主和从服务器都安装mysql相关软件，命令如下：
yum install -y mysql-server mysql mysql-devel
安装完毕后，在Master修改vi /etc/my.cnf内容如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=1
auto_increment_offset=1
auto_increment_increment=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
replicate-do-db=all
启动mysql即可，/etc/init.d/mysqld restart
然后修改slave mysql数据库my.cnf 配置文件内容如下：
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=2
auto_increment_offset=2
auto_increment_increment=2
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
master-host=192.168.227.138
master-user=root
master-pass=123456
master-port=3306
master-connect-retry=60
replicate-do-db=all
在slave 数据库服务器上设置权限，执行如下命令：
grant replication slave on *.* to 'root'@'%' identified by'123456';
在Master数据库执行如下命令：
mysql>show master status;
然后在slava 服务器指定master ip 和同步的pos点：
【MySQL主从集群构建及故障切换】
mysql> change master to
-> master_host='192.168.227.138',master_user='root',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=253;
在slave启动slave start 并执行show slave\G 查看mysql主从状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.227.138
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysq-bin.000002
Read_Master_Log_Pos: 106
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 250
Relay_Master_Log_File: mysq-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 106
Relay_Log_Space: 406
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
1 row in set (0.00 sec)
在master主服务器上创建一个数据库，slave从服务器上会同步。
当主服务断开连接，从服务切换，修改主服务器 vi config_global.php 文件
[root@localhost html]# cd config/
[root@localhost config]# ls
config_global_default.phpconfig_ucenter_default.phpindex.htm
config_global.phpconfig_ucenter.php
[root@localhost config]# vi config_global.php
文章图片
修改master主服务器/var/www/html/config/config_ucenter.php
文章图片
在slave从服务器上进行授权：
mysql> grant all on discuz.* to discuz@'192.168.227.138' identified by '123456';
推荐阅读
- Docker应用:容器间通信与Mariadb数据库主从复制
- federation--kubernetes集群联邦的实现
- py连接mysql
- 2019-01-18Mysql中主机名的问题
- MySql数据库备份与恢复
- mysql|InnoDB数据页结构
- mysql中视图事务索引与权限管理
- MYSQL主从同步的实现
- (1)redis集群原理及搭建与使用(1)
- MySQL数据库的基本操作