MySQL双机热备+Heartbeat集群+自动故障转移(2秒)
环境说明:本环境由两台mysql 数据库和heartbeat 组成,一台的ip 为192.168.10.197,一台为192.168.10.198,对外提供服务的vip 为
环境说明:本环境由两台mysql 数据库和heartbeat 组成,一台的ip 为192.168.10.197,,一台为192.168.10.198,对外提供服务的vip 为192.168.10.200
备注:heartbeat 本身是不能做到服务不可用自动切换的,所以用结合额外的脚本才可以做到,本文中提到的moniter 脚本即为实现某个mysql 服务不可用的时候自动切换的还可以自动报警
安装和配置过程分为如下几步:
第一部分:mysql 的安装配置
1 安装
1.1. 添加mysql 运行所需的用户和组
groupadd mysql
useradd -g mysql mysql
1.2. 解压安装
tar mysql-5.1.26-rc-linux-x86_64-glibc23.tar.gz
mv mysql-5.1.26-rc-linux-x86_64-glibc23 /usr/local/mysql/
chown -R mysql:mysql /usr/local/mysql
1.3. 复制主配置文件和启动脚本
cd /usr/local/mysql
cp support-files/my-medium.cnf /etc/my.cf
cp support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
1.4. 初始化数据库
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql
1.5. 更改数据目录的所有者和组
2
chown -R mysql:mysql ./data
1.6. 注册mysql 为系统服务
chkconfig --add mysqld
chkconfig --levels 2345 mysqld on
1.7. 输出环境变量
#Vi /etc/profile(内容如下)
PATH=$PATH:/usr/local/mysql/bin
# source /etc/profile
1.8. 设置数据库密码
service mysqld start
mysqladmin password 123456(密码自己定这里只是个例子)
1.9. 开启root 远程访问权限
mysql –p123456
mysql> grant all on *.* to root@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> quit
2 参数设置
2.1. 创建相关目录并设置权限
mkdir /usr/local/mysql/binlog/
chown –R mysql:mysql /usr/local/mysql/binlog/
touch /var/log/mysql.log
chown –R mysql:mysql /var/log/mysql.log
2.2. 添加同步复制的账号(主库和辅库的设置相同)
[root@master ~]# mysql -p
Enter password:(输入root 密码)
mysql> grant all on *.* to qiangao identified by '123456';(其中的账号和密码要
和配置文件中指定的相同)
mysql> flush privileges;
2.3. 修改主配置文件
3
注意:其中ip 要互相指向对方的ip 按实际情况来设定,用户名和密码要和上文的用户名密
码保持一致
主库设置
vi /etc/my.cnf(用如下内容覆盖原有内容)
################################################
##########
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
################### auto_increment
###########################
auto_increment_offset = 1
auto_increment_increment = 2
############## other options ##############
default-character-set = utf8
default-storage-engine = InnoDB
default-table-type = INNODB
max_connections = 800
port = 3306
socket = /tmp/mysql.sock
skip-locking
########## MyISAM options #################
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_sort_buffer_size = 10M
myisam_repair_threads = 1
################ select cache options ##################
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 5M
4
max_allowed_packet = 1M
table_cache = 2048
query_cache_size = 32M
query_cache_limit = 2M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_concurrency = 8
################ index cache options ##################
key_buffer_size = 32M
key_buffer_size = 32M
################# master #########################
server-id = 1
log-bin=/usr/local/mysql/binlog/master-bin
binlog_format=mixed
relay-log=/usr/local/mysql/binlog/mysqld-relay-bin
##################### slave
##########################
relay-log=/usr/local/mysql/binlog/slave-relay-bin
master-host=192.168.10.198
master-user=qiangao
master-password=123456
master-connect-retry=10
############## log ##################
log-error=/var/log/mysql.log
######### INNODB #########
innodb_file_per_table
#+++++++ log ++++++++#
innodb_log_buffer_size = 10M
innodb_mirrored_log_groups = 1
innodb_log_files_in_group = 3
5
innodb_log_file_size = 50M
innodb_flush_log_at_trx_commit = 0
#innodb_log_archive = 0
#+++++++ System buffer +++++++#
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 40M
#+++++++ Other +++++++#
innodb_file_io_threads = 4
innodb_lock_wait_timeout = 5
innodb_force_recovery = 0
innodb_fast_shutdown = 1
innodb_thread_concurrency = 8
innodb_lock_wait_timeout = 50
transaction-isolation = READ-COMMITTED
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics











Scenario description for nodes to completely evacuate from ProxmoxVE and rejoin the cluster. When a node in the ProxmoxVE cluster is damaged and cannot be repaired quickly, the faulty node needs to be kicked out of the cluster cleanly and the residual information must be cleaned up. Otherwise, new nodes using the IP address used by the faulty node will not be able to join the cluster normally; similarly, after the faulty node that has separated from the cluster is repaired, although it has nothing to do with the cluster, it will not be able to access the web management of this single node. In the background, information about other nodes in the original ProxmoxVE cluster will appear, which is very annoying. Evict nodes from the cluster. If ProxmoxVE is a Ceph hyper-converged cluster, you need to log in to any node in the cluster (except the node you want to delete) on the host system Debian, and run the command

PHP database optimization method in high concurrency environment With the rapid development of the Internet, more and more websites and applications need to face high concurrency challenges. In this case, database performance optimization becomes particularly important, especially for systems that use PHP as the back-end development language. This article will introduce some database optimization methods in PHP high concurrency environment and give corresponding code examples. Using connection pooling In a high-concurrency environment, frequent creation and destruction of database connections may cause performance bottlenecks. Therefore, using connection pooling can

In today's cloud computing era, containerization technology has become one of the most popular technologies in the open source world. The emergence of Docker has made cloud computing more convenient and efficient, and has become an indispensable tool for developers and operation and maintenance personnel. The application of multi-node cluster technology is widely used based on Docker. Through multi-node cluster deployment, we can utilize resources more efficiently, improve reliability and scalability, and also be more flexible in deployment and management. Next, we will introduce how to use Docker to

Workerman is a high-performance PHPSocket framework that allows PHP to handle asynchronous network communication more efficiently. In Workerman's documentation, there are detailed instructions and code examples on how to implement a server cluster. In order to implement a server cluster, we first need to clarify the concept of a server cluster. A server cluster connects multiple servers to a network to improve system performance, reliability and scalability by sharing loads and resources. In Workerman, you can use the following two methods

Common clusters in PHP include LAMP cluster, Nginx cluster, Memcached cluster, Redis cluster and Hadoop cluster. Detailed introduction: 1. LAMP cluster. LAMP refers to a combination of Linux, Apache, MySQL and PHP. It is a common PHP development environment. In a LAMP cluster, multiple servers run the same application and are balanced through a load balancer. Requests are distributed to different servers; 2. Nginx cluster, Nginx is a high-performance web server and so on.

How to use MongoDB to implement data clustering and load balancing functions Introduction: In today's big data era, the rapid growth of data volume has put forward higher requirements for database performance. In order to meet these requirements, data clustering and load balancing have become indispensable technical means. As a mature NoSQL database, MongoDB provides rich functions and tools to support data clustering and load balancing. This article will introduce how to use MongoDB to implement data clustering and load balancing functions, and provide specific code

How to implement distributed containers and clusters in PHP microservices In the development of today's Internet applications and systems, microservice architecture has become a popular design pattern. In the microservice architecture, distributed containers and clusters are indispensable components. This article will introduce how to implement distributed containers and clusters in PHP microservices and provide specific code examples. 1. The concept and implementation of distributed containers Distributed containers refer to a way of deploying various components of an application on different servers and working together through network communication. exist

Linux, the full name of GNU/Linux, is a Unix-like operating system that can be used and spread freely. It is a multi-user, multi-task, multi-thread, and multi-CPU operating system based on POSIX. So what is a Linux server cluster system? What are its main components? The following is an introduction to the specific content. The Linux server cluster system is a distributed computing environment based on the Linux operating system. It is composed of multiple independent server nodes. These nodes are connected to each other through high-speed networks to collaboratively complete various computing tasks. The cluster system has high reliability, high performance and scalability, and can provide users with stable and powerful service support. Through the cluster system, servers can be effectively divided into
